View Full Version : Missing index on ppgal_favorites
September 17th, 2008, 05:20 AM
Table ppgal_favorites is missing an index on userid, which causes full table scans when viewing a member profile.
September 17th, 2008, 05:30 AM
Thanks not really a true bug but will have a look.
September 18th, 2008, 05:04 AM
@chuck or zach
if you are going to add an index..
please also add while your at it a datestamp field.. so we can order favorites by date.. asc and desc etc...
September 18th, 2008, 05:59 AM
There is already an index present on the images table for field dateline Luciano
September 18th, 2008, 06:57 AM
english is not my mother tongue...
and.. i was to quick..
but what i meant was:
if you are going to change something in favourite table.. while you are at it.. you could add a dateline field for favorites..
so you can know when user added image to his favorites
this would allow to pull favorites for example in profile.. (show latest favorites of user .. etc.. etc.. or order favorites by addition date etc..)
September 18th, 2008, 06:01 PM
well just a note the sorts are all done on the images table so sort by date is already indexed from what I show.
There is not date field in favorites only userid and imageid
September 19th, 2008, 12:44 AM
I know that...
it was a sugestion only..
because in my install.. I added a date field.. and in userprofile i pull the users favorites.. the last 3 he added.. (if he added some)
works fine in my test install..but needs the extra field.
So my thinking was...
If you were going to change someting in that table anyway.. why not add that date field... (just a suggestion.. not important enough to open an own thread)
then i could post the mod once its finished and I would not have to add the field in my mod.. (because I hate tampering with databases when writing a mod)
that was all .. (didnt mean to highjack this thread..)
September 19th, 2008, 09:45 AM
Okay so you where doing a mod now I know where your coming from because I knew there was no functionality like that.
December 25th, 2008, 07:46 PM
I'd classify this (Kirby's original bug report) as a performance bug; especially for sites that make heavy use of the favorites functionality.
ppgal_favorites.userid is joined/filtered upon in misc.php and showimage.php.
Note: I'm using vbG v2.4.3.
In the meantime, I've done the following for my site:
mysql> alter table vb_ppgal_favorites add foreign key (userid) references vb_user (userid) on delete cascade;
vBulletin® v3.8.1, Copyright ©2000-2014, Jelsoft Enterprises Ltd.