Using the tables below as an example and the listed query as a base query, I want to add a way to select only rows with a max id! Without having to do a second query!
TABLE VEHICLES id vehicleName ----- -------- 1 cool car 2 cool car 3 cool bus 4 cool bus 5 cool bus 6 car 7 truck 8 motorcycle 9 scooter 10 scooter 11 bus TABLE VEHICLE NAMES nameId vehicleName ------ ------- 1 cool car 2 cool bus 3 car 4 truck 5 motorcycle 6 scooter 7 bus TABLE VEHICLE ATTRIBUTES nameId attribute ------ --------- 1 FAST 1 SMALL 1 SHINY 2 BIG 2 SLOW 3 EXPENSIVE 4 SHINY 5 FAST 5 SMALL 6 SHINY 6 SMALL 7 SMALL
And the base query:
select a.* from vehicle a join vehicle_names b using(vehicleName) join vehicle_attribs c using(nameId) where c.attribute in('SMALL', 'SHINY') and a.vehicleName like '%coo%' group by a.id having count(distinct c.attribute) = 2;
So what I want to achieve is to select rows with certain attributes, that match a name but only one entry for each name that matches where the id is the highest!
So a working solution in this example would return the below rows:
id vehicleName ----- -------- 2 cool car 10 scooter
if it was using some sort of max on the id
at the moment I get all the entries for cool car and scooter.
My real world database follows a similar structure and has 10's of thousands of entries in it so a query like above could easily return 3000+ results. I limit the results to 100 rows to keep execution time low as the results are used in a search on my site. The reason I have repeats of "vehicles" with the same name but only a different ID is that new models are constantly added but I keep the older one around for those that want to dig them up! But on a search by car name I don't want to return the older cards just the newest one which is the one with the highest ID!
The correct answer would adapt the query I provided above that I'm currently using and have it only return rows where the name matches but has the highest id!
If this isn't possible, suggestions on how I can achieve what I want without massively increasing the execution time of a search would be appreciated!
If you want to keep your logic, here what I would do:
select a.* from vehicle a left join vehicle a2 on (a.vehicleName = a2.vehicleName and a.id < a2.id) join vehicle_names b on (a.vehicleName = b.vehicleName) join vehicle_attribs c using(nameId) where c.attribute in('SMALL', 'SHINY') and a.vehicleName like '%coo%' and a2.id is null group by a.id having count(distinct c.attribute) = 2;
+----+-------------+ | id | vehicleName | +----+-------------+ | 2 | cool car | | 10 | scooter | +----+-------------+ 2 rows in set (0.00 sec)
As other said, normalization could be done on few levels:
Keeping your current
vehicle_names table as the primary lookup table, I would change:
update vehicle a inner join vehicle_names b using (vehicleName) set a.vehicleName = b.nameId; alter table vehicle change column vehicleName nameId int; create table attribs ( attribId int auto_increment primary key, attribute varchar(20), unique key attribute (attribute) ); insert into attribs (attribute) select distinct attribute from vehicle_attribs; update vehicle_attribs a inner join attribs b using (attribute) set a.attribute=b.attribId; alter table vehicle_attribs change column attribute attribId int;
Which led to the following query:
select a.id, b.vehicleName from vehicle a left join vehicle a2 on (a.nameId = a2.nameId and a.id < a2.id) join vehicle_names b on (a.nameId = b.nameId) join vehicle_attribs c on (a.nameId=c.nameId) inner join attribs d using (attribId) where d.attribute in ('SMALL', 'SHINY') and b.vehicleName like '%coo%' and a2.id is null group by a.id having count(distinct d.attribute) = 2;