| Bug #18138 | FORCE INDEX is ignored when using NDB tables | ||
|---|---|---|---|
| Submitted: | 10 Mar 2006 15:56 | Modified: | 26 Dec 2006 16:33 |
| Reporter: | Walter Bertot | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S1 (Critical) |
| Version: | mysql-5.0 | OS: | Linux (RHE 3.0) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | 5.0.18 | ||
[10 Mar 2006 16:30]
MySQL Verification Team
Changing for Cluster category.
[17 Mar 2006 2:07]
dahong chen
I Have the same issue.in a complex query,Index doesn't work with NDB OS:RedHat 9.0 Version:mysql-5.1.7-beta
[23 Mar 2006 15:11]
Valeriy Kravchuk
Please, send the SHOW CREATE TABLE and SHOW TABLE STATUS for the NDB table in question.
[23 Mar 2006 15:33]
Walter Bertot
mysql> SHOW CREATE TABLE end_user_audit; +----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | end_user_audit | CREATE TABLE `end_user_audit` ( `id` int(11) NOT NULL auto_increment, `target_id` int(11) NOT NULL default '0', `end_user_operation_id` int(11) NOT NULL default '0', `user_id` int(11) NOT NULL default '0', `carrier_id` int(11) NOT NULL default '0', `timestamp` datetime NOT NULL default '0000-00-00 00:00:00', `target_type_id` int(11) NOT NULL default '0', `status` int(2) NOT NULL default '0', `interface_type` int(2) NOT NULL default '0', `interface_code` int(11) default NULL, `la_id` int(11) default NULL, `parent_id` int(11) default NULL, PRIMARY KEY (`id`), KEY `eua_carrier_id_target_id_timestamp` (`timestamp`,`carrier_id`,`target_id`), KEY `eua_user_id_timestamp` (`user_id`,`timestamp`), KEY `eua_timestamp` (`timestamp`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 | SHOW TABLE STATUS where name like 'end_user_audit'; +----------------+------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+-----------------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +----------------+------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+-----------------------+ | end_user_audit | ndbcluster | 10 | Fixed | 100 | 0 | 0 | 0 | 0 | 0 | 1638521 | NULL | NULL | NULL | latin1_swedish_ci | NULL | | number_of_replicas: 2 | +----------------+------------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-------------------+----------+----------------+-----------------------+
[23 Mar 2006 19:35]
Jonathan Miller
Test case files, data loads and results can be found at: http://bugs.mysql.com/bug.php?id=18468 That has been marked as duplicate to this report
[23 Mar 2006 19:43]
Jonathan Miller
Assigned to Pekka per his request. /jeb
[23 Mar 2006 19:53]
Jonathan Miller
Information from customer: mysql> select count(*) from end_user_audit; +----------+ | count(*) | +----------+ | 2205830 | +----------+ 1 row in set (1.89 sec) Just an FYI... I have a much larger query right now that our reporting tool runs that is taking 16 minutes (yes minutes) as oppossed to < 3 seconds on InnoDB. Please let me know what else if anything I can do from my side to help get this resolved. Walter
[23 Mar 2006 20:02]
Jonathan Miller
Current work around to this issue: ndb-index-stat-enable = 0 in my.cnf
[23 Mar 2006 21:22]
Walter Bertot
Pekka, Is this perhaps a 5.1 feature I might not have? I am presently on 5.0.18 and getting the following error message when dropping the workaround in my my.cnf 060324 03:38:19 mysqld started 060324 3:38:19 [ERROR] /usr/sbin/mysqld-max: unknown variable 'ndb-index-stat-enable=0' 060324 03:38:19 mysqld ended Here is the out of the show variables; mysql> show variables like 'ndb%'; +-------------------------------+-------+ | Variable_name | Value | +-------------------------------+-------+ | ndb_autoincrement_prefetch_sz | 32 | | ndb_force_send | ON | | ndb_use_exact_count | ON | | ndb_use_transactions | ON | | ndb_cache_check_time | 0 | +-------------------------------+-------+ Could you confirm if this is a new variable in 5.1 or am I doing something wrong. Thanks, Walter
[24 Mar 2006 19:20]
Walter Bertot
Ok I must be doing something wrong. I have upgraded to 5.1.7 from 5.0.18 and have set ndb_index_stat_enable = 0 However the query still does not use the index. Below is the output from my console mysql> show variables like 'ndb%'; +-------------------------------------+-------+ | Variable_name | Value | +-------------------------------------+-------+ | ndb_autoincrement_prefetch_sz | 32 | | ndb_cache_check_time | 0 | | ndb_extra_logging | 0 | | ndb_force_send | OFF | | ndb_index_stat_cache_entries | 32 | | ndb_index_stat_enable | OFF | | ndb_index_stat_update_freq | 20 | | ndb_report_thresh_binlog_epoch_slip | 3 | | ndb_report_thresh_binlog_mem_usage | 10 | | ndb_use_exact_count | OFF | | ndb_use_transactions | ON | +-------------------------------------+-------+ 11 rows in set (0.00 sec) mysql> explain select id from end_user_audit where timestamp >='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00'; +----+-------------+----------------+-------+--------------------------------------------------+------------------------------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+-------+--------------------------------------------------+------------------------------------+---------+------+------+-------------+ | 1 | SIMPLE | end_user_audit | range | eua_carrier_id_target_id_timestamp,eua_timestamp | eua_carrier_id_target_id_timestamp | 8 | NULL | 10 | Using where | +----+-------------+----------------+-------+--------------------------------------------------+------------------------------------+---------+------+------+-------------+ 1 row in set (0.00 sec)
[24 Mar 2006 19:31]
Jonathan Miller
Walter, not sure I understand now. Here is the output of the tables t1 ndb and t2 innodb = ndb_index_stat_enable=1 explain select id from t1 where timestamp >='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00'; 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 explain select id from t1 FORCE INDEX (eua_timestamp) where timestamp >='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00'; 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 explain select id from t2 where timestamp >='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range eua_carrier_id_target_id_timestamp,eua_timestamp eua_timestamp 8 NULL 10 Using where; Using index explain select id from t2 FORCE INDEX (eua_timestamp) where timestamp >='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range eua_timestamp eua_timestamp 8 NULL 10 Using where; Using index Here is the output of the tables t1 ndb and t2 innodb = ndb_index_stat_enable=0 explain select id from t1 where timestamp >='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range eua_carrier_id_target_id_timestamp,eua_timestamp eua_carrier_id_target_id_timestamp 8 NULL 10 Using where explain select id from t1 FORCE INDEX (eua_timestamp) where timestamp >='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range eua_timestamp eua_timestamp 8 NULL 10 Using where explain select id from t2 where timestamp >='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range eua_carrier_id_target_id_timestamp,eua_timestamp eua_timestamp 8 NULL 10 Using where; Using index explain select id from t2 FORCE INDEX (eua_timestamp) where timestamp >='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 range eua_timestamp eua_timestamp 8 NULL 10 Using where; Using index I guess you are stating that it is missing the Using where; Using index? HAve you tried the query to see if the time has improved?
[24 Mar 2006 19:51]
Walter Bertot
Yes, I tried it and there seems to be an improvement. I might be wrong but since it does not say "Using index" in the extra column my assumption is that it did a full table scan on the WHERE clause. For my other more complex queries to function properly these indexes need to be used. Is my understanding above correct?
Description: I have been trying to resolve some serious performance issues on my NDB cluster I have in certain queries. At the heart of most of my problems, per the explain plan the cluster is not using index that InnoDB is using. Even in using the FORCE INDEX syntax I still can not avoid the full table scans. How to repeat: Here is a small example query I am running select id from end_user_audit where timestamp >='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00'; Explain plan on InnoDB +----+-------------+----------------+-------+--------------------------------------------------+---------------+---------+------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+-------+--------------------------------------------------+---------------+---------+------+--------+--------------------------+ | 1 | SIMPLE | end_user_audit | range | eua_carrier_id_target_id_timestamp,eua_timestamp | eua_timestamp | 8 | NULL | 202752 | Using where; Using index | Explain plan on NDB | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+-------+--------------------------------------------------+------------------------------------+---------+------+------+-------------+ | 1 | SIMPLE | end_user_audit | range | eua_carrier_id_target_id_timestamp,eua_timestamp | eua_carrier_id_target_id_timestamp | 8 | NULL | 10 | Using where | +----+-------------+----------------+-------+--------------------------------------------------+------------------------------------+---------+------+------+-------------+ Using FORCE INDEX select id from end_user_audit FORCE INDEX (eua_timestamp) where timestamp >='2005-12-18 02:00:00' and timestamp <'2006-01-01 00:00:00'; NDB explain plan +----+-------------+----------------+-------+---------------+---------------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------------+-------+---------------+---------------+---------+------+------+-------------+ | 1 | SIMPLE | end_user_audit | range | eua_timestamp | eua_timestamp | 8 | NULL | 10 | Using where | +----+-------------+----------------+-------+---------------+---------------+---------+------+------+-------------+ Here is what the table looks like… +-----------------------+----------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------------+----------+------+-----+---------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | target_id | int(11) | NO | | 0 | | | end_user_operation_id | int(11) | NO | | 0 | | | user_id | int(11) | NO | MUL | 0 | | | carrier_id | int(11) | NO | | 0 | | | timestamp | datetime | NO | MUL | 0000-00-00 00:00:00 | | | target_type_id | int(11) | NO | | 0 | | | status | int(2) | NO | | 0 | | | interface_type | int(2) | NO | | 0 | | | interface_code | int(11) | YES | | NULL | | | la_id | int(11) | YES | | NULL | | | parent_id | int(11) | YES | | NULL | | +-----------------------+----------+------+-----+---------------------+----------------+