I have two tables created with following codes in MySQL 5.6
create table color(color_id int auto_increment primary key, color varchar(10));
create table flower(id int auto_increment primary key, name varchar(20), color_id int, constraint fk_color foreign key references color(color_id));
and then I inserted some rows in color then in flower.
then I queried
select flower.name, color.color from flower, color where flower.color_id=color.color_id;
select name,(select color from color where color_id=flower.color_id) color from flower;
select flower.name, color.color from color natural join flower;
select flower.name, color.color from flower natural join color;
All the queries gave me same result.
Which query is fastest and most efficient (nested query or inner join or natural join) and why specially among nested query and inner join?
what practice should we opt for querying?
whats the difference between the last two queries?
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
ALTER TABLE `color` ADD INDEX `color_idx_color_id` (`color_id`); ALTER TABLE `flower` ADD INDEX `flower_idx_color_id` (`color_id`);
SELECT flower.name, color.color FROM flower, color WHERE flower.color_id = color.color_id