Best way to search in a table and get results and number of results (MySQL)

Question

I have a table of "items", and a table of "itemkeywords". When a user searches for a keyword, I want to give him one page of results plus the total number of results.

What I'm doing currently is (for a user that searches "a b c":

SELECT DISTINCT {fields I want} FROM itemkeywords JOIN items   
    WHERE (keyword = 'a' or keyword='b' or keyword='c'
    ORDER BY "my magic criteria"
    LIMIT 20.10

and then I do the same query with a count

SELECT COUNT(*) FROM itemkeywords JOIN items   
    WHERE (keyword = 'a' or keyword='b' or keyword='c'

This may get to get a fairly large table, and I consider this solution suck enormously...
But I can't think of anything much better.

The obvious alternative to avoid hitting MySQL twice , which is doing the first query only, without the LIMIT clause, and then navigating to the correct record to show the corresponding page, and then to the end of the recordset in order to count the result seems even worse...

Any ideas?

NOTE: I'm using ASP.Net and MySQL, not PHP

Answer 1

Add SQL_CALC_FOUND_ROWS after the select in your limited select, then do a "SELECT FOUND_ROWS()" after the first select is finished.

Example:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

Answer 2

You have 2 options:

The MySQL API should have a function that returns the number of rows. Using the older API its mysql_num_rows(). This won't work if you are uisng an unbuffered query.

THe easier method might be to combine both your queries:

SELECT DISTINCT {fields I want}, count(*) as results 
       FROM itemkeywords JOIN items   
       WHERE (keyword = 'a' or keyword='b' or keyword='c'
       ORDER BY "my magic criteria"
       LIMIT 20.10

I did some tests, and the count(*) function isn't affected by the limit clause. I would test this with DESCRIBE first. I don't know how much it would affect the speed of your query. A query that only has to give the first 10 results should be shorter then one that has to find all the results for the count, and then the first 10., but I might be wrong here.

Answer 3

You might look at MySQL SQL_CALC_FOUND_ROWS in your first statement followed by a second statement SELECT FOUND_ROWS() which at least prevents you doing 2 data queries, but will still tend to do an entire table scan once.

See http://dev.mysql.com/doc/refman/5.0/en/select.html and http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

Better to consider: do you really need this feature?

Answer 4

If you're really worried about performance and you do end up needing to make two queries, you might want to consider caching the total number of matches, since that wouldn't change as the user browsed the pages of results.