List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:May 26 2010 9:13pm
Subject:Slow query using string functions
View as plain text  
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




Thread
Slow query using string functionsJerry Schwartz26 May
  • RE: Slow query using string functionsGavin Towey27 May
    • RE: Slow query using string functionsJerry Schwartz27 May
  • Re: Slow query using string functionsJoerg Bruehe27 May
  • Re: Slow query using string functionsBaron Schwartz27 May
    • RE: Slow query using string functionsJerry Schwartz27 May