Database Table

[spring1018]

Databases

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| isutomo            |
| isuwitter          |
| mysql              |
| performance_schema |
| test               |
+--------------------+

[spring1018]

isutomo

MariaDB [isutomo]> show tables;
+-------------------+
| Tables_in_isutomo |
+-------------------+
| friends           |
+-------------------+

---

friends

MariaDB [isutomo]> desc friends;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| me      | varchar(20)         | YES  | UNI | NULL    |                |
| friends | text                | YES  |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+

MariaDB [isutomo]> show index from friends;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| friends |          0 | PRIMARY  |            1 | id          | A         |         944 |     NULL | NULL   |      | BTREE      |         |               |
| friends |          0 | me       |            1 | me          | A         |         944 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

[spring1018]

isuwitter

MariaDB [isuwitter]> show tables;
+---------------------+
| Tables_in_isuwitter |
+---------------------+
| friends             |
| tweets              |
| users               |
+---------------------+

---

friends

MariaDB [isutomo]> desc friends;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| me      | varchar(20)         | YES  | UNI | NULL    |                |
| friends | text                | YES  |     | NULL    |                |
+---------+---------------------+------+-----+---------+----------------+

MariaDB [isutomo]> show index from friends;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| friends |          0 | PRIMARY  |            1 | id          | A         |         944 |     NULL | NULL   |      | BTREE      |         |               |
| friends |          0 | me       |            1 | me          | A         |         944 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

tweets

MariaDB [isuwitter]> desc tweets;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | bigint(20) unsigned | YES  |     | NULL    |                |
| text       | text                | YES  |     | NULL    |                |
| created_at | datetime            | NO   |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

MariaDB [isuwitter]> show index from tweets;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tweets |          0 | PRIMARY  |            1 | id          | A         |      100010 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

users

MariaDB [isuwitter]> desc users;
+----------+---------------------+------+-----+---------+----------------+
| Field    | Type                | Null | Key | Default | Extra          |
+----------+---------------------+------+-----+---------+----------------+
| id       | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20)         | YES  | UNI | NULL    |                |
| salt     | varchar(20)         | YES  |     | NULL    |                |
| password | varchar(40)         | YES  |     | NULL    |                |

MariaDB [isuwitter]> 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         |        1013 |     NULL | NULL   |      | BTREE      |         |               |
| users |          0 | name     |            1 | name        | A         |        1013 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

[spring1018]

test

MariaDB [isuwitter]> use test;
Database changed
MariaDB [test]> show tables;
Empty set (0.00 sec)

[spring1018]

COUNT(*)

MariaDB [isuwitter]> SELECT COUNT(*) FROM friends;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

MariaDB [isuwitter]> SELECT COUNT(*) FROM tweets;
+----------+
| COUNT(*) |
+----------+
|   100018 |
+----------+
1 row in set (0.01 sec)

MariaDB [isuwitter]> SELECT COUNT(*) FROM users;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

[euglena1215]

tweetsにuser_nameを追加

MariaDB [isuwitter]> alter table tweets add user_name varchar(20);
Query OK, 100018 rows affected (0.57 sec)
Records: 100018  Duplicates: 0  Warnings: 0

[euglena1215]

god index

create index user_id_created_at on tweets(user_id, created_at);

[euglena1215]

MariaDB [isuwitter]> alter table tweets add column html text;
Query OK, 100310 rows affected (1.21 sec)
Records: 100310  Duplicates: 0  Warnings: 0