gistfile1.txt

➜  innodb_ruby_test git:(master) ✗ mysql -uroot innodb_ruby_test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 56
Server version: 5.6.23 Homebrew

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(1) from users;
+----------+
| count(1) |
+----------+
|   100000 |
+----------+
1 row in set (0.02 sec)

mysql> select distinct state from users;
+----------------+
| state          |
+----------------+
| South Carolina |
| Oklahoma       |
| Idaho          |
| Minnesota      |
| Maine          |
| Mississippi    |
| Nebraska       |
| Oregon         |
| Kentucky       |
| Wisconsin      |
| Massachusetts  |
| California     |
| Hawaii         |
| Missouri       |
| Tennessee      |
| Kansas         |
| Colorado       |
| Florida        |
| Wyoming        |
| North Carolina |
| Alabama        |
| New York       |
| Montana        |
| Washington     |
| Texas          |
| North Dakota   |
| New Mexico     |
| Alaska         |
| South Dakota   |
| Iowa           |
| West Virginia  |
| Virginia       |
| Vermont        |
| Ohio           |
| Utah           |
| Arizona        |
| Indiana        |
| Georgia        |
| Delaware       |
| Illinois       |
| New Jersey     |
| Maryland       |
| Pennsylvania   |
| Nevada         |
| Connecticut    |
| Louisiana      |
| New Hampshire  |
| Arkansas       |
| Rhode Island   |
| Michigan       |
+----------------+
50 rows in set (0.16 sec)

mysql> select count(distinct companyname) from users;
+-----------------------------+
| count(distinct companyname) |
+-----------------------------+
|                       66210 |
+-----------------------------+
1 row in set (0.27 sec)

mysql> explain select * from users where state = 'Oklahoma' order by companyname;
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                       |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 100201 | Using where; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)

mysql> alter table users add index state_idx (state);
Query OK, 0 rows affected (0.28 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from users;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY   |            1 | id          | A         |       99892 |     NULL | NULL   |      | BTREE      |         |               |
| users |          1 | state_idx |            1 | state       | A         |         158 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> explain select * from users where state = 'Oklahoma' order by companyname;
+----+-------------+-------+------+---------------+-----------+---------+-------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra                                              |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | users | ref  | state_idx     | state_idx | 768     | const | 1960 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table users add index state_companyname_idx (state, companyname);
Query OK, 0 rows affected (0.39 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from users;
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| users |          0 | PRIMARY               |            1 | id          | A         |      100433 |     NULL | NULL   |      | BTREE      |         |               |
| users |          1 | state_idx             |            1 | state       | A         |         128 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | state_companyname_idx |            1 | state       | A         |          64 |     NULL | NULL   | YES  | BTREE      |         |               |
| users |          1 | state_companyname_idx |            2 | companyname | A         |      100433 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

mysql> explain select * from users where state = 'Oklahoma' order by companyname;
+----+-------------+-------+------+---------------------------------+-----------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys                   | key                   | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------------------------+-----------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | users | ref  | state_idx,state_companyname_idx | state_companyname_idx | 768     | const | 1960 | Using where |
+----+-------------+-------+------+---------------------------------+-----------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> show create table users;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(255) NOT NULL,
  `lastname` varchar(255) NOT NULL,
  `phonenumber` varchar(255) NOT NULL,
  `state` varchar(255) DEFAULT NULL,
  `companyname` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `state_idx` (`state`),
  KEY `state_companyname_idx` (`state`,`companyname`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)