Full-text select count(primary key) always slow with only 7000 rows

[kareti rama]

hello,
I have a mysql database run web site.
Table has 7000 rows.
I have fulltext search on three columns.

My queries are like this:

SELECT COUNT(id) FROM table WHERE MATCH (title, keywords, description) AGAINST ('information' IN BOOLEAN MODE );

id is my primary key.


EXPLAIN for this query is :

id : 1
select_type : SIMPLE
table : table
type : fulltext
possible_keys :search
key :search
key_len :0
ref :
rows :1
Extra: Using where



when I use this query in phpmyadmin, i always get the result in less than 1 second.

I have enabled profiling in phpmyadmin and the result shows like this:

Status Time

(initialization) 0.000008
Opening tables 0.000016
System lock 0.000003
Table lock 0.000003
init 0.000004
optimizing 0.000002
statistics 0.000004
preparing 0.000004
executing 0.000019
Sending data 0.000014
end 0.000002
query end 0.000003
freeing items 0.000004
closing tables 0.000002
removing tmp table 0.000011
closing tables 0.000003
logging slow query 0.000002



But, when I look into mysql_slow_queries log, there are great many slow queries like this:

# Fri Jun 27 18:18:47 2008
# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 43
SELECT COUNT(id) FROM table WHERE MATCH (title, keywords, description) AGAINST ('information' IN BOOLEAN MODE )

mostly the query_time is 2.

There is no overload for the table.
I optimize the table every hour because i update the data very often.


I have MYISAM tables.
I have shared hosting. I doubt if shared hosting is responsible for slow queries.

I need your expert advice to solve these slow queries.

Let me know if you wish to have more information.

Please help me to solve these slow queries.

Thank you.



Edited 3 time(s). Last edit at 06/29/2008 01:40AM by kareti rama.