Hi, all
I am using mysql - 3.23.46, Intel, Solaris 8
Some questions:
1) Why cardinality value don't automaticly change when query change set of
unique values in index ( insert or update ) ?
Example:
mysql> create table a ( a int not null, index ( a ) );
Query OK, 0 rows affected (0.02 sec)
mysql> show index from a;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+---------+
| a | 1 | a | 1 | a | A |
NULL | NULL | NULL | |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+---------+
1 row in set (0.00 sec)
mysql> insert into a values ( 1 );
Query OK, 1 row affected (0.00 sec)
mysql> show index from a;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+---------+
| a | 1 | a | 1 | a | A |
NULL -??? | NULL | NULL | |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+---------+
1 row in set (0.00 sec)
mysql> optimize table a;
+-------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------+----------+----------+----------+
| tmp.a | optimize | status | OK |
+-------+----------+----------+----------+
1 row in set (0.04 sec)
mysql> show index from a;
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+---------+
| a | 1 | a | 1 | a | A |
1 | NULL | NULL | |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+---------+
1 row in set (0.00 sec)
2) Why index using in different way when cardinality = 1 and cardinality > 1 ?
Example:
index in calculated_offlc ( contract_id ) have cardinality = 1
mysql> show index from contract;
+----------+------------+------------+--------------+-------------+-----------+-
------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation |
Cardinality | Sub_part | Packed | Comment |
+----------+------------+------------+--------------+-------------+-----------+-
------------+----------+--------+---------+
| contract | 0 | PRIMARY | 1 | id | A |
8520 | NULL | NULL | |
| contract | 0 | deleted_id | 1 | deleted_id | A |
NULL | NULL | NULL | |
| contract | 0 | deleted_id | 2 | number | A |
8520 | NULL | NULL | |
+----------+------------+------------+--------------+-------------+-----------+-
------------+----------+--------+---------+
3 rows in set (0.00 sec)
mysql> show index from calculated_offlc;
+------------------+------------+-------------+--------------+------------------
-+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name
| Collation | Cardinality | Sub_part | Packed | Comment |
+------------------+------------+-------------+--------------+------------------
-+-----------+-------------+----------+--------+---------+
| calculated_offlc | 0 | PRIMARY | 1 | processed_data_id
| A | 46706 | NULL | NULL | |
| calculated_offlc | 1 | contract_id | 1 | contract_id
| A | 1 - !!! | NULL | NULL | |
+------------------+------------+-------------+--------------+------------------
-+-----------+-------------+----------+--------+---------+
2 rows in set (0.00 sec)
mysql> explain SELECT c.id, IFNULL( SUM( c_offlc.value ), 0 ) FROM contract AS c
LEFT JOIN calculated_offlc AS c_offlc ON c_offlc.contract_id = c.id GROUP BY
c.id;
+---------+-------+---------------+---------+---------+------+-------+----------
--------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+---------+-------+---------------+---------+---------+------+-------+----------
--------------------+
| c | index | NULL | PRIMARY | 4 | NULL | 8520 | Using
index; Using temporary |
| c_offlc | ALL | contract_id | NULL | NULL | NULL | 46706 |
|
+---------+-------+---------------+---------+---------+------+-------+----------
--------------------+
2 rows in set (0.01 sec)
Yor can see that index contract_id in table calculated_offlc not used that make
query really slow !!!
After update ( and optimize table ( see question 1 ) )
mysql> show index from srv_0601.calculated_offlc;
+------------------+------------+-------------+--------------+------------------
-+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name
| Collation | Cardinality | Sub_part | Packed | Comment |
+------------------+------------+-------------+--------------+------------------
-+-----------+-------------+----------+--------+---------+
| calculated_offlc | 0 | PRIMARY | 1 | processed_data_id
| A | 46706 | NULL | NULL | |
| calculated_offlc | 1 | contract_id | 1 | contract_id
| A | 2 | NULL | NULL | |
+------------------+------------+-------------+--------------+------------------
-+-----------+-------------+----------+--------+---------+
2 rows in set (0.05 sec)
mysql> explain SELECT c.id, IFNULL( SUM( c_offlc.value ), 0 ) FROM sd.contract
AS c LEFT JOIN srv_0601.calculated_offlc AS c_offlc ON c_offlc.contract_id =
c.id GROUP BY c.id;
+---------+-------+---------------+-------------+---------+------+-------+------
-------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+---------+-------+---------------+-------------+---------+------+-------+------
-------+
| c | index | NULL | PRIMARY | 4 | NULL | 8520 | Using
index |
| c_offlc | ref | contract_id | contract_id | 4 | c.id | 23353 |
|
+---------+-------+---------------+-------------+---------+------+-------+------
-------+
2 rows in set (0.01 sec)
In this case everything is ok.
Wait for your opinions. Thanks
--------------------------------------------------------------------------------
Vlad A. Shalnev
E-mail: [EMAIL PROTECTED]
Pager: (3422) 055111 : 70053
"Gravity can't be blamed
for someone
falling in love"
( Albert Einstein )
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
