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 domainsG
*************************** 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 recordsG
*************************** 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)
Answer :
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';