Hi, There are 2 peoblems with subselect
1. You cant use a limit on the nested select 2. Id the number of elements in the in clause exceeds the subselect buffer you will run into performance issues ans eventually you query will be doomed. Inner joins in,this is the best option for this . You can use a temp table for this On 14 Sep 2011 01:35, "Alex Nikitin" <niks...@gmail.com> wrote: > On Tue, Sep 13, 2011 at 3:45 PM, Dotan Cohen <dotanco...@gmail.com> wrote: > >> On Tue, Sep 13, 2011 at 21:34, Alex Nikitin <niks...@gmail.com> wrote: >> > And this will be faster or at least more efficient with a limit (e.g. >> limit >> > 50) this way when you have found the 50 users in the "in" statement, you >> > don't continue iterating through the rest of your data set... >> > >> >> The number is never exactly 50 but rather some arbitrary large number. >> But there is no need for LIMIT, that is the purpose of the _INNER_ >> JOIN. INNER means to only return the matching rows. >> >> >> -- >> Dotan Cohen >> >> http://gibberish.co.il >> http://what-is-what.com >> > > Dotan, > > IN (the function used in all of the queries above) is not the same as an > INNER_JOIN, inner join joins 2 tables, as you have already described, IN > however is a function that return 1 if the value being searched for is in > the array of its values or 0 if it is not, thus IN is not an inner join, but > a comparator function, thus if you are using IN, limit will indeed be more > efficient than it's omission for exactly the reason i have stated in my > previous post. Because your user array seems to be in php, and implode has > been a topic of discussion above as well, setting an adequate limit is a > simple task with the php's count function. > > This is all ofcourse void if the user array being pulled from mysql, in > which case you could simply join the two tables to get your resulting data > set. The trick there is to use the USING clause which seems to run a lot > faster than any ON clause, or work on an optimized subselect, especially if > you are running a cluster. > > > -- > The trouble with programmers is that you can never tell what a programmer is > doing until it’s too late. ~Seymour Cray
