[Solved] How to speedup MySQL query on 3 tables ( around 60M rows together )

EverSQL Database Performance Knowledge Base

How to speedup MySQL query on 3 tables ( around 60M rows together )

Database type:

right now I have application where I have 4 select from 3 mysql tables where always next one is base on data I get from previous.

Selects:

// Search by single input from user, the VIN

SELECT * FROM axnmrs_cases WHERE vin = :vin ORDER BY date_created DESC

Okey now I have multiple data about all cases with this VIN now I for each of this case make this kind of search:

// case_id and country are from axnmrs_cases table
SELECT * FROM axnmrs_calculations WHERE case_id = :case AND country = :country ORDER BY calculation_id DESC LIMIT 1

After all of this I try to find some detail information in 3th table :

// calculation_id is from axnmrs_calculations and case_id is same as previous
SELECT text FROM axnmrs_positions WHERE calculation_id = :calculationid AND case_id = :case_id AND repairmethod LIKE 'L%' LIMIT 60
// and this:
SELECT text FROM axnmrs_positions WHERE calculation_id = :calculationid AND case_id = :case_id AND repairmethod = 'E' AND guidenumber != 'N/A

The question is how to write single statetment to get all of this pretty fast. I already try to write query which do this for me, however I didn't even finish it because code below already take like 5 minutes:

SELECT c.vin, c.case_id, c.axrmrs_id, c.insurer_memberid, c.country, c.date_created, c.totalloss, c.lastcalc_manufacturer_code, c.audavin_triggered, c.accident_date, c.registration_date, c.manufacturing_year, cl.totalcosts, cl.laborhours, cl.laborcosts, 
  GROUP_CONCAT(DISTINCT IF(po.repairmethod LIKE 'L%',po.text,NULL) ORDER BY 1) AS textL,
  GROUP_CONCAT(DISTINCT IF(po.repairmethod LIKE 'E%',po.text,NULL) ORDER BY 1) AS textE
FROM axnmrs_cases AS c 
  LEFT JOIN axnmrs_calculations as cl on c.case_id = cl.case_id 
  LEFT JOIN axnmrs_positions as po on c.case_id = po.case_id 
WHERE c.vin='U5YFF24128L064909' 
  GROUP BY c.vin, c.case_id, c.axrmrs_id

The reason of this may be number of rows of table

#1 cases - ~3 486 114
#2 calculations - ~2 061 554
#3 positions - ~55 078 708

Guys I need this to create API for frontend however I dont want to select again multiple times to table.

Is there some way how to speedup my query? Right now I have indexes on all "WHERE" selecting columns.

Thank you for any advise

EDIT: I'm adding list of indexes:

cases:
     -id
     -vin
calculations:
     -case_id
positions:
     -calculation_id

http://www.upnito.sk/0/gnwfh8ug9hsnhtj28sa98rwhcdh46qes.png

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.
Optimal indexes for this query:
ALTER TABLE `axnmrs_calculations` ADD INDEX `axnmrs_calculation_idx_case_id` (`case_id`);
ALTER TABLE `axnmrs_cases` ADD INDEX `axnmrs_cases_idx_vin_case_id_axrmrs_id` (`vin`,`case_id`,`axrmrs_id`);
ALTER TABLE `axnmrs_positions` ADD INDEX `axnmrs_positions_idx_case_id` (`case_id`);
The optimized query:
SELECT
        c.vin,
        c.case_id,
        c.axrmrs_id,
        c.insurer_memberid,
        c.country,
        c.date_created,
        c.totalloss,
        c.lastcalc_manufacturer_code,
        c.audavin_triggered,
        c.accident_date,
        c.registration_date,
        c.manufacturing_year,
        cl.totalcosts,
        cl.laborhours,
        cl.laborcosts,
        GROUP_CONCAT(DISTINCT IF(po.repairmethod LIKE 'L%',
        po.text,
        NULL) 
    ORDER BY
        1) AS textL,
        GROUP_CONCAT(DISTINCT IF(po.repairmethod LIKE 'E%',
        po.text,
        NULL) 
    ORDER BY
        1) AS textE 
    FROM
        axnmrs_cases AS c 
    LEFT JOIN
        axnmrs_calculations AS cl 
            ON c.case_id = cl.case_id 
    LEFT JOIN
        axnmrs_positions AS po 
            ON c.case_id = po.case_id 
    WHERE
        c.vin = 'U5YFF24128L064909' 
    GROUP BY
        c.vin,
        c.case_id,
        c.axrmrs_id 
    ORDER BY
        NULL

Related Articles



* original question posted on StackOverflow here.