Hello,
I have two tables:
CREATE TABLE categories (
CategoryNumber int(11) NOT NULL,
ItemNumber int(11) NOT NULL,
KEY CategoryNumber (CategoryNumber),
KEY ItemNumber (ItemNumber)
);
CREATE TABLE items (
ItemNumber int(11) NOT NULL auto_increment,
Description varchar(100) NOT NULL,
Price bigint(20) unsigned NOT NULL,
KEY Price (Price)
);
Each item can have multiple categories.
Each table contains more than 1 million records.
This kind of query gets executed a lot:
SELECT I.* FROM
items AS I,
categories AS C
WHERE
C.CategoryNumber = 123 AND
C.ItemNumber = I.ItemNumber AND
I.Price >= 1000
LIMIT 0,51;
I would like to improve the performance of this query.
My question is:
Would a double index on both fields in the categories table
increase performance?
In other words, would this structure be better:
CREATE TABLE categories (
CategoryNumber int(11) NOT NULL,
ItemNumber int(11) NOT NULL,
KEY CategoryItem (CategoryNumber,ItemNumber)
);
Thanks!
Tim
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
