mysql - Unexpected performance boost after adding JOIN and ORDER BY to query -

mysql - Unexpected performance boost after adding JOIN and ORDER BY to query -

i have next people table:

| id | firstname | children | |----|-----------|----------| | 1 | mark | 4 | | 2 | paul | 0 | | 3 | mike | 3 |

note have non-unique index in firstname , 1 in children.

i need top 10000 first names , children amount of every person has children. decided go solution:

select firstname, children people children > 0 order children desc limit 0, 10000

the thing takes 4 seconds homecoming results table 2.6 1000000 records. explain:

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | | |----|-------------|--------|-------|---------------|----------|---------|--------|------------|-------------| | 1 | simple | people | range | children | children | 4 | (null) | 2677610 | using |

as see it, range telling me index beingness scanned , compared value (in case children > 0). i'd should fast enough. then, guess after getting matching index elements, dbms fetches firstname table internally joining values in index ones in table.

if translate previous paragraph sql this:

select firstname, children people bring together ( select id people children > 0 order children desc limit 0, 10000 ) s on people.id = s.id order children desc

the explain previous sql statement is:

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | | |----|-------------|------------|--------|---------------|----------|---------|--------|---------|---------------------------------| | 1 | primary | <derived2> | | (null) | (null) | (null) | (null) | 10000 | using temporary; using filesort | | 1 | primary | p | eq_ref | primary | primary | 4 | s.id | 1 | | | 2 | derived | people | range | children | children | 4 | (null) | 2687462 | using where; using index |

to surprise, query performs few times faster first one. however, more increment limit x bigger difference becomes (eg: limit 1000000, 10000 sec query still under 1 sec , first 1 exceeds 20 seconds). leads me next questions:

in way mysql handling first query different second? is there way hint mysql execute first query way executes second? is fair lesson learned whenever want fetch value not part of index beingness used, double order , bring together right way go?

additional notes:

sqlfiddle (if makes difference) note i'm running queries sql_no_cache mysql version: 5.5.37

i'm pretty sure can prepare performance of first query having index on children, firstname. covering index query, should eliminate accesses info pages.

the first execution plan says index beingness used where. limit applied last, seems getting firstname all rows before limit applied. seems weird, consistent performance seeing.

in sec version, 10000 ids beingness read. assuming primary keys, info page ups should quite fast -- , controlled explicitly limit. may suggest why version faster, although seems bit of mystery. mostly, though, expect index on children, firstname improve first version of query.

mysql sql performance indexing