Re: Cannot optimize this query - Joseph Bueno - com.mysql.lists.mysql - MarkMail

Want your own MarkMail? Tell us about it.

Refine Search

2 messages in com.mysql.lists.mysqlRe: Cannot optimize this query

From	Sent On	Attachments
Mike Hillyer	15 Apr 2003 15:16
Joseph Bueno	16 Apr 2003 01:22

Subject:	Re: Cannot optimize this query
From:	Joseph Bueno (jose...@trader.com)
Date:	04/16/2003 01:22:28 AM
List:	com.mysql.lists.mysql

Hi,

You should at least create an index on AffiliationID. A compound index on (AffiliationID, ProductGroup, ItemCode) may also help.

You can check: http://www.mysql.com/doc/en/MySQL_indexes.html http://www.mysql.com/doc/en/EXPLAIN.html for more details.

Mike Hillyer wrote:

Hi All;

Just looking for some help in optimizing this query;

SELECT * FROM wd WHERE AffiliationID = 0 ORDER BY ProductGroup, ItemCode;

mysql> desc wd; +--------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------+-------------+------+-----+---------+-------+ | AffiliationID | int(11) | YES | | NULL | | | SupplierID | int(11) | YES | | NULL | | | PriceFileID | int(11) | YES | | NULL | | | ProductGroup | char(4) | YES | | NULL | | | ItemCode | char(50) | YES | | NULL | | | Description | char(50) | YES | | NULL | | | ListPrice | float(18,2) | YES | | NULL | | | Price1 | float(18,2) | YES | | NULL | | | Price2 | float(18,2) | YES | | NULL | | | Price3 | float(18,2) | YES | | NULL | | | Price4 | float(18,2) | YES | | NULL | | | Price5 | float(18,2) | YES | | NULL | | | Cost | float(18,2) | YES | | NULL | | | CoreCost | float(18,2) | YES | | NULL | | | CoreSelling | float(18,2) | YES | | NULL | | | Weight | float(18,2) | YES | | NULL | | | Package | int(11) | YES | | NULL | | | PriceEffectiveDate | datetime | YES | | NULL | | | Popularity | char(2) | YES | | NULL | | | Cat0 | tinyint(4) | YES | | NULL | | | Cat1 | tinyint(4) | YES | | NULL | | | Cat2 | tinyint(4) | YES | | NULL | | | Cat3 | tinyint(4) | YES | | NULL | | | Cat4 | tinyint(4) | YES | | NULL | | | Cat5 | tinyint(4) | YES | | NULL | | | Cat6 | tinyint(4) | YES | | NULL | | | Cat7 | tinyint(4) | YES | | NULL | | | Cat8 | tinyint(4) | YES | | NULL | | | Cat9 | tinyint(4) | YES | | NULL | | | Cat10 | tinyint(4) | YES | | NULL | | | Cat11 | tinyint(4) | YES | | NULL | | | Cat12 | tinyint(4) | YES | | NULL | | | Cat13 | tinyint(4) | YES | | NULL | | | Cat14 | tinyint(4) | YES | | NULL | | | Cat15 | tinyint(4) | YES | | NULL | | | Cat16 | tinyint(4) | YES | | NULL | | | Cat17 | tinyint(4) | YES | | NULL | | +--------------------+-------------+------+-----+---------+-------+ 37 rows in set (0.00 sec)

I know the layout is not normalized, but I am porting an existing ACCESS based app and cannot change the schema in any way that affects the VB client. Anyway, non of the columns are unique, I have tries non-unique indices and the optimizer ignores them. For the given query where would you put the index, and do I need to force index use?