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
0

I went ahead and added my index on entity_id. It made a significant difference for my custom import that relied only on entity_id lookups and found no other downsides in the last 3 months.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.