Been trying to wrap my head around a bug at work, why a simple database query never returned a result set and crashed the server. The query is a simple left join from a “term” table with 40K records to a “term_synonym” table with 140K records. It should be straightforward. It should work.
It didn't.
For a very, very stupid reason on my part.
I'd forgotten to create indexes on the tables and it was doing full table scans. That comes out to 5,600,000,000 iterations over the term_synonym table. No wonder it was slow.
After creating the index:
mysql> select count(*) from term t left join term_synonym ts on t.term_id = ts.term_id; +----------+ | count(*) | +----------+ | 156746 | +----------+ 1 row in set (1.72 sec)
Um, ya, I suck.