mysql performs slow with a subquery, its better to use sql without subquery
On 7/8/08, Lamp Lists <[EMAIL PROTECTED]> wrote: > > > > ----- Original Message ---- > From: John Hicks <[EMAIL PROTECTED]> > To: Lamp Lists <[EMAIL PROTECTED]> > Cc: mysql@lists.mysql.com > Sent: Tuesday, July 8, 2008 11:20:16 AM > Subject: Re: which query solution is better? > > Lamp Lists wrote: > > hi, > > I would like to get your opinions regarding which query you think is > better solution and, of course - why. > > I have (very simplified example) 3 tables: orders, members and addresses > > I need to show order info for specific order_id, > > solution 1: > > select ordered_by, order_date, payment_method, order_status > > > > from orders > > where order_id=123 > > select m.name, a.address, a.city, a.state, a.zip > > from members m, addresses a > > where m.member_id=$ordered_by and a.address_id=m.address_id > //$ordered_by is value from first query > > solution 2: > > select ordered_by, order_date, payment_method, order_status, (select > m.name, a.address, a.city, a.state, a.zip from members m, addresses a > where m.member_id=ordered_by and a.address_id=m.address_id) > > > > from orders > > where order_id=123 > > (queries are written without testing and maybe it doesn't work exactly, > but it's more to gave you the idea what I'm talking about :D) > > also,what if I have to list 20,50 or 100 orders instead one order? would > be subquery still be an option? > > thanks for any opinion. > > -ll > > > > I don't understand what syntax you're using for your second solution. > > Your first solution uses two separate queries which will accomplish the > task. > > They could be combined into a single query like this: > > select * from orders > left join members on member_id = ordered_by > left join addresses on addresses.address_id = members.address_id > where order_id = 123 > > -- john > > sorry john. my bad. I should test the query before I post it because it > doesn't work that way :D > > though, let me modify the question: > > solution 1: > select o.ordered_by, o.order_date, o.payment_method, o.order_status, > concat(m.first_name, ' ', m.last_name) name > left join members m on m.member_id=o.ordered_by > from orders o > where o.order_id=123 > > vs. > > select o.ordered_by, o.order_date, o.payment_method, o.order_status, > (select concat(first_name, ' ', last_name) name from members where > member_id=o.registered_by) name > from orders o > where o.order_id=123 > > in first solution there is join and in second subquery. what's better and > why? > > sorry for this mess :D > > -ll > > > >
