The best way to search a table and get results and the number of results (MySQL)

Question

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

What I am currently doing (for a user who is looking for "ab 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 make the same query with the count

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

This can lead to the fact that you get a rather large table, and I believe that this solution is very suck ...
But I can not come up with anything better.

, MySQL, LIMIT, , , , ...

?

. ASP.Net MySQL, PHP

Answer 1

SQL_CALC_FOUND_ROWS , "SELECT FOUND_ROWS()" .

:

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

Answer 2

, , , , .

Answer 3

2 :

API MySQL , . API, mysql_num_rows(). , .

:

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

, count (*) limit. DESCRIBE. , . , 10 , , , , 10., , , .

Answer 4

You can look at MySQL SQL_CALC_FOUND_ROWSin your first statement, followed by a second statement SELECT FOUND_ROWS()that at least prevents 2 data queries from executing, but will tend to scan the entire table 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 think: do you really need this feature?