Search code examples
mysqlindexingquery-optimizationexplain

How to speed up this mysql join query

I have the following query which takes quite a long time (about 1 min):

SELECT `transaction`.`fuel_terminal_id`, `transaction`.`xboard_id`, `transaction`.`gas_station_id`, `transaction`.`operator_id`, `transaction`.`shift_id`, `transaction`.`fuel_type`, `transaction`.`purchase_type`, `shift`.`num` AS `shiftNum`, `shift`.`shift_state_id` AS `shiftStateId`, `shift`.`start_totalizer_dispenser_1` AS `startTotalizerDispenser1`, `shift`.`start_totalizer_dispenser_2` AS `startTotalizerDispenser2`, `shift`.`end_totalizer_dispenser_1` AS `endTotalizerDispenser1`, `shift`.`end_totalizer_dispenser_2` AS `endTotalizerDispenser2`, min(shift.start_time)AS shiftStartTime, max(shift.end_time)AS shiftEndTime, count(*)AS groupCount, sum(fuel_cost)AS sumFuelCost, sum(payment_cost)AS sumPaymentCost, sum(actual_amount / 100)AS sumActualAmount, min(start_fuel_time)AS firstFuelingDate,max(end_fuel_time)AS lastFuelingDate 
FROM `transaction` 
LEFT JOIN `shift` 
ON shift.gs_id = TRANSACTION .gas_station_id 
AND shift.terminal_id = TRANSACTION .fuel_terminal_id 
AND shift.id = TRANSACTION .shift_id 
AND shift.start_time = TRANSACTION .shift_start_time
GROUP BY `transaction`.`gas_station_id`, 
`transaction`.`fuel_terminal_id`, 
`transaction`.`shift_start_time`, 
`transaction`.`fuel_type`, 
`transaction`.`purchase_type`,  
`transaction`.`operator_id`;

I could speed up the query (about 25%) by changing the data size of the column "operator_id" in table "shift" from VARCHAR 255 to VARCHAR 16 and also changing data type of this column in the table "transaction" from TEXT to VARCHAR 16. However, still I need more speed up (maybe by adding more indexes or changing them?).

This is the result of EXPLAIN: enter image description here

I've read in MySQL 5.7 Reference Manual that if the column "possible_keys" is NULL, there are no relevant indexes. So, I was wondering if someone can help me understand whether I didn't select the right indexes or not? These are the indexes I put on table "transaction":

enter image description here

Solution

  • Well, I just found that the problem in my case was not related to indexes or the query or db structure. When I ran the query on my local server, it was fast while on live db was very slow. After some searching, I found that increasing the buffer_pool_size (a bit larger than your DB size, in my case, I increased it from the defult value (8M) to 2G) improves innoDB performance.

    Reading the following links helped me understand the Buffer pool in innoDb and how to configure it:

    MySQL Reference Manual: the innoDB buffer pool

    Choosing innoDB buffer pool size

    What to set innoDB buffer pool and why

    MySQL Reference Manual: Configuring innoDB buffer pool size