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.
varchar列上的索引是否使查询运行得更慢？我可以把它变成int。而且我不需要进行LIKE％比较。
5 个解决方案
#1
76
Does index on a varchar column make the query run slower?
varchar列上的索引是否使查询运行得更慢？
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.
不，不是的。如果优化器决定使用索引，则查询将运行得更快。该表上的INSERTs / UPDATEs / DELETE更慢，但不太可能注意到。
I don't need to do the LIKE % comparison
我不需要进行LIKE％比较
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.
还要知道MySQL限制了为索引留出的空间量 - 对于MyISAM（InnoDB为767字节）表，它们最长可达1000字节。
#2
9
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.
如果你可以用一个整数替换你的varchar列（我认为这是你的暗示）那么包含整数列的索引将比varchar列表现更好，原因有几个。
- The index size will be smaller and involve less paging.
- 索引大小将更小并且涉及更少的分页。
- The comparison of integers is far better than varchar.
- 整数的比较远远好于varchar。
#3
6
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.
您应该能够在获取行时获得更好的性能，但这取决于您的数据和查询。
#4
2
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
您不会通过索引来减慢查询速度。如果您可以将列设置为int类型，我建议这样做，因为大多数RDBMS可以比varchars更快地搜索int
#5
0
There are performance issues when it comes to inserts. I can for sure tell you that inserting into a large table which has an index over a varchar column can be very slow. (up to 50x)
插入时存在性能问题。我可以肯定地告诉你，插入一个在varchar列上有索引的大表可能会非常慢。 （最多50倍）
#1
76
Does index on a varchar column make the query run slower?
varchar列上的索引是否使查询运行得更慢？
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.
不，不是的。如果优化器决定使用索引，则查询将运行得更快。该表上的INSERTs / UPDATEs / DELETE更慢，但不太可能注意到。
I don't need to do the LIKE % comparison
我不需要进行LIKE％比较
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.
还要知道MySQL限制了为索引留出的空间量 - 对于MyISAM（InnoDB为767字节）表，它们最长可达1000字节。
#2
9
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.
如果你可以用一个整数替换你的varchar列（我认为这是你的暗示）那么包含整数列的索引将比varchar列表现更好，原因有几个。
- The index size will be smaller and involve less paging.
- 索引大小将更小并且涉及更少的分页。
- The comparison of integers is far better than varchar.
- 整数的比较远远好于varchar。
#3
6
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.
您应该能够在获取行时获得更好的性能，但这取决于您的数据和查询。
#4
2
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
您不会通过索引来减慢查询速度。如果您可以将列设置为int类型，我建议这样做，因为大多数RDBMS可以比varchars更快地搜索int
#5
0
There are performance issues when it comes to inserts. I can for sure tell you that inserting into a large table which has an index over a varchar column can be very slow. (up to 50x)
插入时存在性能问题。我可以肯定地告诉你，插入一个在varchar列上有索引的大表可能会非常慢。 （最多50倍）