samedi 25 avril 2015

How to specify sort order on multiple self joins


I've an attribute table like

CREATE TABLE attributes (
    attribute_id INT,
    product_id INT,
    random INT,
    UNIQUE KEY (attribute_id,random,product_id),
    KEY (product_id)
);

random is a random integer number calculated on insert for shuffling products (that's O.K. for my needs). There are self-join queries like

SELECT DISTINCT x.product_id
FROM attibutes x
INNER JOIN attributes y ON x.product_id=y.product_id
INNER JOIN attributes z ON x.product_id=z.product_id
WHERE x.attribute_id IN (20000085,20000090) AND
    y.attribute_id IN (10000007) AND
    z.attribute_id IN (30000050,30000040,30000012)
LIMIT 0,100;

As you can see I want to select products which have at least one attribue in each number range. MySQL is so clever to choose table alias for first query itself, depending on selectivity of UNIQUE key. As expected the result is sorted in order of column random because of UNIQUE key. But how can I advise MySQL to revert the order? When adding ORDER BY x.random DESC it could happen that MySQL uses filesort for ordering because if it uses table alias y for base query (because of better selectivity of attribute ID 10000007) it has to use UNIQUE key of alias x. The problem is: I don't know which alias MySQL does use (it's decided by its query optimizer). So how to specify order direction?

(I want to note that table contains about 60 million rows, so usage of filesort or not would be significant in response time)


Aucun commentaire:

Enregistrer un commentaire