Add indexes to log table to speed up searches

[Cappuccino 43]

Sometimes you have to search for someting on the "log" table in the database "log", but when you have many records the search will take much time.

Why this happens? There are no index on that table, I don't know why anyone didn't add indexes to that table.

Indexes take space on your hard disk, they can take even gigabytes but... why do you have a log table if you can't look up for anything because it takes so much time to filter rows?

 

We add some indexes to that table running this query inside the database "log":

 

ALTER TABLE `log`
ADD INDEX `who_index` (`who`) USING BTREE ,
ADD INDEX `time_index` (`time`) USING BTREE ,
ADD INDEX `how_index` (`how`) USING HASH ,
ADD INDEX `vnum_index` (`vnum`) USING BTREE ;

 

It can take  some time to build these indexes. If it takes too long, you probably should truncate the log table and then create the index.

Edited February 6, 2021 by Cappuccino
fix, thanks to Karbust

[Karbust 4732]

From what I know, indexing such a big table will bring more disadvantages than advantages. Also, each row inserted needs to be indexed, which will impact performance.

 

I don't think a log table should be indexed, only tables like accounts, players, store items (itemshop, not ingame) and stuff like that.

 

And you still have the overhead of having to wait to create the index of an already in production table, which most servers won't probably truncate.

 

Overall, the intention is good, but I don't think it's a good idea to make this type of index.

 

And, time shouldn't be a HASH, the query would need to be an = or <=> Three-way comparison, which isn't ideal for a datetime column, it would probably need to use a >=, <=, > or <.

 

 

[WeedHex 626]

LIMIT 0, 10000

Fiber Optic

Filters

[Cappuccino 43]

9 hours ago, Karbust said:

From what I know, indexing such a big table will bring more disadvantages than advantages. Also, each row inserted needs to be indexed, which will impact performance.

 

I don't think a log table should be indexed, only tables like accounts, players, store items (itemshop, not ingame) and stuff like that.

 

And you still have the overhead of having to wait to create the index of an already in production table, which most servers won't probably truncate.

 

Overall, the intention is good, but I don't think it's a good idea to make this type of index.

 

And, time shouldn't be a HASH, the query would need to be an = or <=> Three-way comparison, which isn't ideal for a datetime column, it would probably need to use a >=, <=, > or <.

 

 

I usually retain log for 1-2 weeks, plus storage is cheap, time not.... so

Of course if you do not clear your logs for years it's gonna take much space 

 

 

[Gurgarath 2444]

Well, both of you are correct. It depends on multiple factors, how much do you access the logs, for how long do you keep them, how big is your table, how big is your storage, how often you clean it, all that stuff and even more, it's just a part of the factors that are in action.

 

Adding indexes won't do much if you don't do the querying right, if you don't access the logs or don't clean them often, because having an index on a big table can increase the time it takes to do an "UPDATE / INSERT" query but in response you can have a better time accessing it ("SELECT"), also, caching the older results can be more efficient on the long run if you have enough RAM and if you do specific queries often (or multiple people from your team).

 

This tutorial is interesting but you need to know if you get an advantage using it. I think that this, coupled with a good SQL routine is the way to go. But you need to do a good querying, to save and clean the table every-once in a while, also a whole log refactor would be interesting. More precise logs in more specific tables could help the log.log getting cleaner and easier to read / manage.

 

Thank you for sharing and thanks for your infos Karbust