mysql - How to speed up sql queries ? Indexes?

Question

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 :

Thanks,

Answer 1

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