I'm facing a refactor of some slow code that engages the DB pretty heavily. We're looking for a way to load arbitrary sets of rows from a table using an indexed column of decently high cardinality, and I'm not sure if IN() is a good way to do it or not.
Basically, we're looking at this: CREATE TABLE `foo` ( `foo_id` INT UNSIGNED NOT NULL AUTO_INCREMENT , `bar_id` INT UNSIGNED NOT NULL , `unimportant_1` VARCHAR(8) , `unimportant_2` VARCHAR(8) , `unimportant_3` VARCHAR(8) , PRIMARY KEY( `foo_id` ) , KEY `idx_bar_id` ( `bar_id` ) ); I need to load an arbitrary list of foos by their bar ID (1:N), and I'm not sure the best way to do it. Joining against the `bar` table is, unfortunately, not really helpful since the set loaded is totally arbitrary. As far as I can tell, the constant list would be ~100 elements at most, but take that with a grain of salt. Right now, I'm considering: SELECT * FROM `foo` WHERE `bar_id` IN( 1,2,3,4,5,6,7, ... ,61,62 ); I've done some testing and the performance doesn't seem to be too bad, but I'm worried about scalability. The query execution isn't too bad. | 1 | SIMPLE | foo | range | idx_bar_id | idx_bar_id | 5 | NULL | 7 | Using where | Does anyone have experience with the performance of IN(), given a list of constants, on an indexed column? Any pointers to more information on this would be greatly appreciated. Thanks in advance! kabel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
