I am looking for a little advice on how to handle this particular problem.
I have a page that returns 50 database rows to a php page based on a certain criteria with a limit statement in the query.
The problem is, I need to pull statistics from the full query.
Basically, I need to tell the user that there are say 10000 entries in the database, and say 6000 of them are this and 3000 of them are that, but only show the top 50.
My php code is inaccurately reporting the numbers because it is based on a query that stops at 50.
I'm trying to figure out the best way to handle this.
I don't think it would be wise to run 2 queries for performance reasons. Would it be more cost effective for performance to maybe run a count query instead?
Or should I run the full query, but simply limit the output to 50 by breaking the while loop?
How best to handle? Thanks.
I don't think it would be wise to run 2 queries for performance reasons. Would it be more cost effective for performance to maybe run a count query instead?
You have to run 1 query just to get complete count and then another query to get 50 records data. You should never run 1 query to grab all data and then show only 50. Its like buying 1000 chocolates from mart and going back home and then eat only 5 and throw the rest, its that costly.
If you are just interessted in the count of specific creteria then i woudl certainly run a separated count queries, otherwise if you need more thant he number you should consider creatign a view whcih contains your interessting data which you can run your end queries on, the count ones and the to 50 ones as well.
You can use SQL_CALC_FOUND_ROWS like this
SELECT `SQL_CALC_FOUND_ROWS` * FROM table_name limit 0,5;
It gets the row count before applying any LIMIT clause.
http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_found-rows
To get the count:
SELECT COUNT(*) FROM table WHERE criteria
To get the 50 records
SELECT columns FROM table WHERE criteria LIMIT 0, 50
create a view table that only contain stats data
Create view name as "query that you want"
and then for stats run them on view table because its more faster and the table will autoupdate it self
actualy if you have 100k rows and update,write,read all the time i would recommend that you create 2 tables 1 ordinary for update/insert/delete and anoter view table for read
you can find more info here http://dev.mysql.com/doc/refman/5.0/en/create-view.html
To get a count of all the rows in the database start with:
SELECT *, Count(*) AS Count
FROM tablename
Then you can further limit and return other criteria with a WHERE clause, such as:
WHERE widgets > 10