Geek Answers Handbook

MySQL How to optimize this query?

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

Basically, I join the current user, acl groups, to get all the groups they are in, then join the groups to companies to get all the companies they are in, and then join the companies to orders to get all orders.

If I delete this line

ORDER BY orders.created_on DESC

then the request is executed in 0.06 s (more than acceptable).

Help, any ideas for optimization? 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, request explanation.

1 SIMPLE orders ALL 9403 Use temporary; Using filesort

1 SIMPLE acl ALL 1859 Use where; Using the connection buffer

1 SIMPLE g ALL 2005 ;

1 eq_ref 52 table.orders.company_uuid 1

1 SIMPLE u ALL 33595 ; ;

+5
5

?

"--", :

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;

. , . , , .

, , , .

+2

, "orders.created_on" ... , , .

0

, .

, ... ?

? , ... .

, ?

0

, 2 . 10 ,

  • acl.user_uuid = u.uuid or acl.group_uuid = g.group_uuid

    UID, , .

  • ORDER BY orders.created_on. Order by date , PK .

  • orders.status <> '' - , , .

  • , , , - . .

, , , UID Joins , ,

0

:

In fact, you do not select orders.created_onin your query. Therefore, it makes no sense to sort by this column. Maybe selecting it ( SELECT orders.created_on...) will help performance (just a wild hunch - I have no idea what I'm saying here).

You can always sort in your application - if there is not a large number of records returned by your request.

Sometimes it makes more sense to use N small queries instead of one large sql query. Pseudocode:

user_id = get_one("SELECT uuid FROM users WHERE ...");
group_ids = get_many("SELECT uuid FROM groups WHERE user_uuid = " + user_id);
comps_ids = get_many("SELECT DISTINCT item_uuid FROM acls WHERE user_uuid = " + user_id + " OR group_uuid IN " + groups_ids.to_q());
orders = get_many("SELECT * FROM orders WHERE company_uuid IN " + comps_ids.as_q() + " WHERE status <> '' ORDER BY created_on");
0
source

More articles:


All Articles