A few weeks ago I faced a performance issue around a lookup query on a 5GB MySQL database. About 10% of these request take 100x more time than the others. Syntax was really simple:
SELECT `authors`.* FROM `authors` WHERE `authors`.`remote_id` = 415418856 LIMIT 1;
Actually it wasn’t a real “slow query”. Table is quite large (3M+ record) and a response time of 300ms was reasonable. Unfortunately my application run this query thousands times each minute and performances were degraded. The
remote_id is a standard
varchar field indexed by a unique Hash index and I couldn’t understand why these queries take so much time.