Details
Major
Description
From jkavalik on #maria:
index_merge/intersection plan is generated even when one index covers all columns:
explain SELECT sql_no_cache SUM(win_real - castka) AS realWinNetto, SUM(castka) AS stake FROM ticket WHERE user_id=286 AND vyplacen=1 GROUP BY user_id; +------+-------------+--------+-------------+-----------------------------------+--------------------------+---------+------+-------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------+-------------+-----------------------------------+--------------------------+---------+------+-------+--------------------------------------------------------+ | 1 | SIMPLE | ticket | index_merge | FK_ticket_1,vyplacen,realWinNetto | FK_ticket_1,realWinNetto | 4,6 | NULL | 44496 | Using intersect(FK_ticket_1,realWinNetto); Using where | +------+-------------+--------+-------------+-----------------------------------+--------------------------+---------+------+-------+--------------------------------------------------------+
show index from ticket; (15 non-relevant indexes removed) +--------+------------+---------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+---------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | ticket | 0 | PRIMARY | 1 | ticket_id | A | 2208874 | NULL | NULL | | BTREE | | | | ticket | 1 | FK_ticket_1 | 1 | user_id | A | 26937 | NULL | NULL | | BTREE | | | | ticket | 1 | vyplacen | 1 | vyplacen | A | 16 | NULL | NULL | | BTREE | | | | ticket | 1 | realWinNetto | 1 | user_id | A | 8398 | NULL | NULL | | BTREE | | | | ticket | 1 | realWinNetto | 2 | vyplacen | A | 10774 | NULL | NULL | | BTREE | | | +--------+------------+---------------------------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
explain SELECT sql_no_cache SUM(win_real - castka) AS realWinNetto, SUM(castka) AS stake FROM ticket FORCE INDEX(realWinNetto) WHERE user_id=286 AND vyplacen=1; +------+-------------+--------+------+---------------+--------------+---------+-------------+-------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+--------+------+---------------+--------------+---------+-------------+-------+-------+ | 1 | SIMPLE | ticket | ref | realWinNetto | realWinNetto | 6 | const,const | 52762 | | +------+-------------+--------+------+---------------+--------------+---------+-------------+-------+-------+
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Hide
Sergei Petrunia
added a comment -
Permalink
Show
Sergei Petrunia
added a comment -
explain select count(*) from ticket where user_id=286;
+------+-------------+--------+------+--------------------------+-------------+---------+-------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+--------------------------+-------------+---------+-------+-------+-------------+
| 1 | SIMPLE | ticket | ref | FK_ticket_1,realWinNetto | FK_ticket_1 | 4 | const | 47566 | Using index |
+------+-------------+--------+------+--------------------------+-------------+---------+-------+-------+-------------+
1 row in set (0.00 sec)
explain select count(*) from ticket where vyplacen=1;
+------+-------------+--------+------+---------------+----------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+---------------+----------+---------+-------+---------+-------------+
| 1 | SIMPLE | ticket | ref | vyplacen | vyplacen | 2 | const | 1023228 | Using index |
+------+-------------+--------+------+---------------+----------+---------+-------+---------+-------------+
describe ticket; (51 rows, decimal, int, datetime...)
+----------------------------+------------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+------------------------+------+-----+---------------------+-------+
| user_id | int(10) unsigned | NO | MUL | 0 | |
| vyplacen | smallint(5) unsigned | NO | MUL | 0 | |
+----------------------------+------------------------+------+-----+---------------------+-------+