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:

SELECT Main.Col1, NoFilter.TotCount, Filter1.SubCount, etc. FROM
(
SELECT Col1 FROM Table WHERE Col1 = X and Col2 > Y and Col3 < Z LIMIT 1, 30
) Main

INNER JOIN
(
SELECT COUNT(*) AS TotCount FROM Table
) NoFilter

INNER JOIN
(
SELECT COUNT(*) AS SubCount WHERE Col2 > Y
) Filter1

ETC.

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?

Thanks
--
View this message in context: http://www.nabble.com/Limit-the-resu...p14549988.html
Sent from the MySQL - General mailing list archive at Nabble.com.

Hello all,

I am trying to add a field to a very large table. The problem is that mysql
locks up when trying to do so. I even tried deleting the foreign keys on
the table and it wont even let me do that, again locking up.

It works for around 5 minutes or so then just either locks or the database
dies and I have to restart it.

Any ideas how to do this?

Thanks,
James

On Dec 30, 2007 1:50 PM, donr2020 <donr@twensoft.com> 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 50000) AS limited_table

I'm not sure this will actually be faster though.

- Perrin

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 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 s. Any other thoughts
would be appreciated.

Best,

Don



Perrin Harkins wrote:
>
> On Dec 30, 2007 1:50 PM, donr2020 <donr@twensoft.com> 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 50000) 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/mysql?unsub=lists@nabble.com
>
>
>

--
View this message in context: http://www.nabble.com/Limit-the-resu...p14561532.html
Sent from the MySQL - General mailing list archive at Nabble.com.

On Dec 31, 2007 3:05 PM, donr2020 <donr@twensoft.com> 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 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 s, 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