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
