MySQL Forums
Forum List  »  Optimizer & Parser

Re: Join Query Performance Problme
Posted by: irek kordirko
Date: February 03, 2012 11:27AM

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.

Options: ReplyQuote

Written By
February 01, 2012 06:51AM
February 01, 2012 11:14AM
February 02, 2012 12:39AM
February 02, 2012 06:47AM
February 02, 2012 07:02PM
February 03, 2012 12:29AM
Re: Join Query Performance Problme
February 03, 2012 11:27AM
February 04, 2012 10:39AM
February 06, 2012 06:13AM
February 14, 2012 06:08PM
February 03, 2012 10:18AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.