Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
    
How many rows does the query return from the 600k? –  ypercube May 8 '13 at 12:04
    
This particular query returns 16 rows. It took 9.24 seconds the first time I ran it. –  Nifle May 8 '13 at 12:11
add comment

1 Answer

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;
share|improve this answer
    
Tried this and unfortunately the explain still shows Using where. Is there a way to encourage the optimizer to us Index-Merge? –  Nifle May 8 '13 at 11:52
    
You may encourage it but it may not be faster! –  ypercube May 8 '13 at 11:53
    
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? –  ypercube May 8 '13 at 11:53
    
Removed the ORED BY an ran explain again. See edit –  Nifle May 8 '13 at 12:09
    
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 –  Nifle May 8 '13 at 12:15
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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