query performance slow

From: Bob Pisani <bpisani(at)schonfeld.com>
Date: Wed Jul 25 2007 - 11:44:22 EDT

Hey all,  

I'm doing some testing of cluster, 5.1.20, on SuSE 10.2. 1 mysql node, 2 data nodes. I have a disk based table, orders, with a primary key on order time and order id. I loaded 1M+ rows into the table and queried as follows:  

select * from orders where order_id = 'h0r50000145096' and order_time = '20070718093222';
....
1 row in set (0.01 sec)  

select * from orders where order_id = 'h0r50000145096' and order_time > '20070718090000' and order_time < '20070718100000'; ....
1 row in set (1 min 27.38 sec)  

Running an explain on the second query shows: +----+-------------+-------------+-------+---------------+---------+--------

-+------+------+-----------------------------------+

| id | select_type | table       | type  | possible_keys | key     | key_len
| ref  | rows | Extra                             |

+----+-------------+-------------+-------+---------------+---------+--------

-+------+------+-----------------------------------+

|  1 | SIMPLE      | orders      | range | PRIMARY       | PRIMARY | 24

| NULL | 1 | Using where with pushed condition |
+----+-------------+-------------+-------+---------------+---------+--------

-+------+------+-----------------------------------+

I know the first query is using the primary key, and it would seem that the second query is supposed to use the ordered index created with the primary hash key. Ndb_desc shows:
....
-- Indexes --
PRIMARY KEY(ORDER_ID, ORDER_TIME) - UniqueHashIndex PRIMARY(ORDER_TIME, ORDER_ID) - OrderedIndex

The question is am I missing something? When I've loaded a few more million rows in, the second query practically never finishes. I would expect that a range query would hit the ordered index and be somewhat performant. Thoughts?

Thanks,
Bob Pisani

This communication is for informational purposes only. It is not intended as an offer or solicitation or as an official confirmation. Market prices and other information are not guaranteed as to completeness or accuracy and are subject to change without notice. Schonfeld Group reserves the right to monitor and review the content of all messages sent to or from this e-mail address.

-- 
MySQL Cluster Mailing List
For list archives: 
http://lists.mysql.com/cluster
To unsubscribe:    
http://lists.mysql.com/cluster?unsub=lists@pantek.com

Received on Wed Jul 25 11:47:53 2007