I was just curious what kind of server load my alerts puts on a forum. I had an idea regarding something similar when a user gets a PM but I was unsure how hars it would be on a forum that is constantly looking for new pms like the my alerts plugin.
Its pretty marginal, but it does have an effect because some of the queries can be slow.
I spent some time the other day adding additional indexes on the alerts table. I will drag them out later to day if they are of interest. They shaved fractions of seconds off of the queries for me, and it all adds up in my opinion.
(2014-07-17, 08:16 AM)Dannymh Wrote: [ -> ]Its pretty marginal, but it does have an effect because some of the queries can be slow.
I spent some time the other day adding additional indexes on the alerts table. I will drag them out later to day if they are of interest. They shaved fractions of seconds off of the queries for me, and it all adds up in my opinion.
Please.
I'm planning for the next release to try cut down the load/query count. Within that plan is to add a few extra indexes. I also plan to change how alerts are inserted so that only one insert query ever runs on the alerts table per page load. I don't believe I can cut down the select queries on page load, but 1.8 does have a new hook I plan to use between global_start and global_end which should help a little.
Well i know the actual alerts plugins does pms, new posts and more i think. I am only interested in a PM function. Basically i just need to know what kind of load comes from doing just pm.
MyAlerts can be setup to only handle PMs by disabling all the other alert types
It'll add 1 query per page, plus 1 query when sending a PM.
(2014-07-17, 08:27 AM)Euan T Wrote: [ -> ] (2014-07-17, 08:16 AM)Dannymh Wrote: [ -> ]Its pretty marginal, but it does have an effect because some of the queries can be slow.
I spent some time the other day adding additional indexes on the alerts table. I will drag them out later to day if they are of interest. They shaved fractions of seconds off of the queries for me, and it all adds up in my opinion.
Please.
I'm planning for the next release to try cut down the load/query count. Within that plan is to add a few extra indexes. I also plan to change how alerts are inserted so that only one insert query ever runs on the alerts table per page load. I don't believe I can cut down the select queries on page load, but 1.8 does have a new hook I plan to use between global_start and global_end which should help a little.
I am just installing a new copy now so I can figure out the indexes I added
Indexes:
mybb_myalerts: uid, Index, not unique
Rationale, this is used within a join, joined fields should always be indexed. This should add a few fractions of a second from 0.0xxxxx to 0.00xxx or 0.000xxxx
mybb_alert_settings
Indexes: 2 to add here
user_id, not unique
setting_id not unique
rationale: again used within joins so should be indexed, on top of that these are perfect candidates for an index given they are numeric and repeatable. Having an index for a group of settings such as on user_id means that the index can point directly to that part of the table and your query is far more targeted
the following query on my site now runs at Query Time: 0.000554084777832 before the change it was running at 0.06xxxxx
SELECT * FROM mybb_alert_settings s LEFT JOIN mybb_alert_setting_values v ON (s.id = v.setting_id) WHERE v.user_id = 2
Probably a couple more, the primary key indexes may be worth repeating as standard indexes since these become foreign keys also.
I know the time involved seems little but it is saving close to a second on mysql query time on my site, so that is a huge saving for me.
Seems fair
How would a unique index across mybb_alert_settings.user_id and mybb_alert_settings.setting_id be too?
could you have unique here?
Any place where a field should be unique make it unique particularly for joins. Innodb will function better with these indexes too.
I think a unique may go either way, let me test it
As suspected you can't have a unique index here because their are multiple entries
There should be only one unique across those two columns
I'm meaning for a compound key:
ALTER TABLE `mybb_alert_settings` ADD UNIQUE `unique_user_setting`(`user_id`, `setting_id`);
I think it should work, if not my table architecture is wrong.
would need to be mybb_alert_setting_values
however not sure how you could get unique on either of those, since setting_id and user_id will have multiple duplicate values in the table