List:General Discussion« Previous MessageNext Message »
From:Chris Date:March 28 2004 5:37pm
Subject:RE: Getting count(*) with LIMIT in SQL
View as plain text  
In 4+ you can use SQL_CALC_FOUND_ROWS flag

http://www.mysql.com/doc/en/SELECT.html

-----Original Message-----
From: Steffan A. Cline [mailto:steffan@stripped]
Sent: Sunday, March 28, 2004 8:47 AM
To: MySql
Subject: Getting count(*) with LIMIT in SQL


I have been tinkering with finding the fastest way to do my searches and
return less info for faster performance. In a project I have, I build the
sql query with information from a form page. The query may look like this:

SELECT * FROM masterlist WHERE market LIKE "%%" AND source LIKE "%%" AND
clientstatus LIKE "%%" AND ( client LIKE "%%" OR contact LIKE "%%" ) ORDER
BY client LIMIT 0,1 ;

Ignore the LIKE "%%". This is caused when fields are selected to search on
and no keywords are found. I'll fix that later. This works fine and dandy
except I need to get the overall found count as well. I was toying with
COUNT(*) to see if I could get the found count returned too. If I do this :

SELECT *, COUNT(*) AS found  FROM masterlist WHERE market LIKE "%%" AND
source LIKE "%%" AND clientstatus LIKE "%%" AND ( client LIKE "%%" OR
contact LIKE "%%" ) ORDER BY client LIMIT 0,1 ;

I get the following error :

Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is
illegal if there is no GROUP BY clause (1140)

If I do the following :

SELECT COUNT(*) AS found  FROM masterlist WHERE market LIKE "%%" AND source
LIKE "%%" AND clientstatus LIKE "%%" AND ( client LIKE "%%" OR contact LIKE
"%%" ) ORDER BY client LIMIT 0,1 ;

I get the response of found 8214. This is correct!

Isn't there some way to get the found count of the query returned with the
results of the search? The goal is NOT to have 2 searches. One that gives
the found count and then one that actually returns the data.

BTW, Is there any MAJOR dent in performance if somehow I end up with a
search where... column LIKE "%%"

SQL Gurus! Any way to do this?


Thanks

Steffan

---------------------------------------------------------------
T E L  6 0 2 . 5 7 9 . 4 2 3 0 | F A X  6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
Steffan@stripped                             Phoenix, Az
http://www.ExecuChoice.net                                  USA
AIM : SteffanC          ICQ : 57234309
The Executive's Choice in Lasso driven Internet Applications
                                  Lasso Partner Alliance Member
---------------------------------------------------------------



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1

Thread
Getting count(*) with LIMIT in SQLSteffan A. Cline28 Mar
  • RE: Getting count(*) with LIMIT in SQLChris28 Mar