CODEWITHSUNDEEP
mysqlsqlsql-serverpostgresql
Devin Dixon
Devin Dixon

Reputation: 12403

Database Design: Index Varchar

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?

Upvotes: 3

Views: 333

Answers (3)

Eelke
Eelke

Reputation: 22023

Would like to add that it will only make queries faster on a sufficiently large table.

On postgres varchar only uses as much space as needed for the actual values the max length just constrains what postgres lets you insert. So having an upper limit of 255 won't waste space in the index.

Upvotes: 0

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181350

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.

Upvotes: 10

Jesse Bunch
Jesse Bunch

Reputation: 6679

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!

Upvotes: 2

Related Questions