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