How to speed up sql queries ? Indexes?

Solution 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

Solution 2:

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.