Mysql Explain Query with type "ALL" when an index is used

Question

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.

Do you want help improving and optimizing this query? If so, we would need to see the whole query (and its EXPLAIN) — Strawberry, Dec 20 '13 at 10:39
Try creating index for `(parentid, time, id)`. For further reference http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/ — Meherzad, Dec 23 '13 at 07:02
Try using `explain extended` with your query it will return a detailed explain statement [refer](http://dev.mysql.com/doc/refman/5.0/en/explain-extended.html) and for your question [refer](http://www.mysqlperformanceblog.com/2006/08/31/derived-tables-and-views-performance/) — Meherzad, Dec 23 '13 at 08:39
@ahoo [refer](http://stackoverflow.com/questions/1180714/how-can-i-further-optimize-a-derived-table-query-which-performs-better-than-the) this question with similar problem.... Hope this helps. — Meherzad, Dec 23 '13 at 08:47
Check this [fiddle](http://www.sqlfiddle.com/#!2/62d36/2) it will answer all your question. Index is not used withing derived table — Meherzad, Dec 23 '13 at 08:53
OK, tnx @Meherzad, it is reason for slow query `Select#1`. My problem is `Select#2` and `type:All`. — ahoo, Dec 23 '13 at 08:59
Have you tried creating a composite index on (parentid, time, id) ?? — Meherzad, Dec 23 '13 at 09:12
@Meherzad, When i create index (parentID, time, id ), the column rows of explain result output 19538 (and type:ref). I Expect 10 due to LIMIT clause. — ahoo, Dec 23 '13 at 09:35
need some `show create table [table_name]` outputs of both tables we cant solve it like this... — Raymond Nijland, Dec 24 '13 at 22:55

score 10 · Answer 1 · answered Dec 24 '13 at 16:49

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.

tnx for replying. i added index `parentid, time, id` but ... (look at my Edit of my above Question which i changed now) — ahoo, Dec 25 '13 at 13:02

score 1 · Answer 2 · answered Dec 22 '13 at 14:25

1

Doing an ORDER BY that is not helped by any index can regularly kill performance. For the inner query, I would have a covering index on (parentID, time, id ) so that both the WHERE and ORDER BY clauses can utilize the index. Since the parentID is also the basis of the join afterwords, it should be good to go there to and be quite fast.

answered Dec 22 '13 at 14:25

DRapp

47,638
12
72
142

@ahoo, no, now a pair of (time,id), you NEED TO INCLUDE THE Parent ID, so the index should be (PARENTID, TIME, ID) – DRapp Dec 23 '13 at 00:12
When i index `(parentID, time, id )`, the column `rows` of explain result output 19538 (and type:ref). I Expect 10 due to `LIMIT` clause. – ahoo Dec 23 '13 at 08:31
1

@ahoo, I would suggest deleting the individual parentID and (timeid, id ) to avoid confusion to the engine as the one index with parentID in the FIRST POSITION of the index would do the same as your stand-alone index, but by also having the time and id as second and third position will resolve the order by clause. – DRapp Dec 23 '13 at 10:32
parentID is defined as a foreign key. all foreign keys is index too. and i don't think can delete index of it. – ahoo Dec 23 '13 at 10:40

Mysql Explain Query with type "ALL" when an index is used

Edit:

2 Answers2