What mysql indexes should I use in this query?… here is a solution to the problem.
What mysql indexes should I use in this query?
SELECT * FROM items WHERE caption LIKE 'name%' AND type = 'private' OR owner LIKE 'name%' type = 'private' ORDER BY uses DESC LIMIT 40;
Possible keys: items_caption, items_owner, items_type
Key: items_uses
(Get these by using the explain command).
The query takes about 1.8 seconds to execute, and there are more than a million records in the table. I don’t know how to index this query, and I don’t have control over the source, so I can’t modify the query.
Solution
You can do full-text indexing of “captions”, which significantly increases query times.
ALTER TABLE items ADD FULLTEXT(caption,owner);
MySQL V versions 5.6 and later support full-text search in Innodb.
https://blogs.oracle.com/MySQL/entry/full_text_search_with_innodb