Covering index decision in MySQL

Question

My MySQL slow query log showed a query that looks rather simple as one of the slowest queries run on my server:

SELECT result_known,AVG(points_total) as points
FROM tbl_points
WHERE uid IN (N,{1023 repeats}N)
GROUP BY gid
ORDER BY gid ASC;

I'm basically trying to find the average points total for a subgroup (set of uids, e.g. based on gender or whatever) of a group. There are individual indexes on uid and gid, but EXPLAIN shows that they're not being used:

| id | select_type | table               | type | possible_keys | key  | key_len | ref  | rows | Extra
|  1 | SIMPLE      | tbl_points          | ALL  | combined      | NULL | NULL    | NULL |   64 | Using where; Using temporary; Using filesort

Now, the obvious solution, as I understand it, is to have a covering index over these fields:

CREATE INDEX index1 ON dbo.tbl_points(result_known, points_total, uid, gid)

And indeed, this makes it use the index:

| id | select_type | table               | type  | possible_keys | key    | key_len | ref  | rows | Extra                                                     |
|  1 | SIMPLE      | tbl_points          | index | combined      | index3 | 18      | NULL |   64 | Using where; Using index; Using temporary; Using filesort |

However, I have two questions:

• In the Extra field, EXPLAIN now says "Using where; Using index; Using temporary; Using filesort". This is bad, right? So should I use this index or not? In dummy terms, is it more important to have type=index and key=something than what happens in the "extra" field, or not?

• What are the effects of having a covering index on large inserts? I am inserting points in this same table by doing a JOIN-UPDATE with a quite large temporary table. I wouldn't want to slow this down too much.

Answer 1

In general, you can use an index to optimize the range predicate of IN(...), or you can use an index to optimize out the temp table caused by the GROUP BY (though you make a good point that this may not work with AVG()). But you can't achieve both uses of indexes in the same SELECT.

I'm going to conclude that you can't get rid of the temp table in this particular query. The best you can do is try to keep it from going to disk by increasing tmp_table_size. Or if it does go to disk, configure a tmpfs filesystem and use that mount point as your tmpdir.

So you have to choose, do you want to search an index for your list of uid values? You have a really long list of uid's, so that's going to be costly just to estimate the row count. Be sure to upgrade to MySQL 5.6, which has some new optimizations in this area (see Equality Range Optimization of Many-Valued Comparisons).

The type=index means it's doing an index scan, which is costly but at least it's getting results from the index alone instead of having to read table rows. So it needs fewer buffer pool pages to satisfy this query.