0

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.

PSZ_Code
  • 1,015
  • 10
  • 29
  • I don't there would be much difference but in either case you might want to change select * to select only those fields that you actually need on your page. – Maximus2012 Jul 24 '13 at 20:57
  • 1
    `SQL_CALC_FOUND_ROWS` look that up, ots the answer –  Jul 24 '13 at 20:58
  • @ Dagon : That indeed seems the best solution! – PSZ_Code Jul 24 '13 at 21:02
  • Not sure if it still holds up in newer versions but according to this it isn't as quick: http://stackoverflow.com/questions/186588/which-is-fastest-select-sql-calc-found-rows-from-table-or-select-count – Pitchinnate Jul 24 '13 at 21:09
  • @Pitchinnate : The comments to the answer of your link seem to state that SQL_CALC_FOUND_ROWS is actually faster. – PSZ_Code Jul 24 '13 at 21:17
  • 1
    I agree however, you don't have to JOIN two tables to get the count with a second query. I guess the only way to find out is to try both methods and see which one is quicker. – Pitchinnate Jul 24 '13 at 21:24
  • @Pitchinnate Do you think joining tables requires a lot of time? – PSZ_Code Jul 24 '13 at 21:26

2 Answers2

2

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();
Matt Pinkston
  • 1,610
  • 15
  • 18
  • but this needs the SQL_CALC_FOUND_ROWS, right? Does it also work with mysqli? – PSZ_Code Jul 24 '13 at 21:05
  • That's right. It's specific to MySQL as far as I know which could harm db portability, but very useful if you only intend on using mysql. It is a function of mysql itself so it will work no matter what adapter you use to connect to the mysql server. – Matt Pinkston Jul 24 '13 at 21:07
  • http://stackoverflow.com/questions/186588/which-is-fastest-select-sql-calc-found-rows-from-table-or-select-count – Pitchinnate Jul 24 '13 at 21:08
  • The answer and it comments (from pitchinnate) seem to disagree (or did I understand wrong). Any idea which is the optimal? sql_calc_rows or count(*)? – PSZ_Code Jul 24 '13 at 21:18
  • like the post says, it depends on how your indexes are defined and how the query is written. no one way is always better than the other. However, if you're in doubt I would suggest using count(*) if for no other reason than portability. – Matt Pinkston Jul 24 '13 at 21:24
  • 1
    WARNING: `SQL_CALC_FOUND_ROWS` will cause a full table scan so it's not good with large tables. – Accountant م Mar 15 '18 at 00:38
0

Use select count(1) as count... for the total number of rows. Then select data as needed for pagination with limit 0,10 or something like that.

Also for total count you don't need to join to the products or categories tables as that would only be used for displaying extra info.

"SELECT count(1) as count FROM `productcategories` WHERE catID=$category";

Then for data:

"SELECT * FROM `productcategories` 
JOIN categories ON categories.catID =  productcategories.catID 
JOIN products ON products.proID = productcategories.proID 
WHERE productcategories.catID=$category limit 0,10";

Replacing * with actual fields needed would be better though.

Pitchinnate
  • 7,517
  • 1
  • 20
  • 37