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

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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