I have a table with the following:
There will be 100/1000s of events for each itemid. What I need is to get the X newest events from the table for every unique value of itemid. In this case X is 20 and "newest" is the highest eventid.
What I was doing before is getting the entire table and only keeping the 20 newest for each itemid. This is very slow and inefficient.
Edit: I'm using opennms and the Events table (OpenNMS create.sql): (itemid == nodeID)
create table events ( eventID integer not null, eventUei varchar(256) not null, nodeID integer, eventTime timestamp with time zone not null, eventHost varchar(256), eventSource varchar(128) not null, ipAddr varchar(16), eventDpName varchar(12) not null, eventSnmphost varchar(256), serviceID integer, eventSnmp varchar(256), eventParms text, eventCreateTime timestamp with time zone not null, eventDescr varchar(4000), eventLoggroup varchar(32), eventLogmsg varchar(256), eventSeverity integer not null, eventPathOutage varchar(1024), eventCorrelation varchar(1024), eventSuppressedCount integer, eventOperInstruct varchar(1024), eventAutoAction varchar(256), eventOperAction varchar(256), eventOperActionMenuText varchar(64), eventNotification varchar(128), eventTticket varchar(128), eventTticketState integer, eventForward varchar(256), eventMouseOverText varchar(64), eventLog char(1) not null, eventDisplay char(1) not null, eventAckUser varchar(256), eventAckTime timestamp with time zone, alarmID integer, constraint pk_eventID primary key (eventID) );
My query was very simple:
SELECT eventid, nodeid, eventseverity, eventtime, eventlogmsg FROM events WHERE nodeid IS NOT NULL;
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `events` ADD INDEX `events_idx_nodeid` (`nodeid`);
SELECT events.eventid, events.nodeid, events.eventseverity, events.eventtime, events.eventlogmsg FROM events WHERE events.nodeid IS NOT NULL