Joomla! Discussion Forums

I made an alteration that significant improves the processing of the database. my table mos_core_log_itens have 230,000 contents only one simple update of hit+1 made a consultation in the 230 a thousand registers after I to create one INDEX keys  in ( time_stamp, item_id ) fields, making with that the mysql now read only 17 rows. I had a very good result.

UPDATE mos_core_log_items
SET hits = ( hits + 1 )
WHERE time_stamp = '2007-04-16'
AND item_table = '#__content'
AND item_id = 14316;

without INDEX keys :  7,0241 seg
with INDEX keys : 0.002 seg

It's very very good for performance in big database;

full idex on all 3 values used in the where might bring even slightly better performances (even if 3rd might seem redundant: experienced that on a busy website being optimized together with MySQL engineers), as it becomes a "covered index" (means index and only index will be used always for sure) !

index ( time_stamp, item_id,  item_table )

OR, to make it also usable for searches by item_id and table (when left parts can be used for a query, the index will be used if the sql optimizer finds it "selective enough"):

index ( item_id,  item_table, time_stamp )

probably the latest is the one which makes most sense.

When creating this last index, don't forget to remove existing index on item_id, if there is one, to avoid duplicate indexes which damage performance.