Indexing issue.

Checked different variations of idata_xxx indexing.
Only indexing item_id field seems to make any sense.
Any other combinations are not used by MySQL query optimizer.
 
So I updated config on my server to the following:
 
CREATE TABLE idata_%d (
        item_id integer not null,idata_timestamp integer,idata_value
varchar(255), index item_id(item_id)
) TYPE=InnoDB;
 
As more DCI's for host we have - as greater performace we get from this
index, as it efficiently cut's out other DCI's from processing.
 
Also performace can be increased significantly by replacing
"SELECT idata_timestamp,idata_value FROM idata_%d WHERE item_id=%d%s ORDER
BY idata_timestamp DESC"
to
"SELECT idata_timestamp,idata_value FROM idata_%d WHERE item_id=%d%s ORDER
BY idata_timestamp DESC limit XXX"
in ./server/core/session.cpp.
Where XXX is actual ammount of records that we plan to show.
 
My tests show that "limit" gives at least 4x higher performace in case if
table is quite big. And up to ~100 times higher performace on repeated
queries with same parameters. Anyway it's much more resource efficient and
also gives MySQL optimizer a chance to use some additional tricks.
 
I didn't succed to make MySQL using any other indexes when sorting timestamp
and limiting ammount of records to select. He didn't use them in any
combination.
 
Don't know about other SQL servers.
 
Regards,
Igor.
 
PS. On my server I just did "show tables", copied it to file and replaced
spaces and symbols "|" around table names to alter table command parts. It
took 30 seconds in vim. Running resulting "script" took about 10 minutes.
 
Received on Sat Feb 19 2005 - 18:22:31 EET