Impact of MySQL Query

Question

I'm trying to figure out why my MySQL query is taking over a minute to run. I can't figure out if I'm programming something inefficiently or if I should expect lag times like this. Below is the code.

Keep in mind that there are between 10,000 and 30,000 records in most of the tables I'm searching through (shipments, shipment_financials, shipment_consignees, shipment_shippers).

Here is the code:

SELECT users.*, COUNT(DISTINCT shipments.shipment_id) as COUNT_SHIPMENTS, COUNT(DISTINCT clients.client_id) as COUNT_CLIENTS, SUM(shipment_financials.shipment_financial_revenue) as SUM_USER_REVENUE, SUM(shipment_financials.shipment_financial_cost) as SUM_USER_COST
FROM users
JOIN shipments ON shipments.shipment_details_sales_rep = users.user_id
LEFT JOIN shipment_financials ON shipment_financials.shipment_financial_shipment_id = shipments.shipment_id
JOIN clients ON clients.client_id = shipments.shipment_details_client
JOIN shipment_consignees ON shipment_consignees.shipment_consignee_shipment_id = shipments.shipment_id
JOIN shipment_shippers ON shipment_shippers.shipment_shipper_shipment_id = shipments.shipment_id

WHERE shipment_consignees.shipment_consignee_id = (
        SELECT MAX(shipment_consignees.shipment_consignee_id)
        FROM shipment_consignees
        WHERE shipments.shipment_id = shipment_consignees.shipment_consignee_shipment_id
    )
    AND shipment_shippers.shipment_shipper_id = (
        SELECT MIN(shipment_shippers.shipment_shipper_id)
        FROM shipment_shippers
        WHERE shipments.shipment_id = shipment_shippers.shipment_shipper_shipment_id
    )
    AND users.user_account_id = $account_id
    AND shipments.shipment_voided = 0
GROUP BY users.user_id
ORDER BY SUM_USER_REVENUE desc

Not running fast because: large numbers of records, 5 joins, multiple correlated subqueries. — Tim Biegeleisen, Jun 14 '16 at 05:29
@BradH read here: http://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html — andrew, Jun 15 '16 at 05:14
@andrew - Oh, so you mean to make something like shipment_id the primary column and use that to search? If so, yes, shipment_id is set to be the primary. — Brad H, Jun 16 '16 at 06:18
@BradH. Not just that. You can user_id also as index (since you use it i a WHERE clause and in GROUP BY. And all ids you use for JOINS and WHERE. Using indexes, speed up the proccess since the db has to look for relative data only in the specific index and not in all datarows of the table. Also, read more here: http://stackoverflow.com/questions/3567981/how-do-mysql-indexes-work — andrew, Jun 16 '16 at 07:35
The bottleneck is sure to be those correlated subqueries. Change those to uncorrelated subqueries, and everything should whoosh along. — Strawberry, Jun 16 '16 at 07:42

score 0 · Answer 1 · answered Jun 14 '16 at 05:35

Mainly the inline queries in the where statement is also slowing down the processing of the query. But if it is inevitable and you have to use the inline query then you might reduce the output of "JOIN" by doing "LEFT OUTER JOIN".

"users.*" is also fetching the whole records from the table, hence slowing it down, but still the Joining is the main issue here for slowing down of the query.

score 0 · Answer 2 · answered Jun 16 '16 at 06:20

0

It was the joins and multiple queries. The trick was to save the information I needed all in one table (the shipments table). That way I didn't have to join multiple tables.

Not the most fun way to accomplish it but man did it speed things up!

answered Jun 16 '16 at 06:20

Brad H

11
2

1

That doesn't sound like much of a solution :-( – Strawberry Jun 16 '16 at 06:22

score 0 · Answer 3 · edited Jun 16 '16 at 07:40

You may can explain you queries using following command

*explain SELECT 
        users.*
        ,COUNT(DISTINCT shipments.shipment_id) as COUNT_SHIPMENTS
        ,COUNT(DISTINCT clients.client_id) as COUNT_CLIENTS
        ,SUM(shipment_financials.shipment_financial_revenue) as SUM_USER_REVENUE
        ,SUM(shipment_financials.shipment_financial_cost) as SUM_USER_COST
FROM users
JOIN shipments 
    ON shipments.shipment_details_sales_rep = users.user_id
LEFT JOIN shipment_financials 
    ON shipment_financials.shipment_financial_shipment_id = shipments.shipment_id
JOIN clients 
    ON clients.client_id = shipments.shipment_details_client
JOIN shipment_consignees 
    ON shipment_consignees.shipment_consignee_shipment_id = shipments.shipment_id
JOIN shipment_shippers 
    ON shipment_shippers.shipment_shipper_shipment_id = shipments.shipment_id
WHERE shipment_consignees.shipment_consignee_id = 
    (
        SELECT MAX(shipment_consignees.shipment_consignee_id)
        FROM shipment_consignees
        WHERE shipments.shipment_id = shipment_consignees.shipment_consignee_shipment_id
    )
AND shipment_shippers.shipment_shipper_id = 
    (
        SELECT MIN(shipment_shippers.shipment_shipper_id)
        FROM shipment_shippers
        WHERE shipments.shipment_id = shipment_shippers.shipment_shipper_shipment_id
    )
AND users.user_account_id = [TEST ACCOUNT ID]
AND shipments.shipment_voided = 0
GROUP BY users.user_id
ORDER BY SUM_USER_REVENUE desc;*

that will help you to optimize your queries

Impact of MySQL Query

3 Answers3