Re: MySQL Cluster performance question

On Mon, 2007-08-20 at 15:53 -0500, Al wrote:
> It seems better without the force index:

Likely because secondary index scan needs an extra lookup than going directly through the table, so this doesn't seem unreasonable.

> mysql> select distinct gv_symbol from past30_ndb where 1;
> ...
> 90 rows in set (1 min 10.99 sec)
> 90 rows in set (1 min 10.33 sec)
>
> mysql> select distinct gv_symbol from past30_MYISAM where 1;
> ...
> 90 rows in set (0.00 sec)
> 90 rows in set (0.00 sec)

Since we don't push down DISTINCT to the data nodes,

> And here are some EXPLAINs:
>
> mysql> explain select distinct gv_symbol from past30_ndb where 1;
> ----+-------------+------------+------+---------------+------+---------+------+----------+-----------------+
> | id | select_type | table | type | possible_keys | key | key_len |
> ref | rows | Extra |
> +----+-------------+------------+------+---------------+------+---------+------+----------+-----------------+
> | 1 | SIMPLE | past30_ndb | ALL | NULL | NULL | NULL |
> NULL | 10000000 | Using temporary |
> +----+-------------+------------+------+---------------+------+---------+------+----------+-----------------+
> 1 row in set (0.00 sec)

will be doing full table scan.

> mysql> explain select distinct gv_symbol from past30_ndb force index
> (`gvsymtime`) where 1;
> +----+-------------+------------+-------+---------------+-----------+---------+------+----------+-------+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> +----+-------------+------------+-------+---------------+-----------+---------+------+----------+-------+
> | 1 | SIMPLE | past30_ndb | index | NULL | gvsymtime |
> 56 | NULL | 10000000 | |
> +----+-------------+------------+-------+---------------+-----------+---------+------+----------+-------+
> 1 row in set (0.00 sec)

will be doing full INDEX scan. So after looking up index, needs to then look up table.

-- 
Stewart Smith, Senior Software Engineer
MySQL AB, www.mysql.com
Office: +14082136540 Ext: 6616
VoIP: 6616@sip.us.mysql.com
Mobile: +61 4 3 8844 332

Jumpstart your cluster:
http://www.mysql.com/consulting/packaged/cluster.html


application/pgp-signature attachment: This is a digitally signed message part

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek