will fleury Wrote:
-------------------------------------------------------
> so now i have four result sets in memory with 6000
> rows each (~5MB).
Does it mean that a combination of status+time is unique in all three tables ?
Well, there is no information about that in tables structures.
You know that, but MySql doesn't know.
Tell MySql about that:
alter table fastocksdata
add constraint symbol_ldtime_unq unique( stocksymbol, loadedTime );
alter table pricerelated
add constraint symbol_ldtime_unq unique( stocksymbol, loadedTimeTA );
alter table indicators
add constraint symbol_ldtime_unq unique( stocksymbol, loadedTimeTA );
You can experiment with order of columns - stocksymbol first, then loadedTime, or inversly. The constraint's order has infuence on the order of columns in corresponding index that MySql creates for the constraint.
Also for table stockssymbols, if stockSymbol column is unique then it means,
that this column should be marked as the primary key.
If you don't want to change the table structure, just add a constraint:
alter table stockSymbol
add constraint symbol_unq unique( stocksymbol )
but the best approach would be to drop `symbolID`column, alter the table and mark stocksymbol as a primary key, then alter remaining three tables and add a foreign key constraint on stocksymbol column to them.
I don't know if MySql uses information about referential integrity, but most optimizers in other leading databases like oracle or MS SQL can use it to choose beter execution plans.
Try the above commands, then check how your query will perform after these modifications.
Edited 2 time(s). Last edit at 02/03/2012 11:32AM by irek kordirko.