Re: optimizing count statement
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?
Subject
Views
Written By
Posted
4339
October 11, 2005 11:30AM
Re: optimizing count statement
2048
October 12, 2005 04:56AM
1955
October 12, 2005 06:31AM
1620
October 12, 2005 07:11AM
1860
October 12, 2005 07:40AM
1750
October 12, 2005 07:53AM
1930
October 12, 2005 07:50AM
1905
October 12, 2005 08:01AM
1879
October 12, 2005 08:05AM
1625
October 12, 2005 08:23AM
1905
October 12, 2005 08:22AM
1859
October 12, 2005 08:38AM
1996
October 12, 2005 08:46AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.