Quote
Please show your real query, not a bogus one
OK, it can't get any reallier than this:
There are exactly 1 million rows in this static example table, of which exactly 612 match uid=206.
mysql> show index from logs;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| logs | 1 | uid | 1 | uid | A | 4878 | NULL | NULL | | BTREE | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
key was created by KEY `uid` (`uid`)
Now I'd like this query to forgo using a temporary table:
mysql> EXPLAIN SELECT SUM(uid) FROM logs WHERE uid='206' GROUP BY uid ORDER BY NULL;
+----+-------------+----------+------+---------------+------+---------+-------+------+-------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+-------+------+-------------------------------------------+
| 1 | SIMPLE | logs | ref | uid | uid | 4 | const | 612 | Using where; Using index; Using temporary |
+----+-------------+----------+------+---------------+------+---------+-------+------+-------------------------------------------+
1 row in set (0.00 sec)
Simple as that. I'd be grateful if someone could shed some light of why this doesn't work despite the docs meantioning it should be fetching the data directly from the index;