Efficient index design for alternating NULL values

[Martin Luy]

Hi,

I have a new MySQL InnoDB table with the following anonymized schema:

CREATE TABLE `sometable` (
`id` bigint(20) NOT NULL,
`col1` bigint(20) DEFAULT NULL,
`col2` varchar(13) DEFAULT NULL,
`col3` varchar(1) NOT NULL,
`col4` datetime NOT NULL,
`col5` datetime DEFAULT NULL,
`col6` varchar(64) DEFAULT NULL,
`col7` int(11) DEFAULT NULL,
`col8` int(11) DEFAULT NULL,
`col9` varchar(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

For every row, the value of either col1 or col2 will be NOT NULL and the other one will be NULL. In most cases col1 will be NOT NULL and col2 will be NULL.
col3 will have 3 possible values. Each combination of col1 and col3 will be unique (except of NULL values in col1), and the same with col2 and col3.
For most NOT-NULL values of col1 and col2, there will be all 3 rows with the 3 possible values of col3.
The DB table might have around 30 Mio. rows.

The application will use queries like
- "SELECT * FROM sometable WHERE col1 = ..."
- "SELECT * FROM sometable WHERE col1 IN (...)"
- "SELECT * FROM sometable WHERE col2 = ..." -> can also be written as "SELECT * FROM sometable WHERE col1 IS NULL AND col2 = ..."
- "SELECT * FROM sometable WHERE col2 IN (...)" -> can also be written as "SELECT * FROM sometable WHERE col1 IS NULL AND col2 IN (...)"

What is more performant regarding runtime for my special case:
- 2 indices:
ALTER TABLE sometable ADD UNIQUE KEY `index1` (`col1`,`col3`);
ALTER TABLE sometable ADD UNIQUE KEY `index2` (`col2`,`col3`);
- or 1 index:
ALTER TABLE sometable ADD UNIQUE KEY `index1` (`col1`,`col2`,`col3`);

Best regards
Martin