Mysql often handles subqueries poorly. It's best to rewrite that as a JOIN instead:
http://dev.mysql.com/doc/refman/5.1/en/rewriting-subqueries.html If you have further questions after doing that, show the table structures, the query, and the explain output. -----Original Message----- From: Aaron Turner [mailto:[email protected]] Sent: Tuesday, December 07, 2010 11:10 AM To: [email protected] Subject: explain shows type = ALL for indexed column 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/[email protected] This message contains confidential information and is intended only for the individual named. If you are not the named addressee, you are notified that reviewing, disseminating, disclosing, copying or distributing this e-mail is strictly prohibited. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any loss or damage caused by viruses or errors or omissions in the contents of this message, which arise as a result of e-mail transmission. [FriendFinder Networks, Inc., 220 Humboldt Court, Sunnyvale, CA 94089, USA, FriendFinder.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[email protected]
