-1

I have one table called users with a PRIMARY key on users.zbid. However, all SELECT queries run on it include the WHERE clause cid='$cid' AND zbid='$zbid'. Should I add an index for cid or a multi-column index for cid,zbid ? I know the WHERE clause on cid seems redundant as zbid is PRIMARY, but it's mainly there as a precautionary step, each cid designates a different forum, and in case something went wrong I don't want every forum to be effected.

Secondly, I have another table called notifications. The only query that is run on this table uses the where clause cid='$cid' AND zbid='$zbid', should I add them both as individual indexes or just one? Why?

Keir Simmons
  • 1,634
  • 7
  • 21
  • 37

3 Answers3

1
  1. If you have primary key on users.zdib, there is no need for composite index (mysql will use the existing index on zdib)
  2. If cid is used in a similar way as in your first table, no need to have composite index, it should be enough just an index on zdib. Otherwise, make it on 2 columns.
a1ex07
  • 36,826
  • 12
  • 90
  • 103
0

I think the multi-column index will perform better.

cid,zbid will require 1 seek

having 2 indices will require 2 seeks

mson
  • 7,762
  • 6
  • 40
  • 70
0

In the first case MySQL will use the PRIMARY_KEY (it doesn't matter what other AND's you have).

In the second case you should add both of them in one index (assuming the columns are non-unique) that will leave fewer results to filter afterwards.

You should also check covering indexes. But i don't think those will make a difference in your case.

Community
  • 1
  • 1
Vatev
  • 7,493
  • 1
  • 32
  • 39