2

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

Andurit
  • 5,612
  • 14
  • 69
  • 121
  • 1
    What are indexes on your tables? Execution plans would help too. – Evaldas Buinauskas Nov 25 '15 at 16:40
  • Hey @EvaldasBuinauskas I just added indexes to my question, sorry I feel really noobish but not sure what you mean with "execution plan" – Andurit Nov 25 '15 at 16:44
  • @Andurit: execute the query with `EXPLAIN` as a prefix and you'll get the query plan. If at all possible I would recommend swapping out MySQL for PostgreSQL as it's much more intelligent in optimising queries. – Wolph Nov 25 '15 at 16:47
  • I'm not sure how many row in each of your tables. So this is based on each table have almost the same size. And I don't think the left join is what you want(maybe inner join is enough) Since you using left join and the condition is based on case_id, It will be full table scan both in positions and cases table and index scan in calculations table. The full table scan will cause lots of resources. if you want to increase the search speed, you need to add index to positions and cases. – Roger Dwan Nov 25 '15 at 16:57
  • Hi @RogerDwan, its at the end so it's easy to miss, number of rows of each table is in my question :) However it is: #1 cases - ~3 486 114 #2 calculations - ~2 061 554 #3 positions - ~55 078 708 – Andurit Nov 25 '15 at 17:02

1 Answers1

0

Please provide SHOW CREATE TABLE.

WHERE vin = :vin ORDER BY date_created DESC needs this composite index: INDEX(vin, date_created).

WHERE case_id = :case AND country = :country ORDER BY calculation_id DESC needs either INDEX(case_id, country, calculation_id) or INDEX(country, case_id, calculation_id).

INDEX(calculation_id, case_id, repairmethod) or INDEX(case_id, calculation_id, repairmethod)

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' -- c needs:INDEX(vin) and cl and pl need:INDEX(case_id). Don't say LEFT unless you mean that the 'right' table is optional.

Note that the INDEX(vin, date_created) from the first query suffices for the vin index here.

See my Index Cookbook.

Rick James
  • 135,179
  • 13
  • 127
  • 222