MySQL Forums
Forum List  »  Full-Text Search

Full-text select count(primary key) always slow with only 7000 rows
Posted by: kareti rama
Date: June 27, 2008 09:58PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Full-text select count(primary key) always slow with only 7000 rows
8257
June 27, 2008 09:58PM


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.