Using filesort to order by price column in MySQL

Question

I have a query which is a bit more complicated than this one (getting cheapest price) but this represents the performance problem good. So the query cannot change.

I tried creating different indexes for speeding up ordering without luck.

What index is needed to get this 1.4 seconds lasting query quicker? If i remove the ORDER BY the query lasts 0.05 seconds, but i need the ordering.

Query

SELECT id AS pid                    
  FROM prices pt                    
  WHERE pt.id = (    
      SELECT pt2.id                        
      FROM prices pt2                          
      WHERE pt2.oid=pt.oid                         
      ORDER BY pt2.price                           
      LIMIT 1
    )

Explain

1   PRIMARY pt  index   NULL    id_price    12  NULL    9144    Using where; Using index
2   DEPENDENT SUBQUERY  pt2 ref oid,oid_price   oid 4   oid 23  Using where; Using filesort

Indexes

PRIMARY     PRIMARY 9144    id
price       INDEX   703     price
oid         INDEX   397     oid
id_price    INDEX   9144    id,price,oid
oid_price   INDEX   4572    oid,price

Answer 1

It is very rare that a query can't change; without seeing the original, it is hard to say for sure. For example, it looks like you are trying to get a list of id's where those id's are the lowest prices for their "oid".

The query you've given as an example could easily be rewritten to not need a correlated subquery; correlated subqueries are often the source of performance issues.

SELECT p.id 
FROM (SELECT oid, MIN(price) AS minPrice FROM prices GROUP BY oid) AS mins
INNER JOIN prices AS p ON mins.oid = p.oid AND mins.minPrice = p.price
;

An index on (oid, price) would likely be all that was needed to make the above query fast.

Note: the only difference in the results should be this one can return multiple id values for the same oid value if there is more than one entry with the least price; instead of effectively randomly picking one of the id values with the least price.