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/[email protected]
