MySQL: combining queries for speed

Question

MySQL database with more than 10 million rows. I am currently displaying the first ten results and then counting the total number of matches. The contents of the first ten results are echoed in PHP. Then the total number of hits is displayed.

First statement (fast):

SELECT * FROM `bigTable` WHERE `name` LIKE '%steve%' LIMIT 10

Second statement (slow):

SELECT COUNT(1) FROM `bigTable` WHERE `name` LIKE '%steve%'

I want to combine these queries to increase speed. I want to preserve the functionality where users see the first ten results, while they are waiting for the system to find the total count. Is this possible?

Answer 1

Your second query requires inspecting the name column for every row. If you need that number with that condition it will always be slow.

You might consider keeping a separate table with word-occurs in row(id), (keep it updated with all inserts/updates/deletes, can be maintained with a trigger). Your search will always be a join to this table with word='name' (to be indexed on this column: word).

You can also use FULLTEXT indexing as suggested by others; if available to you.

Answer 2

If you are searching for a "word", then use a FULLTEXT index and MATCH(name) AGAINST("+steve") -- both queries will by much faster.

If, on the other hand, you must search for middles of words (etc), then doing it with LIKE and a leading wild card is as fast as it gets. But, the COUNT will always have to scan the entire table, and the SELECT * will scan until the end of the table, too, unless it finds 10 before that.

Depending on what version you are using, you may be able to use SQL_CALC_FOUND_ROWS and FOUND_ROWS, but it won't be much faster.