0

We recently did a big server update, in the process migrating from mysql-5.0 to 5.5.40 Several of the queries, which had been running well on the old server, now grind everything to a screeching halt. After some investigation, I figured out the following minimal example of a query that triggers the problem:

SELECT 
    (SELECT date FROM results 
        WHERE person_id = people.id 
        ORDER BY results.date DESC limit 1
        ) AS date
FROM people
WHERE people.boss_id = 123;

Some base info:

  • Both people and results are MyISAM tables
  • There are primary key ids on both tables and no other indexes
  • results is one of our larger tables, currently clocking in at about 4 million rows
  • people.boss_id = 123 filters down to ~3000 rows
  • The above the query runs in about 4 seconds on the older mysql-5.0 server
  • On the newer, mysql-5.5 server the same type of query on the same dataset has run for up to three hours before I manually killed the process, during which it reported as "sending data" My research suggests that that means that the database is filtering the data, but I'm not 100% sure on that.
  • Running the above query on the newer server produces the same results: stuck query "sending data" until I manually kill the process.
  • Both tables work fine on their own
  • I can actually run a single isolated version of the subquery (ie where person_id = 123 or whatever). It takes a few microseconds on the newer server.

Here's what an EXPLAIN EXTENDED looks like:

select_type         table    type   possible_keys   key         key_len     ref     rows    filtered    Extra   
PRIMARY             people   ref    boss_id         boss_id     4           const   4217    100.00      ----
DEPENDENT SUBQUERY  results  index  boss_id         date        32          NULL    1       1700.00     Using where

So, any ideas on what's causing this? I'm more than a little stuck. Have I run into some sort of bug? Could upgrading to mysql-5.6 possibly fix this (it's got a new query planner, after all)? Might changing one or both of the tables to InnoDB help? Would repairing one table or the other help?

Incidentally, one of the annoying side effects of this problem is that it locks up phpmyadmin. It's a little unnerving, actually. The commandline works fine and well, but phpmyadmin will just sit there "[loading]" until I kill the rogue process via command line. Any ideas on why a stuck query might do that?

  • date is a keyword. Assuming that's part of your actual DB, try backtick escaping those columns (`) or quote escaping those (") – mpag Jul 7 '16 at 20:08
1

This does not work in MYSQL?
Give it a try - what do you have to lose?

SELECT people.id, max(results.date) 
FROM people 
JOIN results 
      on people.id = results.person_id 
     and people.boss_id = 123 
GROUP BY people.id;

Would benefit from indexes on:
1) results.person_id
2) people.boss_id

Index on date may help but start without

  • INDEX(date) would not help, but (person_id, date) would (because it is 'covering'). INDEX(boss_id, id) would be better (because it is 'covering') than just (boss_id) since it is MyISAM. – Rick James Jan 15 '16 at 6:46
0

Subqueries are not necessarily well optimized. This is a "correlated" subquery, which makes it difficult to reformulate.

Please show us SHOW CREATE TABLE for each table -- there could be subtle issue, such as datatypes.

Try this 'compound' index on results: INDEX(person_id, date)

If that does not help, let's try to turn the query inside-out.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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