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:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
