Adding indexes to gerrund tables

Question

So, I have a subscriptions table:

id - int(11) (With Primary Key)
user_id - int(11)
group_id - int(11)
role - int(11)
pending - tinyint(1)
created_at - datetime
updated_at - datetime

I'm often doing queries to see if users have access rights similar to this:

SELECT * FROM `subscriptions` WHERE (group_id = 1 AND user_id = 2 AND pending = 0) LIMIT 1

I'm wondering if adding a unique index on subscriptions(group_id, user_id, pending) would help or hinder in this case? What are the best practices for indexing almost an entire table?

Answer 1

This will certainly help, especially if you replace * with 1 in your query (if you just want to check that the row exists).

It may have a little impact on DML though.

Creating an index is in fact creating a B-Tree which has this structure:

indexed_col1
indexed_col2
...
indexed_colN
row_pointer

as a key, the row_pointer being a file offset (for MyISAM) or the value of the row's PRIMARY KEY (for InnoDB)

If you don't use other columns but indexed in you query, all information you need can be retrieved from the ondex alone, without even having to refer to the table itself.

If your data are intrinsically unique, it's always good to create a UNIQUE index on them. This is less of the case for MySQL, but more advanced optimizers (SQL Server, for instance) can utilize the fact the data are unique and build a more efficient plan.

See this article in my blog for an example:

• Making an index UNIQUE