On Friday 15 January 2010 13:55:18 fsb wrote: > the example you gave would work with a range constraint: > > WHERE `bar_id` > 0 AND `bar_id` < 63 > > but i guess this is not a general solution. > > i've done exactly this kind of select using an IN constraint very often. > i've not had any trouble with lists of a few hundred so long as i have the > necessary index.
That's a relief to hear. I know my mileage may vary (MMMV?) but it's good to hear someone else has done it. > however, when the list gets really long i've divided it into multiple > selects in the app. i've nothing concrete to go on but a very long list of > constants makes me nervous. can you break something if query length exceeds > packet size? Yeah, absolutely. I didn't think about packet size, more about performance, but that's a great point. Breaking it into smaller chunks is very workable. You probably just saved me from receiving a panicky midnight phone call. ;-) > one thought i had was to create another table `baz` just to store the list > of `bar_id` values. the app would: 1) truncate `baz`, 2) write the list > `bar_ids` into `baz`, 3) select from `foo` joining `baz`. perhaps not a > high performance solution but you could make it scale all the way up. That's not a bad way to go, and I like it a lot better than the IN() thing, but this is almost guaranteed to be run several times concurrently and, since it may involve multiple connections (please don't ask) I don't think temp tables would work. I could take it with CREATE TABLE `baz_$SESSION_ID`... etc., where I generate some session ID, but I may go with the IN() stuff for starters just to see. Thank you VERY much for your help! kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
