Peter Jurkovic Peter Jurkovic - 14 days ago 6
MySQL Question

Mysql query optiomization (to avoid using UNION ALL)

Does exist any way how to optimize given query? I would like to get always all result from the

user
table and + also result form the
picture
table (if related exists). It is possible without using
UNION ALL
?

Lets consider following example

+----+--------+
| id | name |
+----+--------+
| 1 | Drosos |
| 2 | Jack |
+----+--------+

+----+---------+--------------+
| id | user_id | picture_name |
+----+---------+--------------+
| 1 | 1 | avatar.jpg |
| 2 | 1 | avatar2.jpg |
+----+---------+--------------+


Expected result

+--------+--------------+
| name | picture_name |
+--------+--------------+
| Drosos | avatar.jpg |
| Drosos | avatar2.jpg |
| Drosos | NULL |
| Jack | NULL |
+--------+--------------+
4 rows in set (0.00 sec)


User

CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;


Picture table

CREATE TABLE `picture` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`picture_name` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Query

SELECT u.name, p.picture_name FROM user u
INNER JOIN picture p ON p.user_id = u.id
UNION ALL
SELECT u.name, NULL FROM user u;


http://sqlfiddle.com/#!9/46d18a/1

Answer

The best optimization which I have achieved is with using "materialized view" and applied needed indexes. The query which used to take ~0.4000 sec now takes ~0.0025 sec.

Materialized views are not supported by MySQL so I had to create table table and trigger manually (which is not great but in my case was worth to do).