MySQL: join query

Question

I've got the following two tables:

mysql> DESC domains;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | UNI | NULL | |
| master | varchar(128) | YES | | NULL | |
| last_check | int(11) | YES | | NULL | |
| type | varchar(6) | NO | | NULL | |
| notified_serial | int(11) | YES | | NULL | |
| account | varchar(40) | YES | | NULL | |
+-----------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)
mysql> DESC records;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| id | bigint(11) | NO | PRI | NULL | auto_increment |
| domain_id | int(11) | YES | MUL | NULL | |
| name | varchar(255) | YES | MUL | NULL | |
| type | varchar(6) | YES | | NULL | |
| content | varchar(455) | YES | | NULL | |
| ttl | int(11) | YES | | NULL | |
| prio | int(11) | YES | | NULL | |
| change_date | int(11) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

I would like to join domains.notified_serial to the query below, id field is common on both tables, could you also please advise if the query below is optimal?

mysql> SELECT * FROM records WHERE name IN ('example.com', 'www.example.com') AND type = 'A';
+------------+-----------+-------------------+------+-----------------+------+------+-------------+
| id | domain_id | name | type | content | ttl | prio | change_date |
+------------+-----------+-------------------+------+-----------------+------+------+-------------+
| 2926120529 | 620750 | example.com | A | 192.168.1.100 | 600 | 0 | 1325776553 |
| 2926120595 | 620750 | www.example.com | A | 192.168.1.100 | 600 | 0 | 1325776553 |
+------------+-----------+-------------------+------+-----------------+------+------+-------------+
4 rows in set (0.00 sec)

Edit: Wed Jan 29 22:39:41 GMT 2014

mysql> SHOW CREATE TABLE domains\G
*************************** 1. row ***************************
 Table: domains
Create Table: CREATE TABLE `domains` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `master` varchar(128) DEFAULT NULL,
 `last_check` int(11) DEFAULT NULL,
 `type` varchar(6) NOT NULL,
 `notified_serial` int(11) DEFAULT NULL,
 `account` varchar(40) DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `name_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=623933 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE records\G
*************************** 1. row ***************************
 Table: records
Create Table: CREATE TABLE `records` (
 `id` bigint(11) NOT NULL AUTO_INCREMENT,
 `domain_id` int(11) DEFAULT NULL,
 `name` varchar(255) DEFAULT NULL,
 `type` varchar(6) DEFAULT NULL,
 `content` varchar(455) DEFAULT NULL,
 `ttl` int(11) DEFAULT NULL,
 `prio` int(11) DEFAULT NULL,
 `change_date` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `rec_name_index` (`name`),
 KEY `nametype_index` (`name`,`type`),
 KEY `domain_id` (`domain_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8544581783 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

• Raymond Nijland: can you also post the show create table [table_name] outputs off both tables?
• HTF: Done, basically these are PowerDNS tables.

Answer 1

someting like that maybe?

SELECT r.*,d.notified_serial 
FROM records r
left join domains d
 on d.id = r.domain_id
WHERE name IN ('example.com', 'www.example.com') AND type = 'A';

• HTF: - slightly modified: SELECT r.*, d.notified_serial FROM records r LEFT JOIN domains d ON d.id = r.domain_id WHERE r.name IN ('example.com', 'www.example.com') AND r.type = 'A';