Re: counting on a complex query
On 8/1/07, Les Fletcher <[EMAIL PROTECTED]> wrote: > SQL_CALC_FOUND_ROWS isn't an option for us. Right now I am doing two > queries, but I am just trying to see if there is a better way to do the > count query than to just turn it into a dervied table and count the > results i.e. SELECT COUNT(*) FROM (big nasty query) t1. I don't think so. That's the way I've done it. You may want to experiment with changing the SELECT on the inside query to minimize the amount of data that gets put into the temp table. I don't know if the optimizer is smart enough to skip fetching those values or not. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: counting on a complex query
SQL_CALC_FOUND_ROWS isn't an option for us. Right now I am doing two queries, but I am just trying to see if there is a better way to do the count query than to just turn it into a dervied table and count the results i.e. SELECT COUNT(*) FROM (big nasty query) t1. I think what the question is boiling down to is how to do count queries when using group by and/or having clauses. Les Perrin Harkins wrote: On 8/1/07, Jerry Schwartz <[EMAIL PROTECTED]> wrote: Will SELECT SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() do what you want? Be careful. While it will give the correct number of rows that would have been found if LIMIT had not been used, it makes the server compute the whole result set, disabling the shortcuts that LIMIT normally allows. I found that with my large queries it was faster to do two separate queries (a COUNT for the number of rows and a LIMIT for one page of results) than to use LIMIT and FOUND_ROWS(). - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: counting on a complex query
On 8/1/07, Jerry Schwartz <[EMAIL PROTECTED]> wrote: > Will SELECT SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() do what you want? Be careful. While it will give the correct number of rows that would have been found if LIMIT had not been used, it makes the server compute the whole result set, disabling the shortcuts that LIMIT normally allows. I found that with my large queries it was faster to do two separate queries (a COUNT for the number of rows and a LIMIT for one page of results) than to use LIMIT and FOUND_ROWS(). - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: counting on a complex query
Will SELECT SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS() do what you want? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com > -Original Message- > From: Les Fletcher [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 01, 2007 3:27 PM > To: mysql@lists.mysql.com > Subject: counting on a complex query > > I have a nice little query that I can get the results from > fine, but am > having problems figuring out the best way to get the count > directly from > mysql. The query looks like the following: > > SELECT t1.id, t1.col1, t2.id, t2.col1, t2.col2, t2.col3, > t2.col4 FROM t1 > JOIN t2 ON ( t2.id = t1.col1 ) WHERE ( ... lots of OR's and > AND's on t1 > ... ) GROUP BY t1.col1 HAVING ( count(*) = t2.col2 ) ORDER BY > t2.col3, > t2.col4; > > > There is a one-to-many relationship between t2 and t1 ( lots > of entries > in t1 associated with an entry in t2 ). The group by is just > collapsing > the t1 matches to get unique entries in t2 while the HAVING is then > cutting that result set down further based on some predefined > criteria. > > This gets the set of records that I want. The problem is that I also > want to be able to page through the records, but still know > how many of > them are, hence wanting to be able to count the number of > results in the > set. Right now I know of two options, one is to just run the > query with > out any limits and count the records in my application. The other is > two do the following: > > SELECT COUNT(*) from ( SELECT t1.id, t1.col1, t2.id, t2.col1, > t2.col2, > t2.col3, t2.col4 FROM t1 JOIN t2 ON ( t2.id = t1.col1 ) WHERE > ( ... lots > of OR's and AND's on t1 ... ) GROUP BY t1.col1 HAVING ( count(*) = > t2.col2 ) ORDER BY t2.col3, t2.col4 ) t3; > > > Not sure if this is an optimal way to do it or not. Any > suggestions on > a better way of getting the count? > > Les > > -- > 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]
counting on a complex query
I have a nice little query that I can get the results from fine, but am having problems figuring out the best way to get the count directly from mysql. The query looks like the following: SELECT t1.id, t1.col1, t2.id, t2.col1, t2.col2, t2.col3, t2.col4 FROM t1 JOIN t2 ON ( t2.id = t1.col1 ) WHERE ( ... lots of OR's and AND's on t1 ... ) GROUP BY t1.col1 HAVING ( count(*) = t2.col2 ) ORDER BY t2.col3, t2.col4; There is a one-to-many relationship between t2 and t1 ( lots of entries in t1 associated with an entry in t2 ). The group by is just collapsing the t1 matches to get unique entries in t2 while the HAVING is then cutting that result set down further based on some predefined criteria. This gets the set of records that I want. The problem is that I also want to be able to page through the records, but still know how many of them are, hence wanting to be able to count the number of results in the set. Right now I know of two options, one is to just run the query with out any limits and count the records in my application. The other is two do the following: SELECT COUNT(*) from ( SELECT t1.id, t1.col1, t2.id, t2.col1, t2.col2, t2.col3, t2.col4 FROM t1 JOIN t2 ON ( t2.id = t1.col1 ) WHERE ( ... lots of OR's and AND's on t1 ... ) GROUP BY t1.col1 HAVING ( count(*) = t2.col2 ) ORDER BY t2.col3, t2.col4 ) t3; Not sure if this is an optimal way to do it or not. Any suggestions on a better way of getting the count? Les -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
