XCHAOS:
teď teda nezbývá počkat, než mi nějaká SQL databáze ukáže různé DESCRIBE pro různé obsahy tabulek - a jako obvykle, obecni idiot radsi tlacha o vecech kterejm nerozumi, pripade "ceka" az az se mu neco "ukaze", misto toho aby si neco nastudoval nebo vyzkousel. A pritom je to tak snadne.
Nu co, v Uzbekistanu dnes slavi Den Kdy Kazdy Pravy Uzbek Musi Byt Hodny Na Vesnickeho Idiota, proc se nepripojit k oslavam, i kdyz nejsem uzbek, ze?
mysql> describe users;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(8) | YES | MUL | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> describe posts;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| user_id | int(8) | YES | | NULL | |
| text | varchar(255) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
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 | 1 | idx_users_id | 1 | id | A | NULL | NULL | NULL | YES | BTREE | | |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
# Ruzne vysledky describe pro ruzne obsahy tabulek:
# hodne radku v posts i users
mysql> describe select users.id, posts.text from users, posts where posts.user_id = users.id;
+----+-------------+-------+------+---------------+--------------+---------+---------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------------+---------+---------------------+------+--------------------------+
| 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 4096 | |
| 1 | SIMPLE | users | ref | idx_users_id | idx_users_id | 5 | rtest.posts.user_id | 11 | Using where; Using index |
+----+-------------+-------+------+---------------+--------------+---------+---------------------+------+--------------------------+
2 rows in set (0.00 sec)
# Hodne radku v posts, jeden radek v users
mysql> describe select users.id, posts.text from users, posts where posts.user_id = users.id;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | users | system | idx_users_id | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 4096 | Using where |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)
# jeden radek v posts i users;
mysql> describe select users.id, posts.text from users, posts where posts.user_id = users.id;
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | users | system | idx_users_id | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | posts | system | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+--------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)
# posts i users prazdne
mysql> describe select users.id, posts.text from users, posts where posts.user_id = users.id;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
# hodne radku v posts, nekolik v users
mysql> describe select users.id, posts.text from users, posts where posts.user_id = users.id;
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+---------------------------------------------+
| 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 14496 | |
| 1 | SIMPLE | users | index | idx_users_id | idx_users_id | 5 | NULL | 8 | Using where; Using index; Using join buffer |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+---------------------------------------------+
2 rows in set (0.00 sec)
# hodne radku v posts, dva v users
mysql> describe select users.id, posts.text from users, posts where posts.user_id = users.id;
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+--------------------------------+
| 1 | SIMPLE | users | index | idx_users_id | idx_users_id | 5 | NULL | 2 | Using index |
| 1 | SIMPLE | posts | ALL | NULL | NULL | NULL | NULL | 14496 | Using where; Using join buffer |
+----+-------------+-------+-------+---------------+--------------+---------+------+-------+--------------------------------+
2 rows in set (0.00 sec)