database - How to speed up mySQL query when I only need the latest entry of each group? -

so working on project transportation system. there buses reporting server. buses insert new rows table called events every 2 sec, large table.

each bus has unique busid. want table contains buses latest report.

here things tried:

• firstly think order time desc limit 20 turns out sorting entire table first doing limit thing second... make sense, how else sort?
• so googling , found out faster sort index. did order id desc limit 20; gave me latest 20 entries pretty fast.
• however don't need latest 20 entries instead need latest entry buses. thinking combining group bus order id somehow didn't figure out...
• next read post on site speeding things when need max value of column in each group. came select driver,busid,route,timestamp,max(id) events group bus seems using max(id) not help...
• and think first using order id limit (some number) make sub table, find newest entry of each bus within sub table. problem that, tablet on bus sending report might accidentally go offline unable insert new rows. don't know how large should make sub table contains @ least latest entry of each bus...

so kinda running out of ideas... still noob in mysql, maybe there other better functions use? or maybe complexing things? though wouldn't hard @ begin ...

any advice appreciated.

---

i read retrieving last record in each group brilliant! still takes forever in case...

---

create table `events` (   `id` bigint(20) not null auto_increment,   `driver` varchar(200) collate utf8_unicode_ci default null,   `bus` varchar(200) collate utf8_unicode_ci default null,   `route` varchar(50) collate utf8_unicode_ci default null,   `time` datetime default null,   `clienttime` datetime default null,   `latitude` decimal(30,20) default null,   `longitude` decimal(30,20) default null,   `accuracy` int(11) default null,   `speed` decimal(30,20) default null,   `heading` decimal(30,20) default null,   primary key  (`id`) ) engine=innodb auto_increment=66528487 default charset=utf8 collate=utf8_unicode_ci 

---

thank helping me！ time talk professor! maybe not supposed hmm...

you have use indexes: id primary key , indexed, sorting id should fast, bus , time not indexed. add composite unique index this:

alter table events add unique index idx_bus_time (bus, time); 

this should make following query faster:

select bus, max(time) events group bus 

then can last info each bus:

select e.* events e inner join (   select bus, max(time) max_time   events   group bus) l on e.bus=l.bus , e.time=l.max_time 

another thing can improve performances create busses table:

create table busses (   id int primary key auto_increment,   bus varchar(200) ) 

and alter original table, , use bus_id int instead of bus varchar(200), , index bus_id , time column together.