On Mon, Sep 08, 2003 at 05:50:15PM +0200, Bertil Wergelius wrote: > I have an application, where I use the following select-query: > > select h.huvud_id,datum, start_tid, stopp_tid,l.lokal_beteckning, > namn,adress,rubrik,amne,grupp,kursnamn,lr.signatur > from huvud_schema as h,lokaler as l,huvud_amnen as ha,amnen as a, > grupper as g,huvud_grupper as hg,kurser as k,kurs_huvud as kh, > larare as lr,larare_huvud as lrh > where h.lokal_beteckning=l.lokal_beteckning > and h.huvud_id=ha.huvud_id > and a.amn_id=ha.amn_id > and h.huvud_id=hg.huvud_id > and g.grupp_id=hg.grupp_id > and h.huvud_id=kh.huvud_id > and k.kurs_id=kh.kurs_id > and h.huvud_id=lrh.huvud_id > and lr.signatur=lrh.signatur > and datum between '2003-09-01' and '2004-01-30' > order by datum,start_tid,huvud_id,a.amn_id,lr.signatur > > The problem is that it’s very slow. Time measured (approx. 100 sec.) for > 390 records. I have made an explain select etc with the same select. > Which seem to point out the table amnen (alias a) or huvud_amnen (alias > ha) as the culprit: > > table;type;possible_keys;key;key_len;ref;rows;Extra > a;ALL;PRIMARY;NULL;NULL;NULL;7;Using temporary; Using filesort > ha;ref;PRIMARY;PRIMARY;4;a.amn_id;3;Using index > g;ALL;PRIMARY;NULL;NULL;NULL;13; > lr;index;PRIMARY;PRIMARY;10;NULL;19;Using index > k;ALL;PRIMARY;NULL;NULL;NULL;20; > h;eq_ref;PRIMARY,datum;PRIMARY;4;ha.huvud_id;1;Using where > l;eq_ref;PRIMARY;PRIMARY;15;h.lokal_beteckning;1; > kh;eq_ref;PRIMARY;PRIMARY;8;k.kurs_id,h.huvud_id;1;Using where; Using index > hg;eq_ref;PRIMARY;PRIMARY;8;g.grupp_id,h.huvud_id;1;Using where; Using index > lrh;eq_ref;PRIMARY;PRIMARY;14;lr.signatur,h.huvud_id;1;Using where; > Using index
The biggest problem seems to be the fact that MySQL is doing a full scan on "a" rather than using any indexes. But it's a little hard to deciper your output... -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! <[EMAIL PROTECTED]> | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 4 days, processed 158,987,801 queries (438/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
