JOIN performance on different integer types
Date: Mon, 29 Feb 2016 18:46:58 -0800 (PST)
Message-ID: <40b72e2f-3b84-4b37-b48f-8fe4e7473149_at_googlegroups.com>
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? Received on Tue Mar 01 2016 - 03:46:58 CET