Basically, I'm doing a: select <fields> FROM Database1.table1 WHERE indexed_field IN (Select field from Database2.table2, ....);
It's taking about 40sec to execute where table1 (InnoDB) only has about 33k records and my subselect is returning about 600 records. Explain shows that it's doing a type=ALL against table1, even though the indexed_field is an indexed varchar(64). I've verified the subselect executes in under 1 second so I know it's not the problem. I'm guessing that MySQL just can't keep everything in memory at once to use the index since the indexed_field is relatively large. Normally, I'd compare against an integer primary key, but that's not possible and I can't modify the schema to make it possible. I've been reading the my.cnf documentation and various tuning articles, but it's not clear what variables I should tweak to solve this specific issue. Server is a dual-quad core w/ 4GB of RAM, although it's not dedicated to MySQL (webserver and some other database centric background jobs run). Table1 however is on a dedicated RAID1 disk pair and is getting regular inserts/deletes (it's a log table). Any advice would be appreciated! -- Aaron Turner http://synfin.net/ Twitter: @synfinatic http://tcpreplay.synfin.net/ - Pcap editing and replay tools for Unix & Windows Those who would give up essential Liberty, to purchase a little temporary Safety, deserve neither Liberty nor Safety. -- Benjamin Franklin "carpe diem quam minimum credula postero" -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
