Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

16.10.1. MySQL Cluster Changes in MySQL 5.0

MySQL Cluster in versions 5.0.3-beta and later contains a number of new features that are likely to be of interest:

  • Push-Down Conditions: Consider the following query:

    SELECT * FROM t1 WHERE non_indexed_attribute = 1;
    

    This query will use a full table scan and the condition will be evaluated in the cluster's data nodes. Thus, it is not necessary to send the records across the network for evaluation. (That is, function transport is used, rather than data transport.) Please note that this feature is currently disabled by default (pending more thorough testing), but it should work in most cases. This feature can be enabled through the use of the SET engine_condition_pushdown = On statement. Alternatively, you can run mysqld with the this feature enabled by starting the MySQL server with the --engine-condition-pushdown option.

    A major benefit of this change is that queries can be executed in parallel. This means that queries against non-indexed columns can run faster than previously by a factor of as much as 5 to 10 times, times the number of data nodes, because multiple CPUs can work on the query in parallel.

    You can use EXPLAIN to determine when condition pushdown is being used. See Section 7.2.1, “Optimizing Queries with EXPLAIN.

  • Decreased IndexMemory Usage: In MySQL 5.1, each record consumes approximately 25 bytes of index memory, and every unique index uses 25 bytes per record of index memory (in addition to some data memory because these are stored in a separate table). This is because the primary key is not stored in the index memory anymore.

  • Query Cache Enabled for MySQL Cluster: See Section 5.13, “The MySQL Query Cache”, for information on configuring and using the query cache.

  • New Optimizations: One optimization that merits particular attention is that a batched read interface is now used in some queries. For example, consider the following query:

    SELECT * FROM t1 WHERE primary_key IN (1,2,3,4,5,6,7,8,9,10);
    

    This query will be executed 2 to 3 times more quickly than in previous MySQL Cluster versions due to the fact that all 10 key lookups are sent in a single batch rather than one at a time.

  • Limit On Number of Metadata Objects: In MySQL 5.1, each Cluster database may contain a maximum of 20320 metadata objects, including database tables, system tables, indexes and BLOBs.


 
 
  Published under the terms of the GNU General Public License Design by Interspire