0

I'm trying to optimise the number of SQL request. I've found that i've used twice the same request like that :

//a big select without limit
$select="select * from tab1, tab2 ,tab 3 where (jointure..)";

// return to me 20 line
$nbtotal=mysql_num_rows(mysql_query($select));
// I apply the limit for my paging system 5 per page
$select.=" limit  ".$offset.",".$limit;

// all the information that i need to fetch
$result_of_myrequest=mysql_query($select);

I've tried count(article.id) but it return to me a big number in each count!

can i combine in the same request (with limit 0,5) the $nbtotal AND the results of my request ??

Many thanks!

Explosion Pills
  • 188,624
  • 52
  • 326
  • 405
ranell
  • 683
  • 13
  • 29
  • That depends on what you want. You want the total number of results, don't you? And after you know, how many results in total there are, you get a 'select few' from the database. Correct? Now, I know this isn't tagged 'JS', but if you're aiming for a 'low query count-pagination', I'd consider using that. – ATaylor Oct 11 '12 at 16:03
  • 1
    See this other question that has been answered: http://stackoverflow.com/questions/3453809/how-to-use-mysql-found-rows-in-php – Seth Oct 11 '12 at 16:07

1 Answers1

3

Run the limit query which will return you your results to show, then you can call another query:

SELECT FOUND_ROWS();

Which will return the total number of rows from the previous query whilst ignoring the limit.

//a big select with limit
$select="select * from tab1, tab2 ,tab 3 where (jointure..) limit ".$offset.",".$limit;

// all the information that i need to fetch
$result_of_myrequest=mysql_query($select); 

// return to me 20 line
$nbtotal = mysql_query("SELECT FOUND_ROWS()");
MatthewMcGovern
  • 3,466
  • 1
  • 19
  • 19
  • it works for me, thanks :) I'm just curious about this query (SELECT FOUND_ROWS()), do you think that it runs the same previous query or it catches a VAR in the previous result or something like that? (I'm just caring about optimisation and i don't know if this query is lighter than the previous one.) – ranell Oct 11 '12 at 16:21
  • 1
    The performance overheard for `"SELECT FOUND_ROWS()"` is negligible. SQL has been designed to handle large queries, large quantity of queries and large amounts of data. This query is tiny in comparison so there's nothing to worry about. Our company has multiple databases with up to 100 tables, millions of records, etc and our Web services still load nearly instantly to the users. To answer the question, I'm not 100 sure but I think it's cached the results from the LIMIT query, as the limit query returns all the rows then truncates the unnecessary ones and is simply returning the total rows. – MatthewMcGovern Oct 11 '12 at 16:25