CODEWITHSUNDEEP
mysqldatabasedatabase-performance
user3695760
user3695760

Reputation: 179

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

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:

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...

Upvotes: 1

Views: 84

Answers (3)

Rick James
Rick James

Reputation: 142433

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.

Upvotes: 0

fthiella
fthiella

Reputation: 49089

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.

Upvotes: 2

Hytool
Hytool

Reputation: 1368

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

Upvotes: 0

Related Questions