UPDATE: SELECT statement changed (the first one was not wrong but foolish…
I have the following table of objects identified by columns ‘objID’ and ‘stackVer’ with data columns data1, data2 … (much more) in a table “Object”.
Data processing must be made with the data stored in data1, data2…, using a certain set of parameters defined in table “Recipe”.
For processing, different configurations may be used, defined in table “Config”, each configuration holds the “recipeID” of the parameter set that is currently active.
Now I need all rows from the “Object” table that have value ‘prepared’ set in column “processingStatus” in table “ObjectStatus”, and the appropriate recipeID from table “Config” with “configType” set to e.g. ‘Auto’.
Output should be like this:
objID stackVer data1 data2 data3 recipeID---------------------------------------------------10001 1 66.4 2.3 3 101
I hope this is understandable 
The SELECT statement below works, but it […] is quite slow (several seconds with 1.4 million objects, there will be several billions later…)
Any idea how I can speed up this query with appropriate indexing and/or a more sophisticated SELECT statement and/or better table organizing?
Many thanks for help!
Robert
Tables:
drop table if exists Object;create table Object( objID bigint not null, stackVer tinyint not null, data1 real, data2 real, data3 real);create index idx_Object_objID on Object (objID);create index idx_Object_stackVer on Object (stackVer);create index idx_Object_objID_stackVer on Object (objID, stackVer);drop table if exists ObjectStatus;create table ObjectStatus( objID bigint not null, stackVer tinyint not null, configID tinyint, processingStatus varchar(20) /* may be ‘prepared’, ‘processing’, ‘processed’ /);create index idx_ObjectStatus_objID on ObjectStatus (objID);create index idx_ObjectStatus_stackVer on ObjectStatus (stackVer);create index idx_ObjectStatus_objID_stackVer on ObjectStatus (objID, stackVer);create index idx_ObjectStatus_processingStatus on ObjectStatus (processingStatus);drop table if exists Config;create table Config( configID tinyint not null, configType varchar(20), / may be ‘Auto’, ‘Manual’, ‘User1’, ‘User2’… */ recipeID tinyint, configDescription varchar(256));insert into Config values (101, “Auto”, 101, “Default”);insert into Config values (102, “Manual”, 101, “Default”);drop table if exists Recipe;create table Recipe( recipeID tinyint not null, recipePath varchar(256), recipeDescription varchar(256));insert into Recipe values (101, “/path/to/recipes/Default”, “Default”);
My current SELECT statement to do this (updated):
select Object.objID, Object.stackVer, Object.IcB1, Object.IcB2, Object.IcB3, a.recipeID from Object left outer join (select Config.recipeID, ObjectStatus.objID, ObjectStatus.stackVer, ObjectStatus.processingStatus from ObjectStatus left outer join Config on Config.configID=ObjectStatus.configID where ObjectStatus.processingStatus=‘prepared’) a on a.objID=Object.objID and a.stackVer=Object.stackVer where a.recipeID limit 2;
EXPLAIN says (updated):
±—±------------±-------------±-----±---------------------------------------------------------------±----------------------------------±--------±-------------------±--------±------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±-------------±-----±---------------------------------------------------------------±----------------------------------±--------±-------------------±--------±------------+| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 1070000 | Using where || 1 | PRIMARY | Object | ref | idx_Object_objID,idx_Object_stackVer,idx_Object_objID_stackVer | idx_Object_objID_stackVer | 9 | a.objID,a.stackVer | 11 | || 2 | DERIVED | ObjectStatus | ref | idx_ObjectStatus_processingStatus | idx_ObjectStatus_processingStatus | 23 | | 1176300 | Using where || 2 | DERIVED | Config | ALL | NULL | NULL | NULL | NULL | 2 | |±—±------------±-------------±-----±---------------------------------------------------------------±----------------------------------±--------±-------------------±--------±------------+