Bug #55645 | LIKE search is slow using varbinary indexes on cluster | ||
---|---|---|---|
Submitted: | 30 Jul 2010 9:16 | ||
Reporter: | Mikiya Okuno | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S3 (Non-critical) |
Version: | mysql-5.1-telco-7.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | 7.0.14 |
[30 Jul 2010 9:16]
Mikiya Okuno
[16 Aug 2010 10:57]
Hartmut Holzgraefe
With VARCHAR both the LIKE and BETWEEN queries are pushed down to ndb and return results immediately. With VARBINARY the LIKE variant still uses pushdown but takes noticeable time (e.g. about 0.1s for a 64K row table. BETWEEN on the other hand does not cause a pushdown and returns results immediately. DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, vc VARCHAR(10) NOT NULL DEFAULT '', KEY vc (vc), vb VARBINARY(10) NOT NULL DEFAULT '', KEY vb (vb) ) ENGINE=ndbcluster; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; INSERT INTO t1 SELECT NULL, rand() * 10000000000.0, rand() * 10000000000.0 FROM t1; Checking timing: mysql> select count(*) from t1 where vc like '123%'; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from t1 where vb like '123%'; +----------+ | count(*) | +----------+ | 11 | +----------+ 1 row in set (0.13 sec) mysql> select count(*) from t1 where vc between '123000000' and '123999999'; +----------+ | count(*) | +----------+ | 5 | +----------+ 1 row in set (0.00 sec) mysql> select count(*) from t1 where vb between '123000000' and '123999999'; +----------+ | count(*) | +----------+ | 11 | +----------+ 1 row in set (0.00 sec) Checking execution plans: mysql> explain select count(*) from t1 where vc like '123%'; +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------+ | 1 | SIMPLE | t1 | range | vc | vc | 12 | NULL | 10 | Using where with pushed condition | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------+ 1 row in set (0.00 sec) mysql> explain select count(*) from t1 where vb like '123%'; +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------+ | 1 | SIMPLE | t1 | range | vb | vb | 12 | NULL | 10 | Using where with pushed condition | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------+ 1 row in set (0.00 sec) mysql> explain select count(*) from t1 where vc between '123000000' and '123999999'; +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------+ | 1 | SIMPLE | t1 | range | vc | vc | 12 | NULL | 10 | Using where with pushed condition | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------+ 1 row in set (0.00 sec) mysql> explain select count(*) from t1 where vb between '123000000' and '123999999'; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t1 | range | vb | vb | 12 | NULL | 10 | Using where | +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+ So for VARCHAR both LIKE and BETWEEN are pushed down, and return results fast. For VARBINARY LIKE is pushed down while BETWEEN is not, and the pushed down LIKE is much slower than the BETWEEN or than both operations on VARCHAR.