Hi all,
Here's a small report which show that it's worth comparing composite
index with memory tables.
It would be worth to experiment on wider tables with more column.
Program is attached (I hope it is correct...), code is below.
Cheers,
create table X1 (objectId BIGINT, chunkId INT, subChunkId INT,
index(objectId), index(subChunkId)) ENGINE=MyISAM
create table X2 (objectId BIGINT, chunkId INT, subChunkId INT,
index(objectId, subChunkId)) ENGINE=MyISAM
create table X3 (objectId BIGINT, chunkId INT, subChunkId INT,
index(subChunkId, objectId)) ENGINE=MyISAM
Lines per table 90000
Launching ['select * from X1 where subChunkId = %s'], 300 times against X1
Elapsed time 0.456371068954
Launching ['select * from X2 where subChunkId = %s'], 300 times against X2
Elapsed time 1.83970189095
Launching ['select * from X3 where subChunkId = %s'], 300 times against X3
Elapsed time 0.513654947281
Launching ['select * from X1 where subChunkId = %s'], 300 times against X1
Elapsed time 0.514945983887
Launching ['select * from X2 where subChunkId = %s'], 300 times against X2
Elapsed time 1.85265922546
Launching ['select * from X3 where subChunkId = %s'], 300 times against X3
Elapsed time 0.534698009491
Launching ['select o1.objectId, o2.objectId from X1 o1, X1 o2 where
o1.subChunkId = %s and o2.subChunkId = %s and o1.objectId <>
o2.objectId'], 10 times against X1
Elapsed time 1.41969299316
Launching ['select o1.objectId, o2.objectId from X2 o1, X2 o2 where
o1.subChunkId = %s and o2.subChunkId = %s and o1.objectId <>
o2.objectId'], 10 times against X2
Elapsed time 1.15225410461
Launching ['select o1.objectId, o2.objectId from X3 o1, X3 o2 where
o1.subChunkId = %s and o2.subChunkId = %s and o1.objectId <>
o2.objectId'], 10 times against X3
Elapsed time 1.15079092979
Launching ['CREATE TABLE dummy SELECT * FROM X1 WHERE subChunkId = %s',
'select o1.objectId, o2.objectId from dummy o1, dummy o2 where
o1.subChunkId = %s and o2.subChunkId = %s and o1.objectId <>
o2.objectId', 'DROP TABLE dummy'], 10 times against X1
Elapsed time 1.43273210526
Launching ['CREATE TABLE dummy SELECT * FROM X2 WHERE subChunkId = %s',
'select o1.objectId, o2.objectId from dummy o1, dummy o2 where
o1.subChunkId = %s and o2.subChunkId = %s and o1.objectId <>
o2.objectId', 'DROP TABLE dummy'], 10 times against X2
Elapsed time 1.356112957
Launching ['CREATE TABLE dummy SELECT * FROM X3 WHERE subChunkId = %s',
'select o1.objectId, o2.objectId from dummy o1, dummy o2 where
o1.subChunkId = %s and o2.subChunkId = %s and o1.objectId <>
o2.objectId', 'DROP TABLE dummy'], 10 times against X3
Elapsed time 1.33042907715
########################################################################
Use REPLY-ALL to reply to list
To unsubscribe from the QSERV-L list, click the following link:
https://listserv.slac.stanford.edu/cgi-bin/wa?SUBED1=QSERV-L&A=1
|