Re: JOIN performance on different integer types

On 2/29/2016 9:46 PM, ryantomko_at_gmail.com wrote:
> I was wondering if anyone has any data to indicate if there is a performance gain to keeping joining columns the same integer type as their target? For example:
>
> CREATE TABLE a (
> `id` INT unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
> `name` varchar(100) NOT NULL,
> );
>
> CREATE TABLE b (
> `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
> `a_id` TINYINT NOT NULL,
> INDEX `a_id` (`a_id`),
> );
>
> then
>
> SELECT * FROM a INNER JOIN b ON b.a_id = a.id
>
> Is there a performance hit because b.a_id is defined as a tinyint but the joined column a.id is defined as int? Would the query run faster if both columns were the same type?
>
>
> The MySQL docs states the following:
> "To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more efficiently if they are declared as the same type and size"
> http://dev.mysql.com/doc/refman/5.7/en/mysql-indexes.html
>
> However i assume that it means joining a varchar to an int, or a decimal to a char, or two chars of different size. Does the doc comment apply to tinyint and int?
>

Ryan,

I don't know for sure - I've never timed it. But there is going to be some hit, because the tinyint is going to have to be converted to an int before the compare. It may one be one time, and therefore not noticeable, but I really don't know.

The real question her is, though - why do you have two different types for the same information? If it fits into a tinyint in one table, it will fit into a tinyint in the other table (if the other table requires an int, you have other problems).

And BTW, if you're using the INNODB engine, you can create a foreign key which will help maintain database integrity.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================