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

advertisements

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.


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

Thank you for all help, folks :)