3

Is there any reason why there is no index on the entity_id column on the url_rewrite table? Or on entity_id and entity_type?
The only index I see is on entity_id and store_id.
I understand that that usually a select is done on entity_id AND store_id but still...
Side question... are there any drawbacks if I add this index myself?

For the record, this is what show create table url_rewrite shows:

CREATE TABLE `url_rewrite` (
  `url_rewrite_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Rewrite Id',
  `entity_type` varchar(32) NOT NULL COMMENT 'Entity type code',
  `entity_id` int(10) unsigned NOT NULL COMMENT 'Entity ID',
  `request_path` varchar(255) DEFAULT NULL COMMENT 'Request Path',
  `target_path` varchar(255) DEFAULT NULL COMMENT 'Target Path',
  `redirect_type` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Redirect Type',
  `store_id` smallint(5) unsigned NOT NULL COMMENT 'Store Id',
  `description` varchar(255) DEFAULT NULL COMMENT 'Description',
  `is_autogenerated` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Is rewrite generated automatically flag',
  `metadata` varchar(255) DEFAULT NULL COMMENT 'Meta data for url rewrite',
  PRIMARY KEY (`url_rewrite_id`),
  UNIQUE KEY `URL_REWRITE_REQUEST_PATH_STORE_ID` (`request_path`,`store_id`),
  KEY `URL_REWRITE_TARGET_PATH` (`target_path`),
  KEY `URL_REWRITE_STORE_ID_ENTITY_ID` (`store_id`,`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 COMMENT='Url Rewrites'
  • I don't think any drawback over on add of entity_id.Magento already add index at 2.3 – Amit Bera Feb 12 at 9:08
  • i have checked at also at 2.2.6. index key exists on entity_id.On which you donot see any index key? – Amit Bera Feb 12 at 9:09
  • @AmitBera I updated the question with how my url_rewrite table looks like. I checked on 2 instances of 2.2.6 that I have. Maybe my db is corrupted. – Marius Feb 12 at 9:15
  • In 2.3 there is also no index on entity_id. Just the composed index of store_id and entity_id. I don't think your db is corrupted :-) – HelgeB Feb 12 at 9:18
  • yes, both 2.2.6 and 2.3 has composed index for store_id and entity_id – Amit Bera Feb 12 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.