MySQL Forums
Forum List  »  Performance

Using more than one Index
Posted by: Christian Wolf
Date: December 18, 2004 09:04AM

Hello!

I'm migrating a database from Sybase to MySQL. I have some trouble, searching for records in a table. I use InnoDB for a Table with about 7,5 million rows. There are indexes on four columns. When searching this table, there are always at least two predicates used, which have an index. The tables looks like this:

CREATE TABLE Orders (
oid int(11) NOT NULL default '0',
CreateTimeStamp int(11) NOT NULL default '0',
CustomerID int(11) NOT NULL default '0',
ArticleID int(11) NOT NULL default '0',
CustomerKey char(28) character set latin1 collate latin1_bin NOT NULL default '',
more columns without indexes,
PRIMARY KEY (`oid`),
KEY `Orders_CreateTimeStamp` (`CreateTimeStamp`),
KEY `Orders_CustomerID` (`CustomerID`),
KEY `Orders_ArticleID` (`ArticleID`),
KEY `Orders_CustomerKey` (`CustomerKey`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Is it correct, that MySQL can use only on index per table for a query?

If this is true, I would have to create an index for each combination of predicates I will use for queries on this table? If I use two out of four possible predicates, I would need 6 multi-column indexes, instead of 4 single-column indexes. Like this?

CREATE TABLE Orders (
oid int(11) NOT NULL default '0',
CreateTimeStamp int(11) NOT NULL default '0',
CustomerID int(11) NOT NULL default '0',
ArticleID int(11) NOT NULL default '0',
CustomerKey char(28) character set latin1 collate latin1_bin NOT NULL default '',
more columns without indexes,
PRIMARY KEY (`oid`),
KEY `Orders_CustomerID_CreateTimeStamp` (`CustomerID`,`CreateTimeStamp`),
KEY `Orders_CustomerID_ArticleID` (`CustomerID`,`ArticleID`),
KEY `Orders_CustomerID_CustomerKey` (`CustomerID`,`CustomerKey`),
KEY `Orders_ArticleID_CreateTimeStamp` (`ArticleID`,`CreateTimeStamp`),
KEY `Orders_ArticleID_CustomerKey` (`ArticleID`,`CustomerKey`),
KEY `Orders_CustomerKey_CreateTimeStamp` (`CustomerKey`,`CreateTimeStamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Thanks for your help.

Greetings,
Chris

Options: ReplyQuote


Subject
Views
Written By
Posted
Using more than one Index
4390
December 18, 2004 09:04AM
2339
December 18, 2004 11:30AM
2344
December 18, 2004 12:36PM
2205
December 20, 2004 06:06AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.