Does index on Varchar make performance difference?

mysql sql performance indexing varchar
Murvinlai picture Murvinlai · Dec 14, 2009 · Viewed 58.1k times · Source

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.

Answer

OMG Ponies picture OMG Ponies · Dec 14, 2009

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.

Related questions

Two single-column indexes vs one two-column index in MySQL?

I'm faced with the following and I'm not sure what's best practice. Consider the following table (which will get large): id PK | giver_id FK | recipient_id FK | date I'm using InnoDB and from what I understand, it creates indices …

Read More
MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET

What is main difference between INSERT INTO table VALUES .. and INSERT INTO table SET? Example: INSERT INTO table (a, b, c) VALUES (1,2,3) INSERT INTO table SET a=1, b=2, c=3 And what about performance of these two?

Read More
Best way to test if a row exists in a MySQL table

I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE ... and check to see if the total is non-zero or …

Read More