New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
MySQL, finding total number of records as well as detail records
I need help with a MySQL query.
I need to find out the total number of items in a database (which match my query) and getting some rows out.
If I wrote "SELECT count(ID), name ..... WHERE ....", the Query will only return the first row. The query is complex and I don't want to run the query twice just to get the head count. Counting items in PHP is unreliable as if I do "LIMIT 0,30" it will only tell you there are 30 items not the total number.
Is there a way to query the actual row as well as head count in one query?
Comments
You can combine the selects in one - example:
http://sqlfiddle.com/#!2/ca27b/1
As @Evo said, SELECT (SELECT count(ID) FROM mytable), name from mytable WHERE ....
This is however very expensive, why not select it into a var and select that each time?
select @var = count(ID) FROM mytable
SELECT @var, name from mytable WHERE ....
In that case, you essentially run the query twice
It seems to me that you are missing the 'group by', have you tried that?
The query is only run once to get a total and then that total is used in all selects. It's a lot more efficient than running the select count on all rows.
How many rows is your where query selecting? If its one, then use the nested select. If its more than 1 then my other solution is more efficient.
Edit:
Sorry just reread it and its only one row you are returning. Use the nested select.
Group by what? I have nothing to group by. I am selecting transaction logs that match certain condition and join it with other information like user profile
Depends on the storage engine. It's instant for MyISAM but slow for InnoDB.
To use a group function like count, you need to a group by clause, otherwise you just get a single row like you have found. In other databases it is a syntax error to omit the group by.
I'm not sure exactly what you are trying to do, but it sounds like count() is not going to work for you.
What do you mean by 'head count'? Are you wanting the count of the transactions?
I work a lot with mysql and would be happy to assist, but it is hard without knowing what you need and the actual query.
Direct message me if you want.
I get the feeling that you're asking the wrong question here. What I mean is that you're trying to get a particular outcome, and you're asking about a specific way to do what you think you want to do, when in reality, a different approach would probably work out better.
I already found a solution. Thanks
post the solution....
I will. Those stuff were at the office. I will post it tomorrow
SELECT SQL_CALC_FOUND_ROWS * FROM *** And then run this Query " SELECT FOUND_ROWS()" Yes, it is two queries. But the second query is much much faster.