Then eliminate the subquery and SQL_CALC_FOUND_ROWS will be an option for you again. If you would like some help refactoring your query, I volunteer. If I can't help, I know there are several others on the list just as capable as I (or more so).
Shawn Green Database Administrator Unimin Corporation - Spruce Pine pow <[EMAIL PROTECTED]> wrote on 09/07/2005 02:33:09 PM: > Very sorry for having troubled all regarding the seemingly stupid rtfm > garnering request for help. > > But in my attempt to make the question as simple and succinct as > possible, i forgot to state one EXTREMELY important chunk of information. > > I can't use SQLCALCFOUNDROWS because there is a subqry in my statement. > SQLCALCFOUNDROWS does not like being in the subqry, always gives me an > error (it runs fine as a standalone query though) > > The total row count has to be from the subqry, because thats where the > LIMIT is. > > Many thanks and apologies. > > [EMAIL PROTECTED] wrote: > > > > > RTFM - It is online, it has an index, and it is searchable: > > http://dev.mysql.com/doc/mysql/en/select.html (hint: look for the > > phrase "how many rows" on this page) > > > > If you looked in the index for "rows", you would find the function > > ROW_COUNT() (which is close but not exactly what you wanted). However, > > by clicking on the link next to that function, you wind up at another > > page that also has the answer you seek. > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > > > > > pow <[EMAIL PROTECTED]> wrote on 09/07/2005 06:15:12 AM: > > > > > Hi everyone, Im executing the following query: > > > > > > SELECT * > > > FROM > > > table1 > > > WHERE > > > table1.field1 = 'A' AND table1.field2 = 'B' > > > LIMIT 0,10 > > > > > > I also need to get the total record count for the above query, but > > > without the limit clause (limit is for pagination purposes) > > > Is there any way to extract this total record count without the need to > > > do another query? > > > > > > Right now I am using another query to get the total record count: > > > > > > SELECT COUNT(*) > > > FROM > > > table1 > > > WHERE > > > table1.field1 = 'A' AND table1.field2 = 'B' > > > > > > > > > In reality, my tables are very large, and involve joins, so executing > > > the query TWICE is taking its toll on the server. > > > Thanks! > > > Pow > > > > > > -- > > > MySQL General Mailing List > > > For list archives: http://lists.mysql.com/mysql > > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > >
