Re: optimizing count statement

[Benoit St-Jean]

Karolis wrote:
> hello,
>
> I am working on a search engine PHP script and I
> have a problem with select count(*) performance:
> The table I am working on has 2 columns:
> KeywordID (int), DocumentID(int);
>
> I have created indexes for both of those columns.
>
> My SQL statement is as follows:
> SELECT COUNT(*) AS Cnt FROM TABLE T1, TABLE T2
> WHERE (T1.KeywordID = 7 AND T2.KeywordID = 79 )
> AND (T1.DocumentID = T2.DocumentID );
>
> Problem:
> when MySQL finds no more than ~3000 results, the
> query is executed in ~0.01 seconds with about 1
> million items in the table. However, when there is
> more than 4000 results found, the query takes
> about 2 seconds to execute.
>
> Is there any way to optimize this selecting this
> count?
>
> thanks,
> Karolis

Can you post the EXPLAIN of this query as well as table structure and SHOW INDEX? Are you using InnoDB tables?