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