CODEWITHSUNDEEP
mysqlsqljoin
meder omuraliev
meder omuraliev

Reputation: 186572

Optimize this double left join query?

I'm modifying my original query which is just pulling reservations from the bookings table to join to a table of units which joins to a table of buildings in order to retrieve the building names and unit id for each booking.

I have created a SQLFiddle for this with the explain prefixed to the query. Per @Alex, I added an index on bookings.unit_number which in the SQLFiddle is bookings.external_system_id. ( Ignore the secondary field ).

This is still taking some time ( minutes ) when I do a full export for all bookings. I'm just trying to optimize this as best I can as I realize a full export will still take awhile ( total of 13019077 results ).

Upvotes: 2

Views: 95

Answers (1)

O. Jones
O. Jones

Reputation: 108706

You're dealing with a complex aspect of SQL. You might want to read http://use-the-index-luke.com

With respect, your statement

There are filters for date range and property but these are optional

is likely to make any advice you get from us entirely worthless unless you show us what those filters look like. But, still, I'll try to help.

Create compound covering indexes on units (unit_number, id) and buildings(id,name) and see if performance improves. These compound indexes should help performance. If you're filtering on bookings.arrival then you might also create a compound index on bookings (arrival, unit_number) for the same purpose.

In InnoDB there's no point to putting the primary key as the last column in a compound index; MySQL puts the equivalent on every index. In MyISAM you should put the PK there if your query needs it. Compound indexes that start with the primary key, or otherwise contain it, are still potentially useful.

A collection of single-column indexes cannot fulfill the same purpose as a well-chosen compound index. Having lots of indexes increases the chance that the query planner will find a useful one for a given query. But covering indexes designed for your queries can radically speed things up.

The space consumed by indexes is very roughly proportional to the amount of data they contain. Hard drive space is astonishingly cheap these days.

In your Sql Fiddle it's clear you should change the indexing on your units table. Get rid of the single-column index on external_system_id and replace it with this compound index.

INDEX `units_joindex` (`external_system_id`, `building_id`)

Like so: (http://sqlfiddle.com/#!9/e5ffd/2/0). Be aware that EXPLAIN results, and the query plans, depend heavily on the contents and number of rows in the tables as well as the indexes. Queries like yours necessarily must pull a whole table, so you won't see much index exploitation there.

Upvotes: 4

Related Questions