Hi Guys,
Here a small contribution to Joomla 1.5 SQL performances, taken from optimizations on 3 high-traffic (>50k users, sometimes >1k users logged-in same time) websites.
Took many days of work, partly paid by customers agreeing to share the results with the Community
(and for Query 2 and 3 optimization proposals, they come from a famous MySQL performance experts team
). Acknowledgements will follow if wished by the clients.
These optimizations have been done and benchmarked + tested on all 3 high-traffic websites in Joomla 1.0.12, so they are pretty safe to be included for 1.5 beta 2, if you wish to do so, as Joomla's 1.0 database structure is frozen and Joomla 1.5 is the (near) future.
Hope it makes it into 1.5 beta 2...
Best Regards.
Beat
------------------------------------------------------------
Query 1.
Query: SELECT g.* FROM jos_core_acl_aro_groups AS g
INNER JOIN jos_core_acl_groups_aro_map AS gm ON gm.group_id = g.group_id
INNER JOIN jos_core_acl_aro AS ao ON ao.aro_id = gm.aro_id
WHERE ao.value='20608';
Issue: there are no suitable indexes which would be selective enough
and this query causes a full scan.
Solution: an appropriate index needs to be created on this table.
ALTER TABLE `jos_core_acl_aro` ADD UNIQUE INDEX `value` ( `value` );
ALTER TABLE `jos_core_acl_groups_aro_map` ADD INDEX `aro_id` ( `aro_id` );*******************************************************************************
Query 2.
Stats: ~2200 times/day, ~90K rows/query, ~0.3 sec/query
Query: select gr.group_id
from jos_core_acl_groups_aro_map as gr
left join jos_core_acl_aro as aro on aro.aro_id = gr.aro_id
where value=21587;
Issue: "value" column is VARCHAR(240) and 21587 is integer which prevents
MySQL from using an index.
Solution: either key value should either be taken in quotes, ie:
... where value='21587';
or the column should be converted to INTEGER type if it only contains
integer data.
We chose to convert it to INTEGER to check it works fine (indeed, as it's anyway used as such ). Also the index might be smaller and faster with INTEGER than with VARCHAR.
------------------------------------------------------------
Query 3.
Stats: ~400 times/day, ~45K rows/query
Query: SELECT id FROM jos_users WHERE email =
'ken@example.com' AND id != 13727;
Issue: there is no index on "email" column and full scan is performed.
Solution: create an index on "email" column.
CREATE INDEX idxemail ON jos_users ( email );
*******************************************************************
Query 4:
SELECT a.id, a.title, a.hits, a.created
FROM jos_content AS a
WHERE a.created_by = 1639
AND a.state = 1
AND a.sectionid=4
ORDER BY a.created DESC
LIMIT 0,10
Problem: full table scan.
Solution: add appropriate index:
ALTER TABLE `jos_content` ADD INDEX `idx_created_by` ( `created_by` )
********************************************************************
Query 5:
SELECT ....
LEFT JOIN u.username FROM jos_users u ON u.id = c.id
WHERE u.block = 0;
Problem: full table scan...
Solution: add index (and drop an unused one):
ALTER TABLE `jos_users` DROP INDEX `block_regdate`;
ALTER TABLE `mos_users` ADD INDEX `block_id` ( `block` , `id` );
**************************************************************
Query 6: on login: search in jos_users on username
Problem: makes a full table search, since username is not indexed.
Solution: create index:
ALTER TABLE `jos_users` ADD INDEX `username` ( `username` );
****************************************************************
Query 7:
find session of user in jos_sessions to see if user is logged-in (e.g. in users online module):
Problem: search on user_id does full scan, as it's not indexed.
Solution: create index:
ALTER TABLE `jos_session` ADD INDEX `userid` ( `userid` );
********************************************************
Query 8:
Problem:
Duplicate indexes are bad since mysql sometimes (often) gets fooled and misses to use the right one, leading to way slower queries than without indexes at all !!!
e.g. jos_categories:
idx_section: section
cat_idx: section - published - access
idx_section is redundant (you can point phpMyAdmin to jos_category of Joomla 1.5 svn and see the yellow warning).
why is it redundant ?
mysql is clever enough to use cat_idx when it needs an index on section column, ignoring the rest of the index.
Solution: Remove the redundant index with the smallest amount of fields:
ALTER TABLE `jos_categories` DROP INDEX `idx_section`;
*******************************************************************************
Query 9:
Same Problem in jos_core_acl_aro_sections:
value_aro_sections : UNIQUE: value
jos_gacl_value_aro_sections : UNIQUE: value
hidden_aro_sections: hidden
jos_gacl_hidden_aro_sections: hidden
Solution: remove 2 of them: e.g.
ALTER TABLE `jos_core_acl_aro_sections` DROP INDEX `jos_gacl_value_aro_sections`;
ALTER TABLE `jos_core_acl_aro_sections` DROP INDEX `jos_gacl_hidden_aro_sections`;
****************************************************
Query 10:
jos_core_log_searches has no indexes at all.
Solution:
Would make sense to add an index on hits (so the table is pre-sorted when displaying top searches), as well as an index on search_term (so adding hits is quick):
ALTER TABLE `jos_core_log_searches` ADD INDEX ( `hits` );
ALTER TABLE `jos_core_log_searches` ADD INDEX ( `search_term` ( 16 ) )
********************************************************
Query 11:
jos_core_log_items and jos_stats_agents have no index at all.
It would probably make sense to create indexes: not sure which are needed.
**********************************************************
Suggestion 12:
NULL fields: avoid them as much as possible, they make "holes" in indexes, lowering performance more often than increasing it.
e.g. jos_content.date (date 000-00-00 00:00:00 can be used similar way...and has to be handled as well as such).
SOLUTION: change DEFAULT NULL and add NOT NULL in table creations.
(this one might need a little more tests before going to stable, but hey, it's the right time).
***********************************************************
That's it for today....
Login
Register
Rules
FAQ
The team
: