Speed problem on large tables

[Dan Holdway]

Hi,

I'm tring to migrate from MS SQL 2000 to MySQL, and have come accross a problem that I hope someone can help me with;

I've got a select statement that groups on the first letter of a table field and produces a count for each initial letter, which I use to find the number of products in each alphabetical category. In MS SQL server, this query ran in well under a second, but in MySQL I can't get it under 15 seconds nomatter how I index the tables.

The problem may be the number of rows in the tables as the query joins two tables, one with 250k records and the other with 650k, but as I said MS SQL has no problem with this?

The query I'm trying to run is;

Select left(Amazon_Items.Product_Description, 1) As IndexLetter, Count(Amazon_Items.Amazon_Product_ID) As NumProducts
From Amazon_Items
Inner Join site_amazon_items On Amazon_Items.Amazon_Product_ID = site_amazon_items.Amazon_Product_ID
Where site_amazon_items.Site_ID = 5
Group By left(Amazon_Items.Product_Description, 1)

And the table definitions are;

CREATE TABLE `amazon_items` (
`Amazon_Product_ID` int(11) NOT NULL auto_increment,
`Locale_ID` int(11) NOT NULL default '1',
`ASIN` varchar(50) NOT NULL default '',
`Product_Description` mediumtext,
`Web_Page_File_Name` varchar(255) default NULL,
PRIMARY KEY (`Amazon_Product_ID`),
UNIQUE KEY `Amazon_Product_ID` (`Amazon_Product_ID`),
UNIQUE KEY `Unique_ASIN` (`Locale_ID`,`ASIN`),
UNIQUE KEY `Product_Key_Letter` (`Amazon_Product_ID`,`Product_Description`(1)),
KEY `Locale_ID` (`Locale_ID`),
KEY `ASIN` (`ASIN`),
KEY `Web_Page_File_Name` (`Web_Page_File_Name`)
) TYPE=InnoDB

CREATE TABLE `amazon_items` (
`Amazon_Product_ID` int(11) NOT NULL auto_increment,
`Locale_ID` int(11) NOT NULL default '1',
`ASIN` varchar(50) NOT NULL default '',
`Product_Description` mediumtext,
`Web_Page_File_Name` varchar(255) default NULL,
PRIMARY KEY (`Amazon_Product_ID`),
UNIQUE KEY `Amazon_Product_ID` (`Amazon_Product_ID`),
UNIQUE KEY `Unique_ASIN` (`Locale_ID`,`ASIN`),
UNIQUE KEY `Product_Key_Letter` (`Amazon_Product_ID`,`Product_Description`(1)),
KEY `Locale_ID` (`Locale_ID`),
KEY `ASIN` (`ASIN`),
KEY `Web_Page_File_Name` (`Web_Page_File_Name`)
) TYPE=InnoDB

Any help would be most appreciated!

Dan