Optimizing SELECT in big table

Question

I have a table containing logs from a gameserver. These logs are saved in a pretty bad way (imo) but i need to make a way to collect them. This table currently holds 4,000,000 rows and grows (this is 4 or 5 months worth of logs).

Now i'm searching for a way to filter the SELECT query without putting to much load on the database. This is the table structure:

CREATE TABLE `rp_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `time` int(11) NOT NULL,
  `type` enum('joinleave','kill','chat','spawn','ac','cmd','misc','hidden','cp','admin') NOT NULL,
  `msg` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Now i can filter on time range and the type of log. But I would like more. The log msg most of the time contains a player ID. It would be nice if i could add a msg LIKE '%playerid%'. But if I just do that with a plain simple query it expends the search action to +30 seconds.

Currently I just do (this is not 100% the code just the method i do):

SELECT * FROM rp_logs 
WHERE time > $start AND time < $end 
AND (type = 'kill' or type = 'cmd' or ... );

Is there are better way of optimizing the query so it is not as heavy.

What I know is possible is make a query on the time and then subquery that with the filter on msg but i have no idea how that works. I can't seem to get that working.

Here is 30 rows of sample data:

INSERT INTO `rp_logs` (`id`, `time`, `type`, `msg`) VALUES
(4314121, 1366788849, 'chat', '[OOC] Danny Doss (STEAM_0:0:20730895): He logged a long time ago Itt'),
(4314122, 1366788849, 'chat', 'Eric Crendor (STEAM_0:0:46944468): the *'),
(4314123, 1366788851, 'spawn', 'Danny Doss (STEAM_0:0:20730895) created entity prop_physics with model models/props_building_details/storefront_template001a_bars.mdl'),
(4314124, 1366788855, 'chat', '[OOC] Eric Crendor (STEAM_0:0:46944468): did he?'),
(4314125, 1366788868, 'chat', '[OOC] Danny Doss (STEAM_0:0:20730895): 5 minutes ago'),
(4314126, 1366788872, 'misc', 'Daedalus Richmond (STEAM_0:1:20442870) has been made a Arms Dealer'),
(4314127, 1366788879, 'chat', '[OOC] Eric Crendor (STEAM_0:0:46944468): nontheless we killed him then started cracking, he still left whilst raid?'),
(4314128, 1366788893, 'chat', '[OOC] Jasonafex Mittsies (STEAM_0:0:29686591): He left because he went to bed'),
(4314129, 1366788899, 'chat', '[OOC] Danny Doss (STEAM_0:0:20730895): Can''t do anything about it'),
(4314130, 1366788903, 'chat', 'Eric Crendor (STEAM_0:0:46944468): left go to next dealer'),
(4314131, 1366788904, 'chat', '[OOC] Danny Doss (STEAM_0:0:20730895): It is not warnable'),
(4314132, 1366788909, 'chat', 'Eric Crendor (STEAM_0:0:46944468): * my grAMMAR'),
(4314133, 1366788912, 'chat', 'Eric Crendor (STEAM_0:0:46944468): damn keyboard'),
(4314134, 1366788913, 'spawn', 'Danny Doss (STEAM_0:0:20730895) created entity prop_physics with model models/props_c17/furnituredrawer001a.mdl'),
(4314135, 1366788919, 'spawn', 'Danny Doss (STEAM_0:0:20730895) created entity prop_physics with model models/props_combine/combine_window001.mdl'),
(4314136, 1366788921, 'chat', 'Eric Crendor (STEAM_0:0:46944468): k'),
(4314137, 1366788923, 'spawn', 'Danny Doss (STEAM_0:0:20730895) created entity prop_physics with model models/props_c17/furniturecouch002a.mdl'),
(4314138, 1366788932, 'kill', 'Danny Doss (STEAM_0:0:20730895) was killed by Entity [0][worldspawn], weapon: N/A'),
(4314139, 1366788947, 'spawn', 'Jasonafex Mittsies (STEAM_0:0:29686591) created entity prop_physics with model models/props_lab/blastdoor001c.mdl'),
(4314140, 1366788960, 'misc', 'AceFTFW (STEAM_0:0:37117785) has been made a Citizen'),
(4314141, 1366788960, 'joinleave', 'Ace Jones (STEAM_0:0:37117785) joined the game.'),
(4314142, 1366788967, 'joinleave', 'Jasonafex Mittsies (STEAM_0:0:29686591) has left the game.'),
(4314143, 1366788971, 'joinleave', 'Danny Doss (STEAM_0:0:20730895) has left the game.'),
(4314144, 1366788981, 'misc', 'Ace Jones (STEAM_0:0:37117785) has been made a Arms Dealer'),
(4314145, 1366788989, 'chat', 'Eric Crendor (STEAM_0:0:46944468): okay'),
(4314146, 1366788996, 'chat', 'Eric Crendor (STEAM_0:0:46944468): Ace'),
(4314147, 1366789011, 'chat', 'Eric Crendor (STEAM_0:0:46944468): wheres your sho'),
(4314148, 1366789014, 'chat', 'Eric Crendor (STEAM_0:0:46944468): we wanna get some guns'),
(4314149, 1366789018, 'chat', 'Eric Crendor (STEAM_0:0:46944468): ah k'),
(4314150, 1366789035, 'chat', 'Eric Crendor (STEAM_0:0:46944468): no literally');

Perhaps you can save the result of the time based query in a table view, than ask a new query based on player name on the table view. Take a look at SQL - Views — Freeman Lambda, Apr 24 '13 at 07:48
What time range are you typically querying - less than a day, more than a month, ...? — , Apr 24 '13 at 08:01
Most a few hours, but some searches reach a few days. But loading days is a big load — s.lenders, Apr 24 '13 at 08:03
**Can** you create indexes (or ask for them to be created)? For a time period of a few hours/days, an index on `time` should significantly improve performance (as suggested in my answer). — , Apr 24 '13 at 08:05
@MarkBannister great from .9 s to 0.0005 load on the logs of 1 day. Let me take a look in the fulltext thing to see if it is any good — s.lenders, Apr 24 '13 at 08:27
Probably also worthwhile putting an index on both time AND type columns (MySQL will only use one index on a table in a query). — Kickstart, Apr 24 '13 at 08:31

score 2 · Answer 1 · answered Apr 24 '13 at 08:00

2

Add a repeating index on your time field. As long as you query a relatively narrow time range, the performance increase on your existing query should be significant.

answered Apr 24 '13 at 08:00

1

This scaled search of 1 day indeed down from .9 sec to .0005 sec, ty – s.lenders Apr 24 '13 at 08:28

score 1 · Answer 2 · answered Apr 24 '13 at 07:47

1

Add a new field to the DB which contains the username. Doing a "LIKE", especially with wildcards, always takes up a whole lot of resources (and thus time). Besides, if you have a seperate field for the username, you can put an index on it, and make it even faster.

answered Apr 24 '13 at 07:47

Borniet

3,544
4
24
33

I know, and i'd like to but it is not an option – s.lenders Apr 24 '13 at 07:51
Can you create anything in the db? And do you need the data to be realtime? Otherwise you could create a new table based on a (long running) query that filters out the username and puts it in a seperate field. A bit of an ETL step inbetween. – Borniet Apr 24 '13 at 07:58
I can't change the way the server saves the logs. I could indeed parse the data into a new table. But there are so many different types of ways a message is saved that i'd rather not – s.lenders Apr 24 '13 at 08:07
Is the 'msg' field indexed? I think that's just about the only thing left to try... – Borniet Apr 24 '13 at 08:16
Not yet, i will take a look into Scorpi0's answer for that – s.lenders Apr 24 '13 at 08:28
The Fulltext option will indeed be faster then the LIKE you're currently trying, so that will be a good start indeed. – Borniet Apr 24 '13 at 08:43

score 1 · Accepted Answer · edited May 23 '17 at 10:25

A LIKE query can be replaced by a FULLTEXT search.

SELECT * 
FROM rp_logs 
WHERE MATCH (msg) AGAINST ('playerid');

You have to first declare you msg column as a FULLTEXT

ALTER TABLE rp_logs ADD FULLTEXT(msg);

Or

CREATE TABLE `rp_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `time` int(11) NOT NULL,
  `type` enum('joinleave','kill','chat','spawn','ac','cmd','misc','hidden','cp','admin') NOT NULL,
  `msg` text NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT (msg)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Optimizing SELECT in big table

3 Answers3