How to speed up sql queries? Indices?

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 in these tables, and I'm trying to optimize the query to run as quickly 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 to make this request as fast as possible? Should I use indexes? If so, in which columns should tables be indexed?

Here is an image of my query plan:

Thanks,

Answer 1

Indexes are required for any database.

Speaking in terms of “layman,” indexes ... well, that’s it. You can imagine the index as a second, hidden table, in which two things are stored: sorted data and a pointer to its position in the table.

Some thumb rules for creating indexes:

• Create indexes for each field that (or will be) used in joins.
• Create indexes in each field in which you want to fulfill the frequent where clauses.
• Avoid creating indexes for everything. Create an index in the corresponding fields of each table and use the relations to obtain the desired data.
• Avoid creating indexes in double fields if absolutely necessary.
• Avoid creating indexes in varchar fields unless absolutely necessary.

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

Answer 2

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

You should also consider indexing Shipments.MarketPlace , Shipments.ShipmentChannel and Shipments.Component .

However, adding indexes increases the workload of maintaining them. Although they may give you better performance with this query, you may find that updating tables becomes unacceptably slow. In any case, the MySQL optimizer may decide that a full table scan is faster than accessing it by index.

In fact, the only way to do this is to tune the indexes, which seem to give you the best result, and then evaluate the system to make sure that you get the results you want here, but without compromising performance elsewhere. Make good use of the EXPLAIN statement to find out what is going on, and remember that optimizations made by yourself or the optimizer on small tables may not be the same optimizations you need for larger ones.

Answer 3

The other three answers seem to contain indexes, so this is in addition to the indexes. You have no where clause, which means you always select the entire darn database. In fact, your database design has nothing useful in this regard, such as delivery dates. Think about it.

You also have the following:

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

This is all good and good, but you do not select any of this subquery. So why do you have this? If you want to select something, for example, the amount (amount), you will need to provide this alias to make it available in the select clause.