Slow-running query on clustered MySQL environment

Question

I've got a very simple query, directly equivalent to

SELECT * FROM sometable t WHERE somecol = 'somevalue' ORDER BY createdon DESC LIMIT 0,20000;

When I run this query on my non-clustered development machine, it runs in 0.07 of a second. When I run the same query on one of our clustered load-balanced servers, it takes upwards of half an hour to complete. The data on both servers is exactly the same.

Why is this occurring?

Here is some more info on the setup:

• The total number of rows in the table is ~14k, so it doesn't get anywhere near the LIMIT.
• MySQL version is MySQL Server: 5.1.56-ndb-7.1.15a-cluster-gpl
• Ubuntu 11.04 (GNU/Linux 2.6.38-8-server x86_64).
• Storage Engine NDBCLUSTER
• InnoDB on my local server

The table schema is as follows, where {X} is InnoDB on my local machine or NDBCluster on the production servers.

CREATE TABLE `sometable` (
    `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `UserId` bigint(20) unsigned NOT NULL,
    `Cookie` varchar(255) DEFAULT NULL,
    `somecol` varchar(30) DEFAULT NULL,
    `IpAddress` varchar(255) DEFAULT NULL,
    `SomeCollection` text,
    `someothercolumn` decimal(6,2) NOT NULL,
    `someothercolumn2` decimal(6,2) DEFAULT NULL,
    `Result` tinyint(4) NOT NULL,
    `Version` tinyint(4) NOT NULL,
    `Source` varchar(255) DEFAULT NULL,
    `CreatedOn` datetime NOT NULL,
    PRIMARY KEY (`Id`),
    KEY `CreatedOnIndex` (`CreatedOn`),
    KEY `SomeColIndex` (`somecol`),
    KEY `ResultIndex` (`Result`),
    KEY `SomeCol2Index` (`someothercolumn2`)
) ENGINE={X} AUTO_INCREMENT=97043 DEFAULT CHARSET=latin1;

Thanks in advance.

Answer 1

You may want to consider if you really need the MySQL Cluster, it may be overkill for your purposes and may not justify the complexity. A pair of MySQL Master-Master or Master-Slave instances with replication are good enough for many shops and will deliver better performance in this scenario.

If you have to stay with your current architecture, you can add Index on column "somecol", first try HASH, then B-TREE index. These should help, still performance would not be as good as a simple host instance.