Poor SQL Table Indexing

[Cookie]

bugreport:3289

I've toyed around with this bug report for awhile, and we haven't acted upon it yet. Due to the nature of the bug report, possible change significance to the core, and the latter, I figured we'd create a topic after our IRC discussion we had tonight. I know there's a lot of talented DB experts on the rAthena team, and I'd like to see everyone's angle on this bug report so we can come up with a plan to implement. One of us could easily throw in fixes, and changes but I believe everyone contributing to the changes is the best idea - whether it's ideas, or code. The ultimate goal, of course, is to decrease the execution time of queries, and to improve performance.

@Ind contributed a diff in the bug report tonight. It was a few changes he made while discussing with us over IRC. I'd say taking a look at the bug report, and viewing his ideas would be the best start.

Finally, prior to implementing it if we do alter tables and manipulate constraints, and what-not, we'll want to publicly make it apparent (at least in my opinion) - digest, noticeable commit, and such. The goal of this thread is to make sure we fix the poor performance, and properly implement it with as least repercussions as possible.

Discuss!

P.S. - I'll use my server as a guinea pig as I do a lot for rA commits. My poor players.

Edit #1:

I forced @Trojal into #rdev last night (his precious 80 minutes of his life gone) as he didn't want to write a novel in response to this post. Here is what he came up with for indexing:

For Control Panel / general usage:

auction:

inventory:

storage:

guild_storage:

cart_inventory:

nameid

card0

card1

card2

card3

For existing application queries:

pet:

char_id

friend:

friend_id

char:

class OR fame

name

guild:

name

mercenary:

char_id

party:

name

ipbanlist:

rtime

[Kenpachi]

Well, that's funny. I never saw that topic because it's hidden in the Core section. It's obviously a database issue...

The poor indexing results from not knowing how the table will be used when creating it - that's kind of normal. Most of the suggestions theultramage wrote are pretty simple to see if you know how the table is used by the server.

Just add indexes on the columns that are used to look up the results and the task is done. (Actually that's what theultramage suggested 3 years ago...)

[Cookie]

Well, that's funny. I never saw that topic because it's hidden in the Core section. It's obviously a database issue...

The poor indexing results from not knowing how the table will be used when creating it - that's kind of normal. Most of the suggestions theultramage wrote are pretty simple to see if you know how the table is used by the server.

Just add indexes on the columns that are used to look up the results and the task is done. (Actually that's what theultramage suggested 3 years ago...)

Right, Trojal broke it down in a list. Agreed. +1.