The inner join should ALWAYS return a faster result than the union, if you have the indexes correctly.
Can you send me the explain of the inner join version and also the full table structure and indexes on the table? This should be fairly easy to solve. > -----Original Message----- > From: Fredrik Carlsson [mailto:[EMAIL PROTECTED] > Sent: Sunday, January 02, 2005 4:45 PM > To: mysql@lists.mysql.com > Subject: Re: Max connections being used every 10-12 day. > > The inner join statement returned the same stuff but it was not as fast > as the union is and the inner join seems to use more cpu resources. > > Could these union queries really be the problem behind my occasional > lock ups and that 200 connections being used? i mean the server is not > that loaded and the http logs show amazeingly low http traffic the night > of the lock up. > > I upgraded mysql to 4.0.22 and tuned down thread_concurency to 2 to se > if that helps. > > When these lock ups occures it dont seems like mysql is freeing the > connections, > for exampel the last time it happend was around 02:00 a couple of days > ago and when i checked the server 14 hours later (16:00) it still said > that max_connections was full and mysqld was idling using 0% of the CPU, > if the queries was queued up would'nt mysql at least show some activity? > > // Fredrik > > Donny Simonton wrote: > > >Frederick, > >What exactly are you trying to accomplish? Personally, I don't recommend > >using union unless absolutely necessary, since most people don't really > >understand when it should be used. And I think it shouldn't be used in > this > >case either. > > > >Select A.id, A.parent, B.id, B.parent > >from art A inner join art B using (id) > >order by A.date; > > > >See if that gives you the same results as the original query and then > >explain it to see if you get anything differently. > > > >Also what is the table structure including indexes of the table? > > > >Donny > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
