How to speed up sql queries ? Indexes?
database, mysql, optimization, sql
Solution
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:
- Create indexes on every field that is (or will be) used in joins.
- Create indexes on every field on which you want to perform frequent `where` conditions.
- Avoid creating indexes on everything. Create index on the relevant fields of every table, and use relations to retrieve the desired data.
- Avoid creating indexes on `double` fields, unless it is absolutely necessary.
- 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
Problem
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,