I've indexed a combined column, should I index separate column too?

Question

I have a table like this

CREATE TABLE `tbl` (
    `id` int(10) unsigned NOT NULL,
    `pid` int(10) unsigned NOT NULL,
    `cid` int(10) unsigned NOT NULL,
    `name` int(11) DEFAULT NULL,
    `desc` varchar(500),
    `deleted` tinyint(1) unsigned NOT NULL DEFAULT 0,
    `createdAt` datetime DEFAULT NULL,
    PRIMARY KEY `pid_cid` (`pid`,`cid`)
    ) ENGINE=InnoDB

WHERE clause of select queries are those:

where pid=pid [and deleted = 1];
where cid=cid [and deleted = 1];
where pid=pid and cid=cid [and deleted = 1];

Should I index pid and cid separately?
Should I index deleted?

Please read https://stackoverflow.com/questions/2955459/what-is-an-index-in-sql and https://stackoverflow.com/questions/1108/how-does-database-indexing-work — Ferdinand Gaspar, Aug 21 '17 at 01:48
It's really odd to have a column called id that *isn't* primary — Strawberry, Aug 21 '17 at 07:15
And in my view, calling a column desc is just asking for trouble — Strawberry, Aug 21 '17 at 07:16

score 1 · Accepted Answer · answered Aug 21 '17 at 02:02

The optimal set of indexes would be:

pid, deleted
cid, deleted
pid, cid, deleted

Whether you need all three depends on your data. How many different deleted values are there for each pid/cid. The first two may be sufficient.

Indexing deleted by itself is probably not useful. Presumably, it only takes on two values, which is generally not a good candidate for an index.

I've indexed a combined column, should I index separate column too?

1 Answers1