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. <\/p>\n
It didn't.<\/p>\n
For a very, very stupid reason on my part. <\/p>\n
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.<\/p>\n
After creating the index: <\/p>\n
\nmysql> select count(*) from term t left join term_synonym ts on t.term_id = ts.term_id;\n+----------+\n| count(*) |\n+----------+\n| 156746 |\n+----------+\n1 row in set (1.72 sec)<\/pre>\nUm, ya, I suck.<\/p>\n","protected":false},"excerpt":{"rendered":"
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….<\/p>\n