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
Best Answer
Conditions with
OR
are harder for the optimizer than conditions withAND
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:
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):
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.
However, and since you have the
ORDER BY
, a combined index might be better for efficiency. Try adding this index:and running the query as:
and as: