From: Joerg Bruehe
Date: August 11 2010 12:38pm
Subject: Re: Slow query using string operator
List-Archive: http://lists.mysql.com/mysql/222488
Message-Id: <4C6299A8.2000804@oracle.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15
Content-Transfer-Encoding: quoted-printable

Hi Jerry, all!


I second Travis' advice:

Travis Ard schrieb:
> Can you create a second, indexed column in your feed_new temp table tha=
t
> includes the title without the year appended?  That might allow you to =
get
> by with a single pass through the larger prod table and avoid reading r=
ows
> from the feed_new table.=20

The original query does a string operation on the values from both sides
before checking the result for equality:

> -----Original Message-----
> From: Jerry Schwartz [mailto:jerry@stripped]=20
> Sent: Tuesday, August 10, 2010 3:39 PM
> To: mysql@stripped
> Subject: Slow query using string operator
>=20
> I'm running a set of queries that look like this:
>=20
> [[...]]
>=20
> SELECT
> 	feed_new.new_title AS `New Title FROM Feed`,
> 	prod.prod_pub_prod_id AS `Lib Code FROM DB`,
> 	prod.prod_title AS `Title FROM DB`,
> 	prod.prod_num AS `Prod Num`,
> 	prod.prod_published AS `Published FROM DB`
> FROM feed_new JOIN prod
> ON LEFT(feed_new.new_title, LENGTH(feed_new.new_title) - 5) =3D=20
> LEFT(prod.prod_title, LENGTH(prod.prod_title) - 5)
> WHERE prod.pub_id =3D @PUBID AND prod.prod_discont =3D 0
> ORDER BY feed_new.new_title;

So neither value is taken directly, which means the values in the
indexes (if defined) cannot be used anyway.

If you need these calculations, you should compute and maintain these
values when inserting/updating data (define triggers doing this, or run
periodic maintenance/check jobs), and store them in suitable indexes.

AFAIK, this applies to all comparisons which use function results rather
than column values directly.


HTH,
J=F6rg

--=20
Joerg Bruehe,  MySQL Build Team,  joerg.bruehe@stripped
ORACLE Deutschland B.V. & Co. KG,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603