0

As far as I can tell, for some reason a small fraction of very common queries fail to use indexes. If all such queries failed to use indexes, the server would crash within 1 minute hence only a few such queries behave like this.

How should I prevent these occasional slow queries which in vast majority of cases work fine?

My create tables are:

CREATE TABLE msgs (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  sender text NOT NULL,
  receiver text NOT NULL,
  cont blob NOT NULL,
  img text NOT NULL,
  orient text NOT NULL,
  d_t datetime NOT NULL,
  convo text NOT NULL,
  u_code text NOT NULL,
  viewed datetime NOT NULL,
  stat int(11) NOT NULL,
  device text NOT NULL,
  addr text NOT NULL,
  PRIMARY KEY (id),
  KEY msg_u_code (`u_code`(24)),
  KEY receiver (`receiver`(24)),
  KEY sender (`sender`(24)),
  KEY img (`img`(28)),
  KEY convo (`convo`(49))
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE usrs (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  usr_name text NOT NULL,
  img text NOT NULL,
  orient text NOT NULL,
  `password` text NOT NULL,
  u_code text NOT NULL,
  d_t datetime NOT NULL,
  stat int(11) NOT NULL,
  device text NOT NULL,
  addr text NOT NULL,
  PRIMARY KEY (id),
  KEY img (`img`(28)),
  KEY usr_code (`u_code`(24))
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

And my slow query log entry is:

 # Time: 171115  6:26:37
 # User@Host: xxx[xxx] @ localhost []
 # Thread_id: 25524888  Schema: xxx  QC_hit: No
 # Query_time: 32.423430  Lock_time: 0.000425  Rows_sent: 1  Rows_examined: 30998008
 # Rows_affected: 0
 use xxx;
 SET timestamp=1510723597;
 select msg_cont, msg_u_code, msg_d_t, msg_viewed, usr_u_code, usr_name from
        (select
            msgs.id as msg_id,
            msgs.cont as msg_cont,
            msgs.u_code as msg_u_code,
            msgs.d_t as msg_d_t,
            msgs.viewed as msg_viewed,
            usrs.u_code as usr_u_code,
            usrs.usr_name as usr_name
            from msgs
            left join usrs on msgs.sender = usrs.u_code
    where  msgs.convo = 'aaaaaaaaaabfbaghdgcigfid_aaaaaaaaaabeiaccjfhjfach'
      and  (msgs.sender = 'aaaaaaaaaabfbaghdgcigfid'
              or  msgs.receiver = 'aaaaaaaaaabfbaghdgcigfid'
           )
      and  msgs.stat = '1'
      and  usrs.stat = '1'
      and  usrs.u_code not in('aaaaaaaaaabfaagfbgggiejh',
                              'aaaaaaaaaabfabgbjdfjigbd',
                ...... !!!!![here go 400 more usr_u_codes]!!!!!
                          )
      and  msgs.id > 30997997
        ) a order by msg_id asc;
7
  • Am I right in thinking that you list of 400 more usr_u_codes is not manually typed in? Is an ORM or some machine generated code at work here?
    – Vérace
    Commented Nov 15, 2017 at 19:44
  • @Vérace yes that is machine generated with high variability.
    – John Doe
    Commented Nov 15, 2017 at 21:42
  • What's the record count in usrs.u_code? I'd bet that the "optimiser" in MySQL is switching based on the number of entries in the IN clause from an index based search to a FTS (Full Table Scan). Can you provide plans of a slow and a fast query? Just note that such switches are not necessarily bad - if you're going to retrieve more than (IIRC) than 10% (rule of thumb) of a table, an FTS is better.
    – Vérace
    Commented Nov 15, 2017 at 23:19
  • @Vérace usrs.u_code have 100K rows. The puzzling part is that this query is usually fast but occasionally slow (1 in 1000 calls). Retrieved portion of table is always just a few rows, nowhere near even 1% of each table. I cant figure out why would MySQL decide 999 times to use an index and then 1 time not.
    – John Doe
    Commented Nov 15, 2017 at 23:53
  • How do you know that it's not using the index 1 time in ~ 1000?
    – Vérace
    Commented Nov 16, 2017 at 1:35

1 Answer 1

0

While I am unable to explain why MySQL occasionally decided not to use index on a very obvious and very common query, the solution was to simply force the index.

In my particular case:

select msg_cont, msg_u_code, msg_d_t, msg_viewed, usr_u_code, usr_name from
        (select
            msgs.id as msg_id,
            msgs.cont as msg_cont,
            msgs.u_code as msg_u_code,
            msgs.d_t as msg_d_t,
            msgs.viewed as msg_viewed,
            usrs.u_code as usr_u_code,
            usrs.usr_name as usr_name
            from msgs FORCE INDEX (convo)
            left join usrs FORCE INDEX (u_code) on msgs.sender = usrs.u_code
    where  msgs.convo = 'aaaaaaaaaabfbaghdgcigfid_aaaaaaaaaabeiaccjfhjfach'
      and  (msgs.sender = 'aaaaaaaaaabfbaghdgcigfid'
              or  msgs.receiver = 'aaaaaaaaaabfbaghdgcigfid'
           )
      and  msgs.stat = '1'
      and  usrs.stat = '1'
      and  usrs.u_code not in('aaaaaaaaaabfaagfbgggiejh',
                              'aaaaaaaaaabfabgbjdfjigbd',
                ...... !!!!![here go 400 more usr_u_codes]!!!!!
                          )
      and  msgs.id > 30997997
        ) a order by msg_id asc;

Slow query log proved this solution to be effective as no new slow entries emerge.

3
  • Your solution is a band-aid and when your data changes it may become sub-optimal. But, hey, it's stuff like this that keeps us in work, eh? ;-)
    – Vérace
    Commented Nov 16, 2017 at 11:15
  • @Vérace true :)
    – John Doe
    Commented Nov 16, 2017 at 11:49
  • If you could tell us A. how much RAM exists on your host and post text results of B. SHOW GLOBAL STATUS, C. SHOW GLOBAL VARIABLES; and D. SHOW ENGINE INNODB STATUS; some suggestions for smoothing your servers workload will be posted. Commented Nov 19, 2017 at 0:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.