Jerry, Are you sure this is really your explain plan for this query? That's not at all what I would expect to see.
Regards, Gavin Towey -----Original Message----- From: Jerry Schwartz [mailto:je...@gii.co.jp] Sent: Wednesday, May 26, 2010 2:14 PM To: mysql@lists.mysql.com Subject: Slow query using string functions I have a pretty simple query that seems to take a lot longer than it ought to (over 2 minutes). Table `feed_new` has a single VARCHAR(255) column, `new_title`, that is an index. Table `prod` has many fields: `prod_title` and `pub_id` are both indexes (VARCHAR). `feed_new` has 895 records, `prod` has 110432. 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; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: feed_new type: index possible_keys: NULL key: new_title key_len: 768 ref: NULL rows: 1 Extra: Using index *************************** 2. row *************************** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: pub_id,pub_id_2 key: pub_id key_len: 48 ref: const rows: 9816 Extra: Using where The query is doing a scan of the 9816 records that have pub_id = @PUBID, but even so this seems like a long time. Are the built-in string functions really that slow? I suspect it would be faster if I built separate tables that had just the shortened versions of the titles, but I wouldn't think that would be necessary. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=gto...@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 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 Humbolt 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=arch...@jab.org
