implicit conversion?

MySQL & MariaDB Other MySQL® Questions
1

Hello, sorry if this question is kinda dumb…

When you create two tables, one with id being small int and another table with id being big int, does mysql internally does a conversion? like, when you inner join both tables: select * from table1 b inner table2 a ON a.id = b.id.

Thanks!

2
[B]osaka_x wrote on Mon, 22 November 2010 12:37[/B]

When you create two tables, one with id being small int and another table with id being big int, does mysql internally does a conversion?

Yes it does! But you should avoid it since the conversion affects the execution speed of the query since the conversion has to take place _before_ comparing the values. So if you are joining two large tables you can end up with a _lot_ of implicit conversions before the query actually returns only a few rows.

And it doesn’t make much sense in having different types. If you have a bigint in one table why shouldn’t the corresponding column in another table also be a bigint?

So always use the same type and size (regardless of if it’s numeric or char columns) on the two columns part of the join condition.

PS: the difference between a bigint and smallint is 6 bytes per row, so even at 1,000,000 rows in your table we are still talking about 6MB (or 12MB if it’s indexed). So if the reason to use it was to save space then that argument falls flat immediately.

3

i see, i just couldn’t find that info.

Yeah, you’re right. I’m going to stick with one type and size. thanks !!!