| Bug #52597 | Mysql Cluster 7.0.13 Does not Report Correct Cardinality | ||
|---|---|---|---|
| Submitted: | 5 Apr 2010 15:22 | Modified: | 12 Apr 2010 5:27 |
| Reporter: | Jakob Lorberblatt | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S2 (Serious) |
| Version: | 7.0.13 | OS: | FreeBSD (8.0) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | cardinality, INDEX, Optimizer, partitioning | ||
[9 Apr 2010 15:19]
Tom Farvour
This problem still persists in our cluster, has anyone come up with a possible solution for this? Using FORCE INDEX () everywhere seems ridiculous. Why are the mysql/API nodes reporting incorrect cardinality? Regards, -Tom
[9 Apr 2010 22:40]
Tom Farvour
Come on guys, this is clearly WRONG! What is going on here? mysql> show indexes in transactions; +--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | transactions | 0 | PRIMARY | 1 | id | A | 29390213 | NULL | NULL | | BTREE | | | transactions | 1 | mem_id | 1 | mem_id | A | 29390213 | NULL | NULL | | BTREE | | | transactions | 1 | art_id | 1 | art_id | A | 29390213 | NULL | NULL | | BTREE | | | transactions | 1 | author | 1 | author | A | 29390213 | NULL | NULL | | BTREE | | | transactions | 1 | email | 1 | email | A | 29390213 | NULL | NULL | | BTREE | | | transactions | 1 | date | 1 | date | A | 29390213 | NULL | NULL | | BTREE | | | transactions | 1 | editor | 1 | editor | A | 29390213 | NULL | NULL | | BTREE | | | transactions | 1 | action | 1 | action | A | 29390213 | NULL | NULL | | BTREE | | | transactions | 1 | action_by | 1 | action_by | A | 29390213 | NULL | NULL | | BTREE | | +--------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 9 rows in set (0.01 sec)
[12 Apr 2010 5:27]
Sveta Smirnova
Thank you for the report. This is duplicate of bug #44760
Description: On tables with multiple index paths to select from within the query optimizer, the optimizer was found to select the wrong index. We upgraded from 7.0.9 to 7.0.13, to fix the partition cardinality always being the same. This did not affect the result. This results in the query optimizer being erratic and occasionally selecting the wrong index. mysql> explain SELECT COUNT(transactions.id) AS idcnt FROM transactions WHERE transactions.action='13' AND transactions.date >= '2010-04-03 00:00:00' AND transactions.date <= '2010-04-05 23:59:59' AND transactions.form='/var/www/virtual/api.ezinearticles.com/api.php'; +----+-------------+--------------+------+---------------+--------+---------+-------+------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------+--------+---------+-------+------+-----------------------------------+ | 1 | SIMPLE | transactions | ref | date,action | action | 4 | const | 1 | Using where with pushed condition | +----+-------------+--------------+------+---------------+--------+---------+-------+------+-----------------------------------+ 1 row in set (0.00 sec) And on the next explain it may result in a differing execution path: mysql> explain SELECT * FROM `ezinearticles_history`.`transactions` WHERE `art_id` = '2978592' && `action` IN ('102','104','168','103','106','172','107','108','110') ORDER BY `date` DESC /* REQUEST=i.ezinearticles.com/e/article/quality-control/ */; +----+-------------+--------------+------+---------------+--------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------+--------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | transactions | ref | art_id,action | art_id | 4 | const | 66 | Using where; Using filesort | +----+-------------+--------------+------+---------------+--------+---------+-------+------+-----------------------------+ 1 row in set (0.00 sec) Not only do the possible keys changes, but the key being used does too! Wors than that, at times it selects the PRIMARY key, when this is not in the WHERE condition, which results in a table scan. If you need further examples, and data detailing this error please let me know. It results in inconsistent behaviour and intermittent above laying interface failure. Jakob Lorberblatt Database Administrator SparkNet Interactive How to repeat: Create a table, using ndbcluster and the default parititiong with one or more indexes in addition to primary, execute explain a number of times against the same query. Eventually 2 or more execution pathes will be generated in under 30 executions of the explain query. Furthermore cardinality of the index is tha same on ALL indexes, which is matching the primary key. Suggested fix: Correct the cardinality count of secondary indexes, especially those that are non -uniqune so that the best overall execution path is chosen by the query optimizer.