Mysql Explain Query with type "ALL" when an index is used
explain, indexing, mysql, query-optimization, sql
Solution
Your subquery:
SELECT * # Select Number 2
FROM post
WHERE parentid = 13
ORDER BY time, id
LIMIT 1, 10;
This mentions three columns explicitly, plus all the rest of the columns You have three indexes. Here is how they can be used:
- id (PRIMARY) -- This index is useless. Although mentioned in the `order by` clause, it is the second condition
- parentid -- This index can be used for satisfying the `where` clause. However, after the correct data is pulled, it then would need to be sorted explicitly.
- time, id (timeid) -- This index can be used for the sort, with a big BUT. MySQL can scan the index to get everything in the right order. But it will have to check, row-by-row, whether the condition on `parentid` is met.
Just to introduce why optimization is hard. If you have a small amount of data (say the table fits on one or two pages), then a full table scan followed by a sort is probably fine. If most of the `parentid` values are `13`, then the second index could be a worst case. If the table does not fit into memory, then the third would be incredibly slow (something called page thrashing).
The correct index for this subquery is one that satisfies the `where` clause and allows ordering. That index is `parentid, time, id`. This is not a covering index (unless these are all the columns in the table). But it should reduce the number of hits to actual rows to 10 because of the `limit` clause.
Note that for the complete query, you want an index on `parentid`. And, happily, an index on `parentid, time, id` counts as such an index. So, you can remove that index. The `time, id` index is probably not necessary, unless you need that for other queries.
Your query is also filtering only those "children" that have "children" themselves. It is quite possible that no rows will be returned. Do you really intend a `left outer join`?
As a final comment. I assume that this query is a simplification of your real query. The query is pulling all columns from two tables -- and those two tables are the same. That is, you will be getting duplicate column names from identical tables. You should have column aliases to better define the columns.
Problem
I ran a query in Mysql like below: ``` EXPLAIN SELECT * FROM( SELECT * # Select Number 2 FROM post WHERE parentid = 13 ORDER BY time, id LIMIT 1, 10 ) post13_childs JOIN post post13_childs_childs ON post13_childs_childs.parentid = post13_childs.id ``` and the result was: ``` id |select_type |table |type |possible_keys |key |key_len |ref |rows |Extra 1 |PRIMARY |<derived2> |ALL | NULL | NULL |NULL |NULL |10 | 1 |PRIMARY |post13_childs_childs|ref |parentid |parentid |9 |post13_childs.id |10 |Using where 2 |DERIVED |post |ALL |parentid |parentid |9 | |153153 |Using where; Using filesort ``` This means it used the index `parentid` but scaned all rows due to `ALL` and `153153`. Why could not the index help to not `Full Scannig`? Although if i run the derived query (Select #2) alone like below: ``` Explain SELECT * FROM post WHERE parentid=13 ORDER BY time , id LIMIT 1,10 ``` the result would be desired: ``` id |select_type |table |type |possible_keys |key |key_len |ref |rows |Extra 1 |SIMPLE |post |ref |parentid |parentid |9 |const|41 |Using where; Using filesort ``` Edit: The table `post` has these indexes: - id (PRIMARY) - parentid - time, id (timeid) count of total rows --> 141280. count of children of `13` (`parentid=13`) --> 41 count of children of `11523` --> 10119 When i add index of `(parent,time,id)`, problem of first query would be solved by the explin output for `13` --> 40 rows, type:ref and for `11523` --> 19538 rows, type:ref!!! this Means all children rows of `11423` is examined while i limited first 10 rows.