0

This query works fine

SELECT MAX(customers.created_at) lst_trx_date, business_infos.id, business_infos.name as business_name, users.name as owner_name  FROM users
    INNER JOIN business_infos ON users.id = business_infos.user_id
    INNER JOIN customers on business_infos.id = customers.businessId
    GROUP BY customers.businessId
    ORDER BY customers.created_at DESC

but this query getting an eerror

SELECT MAX(orders.date) lst_trx_date, business_infos.id, business_infos.name as business_name, users.name as owner_name  FROM users
    INNER JOIN business_infos ON users.id = business_infos.user_id
    INNER JOIN orders on business_infos.id = orders.businessId
    GROUP BY orders.businessId
    ORDER BY orders.created_at DESC

1st query have small data and 2nd query have a large data what should i need to consider ?

3
  • Check your disk volume for free space presence. Check innodb_data_file_path option setting for autoextend is present and max is absent or is set to large enough value. Also check that tmp_table_size and max_heap_table_size are large enough.
    – Akina
    Oct 26, 2022 at 4:14
  • It's more likely that tmpdir points to a too-small disk partition.
    – Rick James
    Oct 26, 2022 at 4:26
  • thank you guys the for the answer!
    – Nullified
    Oct 26, 2022 at 4:30

1 Answer 1

0

It's more likely that tmpdir points to a too-small disk partition.

These indexes may help:

customers:  INDEX(businessId,  created_at)
orders:  INDEX(businessId,  date, created_at)
business_infos:  INDEX(user_id,  id, name)

Even better might be to rearrange the query to something like this:

SELECT ...
    FROM ( SELECT ... GROUP BY ... ) AS x
    JOIN the other tables
    ORDER BY ...

What you have suffered from "explode-implode" because all the JOINing ('explode') is done before the GROUP BY (implode). What I am proposing starts with 'implode'.

This will also shrink the tmp table requirements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.