Missing index on ppgal_favorites

[Kirby]

Table ppgal_favorites is missing an index on userid, which causes full table scans when viewing a member profile.

[Chuck S]

Thanks not really a true bug but will have a look.

[Luciano]

@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...
Luc

[Chuck S]

There is already an index present on the images table for field dateline Luciano

[Luciano]

sorry,
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..)

Luc

[Chuck S]

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

[Luciano]

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..)

Luc

[Chuck S]

Okay so you where doing a mod now I know where your coming from because I knew there was no functionality like that.

[dfidler]

Hey Chuck,

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;

Cheers,
Dave.