Query results taking too long on 200K database, speed up tips?

Question

I have a sql statement where I'm joining about 4 tables, each with 200K rows. The query runs, but keeps freezing. When I do a join on 3 tables instead, it returns the rows (takes about 10secs). Any suggestion why? suggestions to speed up?

Thanks!

Code

SELECT *
FROM equipment, tiremap, workreference, tirework
WHERE equipment.tiremap = tiremap.`TireID` AND 
      tiremap.`WorkMap` = workreference.`aMap` AND
      workreference.`bMap` = tirework.workmap
LIMIT 5

p.s

and if it helps any, I'm using sql alchemy to generate this code, the sqlalchemy code for this is

query = session.query(equipment, tiremap, workreference, tirework)
query = query.filter(equipment.c.tiremap == tiremap.c.TireID)
query = query.filter(tiremap.c.WorkMap==workreference.c.aMap)
query = query.filter(workreference.c.bMap == tirework.c.workmap)
query = query.limit(5)
query.all()

Answer 1

Make sure you have indexes on:

• equipment (tiremap)
• tiremap (TireID)
• tiremap (WorkMap)
• workreference (aMap)
• workreference (bMap)
• tirework (workmap)

Edit: I guess I should provide some context to that for completeness.

The SQL optimizer looks at a statement, parses it and then determines an execution plan for it based on the query, the tables referenced and available indexes. If you do SELECT * FROM tab1 then it'll do a full table scan of tab1 because there's no other way to execute that.

If you do SELECT * FROM person WHERE lastname LIKE 'V%' and you have a million records, it will be slow to interrogate every row but if lastname is indexed it's much more efficient.

With a query like yours one of those tables will be the driving table that regardless of indexes may simply be done as a full table scan. There's nothing wrong with this. One table has to drive the query. If there is a WHERE clause (for something other than join conditions) this may change but otherwise it's generally true.

From that driving table, MySQL will then start appending joins to the execution plan. These joins will require indexes on the other side to make this work efficiently.

So with three tables you may have one table that's not indexed but it doesn't matter because it drives the query. With the fourth table, there may be two unindexed tables and that's now a problem because for each row in one MySQL will have to do a full table scan of the other.

So basically you create an index on every foreign key and join column so MySQL can use what's available to make the best execution plan for the query you give it.

Lastly, most tools will tell you about the database schema. PHPMyAdmin is a popular one for hosted databases. Personally I actually like a desktop app for this kind of thing. Navicat Lite is a decent free tool for this.