Announcement

Announcement Module
Collapse
No announcement yet.

Problem with slow mysql select count(id)

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem with slow mysql select count(id)

    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.
Working...
X