CentOS Question posted in
The official documentation can be found here.

select count(*) query is too slow. I'm guess it is not work by indexing – CentOS

I would like to ask for comment because I did a better search.

My server environment is below.

  • CentOS release 5.1
  • Linux 2.6,18
  • CPU: Intel (R) Xeon (R) CPU E3-1230 v3 @ 3.30GHz 8core
  • M / M: 8 GB
  • MySQL v5.5.40

There are about 260,000 records in this table (kc_article-MyISAM).

 mysql> desc kc_article;

+ --------------------- + ---------------------- + ---- -+ ----- + --------------------- + ---------------- +

| Field | Type | Null | Key | Default | Extra |

+ --------------------- + ---------------------- + ---- -+ ----- + --------------------- + ---------------- +

| idx | int (11) | NO | PRI | NULL | auto_increment |

| w_status | tinyint (4) | NO | MUL | 1 | |

| w_subj | varchar (255) | NO | MUL | NULL | |

~~ omission ~~

| w_section1 | int (11) | NO | MUL | NULL | |

| w_section2 | int (11) | NO | MUL | NULL | |

| w_theme | int (11) | NO | MUL | NULL | |

~~ Lay ~~

Even though the index is created in the query condition, the speed sometimes goes beyond 1, 2, 10, or 20 seconds.
w_status and w_section2 are both indexed.

mysql> explain select count (*) as cnt from kc_article
       where w_status> 5
         and (w_section2 = '68')


+ ---- + ------------- + ------------ + ------ + ---------- ----------- + ------------ + --------- + ------- + ------- + ------------- +

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+ ---- + ------------- + ------------ + ------ + ---------- ----------- + ------------ + --------- + ------- + ------- + ------------- +

| 1 | SIMPLE | kc_article | ref | w_section2, w_status | w_section2 | 4 | const | 33548 | Using where |

+ ---- + ------------- + ------------ + ------ + ---------- ----------- + ------------ + --------- + ------- + ------- + ------------- +

The same is true for checking, restoring, and optimizing the table and recreating it after dropping the index.
The value of w_status is an integer from 0 to 6, with 6 being 95% or more.

I look forward to hearing from you.

Tags:

3

Answers


  1. 0

    To start with, this query:

    select count (*) as cnt from kc_article where w_status> 5 and (w_section2 = '68')

    Should be written as:

    select count (*) as cnt from kc_article where w_status =  and w_section2 = 68

    Parentheses are superfluous, and since w_section2 is an integer it should be compared against an integer, not a string. Also, w_status ranges from 0 to 6, so you can use an equality condition instead of an inequality.

    You mentionned that w_status and w_section2 are both indexed. For this query, you want a compound index on both columns, not an index on each column (otherwise, MySQL cannot use both at the same time). If it does not exist, then create it:

    create index kc_article_status_section_idx on kc_article(w_status, w_section2);

    A few hundred thousand rows is not a big dataset, I would expect that your query should run fast with the above index.

    Login or Signup to reply.
  2. 0

    select count (1) as cnt from kc_article where w_status> 5 and w_section2 = ’68’

    Login or Signup to reply.
  3. 0

    This composite index, in this order is what you need:

    INDEX(w_secdion2, w_status)

    When building an index, start with the = clauses. See http://mysql.rjweb.org/doc.php/index_cookbook_mysql

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search