分页：2个查询（行数和数据）或1个更大的查询

Question

As I do not know anything about speed and complexity of php and mysql(i) scripts, I had this question:

I have a database with 3 tables:

• 'Products' with about 9 fields. Containing data of products, like 'long' content text.
• 'Categories' with 2 fields. Containing name of categories
• 'Productcategories' with 2 fields. Containing which product has which categories. Each product is part of 1-3 categories.

In order to set up pagination (I need row_count because I wish to know what the last page is), I was wondering what the most sufficient way to do it is, and or it depends on the amount of products (50, 100, 500?). The results returned depends on a chosen category:

"SELECT * FROM `productcategories` 
JOIN products ON products.proID = productcategories.proID 
WHERE productcategories.catID =$category";

Idea 1:

• 1 query which only selects 1 field, instead of all. And then counts the total rows for my pagination with mysqli_num_rows().
• A second query which directly selects 5 or 10 (with LIMIT I expect) products to be actually shown.

Idea 2:

• Only 1 query (above), on which you use mysqli_nuw_rows() for row count and later on, filter out the rows you want to show.

I do not know which is the best. Idea 1 seems faster as you have to select a lot less data, but I do not know or the 2 queries needed influence the speed a lot? Which is the fastest: collecting 'big' amounts of data or doing queries? Feel free to correct me if I am completely on the wrong path with my ideas.

Answer 1

It is generally considered best practice to return as little data as possible so the short answer is to use the two queries. However, MySQL does provide one interesting function that will allow you to return the row count that would have been returned without the limit clause:

FOUND_ROWS()

Just keep in mind not all dbms' implement this, so use with care.

Example:

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