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;
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!