Database query - improving the character page generation

by **Fangorn** » Sat Jan 20, 2007 7:44 am

My guildmates have been properly roster trained to upload their data every day (thanks to uniuploader), and with about 150 characters in the database, the display speed of the character page wasn't too good anymore.

I was able to reduce the page generation time by ~66% (down to 0.5 seconds from 1.5 seconds) with the following index:
ALTER TABLE `roster_talents` ADD INDEX `talents_ix01` ( `member_id` , `tree` , `column` , `row` );

Might be interestig to include this (or maybe there's even a better index?) in the next release?

On a side note I wanted to create this as a feature request (bug/feature "forum" in the forum list) but I don't see how to create these anymore?

- Fang

by **lesablier** » Sat Jan 20, 2007 10:50 am

Hello

Check with your tip, great improvement in display speed

Thank you Fangorn

by **PleegWat** » Sat Jan 20, 2007 4:46 pm

Thanks for the headsup, I'll add it. There are some other unkeyed tables round there, I'll give them all a checkup for 1.7.3

by **zanix** » Sat Jan 20, 2007 5:01 pm

Thanks pleeg, databases are still foreign to me

by **PleegWat** » Sun Jan 21, 2007 2:33 pm

I've added primary keys to the talenttree, talent, spellbooktree, and spell tables.

Those of you who have used the above change should remove it before upgrading to 1.7.3, or you will get upgrade errors.

by **lesablier** » Sun Jan 21, 2007 7:41 pm

Ok

And How remove the tip provided ?

Code: Select all: ALTER TABLE `roster_talents` ADD INDEX `talents_ix01` ( `member_id` , `tree` , `column` , `row` );

by **zanix** » Sun Jan 21, 2007 8:11 pm

Code: Select all: ALTER TABLE `roster_talents` DROP INDEX `talents_ix01`;

by **lesablier** » Mon Jan 22, 2007 4:38 am

Thank you Zanix.