Hi Jerry, all!
I second Travis' advice:
Travis Ard schrieb:
> Can you create a second, indexed column in your feed_new temp table that
> 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 rows
> from the feed_new table.
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]
> Sent: Tuesday, August 10, 2010 3:39 PM
> To: mysql@stripped> Subject: Slow query using string operator
>
> I'm running a set of queries that look like this:
>
> [[...]]
>
> 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) =
> LEFT(prod.prod_title, LENGTH(prod.prod_title) - 5)
> WHERE prod.pub_id = @PUBID AND prod.prod_discont = 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örg
--
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

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.