How can I get this query to use it's index?

Question

Queries matching this query below shows up in the slow log (with different contentVersionId & modifiedDateTime).

As I don't really know what I'm doing I tried adding indexes to all columns in the WHERE clause (on at a time) but it the explain still showed Extra: Using where not Extra: Using index as I was expecting.

Am I wrong in assuming Extra: Using where is bad? If I'm not wrong how should I proceed?

_{Table is innodb, primarily read from and contains 600000+ rows}

The Query:

SELECT cmContentVersion.contentVersionId,
       cmContentVersion.stateId,
       cmContentVersion.modifiedDateTime,
       cmContentVersion.versionComment,
       cmContentVersion.isCheckedOut,
       cmContentVersion.isActive,
       cmContentVersion.contentId,
       cmContentVersion.languageId,
       cmContentVersion.versionModifier 
FROM   cmContentVersion 
WHERE  cmContentVersion.languageId = 3 AND 
       cmContentVersion.isActive   = 1 AND 
        (cmContentVersion.contentVersionId > 1207494 OR 
         cmContentVersion.modifiedDateTime > '2013-05-05 23:00:00.0' )
ORDER BY cmContentVersion.contentVersionId;

Explain extended gives:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cmContentVersion
         type: ref
possible_keys: PRIMARY,ixlanguageId,ixisActive,IX_DateTime
          key: ixisActive
      key_len: 1
          ref: const
         rows: 318270
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

And here are the indexes on this table:

+------------------+------------+--------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------
| Table            | Non_unique | Key_name     | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment
|------------------+------------+--------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| cmContentVersion |          0 | PRIMARY      |            1 | contentVersionId | A         |      636541 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | contentId    |            1 | contentId        | A         |      127308 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | ixlanguageId |            1 | languageId       | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | ixstateId    |            1 | stateId          | A         |           6 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | ixisActive   |            1 | isActive         | A         |           6 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | compositeIDX |            1 | contentId        | A         |       63654 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | compositeIDX |            2 | languageId       | A         |       79567 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | compositeIDX |            3 | stateId          | A         |      159135 |     NULL | NULL   |      | BTREE      |         |
| cmContentVersion |          1 | compositeIDX |            4 | isActive         | A         |      212180 |     NULL | NULL   |      | BTREE      |         |
+------------------+------------+--------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+

EDIT:

new explain with the indexes suggested here

           id: 1
  select_type: SIMPLE
        table: cmContentVersion
         type: ref
possible_keys: PRIMARY,ixlanguageId,ixisActive,IX_foo,active_lang_contentversion_IX,active_lang_modified_IX
          key: ixlanguageId
      key_len: 4
          ref: const
         rows: 356055
     filtered: 100.00
        Extra: Using where

This particular query returns 16 rows. It took 9.24 seconds the first time I ran it.

ypercube · Answer 1 · 2013-05-08 12:44:55Z

Conditions with OR are harder for the optimizer than conditions with AND only.

Two or more range conditions (>, >=, <, <=, BETWEEN, LIKE 'search%') are harder than conditions with equality only or with only one range.

Your query has both the above difficulties. Noticing that it is equivalent to this rewriting:

WHERE ( languageId = 3 AND 
        isActive   = 1 AND 
        contentVersionId > 1207494
      )
   OR ( languageId = 3 AND 
        isActive   = 1 AND 
        modifiedDateTime > '2013-05-05 23:00:00.0'
      )

your best option is to have an index that can be used for the first part (equalities and one of the 2 range conditions) and another index for the second part (equalities and the other range):

ALTER TABLE cmContentVersion 
  ADD INDEX active_lang_contentversion_IX
    (isActive, languageId, contentVersionId),
  ADD INDEX active_lang_modified_IX
    (isActive, languageId, modifiedDateTime) ;

The optimizer might then choose to use both indexes and combine them with an Index-Merge access algorithm (the Sort-Union variation).

You can "force" the index-merge to be used by rewriting the query with derived tables.

SELECT cv.contentVersionId,
       cv.stateId,
       cv.modifiedDateTime,
       cv.versionComment,
       cv.isCheckedOut,
       cv.isActive,
       cv.contentId,
       cv.languageId,
       cv.versionModifier 
FROM   cmContentVersion AS cv
  JOIN
    (   SELECT contentVersionId
        FROM cmContentVersion 
        WHERE languageId = 3 AND 
              isActive   = 1 AND 
              contentVersionId > 1207494
      UNION
        SELECT contentVersionId
        FROM cmContentVersion 
        WHERE languageId = 3 AND 
              isActive   = 1 AND 
              modifiedDateTime > '2013-05-05 23:00:00.0'
    ) AS cvi   
      ON cvi.contentVersionId = cv.contentVersionId
ORDER BY cv.contentVersionId;

However, and since you have the ORDER BY, a combined index might be better for efficiency. Try adding this index:

(isActive, languageId, contentVersionId, modifiedDateTime)

and running the query as:

SELECT cv.contentVersionId,
       ...
FROM   cmContentVersion AS cv
WHERE ( languageId = 3 AND 
        isActive   = 1 AND 
        contentVersionId <= 1207494 AND
        modifiedDateTime > '2013-05-05 23:00:00.0'
      )
   OR ( languageId = 3 AND 
        isActive   = 1 AND 
        contentVersionId > 1207494
      )
ORDER BY cv.contentVersionId;

and as:

SELECT cv.contentVersionId,
       ...
FROM   cmContentVersion AS cv
WHERE   languageId = 3 AND 
        isActive   = 1 AND 
        contentVersionId <= 1207494 AND 
        modifiedDateTime > '2013-05-05 23:00:00.0'
UNION ALL      
SELECT cv.contentVersionId,
       ...
FROM   cmContentVersion AS cv
WHERE   languageId = 3 AND 
        isActive   = 1 AND 
        contentVersionId > 1207494
ORDER BY contentVersionId;

Tried this and unfortunately the explain still shows Using where. Is there a way to encourage the optimizer to us Index-Merge?
Can you try without the ORDER BY? Can you also add in the question the new EXPLAIN EXTENDED output, after the addition of the indexes?
sigh even if I remove the OR ie removed this part: ` languageId = 3 AND isActive = 1 AND modifiedDateTime > '2013-05-05 23:00:00.0' ` I still get Using where

asked	29 days ago
viewed	58 times
active	29 days ago

How can I get this query to use it's index?

EDIT:

1 Answer

Your Answer

Not the answer you're looking for? Browse other questions tagged mysql performance index innodb mysql-5.1 or ask your own question.

How can I get this query to use it's index?

EDIT:

1 Answer

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged mysql performance index innodb mysql-5.1 or ask your own question.

Related