0

The table contains 4 columns: id (autoincrement), userid (unique, like ssn), status (TINYINT(1), 0 or 1 values, not NULL), user_info (varchar(1000)).

Should I put index on userid column to increase performance, if: 30% of requests is 

"SELECT user_info from Table1 WHERE userid='1234567'";

40% of requests is:

"SELECT user_info from Table1 WHERE userid='1234567' AND status=0";

20% of requests is:

"SELECT user_info from Table1 WHERE userid='1234567' AND status=1";

Or, there is a better way to increase the performance (I should think about indexing of status column somehow)?

Should I change something if instead of (30%,40%,20%) it will actually become (9%,90%,1%), i.e. most requests are for status=0? Thank you.

Haradzieniec
  • 9,086
  • 31
  • 117
  • 212
  • 1
    A table name like "userid" implies that column is a foreign key (to the User table, id field.) If you defined it as a foreign key on an InnoDB table, it's automatically indexed. http://stackoverflow.com/questions/304317/does-mysql-index-foreign-key-columns-automatically Good info about indexing fk's: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:292016138754 – OpenSorceress Nov 12 '11 at 18:03

2 Answers2

3

An index on status is very unlikely to increase the SELECT performance as the column content is not diverse enough. An index on userid is very likely to increase performance a lot as the column content is very diverse.

When you've defined the userid column as unique the automatically created unique index should already be used in your queries. So there's no need to define an additional index.

tobiasbayer
  • 10,269
  • 4
  • 46
  • 64
0

I suggest delete id column (synthetic key - what is it for?). Than, make a cumulative PK of user_id (first) and status (second).

Even better solution - remove status from PK and select always by user_id only. Check status in application and throw 404 or whatever.

Oroboros102
  • 2,214
  • 1
  • 27
  • 41