MySQL can optimize aggregate functions like MIN and MAX as long as the columns specified are indexed. This means that, in the case of MIN and MAX, the optimizer should be able to identify the highest and lowest values of an indexed column from the B-Tree index. Say I have a table like below:

CREATE TABLE `history` (
   `h_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `u_id` int(10) unsigned NOT NULL,
   `cn_id` int(10) unsigned NOT NULL,
   `f_id` int(10) unsigned NOT NULL
   PRIMARY KEY (`h_id`)
 ) ENGINE=InnoDB

If I want to get the MAX value for cn_id, I’d to a query like this which will be a full table scan:

mysql (test) > EXPLAIN SELECT MAX(cn_id) FROM history \G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: history
          type: ALL
 possible_keys: NULL
           key: NULL
       key_len: NULL
           ref: NULL
          rows: 19908716 …

Read the original article at The Needle in Big Data Noise

Join 5500 others and follow Sean Hull on twitter @hullsean. Also take a look at: I hacked Disqus Digests to discover new blogs Who the heck is Bayes Thomas Bayes was a scientist & thinker, Fellow of the Royal Society, and back in 1763 author of “An Essay toward Solving a Problem in the Doctrine [...]

For more articles like these go to Sean Hull's Scalable Startups

Related posts:

1. Big Data – What is it and why is it important?
2. NYC Tech Firms Are Hiring – Map
…

This is a step by step guide on installing apache with PHP, APC and MySQL. APC- Advance php cache. This tutorial will works for fedora/redhat/centos.

Install Apache httpd

[root@localhost ~]# yum -y install httpd

Start httpd service

[root@localhost ~]# /etc/init.d/httpd start           [  OK  ]
[root@localhost ~]# ps -ef | grep http
root      2011     1  0 11:46 ?        00:00:00 /usr/sbin/httpd
apache    2013  2011  0 11:46 ?        00:00:00 /usr/sbin/httpd
apache    2014  2011  0 11:46 …

If you are using Percona Replication Manager (PRM) with RHEL/CentOS prior to 6.4, upgrading your distribution to 6.4 may break your cluster. In this post I will explain you how to fix your cluster in case it breaks after a distribution upgrade that implies an update of pacemaker from 1.1.7 to 1.18. You can also follow the official documentation here.

The version of Pacemaker (always considered as Technology Preview by RedHat) provided with 6.4 is 1.1.8-x which is not 100% compatible with 1.1.7-x see this report.

So if you want to upgrade, you cannot apply any rolling upgrade process. So like for Pacemaker 0.6.x to 1.0.x, you need again to update all nodes as once. As notified in …

I thought I would take the new subquery optimizations in MySQL 5.6 for a spin today, using the world sample database provided by MySQL for certification and training.

Typical IN subquery

This is a very typical query developers run, which historically has performed very poorly on MySQL:

mysql5.5.31 > EXPLAIN SELECT * FROM City WHERE CountryCode IN
 (SELECT code FROM Country WHERE name = 'United States');
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: City
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3984
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: Country
         type: unique_subquery
possible_keys: PRIMARY …

Thank you for all of you who dropped by the MySQL booth at Drupalcon to say ‘Thank you’ for MySQL. At every show there are several folks who do this but at Drupalcon it seemed like half the 3,000 attending dropped by to say ‘thanks’, grab MySQL stickers, OurSQL Poscasts disks, and talk about MySQL 5.6. I’ll see all y’all next year in Austin.

Speaking of Austin, the Texaslinuxfest.org is this weekend. I will be talking Saturday in Track D at 11:25 on the Proper Care and Feeding of a MySQL Database.

And the FREE Southeast Linux Fest is June 7-9 in Charlotte. This year featuring a ‘Zero to DBA’ track to get all …

Two months ago I posted a performance comparison running Sysbench on MongoDB versus MongoDB with Fractal Tree Indexes v0.0.2. The benchmark showed a 133% improvement in throughput. Nice, but our engineering team had an effort on our road-map for lock refinement that we believed would really boost our performance, which is now available in v0.1.0. The benchmark application itself is unchanged and available on GitHub.

For anyone curious about Sysbench itself, the details are available from the prior blog. The only change for this run was hardware. Our Sun x4150 server recently began rebooting itself at random times, so it has been replaced with a newer HP server. Another change is …

Installing Apache2 With PHP5 And MySQL Support On Debian Wheezy

LAMP is short for Linux, Apache, MySQL, PHP. This tutorial shows how you can install an Apache2 webserver on a Debian Wheezy server with PHP5 support (mod_php) and MySQL support.

Selecting the most appropriate solution for a MySQL HA infrastructure is as much a business and philosophical decision as it is a technical one, but often the choice is made without adequately considering all three perspectives.  When too much attention is paid to one of these aspects at the cost of the others, the resulting system may be over-engineered, poorly-performing, and/or various other flavors of suboptimal.

On Wednesday, June 5, at 10 a.m. PDT (1700 UTC), I will be presenting a webinar entitled, Choosing a MySQL HA Solution, in which we’ll explore the topic of MySQL HA from each of these perspectives.  The goal will be to motivate your thinking about HA in a holistic fashion and help …

Playing around with Percona Replication Manager, and being new to Pacemaker, some errors were somewhat cryptic.

May 26 09:53:39 [2181] ha01.localdomain    pengine:   notice: unpack_rsc_op:    Preventing p_mysql from re-starting on ha02.localdomain: operation monitor failed 'not installed' (rc=5)
May 26 09:53:39 [2181] ha01.localdomain    pengine:   notice: unpack_rsc_op:    Preventing op from re-starting on ha02.localdomain: operation monitor failed 'not installed' (rc=5)

First, it says here that I have ‘operation monitor failed’ on node ha02.localdomain. This error simply means ‘I cannot run the operation monitor because it is not installed’. Where does this operation supposed to come from, from the resource agent. Turns out, I did not have the mysql resource agent in place under /usr/lib/ocf/resource.d/percona.

May 27 23:24:29 [2013] ha01.localdomain crm_resource:    error: unpack_rsc_op:  Preventing reader_vip_2 from …