Hi,

My task is to limit calculation of total number of items in the database
that satisfy certain conditions. I join two tables using WHERE and there are
millions of records as the result. When I do SELECT count(*) .... it takes
really too long. The table has appropriate indexes and I experimented with
replacing the conditions, etc., so I don't think there is a way to make it
work any faster. In my case it would be anough to say that there are "more
than e.g. 50 000" of items instead of calculating the exact quantity. My
question is how to apply a certain limit to count() function in order it
would either return the real quantity if it is smaller than the limit or
return the limit and stop further calculation, quite same as when using
SELECT * FROM ... LIMIT 0, 100

Another option could be estimating approximate quantity in the result but it
seems to me much more complex and I honestly don't know where to start from.

Thanks!
--
View this message in context: http://www.nabble.com/Applying-LIMIT...p14453544.html
Sent from the MySQL - General mailing list archive at Nabble.com.

If exact number isn't important,
you might want to try table_rows in information_schema.tables or show
table status.

On Dec 21, 2007 7:53 PM, Urms <andreym@bigmir.net> wrote:
>
> Hi,
>
> My task is to limit calculation of total number of items in the database
> that satisfy certain conditions. I join two tables using WHERE and there are
> millions of records as the result. When I do SELECT count(*) .... it takes
> really too long. The table has appropriate indexes and I experimented with
> replacing the conditions, etc., so I don't think there is a way to make it
> work any faster. In my case it would be anough to say that there are "more
> than e.g. 50 000" of items instead of calculating the exact quantity. My
> question is how to apply a certain limit to count() function in order it
> would either return the real quantity if it is smaller than the limit or
> return the limit and stop further calculation, quite same as when using
> SELECT * FROM ... LIMIT 0, 100
>
> Another option could be estimating approximate quantity in the result but it
> seems to me much more complex and I honestly don't know where to start from.
>
> Thanks!
> --
> View this message in context: http://www.nabble.com/Applying-LIMIT...p14453544.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/mysql?unsub=ebgssth@gmail.com
>
>

The problem is that there are certain conditions after WHERE different for
each query and the results number can be very different.
--
View this message in context: http://www.nabble.com/Applying-LIMIT...p14459808.html
Sent from the MySQL - General mailing list archive at Nabble.com.

Oh, I misunderstood,sorry.
Using summary tables doesn't work for you?

On Dec 22, 2007 3:00 AM, Urms <andreym@bigmir.net> wrote:
>
> The problem is that there are certain conditions after WHERE different for
> each query and the results number can be very different.
> --
> View this message in context: http://www.nabble.com/Applying-LIMIT...p14459808.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/mysql?unsub=ebgssth@gmail.com
>
>