Databases
StackOverflow2013
Postsdbo
PONot efficient execution plan taken by MySQL InnoDB
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PONot efficient execution plan taken by MySQL InnoDB
    primarykey
    data
    text
    <p>I have trouble to optimize a request with the MySQL InnoDB optimizer. The following query (query 1) runs efficiently:</p> <pre><code>explain select * from ah_problems where rnid in (6022342, 6256614, 5842714, 6302489) and fieldid in (5,6); </code></pre> <p>and the plan (plan 1) is as follows:</p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra = ====== =========== ===== =============================== ============= ======= === ==== ===== 1 SIMPLE ah_problems range CONSTRAINTFIELDID,RNID__FIELDID RNID__FIELDID 8 33 Using where </code></pre> <p>So far, so good.</p> <p>Whereas the slightly modified query (query 2) below will take a catastrophic execution plan:</p> <pre><code>explain select * from ah_problems where rnid in (select rec.rnid as record_id from ar_records rec where rnid in (6022342, 6256614, 5842714, 6302489)) and fieldid in (5, 6) </code></pre> <p>The result is the same, but the plan (plan 2) is now doing this:</p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra = ====== =========== ===== ================== ======== ======= ==== ======= ===== 1 PRIMARY ah_problems ALL CONSTRAINTFIELDID 36177754 Using where 2 DEPENDENT SUBQUERY rec unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where </code></pre> <p>If you wonder, that new sub-query...</p> <pre><code>select rec.rnid as record_id from ar_records rec where rnid in (6022342, 6256614, 5842714, 6302489) </code></pre> <p>...does nothing more than returning the four rows that were hard-coded in query 1:</p> <pre><code>6022342 6256614 5842714 6302489 </code></pre> <p>so queries (1) and (2) are equivalent.</p> <p>Guess what, I need query 2, and not one. And I want query 2 to be as efficient as query 1. I tried the following:</p> <ol> <li><p>Query 3: Add <code>FORCE INDEX(RNID_FIELDID)</code> to query 2. MySQL simply ignores it.</p> <p>explain select * from ah_problems force index (rnid__fieldid) where rnid in (select rec.rnid as record_id from ar_records rec where rnid in (6022342, 6256614, 5842714, 6302489)) and fieldid in (5,6)</p></li> </ol> <p>The execution plan is the same as plan 2.</p> <ol> <li><p>Query 4: Add an <code>ORDER BY RNID, FIELDID</code> to query 3. I saw on some other questions that this might trick the optimizer. It doesn't help.</p> <p>explain select * from ah_problems force index (rnid__fieldid) where rnid in (select rec.rnid as record_id from ar_records rec where rnid in (6022342, 6256614, 5842714, 6302489)) and fieldid in (5, 6) order by rnid, fieldid</p></li> </ol> <p>The plan 4 is now using the index, but the row count is still catastrophic:</p> <pre><code>id select_type table type possible_keys key key_len ref rows Extra = ====== =========== ===== ================== ======== ======= ==== ======= ===== 1 PRIMARY ah_problems index RNID__FIELDID 8 36179307 Using where 2 DEPENDENT SUBQUERY rec unique_subquery PRIMARY PRIMARY 4 func 1 Using index; Using where </code></pre> <p>If this helps, this is the definition of my <code>ah_problems</code> tables. I'm unfortunately not able to change the definition of the table. Is there anything I can do to make MySQL optimizer use plan 1 to attack table <code>ah_problems</code> in query 2?</p> <pre><code>CREATE TABLE `ah_problems` ( `ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Identifier for update statements', `RNID` int(11) NOT NULL COMMENT 'Record number', `FIELDID` int(11) NOT NULL COMMENT 'Which field is value in', `VALUE` varchar(255) NOT NULL COMMENT 'The value the field got on MODIFIED_DATE', `PREVIOUSID` int(11) DEFAULT NULL COMMENT 'Reference to previous value', `MODIFIED_DATE` datetime NOT NULL COMMENT 'When was it changed', `MODIFIED_GROUPID` int(11) DEFAULT NULL COMMENT 'In what group did modified_userid change it', `MODIFIED_USERID` int(11) NOT NULL COMMENT 'Who changed it', PRIMARY KEY (`ID`), KEY `CONSTRAINTFIELDID` (`FIELDID`), KEY `CONSTRAINTMODIFIED_GROUPID` (`MODIFIED_GROUPID`), KEY `CONSTRAINTMODIFIED_USERID` (`MODIFIED_USERID`), KEY `CONSTRAINTPREVIOUSID` (`PREVIOUSID`), KEY `RNID__FIELDID` (`RNID`,`FIELDID`), CONSTRAINT `HPRB_FIELD` FOREIGN KEY (`FIELDID`) REFERENCES `ad_fields` (`ID`), CONSTRAINT `HPRB_MODIFIED_GROUP` FOREIGN KEY (`MODIFIED_GROUPID`) REFERENCES `ap_groups` (`ID`), CONSTRAINT `HPRB_MODIFIED_USER` FOREIGN KEY (`MODIFIED_USERID`) REFERENCES `ap_users` (`ID`), CONSTRAINT `HPRB_PREVIOUS` FOREIGN KEY (`PREVIOUSID`) REFERENCES `ah_problems` (`ID`) ON DELETE CASCADE, CONSTRAINT `HPRB_RN` FOREIGN KEY (`RNID`) REFERENCES `ar_records` (`RNID`) ) ENGINE=InnoDB AUTO_INCREMENT=72305308 DEFAULT CHARSET=utf8 COMMENT='PTR history'$$ </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USGerard Yin
    1. USGerard Yin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PONot efficient execution plan taken by MySQL InnoDB
      1. This table or related slice is empty.
      1. VTUpMod
    1. COIf query 1 and 2 are equivalent, then you must not be using 2 as you wrote it if query 1 doesn't work. Is there some issue you have that prevents 1 from working? Any info may help give better answers.
      singulars
      1. PONot efficient execution plan taken by MySQL InnoDB
      1. USdlp
    2. COQuery 1 works. Query 2 works, but is slow. In fact, both queries are attempts to isolate the issue with the optimizer. My actual query is in fact more complex (the added sub-query fetches dynamically the list of records to get).
      singulars
      1. PONot efficient execution plan taken by MySQL InnoDB
      1. USGerard Yin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload