MySQL Performance: MySQL => Problem with slow mysql select count(id)

Problem with slow mysql select count(id) [message #3227]

Sun, 29 June 2008 02:00

student
Messages: 3
Registered: June 2008

Junior Member

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

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 )

In most of these logs the query_time is 2.

I have almost no problem with other queries in my scripts.

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 run SQL for 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

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 do not have any CPU exceeded logs.

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 know more information.

Please help me to solve these slow queries.

Thank you.

Report message to a moderator