explain shows type = ALL for indexed column

Basically, I'm doing a:

select 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=3DALL 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!

--=20
Aaron Turner
http://synfin.net/=A0 =A0 =A0    Twitter: @synfinatic
http://tcpreplay.synfin.net/ - Pcap editing and replay tools for Unix & Win=
dows
Those who would give up essential Liberty, to purchase a little temporary
Safety, deserve neither Liberty nor Safety.
=A0 =A0 -- 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=3Dgcdmg-mysql-2@m.gmane.o rg

RE: explain shows type = ALL for indexed column

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:synfinatic@gmail.com]
Sent: Tuesday, December 07, 2010 11:10 AM
To: mysql@lists.mysql.com
Subject: explain shows type =3D ALL for indexed column

Basically, I'm doing a:

select 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=3DALL 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 & Win=
dows
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=3Dgtowey@ffn.com


This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f 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-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate 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 transmis=
sion. [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/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: explain shows type = ALL for indexed column

Thanks Gavin. Rewriting the query to not use the subselect solved the prob=
lem!

On Tue, Dec 7, 2010 at 11:33 AM, Gavin Towey wrote:
> Mysql often handles subqueries poorly. =A0It'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.
>

--=20
Aaron Turner
http://synfin.net/=A0 =A0 =A0    Twitter: @synfinatic
http://tcpreplay.synfin.net/ - Pcap editing and replay tools for Unix & Win=
dows
Those who would give up essential Liberty, to purchase a little temporary
Safety, deserve neither Liberty nor Safety.
=A0 =A0 -- 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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: explain shows type = ALL for indexed column

--90e6ba475e5dc223950497fbca19
Content-Type: text/plain; charset=UTF-8

Yeah. The subquery sometimes creates temporary tables, so the performance is
worse than join. Just leaving it.
David Yeung, In China, Beijing.
My First Blog:http://yueliangdao0608.cublog.cn
My Second Blog:http://yueliangdao0608.blog.51cto.com
My Msn: yueliangdao0608@gmail.com



2010/12/8 Aaron Turner

> Thanks Gavin. Rewriting the query to not use the subselect solved the
> problem!
>
> On Tue, Dec 7, 2010 at 11:33 AM, Gavin Towey wrote:
> > 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.
> >
>
> --
> 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=yueliangdao0608@gmail.com
>
>

--90e6ba475e5dc223950497fbca19--