MySQL: join query

join;MySQL

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)

Best 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';