mysql like order by,MySQL查询优化LIKE术语％ORDER BY int

My question is regarding the handling of MySQL index on VARCHAR combined with an int COLUMN when using prefix matching.

e.g. if I have such a query:

SELECT * FROM tbl WHERE name LIKE 'query%' ORDER BY weight DESC LIMIT 5

Considering I have one index one name->weight, does that index need to find all apperances of the prefix query and then ORDER BY, or does he keeps the cross calculation indexed even with the use of prefix matching (%). I'm troubled by it, because for popular names (e.g. query=john) I might find myself searching for a long time for all appearances of john, and that will make the limit useless and the query to become slow as I'm dealing with a large dataset.

解决方案

Provided that 'query' is of equal or shorter length than the indexed prefix of name:

A composite BTREE index on (name, weight) will be ordered by name then weight. Conceptually:

+---------+--------+---------+

| name(7) | weight | address |

+---------+--------+---------+

| queryaa | 500 | 0x1.... |

| queryaa | 500 | 0xe.... |

| queryaa | 498 | 0x8.... |

| queryaa | 491 | 0xb.... |

| queryaa | 486 | 0xc.... |

| queryaa | 430 | 0x3.... |

| queryab | 600 | 0x2.... |

| queryab | 592 | 0x7.... |

| queryab | 550 | 0x4.... |

| queryab | 321 | 0xa.... |

| queryab | 321 | 0x6.... |

| queryab | 304 | 0x9.... |

| queryab | 297 | 0x5.... |

| querybc | 800 | 0xd.... |

: : : :

MySQL can very quickly traverse such an index to find the top 5 weights for each indexed prefix within the range defined by the filter name LIKE 'query%' (I'm not certain that it does this step, but I'd be surprised if it did not):

+---------+--------+---------+

| name(7) | weight | address |

+---------+--------+---------+

| queryaa | 500 | 0x1.... |

| queryaa | 500 | 0xe.... |

| queryaa | 498 | 0x8.... |

| queryaa | 491 | 0xb.... |

| queryaa | 486 | 0xc.... |

| queryab | 600 | 0x2.... |

| queryab | 592 | 0x7.... |

| queryab | 550 | 0x4.... |

| queryab | 321 | 0xa.... |

| queryab | 321 | 0x6.... |

| querybc | 800 | 0xd.... |

: : : :

At this point, MySQL must perform a filesort on the results:

+---------+--------+---------+

| name(7) | weight | address |

+---------+--------+---------+

| querybc | 800 | 0xd.... |

| queryab | 600 | 0x2.... |

| queryab | 592 | 0x7.... |

| queryab | 550 | 0x4.... |

| queryaa | 500 | 0x1.... |

| queryaa | 500 | 0xe.... |

| queryaa | 498 | 0x8.... |

| queryaa | 491 | 0xb.... |

| queryaa | 486 | 0xc.... |

| queryab | 321 | 0xa.... |

| queryab | 321 | 0x6.... |

: : : :

And only then can it use the top 5 results to fetch the associated records from the table:

+---------+--------+---------+

| name(7) | weight | address |

+---------+--------+---------+

| querybc | 800 | 0xd.... | --> fetch from table

| queryab | 600 | 0x2.... | --> fetch from table

| queryab | 592 | 0x7.... | --> fetch from table

| queryab | 550 | 0x4.... | --> fetch from table

| queryaa | 500 | 0x1.... | --> fetch from table

+---------+--------+---------+

If 'query' is longer than the indexed prefix of name, then MySQL must perform lookups into the table in step 1 above in order to adequately filter the records which are subsequently ordered.