Using more than one Index

[Christian Wolf]

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