Separate column index VS Multiple

Question

I have a table with 3 columns id safeid branchid

-some times I need to get records by safeid so I have a safeid (INDEX)

-some times I need to get records by branchid so I have a branchid (INDEX)

-some times I need to get records by safeid and branchid together . so the question is : Do I have to create another branchid_safeid (INDEX) for the two columns together to make selection faster?

Another Question

the id column is a unique primary key .. Do I need to create id (INDEX) or the primary key is an index by itself

Answer 1

(I reopened because the "dups" only answered the second question.)

Question #1: For WHERE safeid = 123 AND branchid = 234, you need INDEX(safeid, branchid) in either order. Better yet, have two indexes:

INDEX(safeid, branchid),  -- for the pair, or for just `safeid`
INDEX(branchid)           -- for branchid

This would take care of the 3 cases you mention.

More discussion here.

Question #2: As for "Another question", in MySQL (InnoDB specifically), the PRIMARY KEY is 'clustered' with the data; it is UNIQUE; and it is an INDEX. There is essentially no reason to duplicate the PRIMARY KEY with the addition of a separate UNIQUE KEY or INDEX.

Here are the "dups" that were previously quoted:

Is the primary key automatically indexed in MySQL?
Do I need to create separate index for primary key of relational database table