Return to Question

'CREATE TABLE `access_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `EventTime` datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  `EventSize` int(10) unsigned NOT NULL DEFAULT ''0'',
  `EventScore` double DEFAULT NULL,
  `EventCustomer` int(11) DEFAULT NULL,
  `event_log_domain_id` smallint(6) DEFAULT NULL,
  `EventUserID` smallint(5) unsigned NOT NULL DEFAULT ''0'',
  `EventType` tinyint(3) unsigned NOT NULL DEFAULT ''0'',
  `EventName` varchar(255) DEFAULT NULL,
  `utc` tinyint(1) DEFAULT ''0'',
  `job_queue_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `EventTime` (`EventTime`),
  KEY `IndexUser` (`EventUserID`),
  KEY `IndexType` (`EventType`),
  KEY `IndexUserType` (`EventUserID`,`EventType`),
  KEY `event_log_domain_id` (`event_log_domain_id`),
  KEY `job_queue_id` (`job_queue_id`),
  KEY `EventTypeAndeventogdomainid` (`EventType`,`event_log_domain_id`),
  KEY `eventogdomainidAndEventType` (`event_log_domain_id`,`EventType`),
  FULLTEXT KEY `IndexEventName` (`EventName`)
) ENGINE=InnoDB AUTO_INCREMENT=123163064 DEFAULT CHARSET=latin1'

1. EventName (wildcard search %search_string%, not always provided)
2. EventCustomer (forward match search_string%, not always provided)
3. EventUserID (forward match search_string%, not always provided)
4. EventType (exact match, one or more ids but not always provided)
5. event_log_domain_id (exact match, one or more ids always provided)

Then we fetch 50/100 records at the time so we do limit too. We also provide default order, or give ability to order by few columns (EventCustomer, EventUserID, EventType and EventTime).

'CREATE TABLE `access_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `EventTime` datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  `EventSize` int(10) unsigned NOT NULL DEFAULT ''0'',
  `EventScore` double DEFAULT NULL,
  `EventCustomer` varchar(255) DEFAULT NULL,
  `event_log_domain_id` smallint(6) DEFAULT NULL,
  `EventIdentifier` varchar(255) DEFAULT NULL,
  `EventType` tinyint(3) unsigned NOT NULL DEFAULT ''0'',
  `EventName` varchar(255) DEFAULT NULL,
  `utc` tinyint(1) DEFAULT ''0'',
  `job_queue_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `EventTime` (`EventTime`),
  KEY `IndexIdentifier` (`EventIdentifier`),
  KEY `IndexType` (`EventType`),
  KEY `IndexUserType` (`EventIdentifier`,`EventType`),
  KEY `event_log_domain_id` (`event_log_domain_id`),
  KEY `job_queue_id` (`job_queue_id`),
  KEY `EventTypeAndeventogdomainid` (`EventType`,`event_log_domain_id`),
  KEY `eventogdomainidAndEventType` (`event_log_domain_id`,`EventType`),
  FULLTEXT KEY `IndexEventName` (`EventName`)
) ENGINE=InnoDB AUTO_INCREMENT=123163064 DEFAULT CHARSET=latin1'

1. EventName (wildcard search %search_string%, not always provided)
2. EventCustomer (forward match search_string%, not always provided)
3. EventIdentifier (forward match search_string%, not always provided)
4. EventType (exact match, one or more ids but not always provided)
5. event_log_domain_id (exact match, one or more ids always provided)

Then we fetch 50/100 records at the time so we do limit too. We also provide default order, or give ability to order by few columns (EventCustomer, EventIdentifier , EventType and EventTime).

UPDATE 1 showing table create statement:

UPDATE 1

Showing table create statement:

What we actually use this page for.

We have built a page that filters on these events, there are 5 filters:

1. EventName (wildcard search %search_string%, not always provided)
2. EventCustomer (forward match search_string%, not always provided)
3. EventUserID (forward match search_string%, not always provided)
4. EventType (exact match, one or more ids but not always provided)
5. event_log_domain_id (exact match, one or more ids always provided)

It is possible for provide more than one filter, so you could provide name and customer for all log domains. or just event type for particular log domain etc.

We have a pagination on the page, so we do need to perform a count to get the row count.

Then we fetch 50/100 records at the time so we do limit too. We also provide default order, or give ability to order by few columns (EventCustomer, EventUserID, EventType and EventTime).

UPDATE 1 showing table create statement:

'CREATE TABLE `access_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `EventTime` datetime NOT NULL DEFAULT ''0000-00-00 00:00:00'',
  `EventSize` int(10) unsigned NOT NULL DEFAULT ''0'',
  `EventScore` double DEFAULT NULL,
  `EventCustomer` int(11) DEFAULT NULL,
  `event_log_domain_id` smallint(6) DEFAULT NULL,
  `EventUserID` smallint(5) unsigned NOT NULL DEFAULT ''0'',
  `EventType` tinyint(3) unsigned NOT NULL DEFAULT ''0'',
  `EventName` varchar(255) DEFAULT NULL,
  `utc` tinyint(1) DEFAULT ''0'',
  `job_queue_id` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `EventTime` (`EventTime`),
  KEY `IndexUser` (`EventUserID`),
  KEY `IndexType` (`EventType`),
  KEY `IndexUserType` (`EventUserID`,`EventType`),
  KEY `event_log_domain_id` (`event_log_domain_id`),
  KEY `job_queue_id` (`job_queue_id`),
  KEY `EventTypeAndeventogdomainid` (`EventType`,`event_log_domain_id`),
  KEY `eventogdomainidAndEventType` (`event_log_domain_id`,`EventType`),
  FULLTEXT KEY `IndexEventName` (`EventName`)
) ENGINE=InnoDB AUTO_INCREMENT=123163064 DEFAULT CHARSET=latin1'