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
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:
Opening tables 0.000016
System lock 0.000003
Table lock 0.000003
Sending data 0.000014
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.