Re: Cluster performance with data node down

On Tue, 2007-08-21 at 11:58 +0200, Giulio Ferro wrote:
> The query is a simple select on a single table. The tables contains
> about 330.000 entries. The queries executed are like this:
> select * from <tablename> where <varchar_field> = '....';

can use index

> select * from <tablename> where <varchar_field> like '%....%';

cannot use index.

> <varchar_field> is a varchar(255) and is also an index.
>
>
>
> explain output:
> +----+-------------+----------------+------+---------------+------+---------+------+--------+-----------------------------------+
> | id | select_type | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
> +----+-------------+----------------+------+---------------+------+---------+------+--------+-----------------------------------+
> | 1 | SIMPLE | <tablename> | ALL | NULL | NULL | NULL
> | NULL | 330122 | Using where with pushed condition |
> +----+-------------+----------------+------+---------------+------+---------+------+--------+-----------------------------------+
> 1 row in set (0.01 sec)

i.e. no index is being used.

Add index to speed up first query.

Not going to happen for 2nd though.

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

You may want to try with condition pushdown disabled, this may be quicker for the second query (or may not). either way, it's a table scan.

-- 
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