How to get the last record in MySql with 2.5m rows

Question

I want to get the last record in my MySql table, but the table has 2.5 million rows. How to get the last row efficiently?

I'm using order and limit but the query runs ~15sec. I have to decrease this value to nearly zero.

My SQL Query :

SELECT id FROM table1 WHERE scenedevice_id = X AND module_id = Y ORDER BY id DESC LIMIT 0,1

EDIT : I tried MAX(id) also.

EDIT : Here is my table -

CREATE TABLE IF NOT EXISTS `realtimedevicedata` (
  `id` int(11) NOT NULL auto_increment,
  `scenedevice_id` int(11) NOT NULL,
  `module_id` int(11) NOT NULL,
  `subid` tinyint(4) NOT NULL default '1',
  `value` varchar(30) collate utf8_turkish_ci NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `scenedevice_id` (`scenedevice_id`),
  KEY `module_id` (`module_id`),
  KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci AUTO_INCREMENT=2428598 ;

Thanks.

Is that table very busy from an insert point of view? Are there delayed inserts? Have you run diagnostics to check the table and index health and repair them if needed? — My Other Me, Aug 16 '12 at 09:30
YES! Table has inserts every 3-4 min. Is this a very big problem? I dont know what is delayed inserts.. And I checked the table and I repaired. — totten, Aug 16 '12 at 09:34

score 4 · Accepted Answer · answered Aug 16 '12 at 12:16

4

I create an index for two coloumns scenedevice_id and module_id, and execution time is now 0ms :)

Thank you for all help, folks :)

answered Aug 16 '12 at 12:16

totten

2,769
3
27
41

Sorry for grave digging this but I have the exact same problem in a very similar configuration. If you remember what you did, could you please give a bit more details ? Thanks :) – Gunga Din Feb 18 '16 at 11:18
Actually, never mind, I got it ! Thank you so much for posting this, you da real MVP ! – Gunga Din Feb 18 '16 at 11:23

score 3 · Answer 2 · answered Aug 16 '12 at 08:30

3

Try

SELECT MAX(id)
FROM table1

answered Aug 16 '12 at 08:30

John Woo

258,903
69
498
492

Sorry. I did not mention. I tried, same time with the above query. – totten Aug 16 '12 at 08:31
1

Is `ID` set as some index (e.g. PRIMARY)? – feeela Aug 16 '12 at 08:32

score 2 · Answer 3 · answered Oct 09 '13 at 10:14

2

Just another way

select * from TableName where ColumnID = (Select max(ColumnID) from TableName)

answered Oct 09 '13 at 10:14

mmrs151

3,924
2
34
38

Wouldn't this be slower than order by? since it scans all table at max(columnID) and again in where clause? – haneulkim Jun 17 '20 at 09:23

score 0 · Answer 4 · answered Aug 16 '12 at 08:31

0

I don't know if it's really faster, but you could try something along:

SELECT * FROM table1 WHERE id = MAX( id );

UPDATE, the correct query to get the least inserted row, should read:

SELECT * FROM `table1` WHERE `id` = ( SELECT MAX(`id`) FROM `table1` )

answered Aug 16 '12 at 08:31

feeela

29,399
7
59
71

In which DBMS is a query like this allowed? – Daniel Hilgarth Aug 16 '12 at 08:32
@Daniel Hilgarth Yes, you are right – it's a bit early this morning. – feeela Aug 16 '12 at 08:35

score 0 · Answer 5 · answered Aug 16 '12 at 08:40

0

Try this solution to get all columns of the row:

SELECT a.*
FROM   realtimedevicedata a
JOIN   (SELECT MAX(id) AS id FROM realtimedevicedata) b ON a.id = b.id

answered Aug 16 '12 at 08:40

Zane Bien

22,685
6
45
57

Does the table with so many keys affects the performance of the query? – John Woo Aug 16 '12 at 08:47
This query helps me to decrease time nearly to 4sec. But it's too long, too. – totten Aug 16 '12 at 09:16

How to get the last record in MySql with 2.5m rows

5 Answers5