есть такая таблица:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
mysql> show fields from boar_mxids;
+---------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+----------------+
| tid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| id | int(9) unsigned | NO | MUL | NULL | |
| mid | int(10) unsigned | NO | MUL | NULL | |
| pref | int(4) unsigned | NO | | NULL | |
| mid_ind | int(9) unsigned | NO | | 0 | |
+---------+------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> show index from boar_mxids;
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| boar_mxids | 0 | PRIMARY | 1 | tid | A | 132817574 | NULL | NULL | | BTREE | | |
| boar_mxids | 0 | index_mid_id | 1 | mid | A | 66408787 | NULL | NULL | | BTREE | | |
| boar_mxids | 0 | index_mid_id | 2 | id | A | 132817574 | NULL | NULL | | BTREE | | |
| boar_mxids | 1 | index_id | 1 | id | A | 132817574 | NULL | NULL | | BTREE | | |
| boar_mxids | 1 | index_mid_mid_ind | 1 | mid | A | 132817574 | NULL | NULL | | BTREE | | |
| boar_mxids | 1 | index_mid_mid_ind | 2 | mid_ind | A | 132817574 | NULL | NULL | | BTREE | | |
+------------+------------+-------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.32 sec)
Выполняю 2 запроса и вижу дикую разницу по времени:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
mysql> SELECT max(mid_ind) as mid_ind FROM `boar_mxids` `t` WHERE mid=5;
+----------+
| mid_ind |
+----------+
| 24974726 |
+----------+
1 row in set (0.01 sec)
mysql> SELECT max(mid_ind),mid as mid_ind FROM `boar_mxids` `t` WHERE mid=5;
+--------------+---------+
| max(mid_ind) | mid_ind |
+--------------+---------+
| 24974726 | 5 |
+--------------+---------+
1 row in set (18.28 sec)
теперь смотрим explain:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
mysql> explain SELECT max(mid_ind) as mid_ind FROM `boar_mxids` `t` WHERE mid=5;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.03 sec)
mysql> explain SELECT max(mid_ind),mid as mid_ind FROM `boar_mxids` `t` WHERE mid=5;
+----+-------------+-------+------+--------------------------------+-------------------+---------+-------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------------------+-------------------+---------+-------+----------+-------------+
| 1 | SIMPLE | t | ref | index_mid_id,index_mid_mid_ind | index_mid_mid_ind | 4 | const | 47917900 | Using index |
+----+-------------+-------+------+--------------------------------+-------------------+---------+-------+----------+-------------+
1 row in set (0.00 sec)
подскажите, откуда такая разница? как это можно оптимизировать?
почему же select max(),id выполняется в тысячи раз медленнее, чем select max() ?
