Does index on Varchar make performance difference?

Question

Does index on a varchar column make the query run slower? I can make that be int. and I don't need to do the LIKE % comparison.

Solution 1

Does index on a varchar column make the query run slower?

No, it does not.
If the optimizer decides to use of the index, the query will run faster. INSERTs/UPDATEs/DELETEs on that table will be slower, but not likely enough to notice.

I don't need to do the LIKE % comparison

Be aware that using:

LIKE '%whatever%'

...will not use an index, but the following will:

LIKE 'whatever%'

The key is wildcarding the lefthand side of the string means that an index on the column can't be used.

Also know that MySQL limits the amount of space set aside for indexes - they can be up to 1000 bytes long for MyISAM (767 bytes for InnoDB) tables.

Solution 2

If you can replace your varchar column with an integer (which i think is what your hinting at) Then an index including the integer column will perform better than the varchar column for a few reasons.

1. The index size will be smaller and involve less paging.
2. The comparison of integers is far better than varchar.

Solution 3

Indexing don't make query slower, database size just get bigger, So go ahead and give it a try.

You should able to get better performance on fetching rows but its depends on your data, your queries.

Solution 4

You are not going to be making your queries any slower by indexing. If you can make the column a type int, I would recommend doing so as most RDBMS's can search int's faster than varchars

View more solutions

Comments

[Anon.]

It needs to be pointed out that indexing does make insertions slower, and it can completely kill performance if it pushes your database size up to the point where you're regularly hitting disk.

[RevNoah]

Thanks, I had no idea removing the left wildcard uses the index. I didn't expect wildcards wouldn't be supported at all.

[shabby]

about point 2 -> where do you need varchar comparison if you have indexed it, other than the LIKE comparison as stated in the most voted answer?

[Peter]

2nd point is so untrue. when you index varchar its becomes INT really for database engine, only overhead is index scan