MySQL - How can this query be optimised?

Question

The following query works, but its very slow for 10 records (2 seconds). The profiling says its creating a tmp table, but I'm not sure why.

Basically, I'm joining the current user, to the acl groups, to get all groups they are in, then joining the groups to the companies, to get all companies they are in, then joining the companies to the orders, to get all the orders..

If I remove this line

ORDER BY orders.created_on DESC 

then the query executes in 0.06 secs (more then acceptable)..

Help, any ideas on how to optimize? Many thanks :)

SELECT
    orders.uuid,
    companies.name as company_name
FROM
    users u
JOIN    
    users_acl_groups g on   u.uuid = g.user_uuid
JOIN
    users_acl acl on (acl.user_uuid = u.uuid or acl.group_uuid = g.group_uuid) 
JOIN 
    companies on acl.item_uuid = companies.uuid
JOIN
    orders on companies.uuid = orders.company_uuid
WHERE
    u.uuid = 'DDEC8073-5056-C000-01ED583A51CBCA32' and orders.status <> ''
ORDER BY orders.created_on DESC 

limit 0, 10;

UPDATE, the explain of the query..

1 SIMPLE orders ALL 9403 Using temporary; Using filesort

1 SIMPLE acl ALL 1859 Using where; Using join buffer

1 SIMPLE g ALL 2005 Using where; Using join buffer

1 SIMPLE companies eq_ref PRIMARY PRIMARY 52 table.orders.company_uuid 1

1 SIMPLE u ALL 33595 Using where; Distinct; Using join buffer

Answer 1

Have you considered making a fact table style design, as a denormalization step?

Basically it's a kind of many-to-many intersection table, for example:

CREATE TABLE user_order_fact (
  user_uuid ...
  order_uuid ...
  order_created_on ...
  order_status ...
  company_name ...,
  primary key (user_uuid, order_uuid),
  key (user_uuid, order_status, order_created_on, order_uuid, company_name)
);

... fill with data ...

SELECT
    order_uuid,
    company_name
FROM
    user_order_fact
WHERE
    user_uuid = 'DDEC8073-5056-C000-01ED583A51CBCA32' and order_status <> ''
ORDER BY order_created_on DESC 

limit 0, 10;

I'm guessing on the compound index. You'll have to experiment until you get it right. Basically you're trying to get the optimizer plan to report that it's Using index.

Of course this is storing data redundantly and in denormalized form, so you need to set up some triggers to keep this in sync with the normalized tables.