MySQL index causes queries to become slow

Question

I have a MySQL table with some 20 million rows of data in it.

+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| b_id        | int(11)     | YES  | MUL | NULL    |                |
| order       | bigint(20)  | YES  | MUL | NULL    |                |
| date        | date        | YES  |     | NULL    |                |
| time        | time        | YES  |     | NULL    |                |
| channel     | varchar(8)  | YES  | MUL | NULL    |                |
| data        | varchar(60) | YES  |     | NULL    |                |
| date_system | date        | YES  | MUL | NULL    |                |
| time_system | time        | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

I had an non unique index on (b_id, channel, date) to speed up queries like:

select date, left(time,2) as hour, round(data,1) as data
from data_lines
where channel='1'
  and b_id='300'
  and date >='2013-04-19'
  and date <='2013-04-26' 
group by date,hour

The problem was that my inserts sometimes overlap, so I wanted to use 'ON DUPLICATE KEY UPDATE', however this needs a unique index. So I create a unique index on (b_id, channel, date, time) as these are the four main characteristics to determine if there is a double value. The inserts now work fine, however my select queries are unacceptable slow.

I'm not quite sure why my selects have become slower since the addition of the new index:

• is time so unique that the index becomes very large --> and slow?
• should I remove the non unique index to speed things up?
• is it my bad querying?
• other ideas welcome!

For the record (order, date_system and time_system) are not used at all in indexes or selects, but do contain data. The inserts are run from C and Python and the selects from PHP.

Per request the explain query:

mysql> explain select date, left(time,2) as hour, round(data,1) as data 
from data_lines 
where channel='1'
  and b_id='300'
  and date >='2013-04-19'
  and date <='2013-04-26'
group by date,hour;

+----+-------------+-----------+------+--------------------------------+------------+---------+-------------+------+----------------------------------------------+
| id | select_type | table     | type | possible_keys                  | key        | key_len | ref         | rows | Extra                                        |
+----+-------------+-----------+------+--------------------------------+------------+---------+-------------+------+----------------------------------------------+
|  1 | SIMPLE      | data_lines| ref  | update_index,b_id,comp_index   | comp_index | 16      | const,const | 3548 | Using where; Using temporary; Using filesort |
+----+-------------+-----------+------+--------------------------------+------------+---------+-------------+------+----------------------------------------------+

The update_index is my unique index of (b_id, channel, date, time) and the comp_index is my non unique index of (b_id, channel, date).

Indexes are:

+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table     | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| data_lines|          0 | PRIMARY      |            1 | id          | A         |    17918898 |     NULL | NULL   |      | BTREE      |         |               |
| data_lines|          0 | id_UNIQUE    |            1 | id          | A         |    17918898 |     NULL | NULL   |      | BTREE      |         |               |
| data_lines|          0 | update_index |            1 | channel     | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          0 | update_index |            2 | b_id        | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          0 | update_index |            3 | date        | A         |       44244 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          0 | update_index |            4 | time        | A         |    17918898 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          1 | box_id       |            1 | b_id        | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          1 | idx          |            1 | order       | A         |    17918898 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          1 | comp_index   |            1 | b_id        | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          1 | comp_index   |            2 | channel     | A         |        6624 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          1 | comp_index   |            3 | date        | A         |      165915 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          1 | date_system  |            1 | date_system | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
| data_lines|          1 | mac          |            1 | mac         | A         |          17 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Answer 1

• Since a PRIMARY KEY is a UNIQUE KEY, get rid of the useless UNIQUE(id).
• Are any of the columns we are talking about ever NULL? If not, make them NOT NULL. (This is important before upgrading the UNIQUE index.)
• Unless you need it for some other query, DROP comp_index. It provides no extra benefit (toward your INSERT or SELECT) over the 4-column unique_index.
• Do you use id anywhere else? If not, promote the 4-col unique index to be PRIMARY KEY. This step is likely to speed things up because now it is not bouncing back and forth between the index and the data (to get data).
• That leaves 4 other indexes; see if you really need them. (I suggest this because a previous step will make secondary indexes bulkier.)
• Change to InnoDB if you are using MyISAM.

When doing lots of ALTERs, do them in a single statement -- it will be a lot faster.

ALTER TABLE ...
    DROP COLUMN id,
    DROP PRIMARY KEY,
    DROP INDEX `id_UNIQUE`,
    DROP INDEX comp_index,
    ADD PRIMARY KEY(channel, b_id, date, time),
    ALTER COLUMN ... NOT NULL,
    ...
    ENGINE=InnoDB;

Or, to be more cautious: CREATE the modified table, then INSERT...SELECT to populate it. Then test. Eventually do RENAME TABLE to put it into place.

It is usually a bad idea to split date and time into two columns instead of having a single datetime. But I won't push it, since it probably does not affect this Question much.

Answer 2

Try explicitly specifying USE INDEX(update_index) in your query.

the optimizer is making wrong choice in selecting in selecting the index because of which the query is becoming slow.

Hope this solves your problem.. :)