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]
