a_join_b.sql

a_join_b.sql

mysql> explain select sql_no_cache a.* from a,b where a.uid=b.reuid and b.uid=13 and a.time<='2012-01-11 12:18:00' and a.type=0 order by a.time desc limit 0,30;
+----+-------------+-------+------+---------------------------+---------+---------+--------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys             | key     | key_len | ref          | rows | Extra                                        |
+----+-------------+-------+------+---------------------------+---------+---------+--------------+------+----------------------------------------------+
|  1 | SIMPLE      | b     | ref  | idx1                      | idx1    | 4       | const        |  499 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | a     | ref  | idx_time_type_uid,idx_uid | idx_uid | 4       | test.b.reuid | 2500 | Using where                                  |
+----+-------------+-------+------+---------------------------+---------+---------+--------------+------+----------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain select sql_no_cache a.* from a,b where a.uid=b.reuid and b.uid=13 and a.time<='2012-01-11 12:17:00' and a.type=0 order by a.time desc limit 0,30;
+----+-------------+-------+-------+---------------------------+-------------------+---------+------------------+------+-------------+
| id | select_type | table | type  | possible_keys             | key               | key_len | ref              | rows | Extra       |
+----+-------------+-------+-------+---------------------------+-------------------+---------+------------------+------+-------------+
|  1 | SIMPLE      | a     | range | idx_time_type_uid,idx_uid | idx_time_type_uid | 10      | NULL             |  903 | Using where |
|  1 | SIMPLE      | b     | ref   | idx1                      | idx1              | 8       | const,test.a.uid | 1252 | Using index |
+----+-------------+-------+-------+---------------------------+-------------------+---------+------------------+------+-------------+
2 rows in set (0.00 sec)

mysql> show create table a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `uid` int(10) NOT NULL,
  `time` datetime NOT NULL,
  `type` tinyint(4) DEFAULT NULL,
  `status` varchar(320) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_time_type_uid` (`time`,`type`,`uid`),
  KEY `idx_uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table b\G
*************************** 1. row ***************************
       Table: b
Create Table: CREATE TABLE `b` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `uid` int(10) NOT NULL,
  `reuid` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx1` (`uid`,`reuid`)
) ENGINE=InnoDB AUTO_INCREMENT=250001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select count(*) from a where time<='2012-01-11 12:18:00';
+----------+
| count(*) |
+----------+
|    13942 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from a where time<='2012-01-11 12:17:00';
+----------+
| count(*) |
+----------+
|     1042 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from a;
+----------+
| count(*) |
+----------+
|   500000 |
+----------+
1 row in set (0.13 sec)

mysql> select 1042/500000;
+-------------+
| 1042/500000 |
+-------------+
|      0.0021 |
+-------------+
1 row in set (0.00 sec)

mysql> select 13942/500000;
+--------------+
| 13942/500000 |
+--------------+
|       0.0279 |
+--------------+
1 row in set (0.00 sec)