Mysql very slow order by - filesort problem

Hello!

I have a normal sized mysql table (~100k records), and a prety simple select query.

The main problem is that I have to order my resultset by a calculated value (eur_price), and according to mysql-faq (http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html) I should index the order by coloumn, which - being calculated - cannot be done.
Because of that, mysql uses filesort ordering my resultset, which can take up to 40sec, which I cannot afford.
I cannot use temporary tables, extra fields, I have only read privilige on tha database.

Any workaround making faster my query should help.
SELECT sales.id, sales.price/conversion.rate AS eur_price
FROM sales
LEFT JOIN conversion ON ( sales.currency = conversion.curr_destination )
WHERE sales.country = 'hu'
AND conversion.curr_source = 'eur'
ORDER BY eur_price ASC LIMIT 100

Open in new window

LVL 1
veszpaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
note:
AND conversion.curr_source = 'eur'
in the where clause makes your LEFT JOIN implicitly a INNER JOIN.
is that what you want? -> use INNER JOIN directly.
if not:
SELECT sales.id, sales.price/conversion.rate AS eur_price
FROM sales
LEFT JOIN conversion ON ( sales.currency = conversion.curr_destination AND conversion.curr_source = 'eur' )
WHERE sales.country = 'hu'
ORDER BY eur_price ASC LIMIT 100

Open in new window

0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ps: this filesort is normal, as it has to sort by a computed column, hence no indexing can help
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
veszpaAuthor Commented:
Yes you are right, thats because I am using an ORM, and has no ability to use a complex join, but I tried also your suggestion, the result is the same, mysql uses filesort.

The main solution would be avoiding filesort, being very slow.

I am afraid that in mysql we cannot avoid filesort in case of an order by a  calculated coloumn.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will need to denormalize the database design, by copying the conversion.rate applied for the sale in the sales table itself, aka the eur_price...
0
TKorhanCommented:
If you can use, try INNER JOIN
0
veszpaAuthor Commented:
The problem is not within the "JOIN part" at all, it dose not matter where the calculated coloumn comes from, the only problem is that we can't index it for the order by.

@angelIII: I can't change anything in the db schema..
0
mrjoltcolaCommented:
Can't you order by the sales.price column directly? Regardless of the conversion rate, the ordering should be the same. Currency conversion doesn't affect the order if all records are having the same conversion.

Besides that, unless mysql add's function based indexes (like Oracle), there is no index for mysql to use in this case. If you have only readonly access to the database, the problem is in the hands of the DBA. :(
0
veszpaAuthor Commented:
@mrjoltcola:
Of course the conversion affects the order, just imagine i have a price column, and a currency one, with EUR, HUF, USD etc., so the ordered list of the price column would cause for example that 2 HUF is greater than 1 USD.. So I can't ignore the conversion process.
0
mrjoltcolaCommented:
>>Of course the conversion affects the order, just imagine i have a price column, and a currency one, with EUR, HUF, USD

Your query only shows one currency at a time, correct? In the query above, you have one source and one destination, or that is how I read it. 'eur' to 'hu'

So where does USD come in?

If I display a price list in a single currency, ordering before or after the conversion doesn't matter.

If I display a price list of mixed currencies, ordering by the post-conversion price is a false ordering, as it is ordering by the lexical value, not the actual value.
0
mrjoltcolaCommented:
>>LEFT JOIN conversion ON ( sales.currency = conversion.curr_destination )

Am I to assume that sales.currency can vary, even if the sales.country is 'hu' ?
0
veszpaAuthor Commented:
Yes, the currency can vary thru every record, the country criteria is non determinant this time.
-> so it can be USD as well, I am converting each price to a common currency, (now EUR, but it dosen't tmatter) just to have a linear and sortable set of values, that can be used in order by.
0
mrjoltcolaCommented:
Then, unfortunately, since you have no access to modify the schema, you are limited to the indexes that are already in place, and the ability to limit your result set. Limiting your result set may improve, but that may also not be possible. The fact that the base result set must be ordered prior to the LIMIT clause, means the ordering must happen in completion.

Essentially the database is designed poorly and should be fixed, and it cannot necessarily be resolved by a query.
0
mrjoltcolaCommented:
When I say "limiting your result set" in this case, I mean by adding predicates. I am not referring to the LIMIT 100 clause. Is there any criteria you can add that will reduce the records matched?
0
Ray PaseurCommented:
It looks like this Q is pretty well covered but going forward, consider using EXPLAIN to see what your queries are doing.  You might also want to ask for CREATE TABLE permissions so you can make some tests by creating temporary tables and comparing the speeds of different query structures.

Best of luck with it, ~Ray
0
Tomas Helgi JohannssonCommented:
     Hi!

If you have privileges on the conversion and sales table to create indexes I suggest you
create and index (curr_source ,curr_destination, rate) all in ascending order on the conversion table
and on the sales table the index (country, currency, price) all in ascending order.

After that you run optimize and analyze on the tables.
http://dev.mysql.com/doc/refman/5.1/en/table-maintenance-sql.html

Regards,
   Tomas Helgi
0
mrjoltcolaCommented:
That is the point of the question, OP says he cannot create indexes or otherwise modify the schema.
0
mrjoltcolaCommented:
The correct answer is: "You cannot do that" and should not be deleted. Actually questions like this are asked frequently, and the basic truth is when you have no privileges to tune the schema, you have few options.
0
mrjoltcolaCommented:
The OP asked how to solve without any physical changes to his DB. So for that reason, the answer has to be, you cannot do it, but to fix, the way to do it will involve removing the constraint of not changing the schema.

My recommendation:

http:#24490448 (angelIII - 350 pts - main answer, cannot do it given OP's constraints)
http:#24492229 (mjc - repeating and expounding 50 pts)
http:#24492788 (Ray - options - 50 pts)
http:#24501743 (Tomas - what really should be done - 50 pts)


mrjoltcola
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.