1347RE: 1346Re: 1344MYSQL IN clause is expensive?

if you need to search on that table yes it will be more expensive, but you are still adding it whether it be in the sub query or explicitly in the join. Out of the two options, the left join should perform better.

When in doubt, write up both queries and put time stamps before and after them. When doing this, just make sure you execute each statement at LEAST five time with different data in each statement - to get a good feel for processing time (otherwise SQL might cache your results and it could be a bad test).

Oh, and make sure you index your tables to increase performance. If you look in the execution plan you will be ablle to see if your joins and queries are scanning the entire table or using your indexes.

> Date: Fri, 11 Dec 2009 01:37:30 -0800
> Subject: 1346Re: 1344MYSQL IN clause is expensive?
> From: natarajanonline@gmail.com
> To: it_help_desk@googlegroups.com
>
> Thanks Dennis
>
> I have one more doubt. Is that join will not be too heavy bcoz
> actually i have shown a sample query, but in real we already have some
> 3-4 tables joined and only for this if we again join then it will be
> expensive. Pls comment on this.
>
> On Dec 10, 7:17 pm, Dennis Benedetto <dbened...@hotmail.com> wrote:
> > yes it is, it is better to do a left join on the table and exclude in the where clause where one of the fields is not null
> >
> >
> >
> >
> >
> > > Date: Wed, 9 Dec 2009 23:20:19 -0800
> > > Subject: 1344MYSQL IN clause is expensive?
> > > From: natarajanonl...@gmail.com
> > > To: it_help_desk@googlegroups.com
> >
> > > Hi,
> >
> > > I would like to use SELECT * from users where userid IN(SELECT userid
> > > from user_book where availability=1)
> >
> > > In the above subquery, it can return 1000 of records. Whether this
> > > operation is expensive?
> >
> > > Dot Net, VB.NET, C#, ADO.Net, ASP.NET, XML, WML, Ajax, SAP, Ruby, Java, SAP, WAP, WML, PHP, MYSQL, ASP, SQL, HTML, DHTML CSS, Flash, Flex, IT Jobs , Freelancer Projects & many more
> >
> > > Read more..http://groups.google.com/group/it_help_desk
> >
> > _________________________________________________________________
> > Windows Live Hotmail gives you a free,exclusive  gift.http://www.microsoft.com/windows/windowslive/hotmail_bl1/hotmail_bl1....- Hide quoted text -
> >
> > - Show quoted text -
>
> Dot Net, VB.NET, C#, ADO.Net, ASP.NET, XML, WML, Ajax, SAP, Ruby, Java, SAP, WAP, WML, PHP, MYSQL, ASP, SQL, HTML, DHTML CSS, Flash, Flex, IT Jobs , Freelancer Projects & many more
>
> Read more.. http://groups.google.com/group/it_help_desk

---

Hotmail: Powerful Free email with security by Microsoft. Get it now.