Database query - improving the character page generation

Requests, feedback, and general discussion about WoWRoster
DO NOT post topics about WoWRoster AddOns here!

Database query - improving the character page generation

Postby 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
ImageImageImage
User avatar
Fangorn
WR.net Apprentice
WR.net Apprentice
 
Posts: 35
Joined: Mon Jul 24, 2006 4:26 am
Location: Ashburn, Virginia, USA

Re: Database query - improving the character page generation

Postby lesablier » Sat Jan 20, 2007 10:50 am

Hello

Check with your tip, great improvement in display speed

Thank you Fangorn :)
lesablier
WR.net Journeyman
WR.net Journeyman
 
Posts: 77
Joined: Sat Jul 15, 2006 4:01 pm

Database query - improving the character page generation

Postby 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
I <3 /bin/bash
User avatar
PleegWat
WoWRoster.net Dev Team
WoWRoster.net Dev Team
 
Posts: 1636
Joined: Tue Jul 04, 2006 1:43 pm

Database query - improving the character page generation

Postby zanix » Sat Jan 20, 2007 5:01 pm

Thanks pleeg, databases are still foreign to me
Read the Forum Rules, the WiKi, and Search before posting!
WoWRoster v2.1 - SigGen v0.3.3.523 - WoWRosterDF
User avatar
zanix
Admin
Admin
WoWRoster.net Dev Team
WoWRoster.net Dev Team
UA/UU Developer
UA/UU Developer
 
Posts: 5543
Joined: Mon Jul 03, 2006 8:29 am
Location: Idaho Falls, Idaho
Realm: Doomhammer (PvE) - US

Database query - improving the character page generation

Postby 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.
I <3 /bin/bash
User avatar
PleegWat
WoWRoster.net Dev Team
WoWRoster.net Dev Team
 
Posts: 1636
Joined: Tue Jul 04, 2006 1:43 pm

Re: Database query - improving the character page generation

Postby 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` );
lesablier
WR.net Journeyman
WR.net Journeyman
 
Posts: 77
Joined: Sat Jul 15, 2006 4:01 pm

Database query - improving the character page generation

Postby zanix » Sun Jan 21, 2007 8:11 pm

Code: Select all
ALTER TABLE `roster_talents` DROP INDEX `talents_ix01`;
Read the Forum Rules, the WiKi, and Search before posting!
WoWRoster v2.1 - SigGen v0.3.3.523 - WoWRosterDF
User avatar
zanix
Admin
Admin
WoWRoster.net Dev Team
WoWRoster.net Dev Team
UA/UU Developer
UA/UU Developer
 
Posts: 5543
Joined: Mon Jul 03, 2006 8:29 am
Location: Idaho Falls, Idaho
Realm: Doomhammer (PvE) - US

Re: Database query - improving the character page generation

Postby lesablier » Mon Jan 22, 2007 4:38 am

Thank you Zanix.
Last edited by lesablier on Mon Jan 22, 2007 4:53 am, edited 1 time in total.
lesablier
WR.net Journeyman
WR.net Journeyman
 
Posts: 77
Joined: Sat Jul 15, 2006 4:01 pm


Return to General Support & Feedback

Who is online

Users browsing this forum: No registered users and 0 guests

cron