1

I'm trying to learn something about Optimizing and indexes because I ran a insert select-query that required 4 min to complete. Now, I've added multiple indexes and it seems to have made my query run in 0.160sec. Now what I'm wondering is why the customer table is getting the using filesort message when i'm ordering by orderdate in my order table. Query and explain:

enter image description here

I've even tried an index in O(Orders) for (orderdate, orderid) and (orderdate, orderid, customerid). I thought one of them would help, but no dice. Can anyone help me understand why?

Improve this question
4
  • It's very unlikely you'll be able to optimize ORDER BY in this particular query. PS: always put SHOW CREATE TABLE for questions about optimization Commented Sep 13, 2012 at 20:49
  • kk. any reason why it's unlikely?(I want to learn :) ). what should include NeXT time? SHOW CREATE TABLE for all tables used or is ex. importorders the most important one? Commented Sep 13, 2012 at 20:58
  • It's unlikely because your "main" table (the one that's used in FROM) is products P and you're sorting by columns from another table orders o. PS: just to make sure you've created correct indexes we need to know the current tables structure Commented Sep 13, 2012 at 21:00
  • kk. the "main" table is importorders(not Products). Also, I've just started to read about Optimizing(10 mins ago :P) so the indexes is created just by looking at which columns are being used in which table(and try to collect those that make it almost as unique as the primary). Commented Sep 13, 2012 at 21:04

1 Answer 1

Reset to default
1

There is nothing wrong with having a query that uses "filesort"; all that means is that the results can't be sorted based on an index.

Now the reason why the sort can't be performed on an index is in this case because your ORDER BY contains columns from tables other than the first table in the join queue.

Since your query result doesn't contain very many rows, the temporary table being used is probably in memory.

What happens is as the query results are fetched from that query is that the results are put into a temporary table so they can later be sorted.

Adding the initial indexes sped up your query most likely because MySQL was doing a full table scan to fetch the results initially which was very time consuming. Once you added the proper indexes, finding the records is extremely quick. It probably had to do a filesort on a temporary table originally but this was likely no slower or faster than it is now.

If you try moving the join for the Orders table and put it before the join of the Products table, you may be able to eliminate the use of the temporary table and file sort.

Check out what does using filesort mean? and How MySQL Uses Internal Temporary Tables for more information.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

ohh. I didn't think the join order was important, just as long as I had the correct values to compare the NeXT join With. I'll try this. Now isn't the "main table" the one in from(importorders)? why is it the first join, or did was the "first join" the from table? EDIT: moving join order was non-successfull. but as you said, it's maybe not very time consuming
It's probably worth mentioning that "adding more indexes" at random is not a good idea. Every index you add dramatically slows down inserts and updates and takes up disk space.
I'm trying to figure out what makes a good index. So after I Discover what indexes are being used by my 2 worst queries(extracting orders and orderlines), I delete the unused ones :)
@Graimer You may want to work backwards from what you have. Instead of adding too many indexes, look at your slow queries and figure out why they are slow and then add an index. Read here for an understanding on what indexes are and how they are used. It's really quite simple. Basically you should have a good index for any data in the WHERE clause or that is being joined on. If you don't have an index on a column being used to fetch information, then MySQL has to examine the whole table to find that information.
IRT moving the JOIN towards the beginning, I said that because of the statement from the "How MySQL Uses Internal Temporary Tables": Temporary tables can be created under conditions such as these: If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created. It may be that since you are joining from several other tables, it has to put all the results in a temp table. But likely the temporary table is in memory so its not a problem.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.