MySQL / PHP query performance?

Question

Im in a dilema on which one of these methods are most efficient.

Suppose you have a query joining multiple tables and querying thousand of records. Than, you gotta get the total to paginate throughout all these results.

Is it faster to?

1) Do a complete select (suppose you have to select 50's columns), count the rows and than run another query with limits? (Will the MySQL cache help this case already selecting all the columns you need on the first query used to count?)

2) First do the query using COUNT function and than do the query to select the results you need.

3) Instead of using MySQL COUNT function, do the query selecting the ID's for example and use the PHP function mysql_num_rows?

I think the number 2 is the best option, using MySQL built in COUNT function, but I know MySQL uses cache, so, selecting all the results on first query gonna be faster?

Thanks,

With regard to the query cache: It won't help you. The MySQL query cache is extremely simple-minded; all it does is return the exact same result set if you make the exact same query. Changing anything about the query, even if it's just the columns requested or the LIMIT, will make it run the whole thing again. — user149341
– user149341, Commented Jul 17, 2012 at 23:33

RobIII · Accepted Answer · 2012-07-17 23:14:22Z

1

Have a look at Found_Rows()

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward:

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

The second SELECT returns a number indicating how many rows the first SELECT` would have returned had it been written without the LIMIT clause.

answered Jul 17, 2012 at 23:14

RobIII

8,8414 gold badges46 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user149341 Over a year ago

Note that adding SQL_CALC_FOUND_ROWS can slow down some queries significantly (as it may force the server to run a query to completion rather than just returning the first matching rows).

RobIII Over a year ago

^ Agree (but so would retrieving a count() and then executing the actual (limit'ed) query). Also not that this does not improve compatibility with other RDBMS'es since this is MySQL specific. On the performance: here is an article exploring the performance hit.

Henrique Over a year ago

This seems a good alternative. Thanks. Do you know if it will be faster than the solutions I presented?

RobIII Over a year ago

@Henrique I linked to an article explaining the performance differences (also make sure you read the comments there!). Having said that: there IS NO one-solution-fits-all answer here. There is only one correct semi-answer: measure and thou shall know. Each situation is unique and so is each possible solution. That's why we invented profiling ;)

hcarver · Accepted Answer · 2012-07-17 23:13:34Z

0

My guess is number 2, but the truth is that it will depend entirely on data size, tables, indexing, MySql version etc.

The only way of finding the answer to this is to try each one and measure how long they take. But like I say, my hunch would be number 2.

answered Jul 17, 2012 at 23:13

hcarver

7,3445 gold badges44 silver badges67 bronze badges

Collectives™ on Stack Overflow

MySQL / PHP query performance?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related