I Like This - Reset 'Likes' every month

hbf

I did not index these columns within the plugin setup method. you can do that through phpmyadmin or whatever mysql admin tool you use. it's not really necessary to modify the plugin code to do it for you.

50sQuiff

@businessdad said:
I'm not sure I understand what do you mean with "getting them indexed from within the Framework". Index are created, once off, on the table itself. If plugin doesn't do it, you either have to do it manually, or alter the code to add such indexes. However, such operation is normally executed on installation, therefore, even if you modified the code, you would have to disable/enable the plugin.

I would've thought it's fairly obvious what I meant. When you install Vanilla, do you have to use phpMyAdmin to index the UserID and Email columns? No, but they're indexed all right. So somewhere in the Garden/Vanilla framework the User table is being created with certain columns indexed.

I'll be grepping around to find out how to do this. In case anyone already knows, do share.

businessdad

@50sQuiff The framework itself doesn't take care of creating indexes on fields added by plugins. As I explained in my previous post, it's the plugin itself that, by using the Schema object, creates and alters the required tables.

How can you do that?
You can do it by using Gdn::Database()->Database->Structure() object. Example:

Gdn::Database()->Database->Structure()->Query('ALTER TABLE MyTable ADD INDEX ...');

50sQuiff

Thanks for that BD!

I think you're arguing semantics though. You're adding the index via the framework there, rather than using an external tool. Ideal. I'll post back with the results later.

50sQuiff

Also, some research and testing is required to see if this is the correct decision, given the potential impact of indexing on these constantly updating columns.

peregrine

@50sQuiff said:
Also, some research and testing is required to see if this is the correct decision, given the potential impact of indexing on these constantly updating columns.

you can always delete the indexes if things go south performance wise, it won't be any worse than the initial setup.

50sQuiff

The answer was exceedingly simple. Declare your columns indexed as follows:

GDN::Structure() ->Table('AllLikes') ->PrimaryKey('ID') ->Column('CommentID', 'int(11)', TRUE, 'index') ->Column('DiscussionID', 'int(11)', TRUE, 'index') ->Column('UserID', 'int(11)', FALSE) ->Set(TRUE);

hbf

@50sQuiff said:
The answer was exceedingly simple. Declare your columns indexed as follows:

GDN::Structure() ->Table('AllLikes') ->PrimaryKey('ID') ->Column('CommentID', 'int(11)', TRUE, 'index') ->Column('DiscussionID', 'int(11)', TRUE, 'index') ->Column('UserID', 'int(11)', FALSE) ->Set(TRUE);

you then have to disable, then re-enable the plugin.

BTW - indexing isnt free.

hbf

also, the code above doesn't impact the indexing of the user table. that is the code which creates the AllLikes table.

50sQuiff

I know and... I know. I was just posting back so anyone searching for how to set up indexes in their plugins knows what to do.