Simple MySQL query with performance issues

Question

I have the following simple MySQL query:

SELECT SQL_NO_CACHE mainID
FROM tableName 
WHERE otherID3=19
AND dateStartCol >= '2012-08-01' 
AND dateStartCol <= '2012-08-31';

When I run this it takes 0.29 seconds to bring back 36074 results. When I increase my date period to bring back more results (65703) it runs in 0.56. When I run other similar SQL queries on the same server but on different tables (some tables are larger) the results come back in approximately 0.01 seconds.

Although 0.29 isn't slow - this is a basic part for a complex query and this timing means that it is not scalable.

See below for the table definition and indexes.

I know it's not server load as I have the same issue on a development server which has very little usage.

+---------------------------+--------------+------+-----+---------+----------------+
| Field                     | Type         | Null | Key | Default | Extra          |
+---------------------------+--------------+------+-----+---------+----------------+
| mainID                    | int(11)      | NO   | PRI | NULL    | auto_increment |
| otherID1                  | int(11)      | NO   | MUL | NULL    |                |
| otherID2                  | int(11)      | NO   | MUL | NULL    |                |
| otherID3                  | int(11)      | NO   | MUL | NULL    |                |
| keyword                   | varchar(200) | NO   | MUL | NULL    |                |
| dateStartCol              | date         | NO   | MUL | NULL    |                |
| timeStartCol              | time         | NO   | MUL | NULL    |                |
| dateEndCol                | date         | NO   | MUL | NULL    |                |
| timeEndCol                | time         | NO   | MUL | NULL    |                |
| statusCode                | int(1)       | NO   | MUL | NULL    |                |
| uRL                       | text         | NO   |     | NULL    |                |
| hostname                  | varchar(200) | YES  | MUL | NULL    |                |
| IPAddress                 | varchar(25)  | YES  |     | NULL    |                |
| cookieVal                 | varchar(100) | NO   |     | NULL    |                |
| keywordVal                | varchar(60)  | NO   |     | NULL    |                |
| dateTimeCol               | datetime     | NO   | MUL | NULL    |                |
+---------------------------+--------------+------+-----+---------+----------------+


+--------------------+------------+-------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table              | Non_unique | Key_name                      | Seq_in_index | Column_name               | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------------+------------+-------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+
| tableName          |          0 | PRIMARY                       |            1 | mainID                    | A         |      661990 |     NULL | NULL   |      | BTREE      |         |
| tableName          |          1 | idx_otherID1                  |            1 | otherID1                   | A         |      330995 |     NULL | NULL   |      | BTREE      |         |
| tableName          |          1 | idx_otherID2                  |            1 | otherID2                   | A         |          25 |     NULL | NULL   |      | BTREE      |         |
| tableName          |          1 | idx_otherID3                  |            1 | otherID3                   | A         |          48 |     NULL | NULL   |      | BTREE      |         |
| tableName          |          1 | idx_dateStartCol              |            1 | dateStartCol               | A         |         187 |     NULL | NULL   |      | BTREE      |         |
| tableName          |          1 | idx_timeStartCol              |            1 | timeStartCol               | A         |       73554 |     NULL | NULL   |      | BTREE      |         |
|tableName          |          1 | idx_dateEndCol                 |            1 | dateEndCol                 | A         |         188 |     NULL | NULL   |      | BTREE      |         |
|tableName          |          1 | idx_timeEndCol                 |            1 | timeEndCol                 | A         |       73554 |     NULL | NULL   |      | BTREE      |         |
| tableName          |          1 | idx_keyword                   |            1 | keyword                    | A         |       82748 |     NULL | NULL   |      | BTREE      |         |
| tableName           |          1 | idx_hostname                 |            1 | hostname                   | A         |        2955 |     NULL | NULL   | YES  | BTREE      |         |
| tableName           |          1 | idx_dateTimeCol              |            1 | dateTimeCol                | A         |      220663 |     NULL | NULL   |      | BTREE      |         |
| tableName           |          1 | idx_statusCode               |            1 | statusCode                 | A         |           2 |     NULL | NULL   |      | BTREE      |         |
+--------------------+------------+-------------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+

Explain Output:

+----+-------------+-----------+-------+----------------------------------+-------------------+---------+------+-------+----------+-------------+
| id | select_type | table     | type  | possible_keys                    | key               | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-----------+-------+----------------------------------+-------------------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | tableName | range | idx_otherID3,idx_dateStartCol | idx_dateStartCol | 3       | NULL | 66875 |    75.00 | Using where |
+----+-------------+-----------+-------+----------------------------------+-------------------+---------+------+-------+----------+-------------+

Answer 1

If this is a recurrent or important query then create a multiple column index:

CREATE INDEX index_name ON tableName (otherID3, dateStartCol)

Delete the non used indexes as they make table changes more expensive.

BTW you don't need two separate columns for date and time. You can combine then in a datetime or timestamp type. One less column and one less index.

The explain output shows it chose the dateStartCol index so you could try the opposite I suggested above:

CREATE INDEX index_name ON tableName (dateStartCol, otherID3)

Notice that the query's dateStartCol condition will still get 75% of the rows so not much improvement, if any, in using that single index.

How unique is otherID3? If there are not many repeated otherID3 you can hint the engine to use it.

Answer 2

If that is really your query (and not a simplified version of same), then this ought to achieve best results:

 CREATE INDEX table_ndx on tableName( otherID3, dateStartCol, mainID);

The first index entry means that the first match in the WHERE is very fast; the same also applies with dateStartCol. The third field is very small and does not slow the index appreciably, but allows for the datum you require to be found immediately in the index with no table access at all.

It is important that the keys are in the same index. In the EXPLAIN you posted, each key is in an index of its own, so even if MySQL chooses the best index, the performances will not be optimal. I'd try and use less indexes, for they also have a cost (shameless plug: Can Indices actually decrease SELECT performance? ).

Answer 3

First try to add the right key. It seems like dateStartCol is more selective than otherID3

ALTER TABLE tableName ADD KEY idx_dates(dateStartCol, dateStartCol)

Second - please make sure you select only rows you need by adding LIMIT clause to the SELECT. This will should up the query. Try like this:

SELECT SQL_NO_CACHE mainID FROM tableName WHERE otherID3=19 AND dateStartCol >= '2012-08-01' AND dateStartCol <= '2012-08-31' LIMIT 10;

Please also make sure that your MySQL tuned up properly. You may want to check key_buffer_size and innodb_buffer_pool_size as described in http://astellar.com/2011/12/why-is-stock-mysql-slow/