Re: Limit the results of a COUNT
On Dec 31, 2007 3:05 PM, donr2020 <[EMAIL PROTECTED]> wrote: > Sorry, I didn't type the subqueries quite correctly. They all have the same > WHERE part (in this case, "WHERE Col1 = X" that essentially "joins" all the > queries. It still doesn't make sense to me. Count queries don't return anything to join. If all you want is to have them in the same result set, you can use subqueries for that, e.g. SELECT (SELECT COUNT(*) FROM x) AS x_count, (SELECT COUNT(*) FROM y) AS y_count, etc. > There are six counts that we need and we first tested it as seven separate > queries; but that took about 20% longer than one nested set of queries, as > there is a little overhead for each query. The overhead of a half-dozen queries shouldn't add up to much with an efficient client library. I think you'd be better off avoiding this complication. At the very least, I'd avoid joining things that can't be joined. > Your suggestion does help somewhat. Changing the subqueries to a count of > limited subqueries reduced a large sample query from 9 seconds down to 5 > seconds. We need to get this down some more to about 1 or 2 seconds (or less > if possible). If LIMIT helps, it probably means you either have a table scan or a subquery that runs separately for every row. Finding a way to improve the use of indexing is your best bet for making a big change. Or some kind of caching scheme. Incidentally, using separate queries would probably increase the chance of hitting the MySQL result cache for some of them. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit the results of a COUNT
Sorry, I didn't type the subqueries quite correctly. They all have the same WHERE part (in this case, "WHERE Col1 = X" that essentially "joins" all the queries. There are six counts that we need and we first tested it as seven separate queries; but that took about 20% longer than one nested set of queries, as there is a little overhead for each query. Your suggestion does help somewhat. Changing the subqueries to a count of limited subqueries reduced a large sample query from 9 seconds down to 5 seconds. We need to get this down some more to about 1 or 2 seconds (or less if possible). We're going to try using VIEW's to see if that helps. Any other thoughts would be appreciated. Best, Don Perrin Harkins wrote: > > On Dec 30, 2007 1:50 PM, donr2020 <[EMAIL PROTECTED]> wrote: >> Our search engine does a master query INNER JOINed to a series of COUNT >> (*) >> subqueries that return what the number of results would have been had the >> user chosen different "filters" (or no filter at all). As an example: > > Hmm. Why are you joining these? There's nothing to join. It looks > like these should be separate queries. > >> This query is being run against a database that currently as 100 Million >> records (and rapidly growing), and if TotCount is over about 50,000, the >> query is unacceptably slow. We need to LIMIT the subqueries to some >> maximum >> count (stop counting at, say, 50,000). Does anyone know a way to do this? > > You can use a temp table, view, or subquery to do it. For example: > > SELECT COUNT(*) FROM > (SELECT id FROM table LIMIT 5) AS limited_table > > I'm not sure this will actually be faster though. > > - Perrin > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- View this message in context: http://www.nabble.com/Limit-the-results-of-a-COUNT-tp14549988p14561532.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit the results of a COUNT
On Dec 30, 2007 1:50 PM, donr2020 <[EMAIL PROTECTED]> wrote: > Our search engine does a master query INNER JOINed to a series of COUNT (*) > subqueries that return what the number of results would have been had the > user chosen different "filters" (or no filter at all). As an example: Hmm. Why are you joining these? There's nothing to join. It looks like these should be separate queries. > This query is being run against a database that currently as 100 Million > records (and rapidly growing), and if TotCount is over about 50,000, the > query is unacceptably slow. We need to LIMIT the subqueries to some maximum > count (stop counting at, say, 50,000). Does anyone know a way to do this? You can use a temp table, view, or subquery to do it. For example: SELECT COUNT(*) FROM (SELECT id FROM table LIMIT 5) AS limited_table I'm not sure this will actually be faster though. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
