1

Describe：

The table profit has about 20 columns, and no big column like text, and have two indexs:

  1. index idx_uid on column uid
  2. index uid_acc_fId_date on column uid,acc,fid and date

The slow query sql is：

select * from profit where (((uid='1111') and (flag=32)) and (fId='2222')) and (date='20161008') limit 1;

and the explain output is：

id:1
select_type:SIMPLE
table:profit
type:ref
possible_keys:uid_acc_fId_date
key:uid_acc_fId_date
key_len:4
ref:const
rows:267
Extra:Using where

Execute the sql with sql_no_cache will spend about 2s, and execute the same sql with sql_no_cache second time will spend about 0.1s.

Env：

  1. mysql 5.5.49-37.9,innodb，percona
  2. SATA HDD
  3. The table profit has about one million rows
  4. Centos 2.6 kernel
  5. rows count for uid=1111 is only 270

PS：

Mysql will backup everyday at midnight， and innodb_old_blocks_time is 0, I think the backup will flush all hot data out of buffer pool。But even so it spend too many time to do the above query。So, is it the disk problem？

Stephen Feng
  • 71
  • 1
  • 6

3 Answers3

1

Expanding upon the comment above, change index uid_acc_fId_date to (uid,fid,date,acc) and change the query to:

SELECT columns
     , I
     , actually
     , want
  FROM profit 
 WHERE uid = 1111 
   AND fId = 2222
   AND date='20161008'
   AND flag = 32
 ORDER 
    BY uid
 LIMIT 1;
Strawberry
  • 33,750
  • 13
  • 40
  • 57
0

Always use column name when you try to select query. Like SELECT name, class_name from table_name;

sunilsingh
  • 503
  • 1
  • 5
  • 9
  • Thank you! It's a good suggestion, maybe I will have a try, but it's really hard for our project to do it, because there are too many to modify, and it's risky to modify the previous code. In fact, I don't think it's the key for this issue – Stephen Feng Oct 09 '16 at 10:52
  • Good advice, but probably not relevant to the sluggishness here. – Rick James Oct 16 '16 at 05:01
0

Try to delete all parentheses in where condition. Unnecessary parentheses cause mysql can not use index correctly.

select * 
from profit 
where 
     uid='1111' 
     and flag=32 
     and fId='2222' 
     and date='20161008' 
limit 1;
Tedo G.
  • 1,556
  • 3
  • 17
  • 29
Mr.Nobody
  • 1
  • Thank you for your time! The parentheses are added by framework. As you say, I remove all parentheses , and the explain output is same. – Stephen Feng Oct 09 '16 at 10:47
  • As you say, the all data rows for uid=1111 has only 270 rows. If the index of uid_acc_fId_date is used, it is impossible to use 2s. – Mr.Nobody Oct 09 '16 at 11:22
  • The MySQL parser is good about ignoring redundant parentheses. – Rick James Oct 16 '16 at 05:00