ORDER BY clause slows down query-CodePudding

I have these two tables.

Products:

Assets:

Result with ORDER BY clause return in 44 seconds.

SELECT p.id, i.filepath, p.TotalDownloads 
FROM products p 
INNER JOIN assets i ON i.hash=p.product_id 
WHERE p.Name like '%rar%' AND i.format='ico' 
ORDER by p.TotalDownloads DESC 
LIMIT 0, 10

Showing rows 0 - 9 (10 total, Query took 44.0769 seconds.) [TotalDownloads: 164554096... - 724290...]

AND without ORDER BY clause, results return in 1 second.

SELECT p.id, i.filepath, p.TotalDownloads 
FROM products p 
INNER JOIN assets i ON i.hash=p.product_id 
WHERE p.Name like '%rar%' AND i.format='ico' 
LIMIT 0, 10

Showing rows 0 - 9 (10 total, Query took 1.0668 seconds.)

Indexes:

CodePudding user response：

Don't use LONGTEXT if you don't need it. Have you ever seen a "name" with millions of characters? VARCHAR(100) is probably overkill. Also, why is "price" not some numeric datatype?

To make use of the following index suggestions, you must avoid any form of TEXT if possible.

My index recommendations are:

p:  INDEX(product_id, Name, TotalDownloads,  id)
p:  INDEX(TotalDownloads,  Name, product_id, id)
p:  INDEX(Name, product_id, TotalDownloads,  id)
i:  INDEX(format, hash,  filepath)

I list multiple arrangements for p; simply add all of them and let the Optimizer pick which to use. It may even use a different for different data values.

All of those are "covering", which gives some performance boost.

The main drawback in optimizing the query is that the WHERE has limitations on both tables. The Optimizer cannot necessarily decide which table to start with. Or it might decide to start with the ORDER BY instead; this saves a sort.

Since there is no one index to get all the way through the WHERE and ORDER BY, all the possible rows must be gathered, then sorted, and finally 10 delivered.

Since you may need a longer "filepath" than indexing would allow, leave it as TEXT and remove it from my index suggestion.