CODEWITHSUNDEEP
mysql
Steven Rogers
Steven Rogers

Reputation: 1994

Is it faster to convert into to varchar or varchar to int when comparing in MySQL?

So I have two tables that have IDs that reference the same object. One of those tables stores it as a varchar and the other stores it as an int. I want subquery (or possibly join) these two tables.

WHERE foo = CAST(bar AS UNSIGNED)

or

WHERE CAST(foo AS CHAR) = bar

Which one is better?

Edit: Neither of the IDs are indexed.

Upvotes: 0

Views: 805

Answers (1)

Psi
Psi

Reputation: 6783

That depends on the indexes. If both columns have indexes set, you should cast to int, because it only needs to compare 4 bytes. If, however, only the varchar column is indexed, cast to varchar.

At the end, you should have your query explained, to see what index gets used, and cast accordingly.

If no index is set, casting to int is the better approach in general. However, if there is no index set, it doesn't really matter, you would save much more time by indexing than by choosing the correct way to cast. Except, again, the tables are small. Then, it doesn't really matter either.

In fact, we should take into account not only the costs for comparison, but also for casting: Casting an int to a varchar is slightly faster than parsing a varchar to an int. However, comparing a varchar that has at least 4 characters will render that advantage useless. If your values tend to be higher than 999, casting to int will be faster. But that now depends on your operating system, the architecture, the instruction set used by your mysql binary and so on. The only way to get a really reliable answer is: Benchmark that situation on the target machine.

Upvotes: 1

Related Questions