CODEWITHSUNDEEP
mysqlsqldatabaseoptimization
Attilah
Attilah

Reputation: 17932

How to speed up sql queries ? Indexes?

I have the following database structure :

create table Accounting
(
  Channel,
  Account
)

create table ChannelMapper
(
  AccountingChannel,
  ShipmentsMarketPlace,
  ShipmentsChannel
)

create table AccountMapper
(
  AccountingAccount,
  ShipmentsComponent
)

create table Shipments
(
   MarketPlace,
   Component,
   ProductGroup,
   ShipmentChannel,
   Amount
 )

I have the following query running on these tables and I'm trying to optimize the query to run as fast as possible :

 select Accounting.Channel, Accounting.Account, Shipments.MarketPlace
 from Accounting join ChannelMapper on Accounting.Channel = ChannelMapper.AccountingChannel

 join AccountMapper on Accounting.Accounting = ChannelMapper.AccountingAccount
 join Shipments on 
 (
     ChannelMapper.ShipmentsMarketPlace = Shipments.MarketPlace
     and ChannelMapper.AccountingChannel = Shipments.ShipmentChannel
     and AccountMapper.ShipmentsComponent = Shipments.Component
 )
 join (select Component, sum(amount) from Shipment group by component) as Totals
    on  Shipment.Component = Totals.Component

How do I make this query run as fast as possible ? Should I use indexes ? If so, which columns of which tables should I index ?

Here is a picture of my query plan :

enter image description here

Thanks,

Upvotes: 13

Views: 38315

Answers (3)

Dan Bracuk
Dan Bracuk

Reputation: 20804

The other three answers seem to have indexes covered so this is in addition to indexes. You have no where clause which means you are always selecting the whole darn database. In fact, your database design doesn't have anything useful in this regard, such as a shipping date. Think about that.

You also have this:

join (select Component, sum(amount) from Shipment group by component) as Totals
on  Shipment.Component = Totals.Component

That's all well and good but you don't select anything from this subquery. Therefore why do you have it? If you did want to select something, such as the sum(amount), you will have to give that an alias to make it available in the select clause.

Upvotes: 2

Barranka
Barranka

Reputation: 21057

Indexes are essential to any database.

Speaking in "layman" terms, indexes are... well, precisely that. You can think of an index as a second, hidden, table that stores two things: The sorted data and a pointer to its position in the table.

Some thumb rules on creating indexes:

  1. Create indexes on every field that is (or will be) used in joins.
  2. Create indexes on every field on which you want to perform frequent where conditions.
  3. Avoid creating indexes on everything. Create index on the relevant fields of every table, and use relations to retrieve the desired data.
  4. Avoid creating indexes on double fields, unless it is absolutely necessary.
  5. Avoid creating indexes on varchar fields, unless it is absolutely necesary.

I recommend you to read this: http://dev.mysql.com/doc/refman/5.5/en/using-explain.html

Upvotes: 27

user1864610
user1864610

Reputation:

Your JOINS should be the first place to look. The two most obvious candidates for indexes are AccountMapper.AccountingAccount and ChannelMapper.AccountingChannel.

You should consider indexing Shipments.MarketPlace,Shipments.ShipmentChannel and Shipments.Component as well.

However, adding indexes increases the workload in maintaining them. While they might give you a performance boost on this query, you might find that updating the tables becomes unacceptably slow. In any case, the MySQL optimiser might decide that a full scan of the table is quicker than accessing it by index.

Really the only way to do this is to set up the indexes that would appear to give you the best result and then benchmark the system to make sure you're getting the results you want here, whilst not compromising the performance elsewhere. Make good use of the EXPLAIN statement to find out what's going on, and remember that optimisations made by yourself or the optimiser on small tables may not be the same optimisations you'd need on larger ones.

Upvotes: 3

Related Questions