JOIN 3 tables and (LIMIT 2 rows ORDER BY time DESC)

Databases
1

I know this question has been asked many times, but I am having trouble implementing it.
I have made a cut down example so its easy to reproduce.
I want to join 3 tables but on the last one I want to limit to 2 rows DESC

CREATE TABLE `cars` (
  `car_id` int(11) NOT NULL AUTO_INCREMENT,
  `plate` varchar(10) NOT NULL,
  `km` int(11) NOT NULL,
  `status` tinyint(1) NOT NULL,
  PRIMARY KEY (`car_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
INSERT INTO `cars` (`car_id`, `plate`, `km`, `status`) VALUES
(1, 'ABC1234', 130123, 1),
(2, 'DEF1234', 100123, 1),
(3, 'QWE1234', 5000, 1),
(4, 'ASD1234', 3000, 1),
(5, 'ZXC1234', 23000, 0);
-- --------------------------------------------------------
CREATE TABLE `cars_to_users` (
  `car_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  UNIQUE KEY `car_id` (`car_id`,`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `cars_to_users` (`car_id`, `user_id`) VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 2),
(5, 2);
-- --------------------------------------------------------
CREATE TABLE `service` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `car_plate` varchar(10) NOT NULL,
  `s_timestamp` int(10) NOT NULL,
  `price` double NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=111 ;
INSERT INTO `service` (`id`, `car_plate`, `s_timestamp`, `price`) VALUES
(1, 'ABC1234', 1312300100, 30),
(2, 'DEF1234', 1312300100, 15),
(3, 'QWE1234', 1312300100, 16),
(4, 'ASD1234', 1312300100, 50),
(5, 'ABC1234', 1312300200, 50),
(6, 'DEF1234', 1312300200, 25),
(7, 'QWE1234', 1312300200, 30),
(8, 'ABC1234', 1312300300, 20),
(9, 'ASD1234', 1312300300, 60),
(10, 'ABC1234', 1312300400, 15),
(11, 'ASD1234', 1312300400, 20);

What I want is this

car_id  plate  km  car_plate  s_timestamp  price  
3 QWE1234 5000 QWE1234 1312300200 30 
3 QWE1234 5000 QWE1234 1312300100 16 
4 ASD1234 3000 ASD1234 1312300400 20 
4 ASD1234 3000 ASD1234 1312300300 60

2 rows from “service” table for every car of the user_id=2 ordered by s_timestamp DESC

ORDER BY s_timestamp LIMIT 2 DESC

I try this query but gives me all the rows from “service”

SELECT ctu.user_id, c.car_id, c.plate, c.km, s.car_plate, s.s_timestamp, s.price
FROM 
cars_to_users ctu 
LEFT JOIN cars c ON  ctu.car_id = c.car_id
LEFT JOIN service s ON c.plate = s.car_plate 
WHERE ctu.user_id = '2' 
AND c.status = 1

If I add “GROUP BY c.car_id” I only get 1 row per car and not 2 I want
I try many queries but not get what I want.

A think to keep in mind is that the table “service” has more than 9 milion rows and more data than the example and grows up.

2

if that’s true, you will defo want to have an index on ( car_plate, s_timestamp )

SELECT ctu.user_id
     , c.car_id
     , c.plate
     , c.km
     , s.car_plate
     , s.s_timestamp
     , s.price
  FROM cars_to_users ctu 
INNER [COLOR="Red"]-- not LEFT OUTER[/COLOR]
  JOIN cars c 
    ON c.car_id = ctu.car_id
   AND c.status = 1
LEFT OUTER
  JOIN service s 
    ON s.car_plate = c.plate
   AND ( SELECT COUNT(*)
           FROM service
          WHERE car_plate = c.plate
            AND s_timestamp > s.s_timestamp ) < 2
 WHERE ctu.user_id = 2 [COLOR="red"]-- no quotes[/COLOR]
3

Thanks for the answer!

Your tip about the index is very helpfull.

Your query works on the example db but on the real db never completed… I have to kill the proccess after 3-5min and the cpu is 100% all that time.

some background
The code I allready have and working is like this

SELECT * FROM cars c, cars_to_users ctu 
WHERE ctu.user_id = [B]$user_id[/B] 
AND ctu.car_id = c.car_id AND c.status = 1 
ORDER BY c.car_id;

foreach($cars as $car){   <--php code
  SELECT * FROM service 
  WHERE car_plate = '[B]$car[/B]' 
  ORDER BY s_timestamp DESC 
  LIMIT 2
}

That code on a user that have 50 cars and 1milion of the 9milion rows in service table could take about 12sec to completed.
After I ADD INDEX (car_plate, s_timestamp) it takes 0.033sec
And no mater how offen I repeat this the cpu not going more than 5-10%

I though that if I have 1 query and not 50+1 queries it will be better and will up the performance but all my tests say the opposite.

From all the code I have test the most efficient is this from @ace

CREATE TEMPORARY table inner_timestamp SELECT car_plate, max(s_timestamp) as s_timestamp FROM service GROUP BY car_plate; 
CREATE TEMPORARY table max_2_timestamp 
(   
	SELECT service.car_plate,max(service.s_timestamp) as s_timestamp   
	FROM service   
	JOIN max_2_timestamp ON max_2_timestamp.car_plate = service.car_plate AND service.s_timestamp < max_2_timestamp.s_timestamp 
	GROUP BY service.car_plate 
);  
SELECT ctu.user_id, c.car_id, c.plate, c.km, s.car_plate, s.s_timestamp, s.price 
FROM cars_to_users ctu  
LEFT JOIN cars c ON  ctu.car_id = c.car_id 
LEFT JOIN service s ON c.plate = s.car_plate 
JOIN max_2_timestamp ON s.car_plate = max_2_timestamp.car_plate AND s.s_timestamp >= max_2_timestamp.s_timestamp 
WHERE ctu.user_id = '2'  
AND c.status = 1 
ORDER BY s_timestamp DESC;

But even this take to completed 30sec before and 12sec after the INDEXING and all this seconds the cpu its up to 100%

so your point about INDEX its “all the money” as we say in Greece

but the chalenge for 1 efficient query remains

4

did you try my query after adding the index?

also, how many cars does a typical user have?

5

Yes I try but I have to kill it again.

Many users have 1-3 cars but few users have many cars 30-250 and some 1000. But if you ask where to focus? I need to focus to 30-250.

Keep in mind that if the user is online the query repeated every 10sec.
New data will be added in service table in times from 5-10secs.

6

could you please try something for me?

do an EXPLAIN on my query

then remove the AND condition which has the COUNT subquery, and test the query again

this will return ~all~ service rows for a given user, and you would need to use an ORDER BY query to get them in the right sequence – then, in your php code, print the first two service rows for each car, and skip over the rest

i know this is extra data transmitted from mysql to php, but i am guerssing the increase in speed will make it a worthwhile alternative solution

7

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY ctu ref user_id,user_id_2 user_id 4 const 32 Using index
1 PRIMARY c eq_ref PRIMARY PRIMARY 4 madcar.ctu.car_id 1 Using where
1 PRIMARY s ref car_plate,car_plate_2 car_plate 47 madcar.c.plate 51311
2 DEPENDENT SUBQUERY points ref car_plate,car_plate_2 car_plate 47 madcar.c.plate 51311 Using where; Using index

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE ctu ref user_id,user_id_2 user_id 4 const 32 Using index
1 SIMPLE c eq_ref PRIMARY PRIMARY 4 madcar.ctu.car_id 1 Using where
1 SIMPLE s ref car_plate,car_plate_2 car_plate 47 madcar.c.plate 51311

8

that looks fine

how’s the performance?

9

On a cut down ‘service’ table of 100.000rows
for a user with 4 cars takes 13sec
for a user with 30 cars (I have to kill the the procces)

10

is that the first query (with the dependent subquery) or the second (without)?

11

Without its fast but I get too much data.
I will procces the data with php and I will tell you the results.

12

When I add the ORDER BY the times is up again.
in real db for a user with 3 car it takes 4sec and with 30 cars 12sec