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:je...@gii.co.jp] > Sent: Tuesday, August 10, 2010 3:39 PM > To: mysql@lists.mysql.com > 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.bru...@oracle.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
