[pLog-general] Database indexes (testing needed)

[Francesc Pla]

Hi,

pLog was born as a personal blogging system, later some community features 
were developed spontaneously becoming a useful tool to create blogging 
communities, ... in previous versions performance for big sites wasn't a main 
goal and 0.3 version lacks a little bit of performance (although there are 
sites with ~2.000 users performing correctly), this situation will be 
improved on pLog 1.0 which will feature Smarty Cache and HTTP Cache to 
provide higher responses, this will also help on bandwidth and hardware 
resources savings. To complete this goal of performing better some database 
improvement is needed, that's why we decided to add some database indexing to 
make queries faster. I just finished on making some basic indexing and I 
would really appreciate if some of you can test it and give me some feedback 
reporting if you notice performance improvements, ...

You can find new database structure in SVN trunk version 
(install/plog_indexed.sql), it hasn't been added to wizard because I want to 
make some tests and have some feedback before making it official.

I also made some SQL queries to update your existing installations to make 
easier for you to test it, at the end of this email you can find updates for 
latest pLog 1.0 snapshot and for 0.3.2 version.

Francesc.

---

INDEX CREATION FOR PLOG 1.0
-----------------------------------

ALTER TABLE plog_articles ADD INDEX (num_reads), ADD INDEX (category_id), ADD 
INDEX (blog_id), ADD INDEX (user_id);
ALTER TABLE plog_articles_categories ADD INDEX (parent_id), ADD INDEX 
(blog_id);
ALTER TABLE plog_articles_comments ADD INDEX (parent_id), ADD INDEX 
(article_id);
ALTER TABLE plog_articles_notifications ADD INDEX (article_id), ADD INDEX 
(user_id), ADD INDEX (blog_id);
ALTER TABLE plog_bayesian_filter_info ADD INDEX (blog_id);
ALTER TABLE plog_bayesian_tokens ADD INDEX (blog_id);
ALTER TABLE plog_blogs ADD INDEX (owner_id);
ALTER TABLE plog_custom_fields_definition ADD INDEX (blog_id);
ALTER TABLE plog_custom_fields_values ADD INDEX (blog_id), ADD INDEX 
(article_id), ADD INDEX (field_id);
ALTER TABLE plog_filtered_content ADD INDEX (blog_id);
ALTER TABLE plog_gallery_albums ADD INDEX (parent_id), ADD INDEX (owner_id);
ALTER TABLE plog_gallery_resources ADD INDEX (album_id), ADD INDEX (owner_id);
ALTER TABLE plog_host_blocking_rules ADD INDEX (blog_id);
ALTER TABLE plog_mylinks ADD INDEX (blog_id), ADD INDEX (category_id);
ALTER TABLE plog_mylinks_categories ADD INDEX (blog_id);
ALTER TABLE plog_myrecent ADD INDEX (blog_id), ADD INDEX (category_id), ADD 
INDEX (user_id);
ALTER TABLE plog_myrecent_categories ADD INDEX (blog_id);
ALTER TABLE plog_referers ADD INDEX (blog_id), ADD INDEX (article_id);
ALTER TABLE plog_trackbacks ADD INDEX (article_id);
ALTER TABLE plog_users_permissions ADD INDEX (blog_id), ADD INDEX (user_id), 
ADD INDEX (permission_id);


INDEX CREATION FOR PLOG 0.3.2
-----------------------------------

ALTER TABLE plog_articles ADD INDEX (num_reads), ADD INDEX (category_id), ADD 
INDEX (blog_id), ADD INDEX (user_id);
ALTER TABLE plog_articles_categories ADD INDEX (blog_id);
ALTER TABLE plog_articles_comments ADD INDEX (parent_id), ADD INDEX 
(article_id);
ALTER TABLE plog_articles_notifications ADD INDEX (article_id), ADD INDEX 
(user_id), ADD INDEX (blog_id);
ALTER TABLE plog_bayesian_filter_info ADD INDEX (blog_id);
ALTER TABLE plog_bayesian_tokens ADD INDEX (blog_id);
ALTER TABLE plog_blogs ADD INDEX (owner_id);
ALTER TABLE plog_filtered_content ADD INDEX (blog_id);
ALTER TABLE plog_gallery_albums ADD INDEX (parent_id), ADD INDEX (owner_id);
ALTER TABLE plog_gallery_resources ADD INDEX (album_id), ADD INDEX (owner_id);
ALTER TABLE plog_host_blocking_rules ADD INDEX (blog_id);
ALTER TABLE plog_mylinks ADD INDEX (blog_id), ADD INDEX (category_id);
ALTER TABLE plog_mylinks_categories ADD INDEX (blog_id);
ALTER TABLE plog_myrecent ADD INDEX (blog_id), ADD INDEX (category_id), ADD 
INDEX (user_id);
ALTER TABLE plog_myrecent_categories ADD INDEX (blog_id);
ALTER TABLE plog_referers ADD INDEX (blog_id), ADD INDEX (article_id);
ALTER TABLE plog_trackbacks ADD INDEX (article_id);
ALTER TABLE plog_users_permissions ADD INDEX (blog_id), ADD INDEX (user_id), 
ADD INDEX (permission_id);