4 messages in com.mysql.lists.clusterRe: ndb unique index performance issues| From | Sent On | Attachments |
|---|---|---|
| Yong Lee | 29 Jun 2006 16:01 | |
| Benton, Kevin | 29 Jun 2006 16:42 | |
| Simon Garner | 29 Jun 2006 17:40 | |
| Stewart Smith | 29 Jun 2006 22:29 |
| Subject: | Re: ndb unique index performance issues |
|---|---|
| From: | Simon Garner (sgar...@expio.co.nz) |
| Date: | 06/29/2006 05:40:21 PM |
| List: | com.mysql.lists.cluster |
On 30/06/2006 11:02 a.m., Yong Lee wrote:
I'm trying to do a query that uses an index along with a unique index and for some reason, the query seems to be doing a table scan. My table has 20 000+ records and my query is taking over 20 seconds to return. Any insight into this problem would be appreciated. Here are the details :
MySQL Cluster has problems with often choosing incorrect indexes for queries. It's something to do with not having accurate index statistics, as I understand it.
For both of your queries, you would expect it to choose the index on email_address (users_Index5), as that is a constant equality (email_address='value'). Because you're comparing the username or the first_name using a != operator, they can only use their indexes in a range scan, which will be slower.
However, for whatever reason NDB may choose the wrong index, resulting in a poor query plan.
To make your first query fast, you'll need to tell it which index to use:
select 1 from users USE INDEX (users_Index5) where email_address = 'test...@test.com' and username != 'testing'
-Simon
mysql 4.1.12, 2 partitions with 2 servers each hosting a data and sql node:
My query (email_address field is indexed and username is unique index):
mysql> select 1 from users where email_address = 'test...@test.com' and username != 'testing' Empty set (20.33 sec)
Similar query using 2 indexed fields: mysql> select 1 from users where email_address = 'test...@test.com' and first_name != 'test'; Empty set (0.00 sec)
Detail info on table:
CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL auto_increment, `username` varchar(40) NOT NULL default '', `password` varchar(40) default NULL, `email_address` varchar(80) default NULL, `status` enum('active','inactive','subscribing') NOT NULL default 'active', `last_name` varchar(40) default NULL, `first_name` varchar(40) default NULL, `middle_name` varchar(20) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), KEY `users_Index2` (`last_name`), KEY `users_Index3` (`first_name`), KEY `users_Index5` (`email_address`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
result of explain statements :
mysql> explain select 1 from users where email_address = 'test...@test.com' and username != 'testing' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users type: range possible_keys: username,users_Index5 key: username key_len: 40 ref: NULL rows: 2 Extra: Using where 1 row in set (0.00 sec)
mysql> explain select 1 from users where email_address = 'test...@test.com' and first_name != 'test' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: users type: ref possible_keys: users_Index3,users_Index5 key: users_Index5 key_len: 81 ref: const rows: 10 Extra: Using where 1 row in set (0.00 sec)