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

Question

So I was working on a project about transportation system. There are buses reporting to the server. All buses insert new rows into a table called Events every 2 sec, so it is a large table.

Each bus has a unique busID. I want to get a table which contains all buses but only their latest report.

Here are things I tried:

• Firstly I think I could ORDER BY time DESC LIMIT 20 It turns out that it is sorting the entire table first then doing the LIMIT thing second... which actually make sense, how else could it sort?
• So I was googling and found out that its much faster to sort with the index. So i did ORDER BY id DESC LIMIT 20; It gave me the latest 20 entries pretty fast.
• However I don't really need the latest 20 entries instead that I need the latest entry from all buses. So I was thinking about combining GROUP BY bus with ORDER BY id somehow but didn't really figure that out...
• Next I read about another post on this site about speeding things up when you only need the max value of a column in each group. So finally I came up with SELECT driver,busID,route,timestamp,MAX(id) FROM Events GROUP BY bus However it seems like using MAX(id) does not really help...
• And I think about first using ORDER BY id LIMIT (some number) to make a sub table, then find the newest entry of each bus within the sub table. But a problem is that, the tablet on the bus which is sending report might accidentally go offline thus unable to insert new rows. So I don't really know how large should I make the sub table so that it contains at least the latest entry of each bus...

So I am kinda running out of ideas... I am still a noob in mySQL, so maybe there are other better functions to use? Or maybe I am complexing things? I though it wouldn't be so hard to do at the begin ...

Any advice would be greatly appreciated.

---

I also read about this Retrieving the last record in each group which is brilliant! But it still takes forever in my 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 you all for helping me！ But it is time to talk to professor! Maybe I am not supposed to do that hmm...

Answer 1

You have to use indexes: id is a primary key and is already indexed, so sorting by id should be fast, but bus and time are not indexed. I would add a composite unique index like this:

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

this should make the following query much faster:

select bus, max(time)
from Events
group by bus

then you can easily get the last info for each bus:

select e.*
from Events e INNER JOIN (
  select bus, max(time) max_time
  from Events
  group by bus) l on e.bus=l.bus AND e.time=l.max_time

another thing you can do to improve performances is to create a busses table:

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

and alter the original table, and use a bus_id INT instead of the bus VARCHAR(200), and index the bus_id and the time column together.

Answer 2

I would rather make it simple,

I would add one column in the table i.e. < latest_record >...

Now, for the particular < bus_id >'s latest record or Event, it would have value < 0 > in < latest_record > field.

once another entry/event for the same < bus_id > is arrived, before inserting that entry/event, I would update previous_latest entry/event's < latest_record >'s value to '1' and the newly arrived entry will have < latest_record >'s value to '0'

Now, you just have to make Index on < latest_record > and you can find all unique bus_id's latest entry by filtering latest_record='0' in WHERE clause

Answer 3

The solution would be so simple if you could simply INSERT into a new table that contains one row per bus -- the current status of the bus.