Jump to content
×
×
  • Create New...

Add indexes to log table to speed up searches


Cappuccino

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 comment
Share on other sites

  • Replies 4
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Posts

Cappuccino

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 runni

Gurgarath

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

Karbust

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, th

  • Web Developer

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

Metin2 Icon Database

Link to comment
Share on other sites

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 comment
Share on other sites

  • Moderator

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

Gurgarath
coming soon

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


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.