Jump to content

Add indexes to log table to speed up searches


Recommended Posts

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 by Cappuccino
fix, thanks to Karbust
  • Metin2 Dev 2
  • Love 1
  • Good 1
Link to post
  • VIP

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 <.

 

 

  • Metin2 Dev 1
  • Good 1

QJ0orbR.png

Logo M2IconDB

Link to post
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 

 

 

Link to post
  • Engineer

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 :D

  • Metin2 Dev 2
  • Love 1
Link to post

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.



Shoutbox

Shoutbox

Chatroom Rules

 

Join our Discord

A request for help = Shoutbox Ban

Be respectful & Respect the rules

 

×
×
  • Create New...

Important Information

Terms of Use / Privacy Policy / Guidelines / We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.