Hello all, Suppose I have 3 tables, 1. master (auto_nr, link, dat)
CREATE TABLE master ( auto_nr int(11) unsigned zerofill NOT NULL, link int(11) unsigned zerofill NOT NULL, dat DATE NOT NULL, PRIMARY KEY auto_nr(auto_nr), KEY link_key(link) ) 2. child1 (auto_nr, link1, value1) CREATE TABLE child1 ( auto_nr int(11) unsigned zerofill NOT NULL, link1 int(11) unsigned , value1 int(11), PRIMARY KEY auto_nr(auto_nr), KEY link1_key(link1) ) 3. child2 (auto_nr, link2, value2) CREATE TABLE child2 ( auto_nr int(11) unsigned zerofill NOT NULL, link2 int(11) unsigned , value2 int(11), PRIMARY KEY auto_nr(auto_nr), KEY link2_key(link2) ) At the moment I use temporary tables following way: CREATE TEMPORARY table temp1 Select link, max(dat) as m_dat, sum(value1) as val1 from master left join child1 on master.link=child1.link1 group by link CREATE TEMPORARY table temp2 Select link, sum(value2) as val2 from master left join child2 on master.link=child2.link2 group by link //OK. Actually I use 2 steps to build one temporary table: a. Full CREATE TEMPORARY TABLE statement using KEY (or maybe better INDEX???) if needed. b. INSERT INTO to populate created table. FINALLY: Select m_dat, temp1.link, val1, val2 from temp1, temp2 where temp1.link=temp2.link Can I do it with one select statement in mysql? Sorry to say, I have to create up to 12 different temporary tables to get result sets for everyday use. As you may guess execution of bunch of queries takes time (up to 1 min) and may be considered as rather messy. I�ve come to conclusion that SQL optimization with MySql (using temporary tables) is time consuming. IMHO I need feature called �CREATE VIEW ��, but perhaps I have to learn some of features of standard SQL (i.e. progressive & extensive use of different JOIN types;-) so, really good SQL books regarding this subject; your recommendations. My database is not large (50 tables, max 25 columns per table, currently overall amount ~50Mb plus about 0.2-1Mb each day). I try to stick with SQL92 and keep my client code independent from particular SQL server implementation; maybe someday I have to shift to other back-end, so I want to make migration to different back-end as easy as possible. Speed penalty is inescapable. My system configuration is Compaq PROLIANT ML370, 128Mb RAM, 933 Mhz PIII, RedHat Linux 7.1. It�s dual processor system, so some hardware upgrade is quite possible. I have small number of clients (1-20). Any suggestions for optimal MySql server configuration? Any help will be appreciated. p.s. What�s the difference between KEY and INDEX in CREATE TABLE syntax (any +/- effect on JOIN)? Regards, Alvis __________________________________________________ Do You Yahoo!? Yahoo! Sports - Coverage of the 2002 Olympic Games http://sports.yahoo.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
