Joomla!
http://forum.joomla.org/

new optmization need
http://forum.joomla.org/viewtopic.php?f=268&t=160762		 Page 1 of 1
Author:  nei_rauni [ Mon Apr 16, 2007 7:50 pm ]
Post subject:  new optmization need
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;
Author:  Beat [ Mon Apr 16, 2007 8:45 pm ]
Post subject:  Re: new optmization need
nei_rauni wrote:
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.
Page 1 of 1 All times are UTC
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/