Database Design: Index Varchar

Question

I have a table that has a primary key which is an integer and then a column that is used for searching by type and it is a varchar. Lets say content_id and content_type.

SELECT * FROM TABLE_A WHERE content_type='abc'

The varchar content_type has a length of 255. I am thinking about indexing it for faster SELECT queries. Will this improve performance? And even though it has a length of 255, if only 5 chars are used and not the whole 255, would that make a difference on performance?

Answer 1

An index on content_type should make your queries faster for:

select * from table_a where content_type = 'whatever';
select * from table_a where content_type like 'whatever%';

But it will not make your queries faster for:

select * from table_a where content_type like '%whatever%';

It doesn't matter if you are only using 5 of the 255 available characters.

Answer 2

Here are some bullet points:

• The index will improve your performance on read queries (ie SELECT). However, it tends to decrease performance on write queries (ie INSERT and UPDATE). This is especially true as you gain more and more records in your table.

• Using only 5/255 characters will not affect your index performance enough to notice.

Hope that helps!