Very slow when querying large data only using "in" condition with mysql

Question

a table with 44532 rows,It takes nearly 5s to complete the query when i only use "in" in one column.

describ of table is :

Field               Type            Null    Key Default Extra
id                  int(11)         NO      PRI         auto_increment
community_code      varchar(50)     NO      MUL     
extension_worker_no varchar(100)    YES         
apply_id            int(11)         YES     MUL     
segi_cust_id        varchar(100)    YES     MUL     
cust_level          int(11)         YES         1   
cust_name           varchar(255)    YES         
cust_phone          varchar(50)     YES         
cust_status         varchar(255)    YES         
age                 int(5)          YES         
sex                 int(1)          YES         
avatar              varchar(255)    YES         
housing_status      varchar(255)    YES         
house_code          varchar(255)    YES     MUL     
auth_type           varchar(255)    YES         
auth_status         int(1)          YES         1   
auth_time           datetime        YES         
enter_time          datetime        YES         
member_no           varchar(100)    YES         
check_in_status     varchar(255)    YES         2   
in_date             datetime        YES         
invite_code         varchar(128)    YES         
invite_code_status  int(1)          YES         0   
reviewer_no         varchar(60)     YES         
status              int(1)          YES         1   
creater             varchar(60)     YES         SYSTEM  
create_time         datetime        YES         
updater             varchar(60)     YES         
update_time         datetime        YES                 on update CURRENT_TIMESTAMP

Discrimination of the target column is about 0.0019 SELECT count(DISTINCT community_code)/count(*) FROM table; i think is very low and scattered.

i execute sql like this

SELECT
    t.* 
FROM
    table t
WHERE
    t.community_code IN ('13091264', '13091266', ......)

has 20 parameters in the condition,takes nearly 5s

i tried EXPLAIN this sql and i found that its not using index,but when i narrow down the parameters to 5,its using index :

20 parameters :

id  select_type table   partitions  type    possible_keys       key key_len ref rows    filtered    Extra
1   SIMPLE      t                   ALL     index_community_code                45896   67.87       Using where

5 parameters :

id  select_type table   partitions  type    possible_keys           key                     key_len ref rows    filtered    Extra
1   SIMPLE      t                   range   index_community_code    index_community_code    152         3110    100.00      Using index condition

i know that mysql will judge whether the index needs to be used or not. i mean,is this normal? or how should i improve this.

my english is not good,thank you all for your help.

Please add table description and the output of the explain plan. `community_code` is it integer or string ? — Ergest Basha, Jun 07 '22 at 14:52
Please post TEXT results of SHOW CREATE TABLE t; - so we can see your indexes. — Wilson Hauck, Jun 11 '22 at 20:59
What is the setting of `innodb_buffer_pool_size`? How much RAM do you have? How big (in GB) is the table? What Engine is the table? — Rick James, Jun 20 '22 at 05:30

score 0 · Answer 1 · answered Jun 07 '22 at 15:04

0

Try using the Index hint and see if the MySQL picks the index while query execution. You can try out below options.

1. USE INDEX Option

SELECT
    t.* 
FROM
    table t USE INDEX (i_index_name)
WHERE
    t.community_code IN ('13091264', '13091266', ......);

2. Force index option

SELECT
    t.* 
FROM
    table t Force INDEX (i_index_name)
WHERE
    t.community_code IN ('13091264', '13091266', ......);

answered Jun 07 '22 at 15:04

shashankqv

497
4
12

thanks your answer, i just tried the two methods you mentioned, even if used index forced, query is still slow – shuai hang hang Jun 08 '22 at 03:32

Very slow when querying large data only using "in" condition with mysql

1 Answers1