Appreciation post: Finally fixed that database query that took me 6 hours

I spent like 6 hours last Tuesday trying to figure out why a simple SQL join kept timing out. Turned out I was pulling in 50,000 rows from a table without an index on the foreign key. Added the index and it ran in 2 seconds. Has anyone else had a bug that felt embarrassing once you found the actual cause?

2 comments

2 Comments

tessa3686d ago

OH the embarrassment is REAL when you find it. But actually, adding an index doesn't always fix the root problem if you're pulling too many rows in the first place. 50k rows is NOTHING for a proper join, so I'm wondering if you also had a missing WHERE clause or something filtering on the unindexed column. Like I once had a query that joined 3 tables with 2 million rows each, ran in 30 seconds after an index. But then I realized I was doing a cross join by accident and just hid the evidence with the index. Sometimes the index just hides the REAL mess underneath.

victor_carr256d ago

Cross join or cartesian product hiding in there?