Tunning Mysql; Rephrase queries Continued
Hey, as of late, I had the opportunity to check the optimization for a heavily loaded phpbb system. It also shows lac of sql optimization in the code.
SELECT DISTINCT t.topic_id, t.topic_title, t.topic_poster, t.topic_first_post_id, t.forum_id, t.topic_replies, u.user_id, u.username, s.post_id, s.post_text FROM phpbb_posts p, phpbb_topics t, phpbb_users u, phpbb_posts_text s WHERE p.topic_id = t.topic_id AND t.topic_poster = u.user_id AND t.topic_first_post_id = s.post_id AND t.forum_id != 2 AND u.user_id != -1 ORDER BY p.post_time desc LIMIT 7;
This is one thing that is tough, and cannot be optimized without modifying the core code, and my exposure with the said script (phpbb) is too low that digging into the system will take many hours. I would recommend to design databases with the end user experience of much priority. For such things like `latest posts`, `latest news`, `new users` and other such things, keep a single table with almost the following structure
create table `aggregate_latest`( `tName` varchar(255) not null default '', `pkId` int unsigned not null default '0', `pOrder` tinyint unsigned not null default '0', unique key `name_pk` (`tName`,`pkId`), key `placement` (`pOrder`) )Engine=MyISAM;
Then every table which needs aggregation is attached with triggers after insert & delete, to update this table with data table name, primary key value and the exact order in chronology or sort order for pOrder. Normally all need the latest on top, that means,
get the max number from config, which would be 7 in this case, delete row 7, for table, increment pOrder by 1 (update aggegate_latest set pOrder = pOrder + 1 where tName='<table>’), insert new row with pOrder as 1.
When reading we use
SELECT group_concat(pkId) into @selected_ids from aggrgate_latest where tName='<table>'; SELECT * from <table> where id in (@selected) order by (@selected);
And for the case above, we would even go for a small piece where the first table would be derived using a subselect, and then join with the other tables, or something similar, even with table joining using where, since sorts are on a very small subset and done on the primary key, the sql server will be really grateful to you.