The latest from the MySQL community

I’ve put online the slides for the two talks that I сo-presented at the Percona Live conference:

• Advanced query optimizer tuning and analysis tutorial (slideshare, pdf)
• MariaDB Cassandra interoperability session (slideshare, …

From https://fedoraproject.org/wiki/Test_Day:2013-04-30_MariaDB, this installment of Fedora’s Test Day focuses on the replacement of MySQL with MariaDB. If you’re a Fedora (or RHEL or CentOS user), do take a peek at the page and see if you can pitch in – it might be a little bit of work for you, but with great benefits in terms of getting the MariaDB performance and features, and specifically on the day the Fedora crowd have extra people on the case to track and address issues you might find, so it’s an ideal opportunity to upgrade on a development or test-prod environment!

It was with pride I entered the stage at the Percona MySQL Conference yesterday to receive the “Storage Engine of the Year 2013 award” for ndb (the MySQL Cluster storage engine).  Thank you very much, on behalf of myself and the MySQL Cluster engineering team. The team has done a great work over the past 10 years to harden the product, and add new features. MySQL Cluster milestone 7.3.2 was just released. It will be my pleasure to present the “trophy” some members of the team when I get back to Stockholm. Please see a previous post for how the MySQL Cluster journey began for myself and the ndb team 10 years ago.

In my Percona live keynote “Driving MySQL Innovation” on Tuesday I talked about the quality problems we had with 5.0 and 5.1 and the switch to the new Milestone Release model. I explained how this new development process allowed us to deliver both 5.5 and 5.6, on time, including innovative features, and with very high quality.

The description of the Milestone Release model has been available here, for almost two years. So, this is not new but it has now been in operation for 3.5 years and it can be worth looking back and summarize.

Our first MySQL Server Milestone 5.4.3 was released on October 9th, 2009 and was then followed up by 5.5.2 on Feb. 26, 2010. Initially there was a time of trying out and fine tuning the model before it found its current form around 5.5 GA. The …

I ran across this gem recently on StackOverflow:

$queryxyzzy12=("SELECT * FROM visitorcookiesbrowsing ORDER by id ASC");
$resultxyzzy23=mysql_query($queryxyzzy12) or die(mysql_error());

while($ckxf = mysql_fetch_assoc($resultxyzzy23)){
    $querycrtx=("SELECT * FROM cart WHERE userkey='$ckxf[usercookie]' ORDER by datebegan  DESC");
    $resultcrtx=mysql_query($querycrtx) or die(mysql_error());
    ------ snip ------
}

Besides the interesting variable names used, it’s also doing a query inside a loop, which is very inefficient. This would be better written as a single JOIN query:

SELECT 
  v.*, 
  c.* 
FROM 
  visitorcookiesbrowsing v 
  LEFT JOIN cart c ON c.userkey=v.usercookie 
ORDER BY 
  v.id ASC, 
  c.datebegan DESC

The details of JOIN vs. LEFT JOIN depend on the actual intent of the code, which wasn’t apparent from the snippet.

If at …

One of the first things we do when preparing a client’s infrastructure for Galera deployment is see whether their schema is suitable.

• Avoiding quirks and edge cases, we can say that Galera simply requires all tables to be InnoDB and also have a PRIMARY KEY (obviously having a PK in InnoDB is important anyway, for InnoDB-internal reasons).
• We want to know about FULLTEXT indexes. With recent InnoDB versions also supporting FULLTEXT we need to check not just whether a table has such an index, but actually which engine it is.
• Spatial indexes. While both InnoDB and MyISAM can deal with spatial datatypes (POINT, GEOMETRY, etc), only MyISAM has the spatial indexes.

Naturally, checking a schema in the server is more effective than going through other sources and possibly missing bits. On the downside, the only viable way to get this info out of MariaDB is INFORMATION_SCHEMA, but because of the way …

I’m presenting two talks at the OpenWest Conference next month.  The first talk, on May 2nd, will be about PHP security.  The second talk, given on May 3rd, will be about database optimization, geared towards web developers.  I’ll discuss some of the same things that I have discussed on this blog.

We’re going to have some great speakers at that conference.  On the PHP front, we’ll have Rasmus Lerdorf giving a keynote as well as another presentation.  From the MySQL community, Mark Callaghan from Facebook will be giving another keynote, and we’ll also have Colin Charles from MariaDB.

It should be an exciting conference!

Joseph Scott pointed me to a little tidbit hidden in the latest Percona Server release notes: “Percona Server for MySQL will now be shipped with the libjemalloc library.”  Percona published the results of some testing of various malloc libraries on their MySQL High Performance Blog last year, and it looks like this will have a very positive impact on performance.

I’m currently using MariaDB, so I’m hoping they pick up this change as well.

Let’s look at the index from the wp_posts table in a standard WordPress installation.

SHOW KEYS FROM wp_posts;
+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name         | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wp_posts |          0 | PRIMARY          |            1 | ID           | A         |        1772 |     NULL | NULL   |      | BTREE      |         |               |
| wp_posts |          1 | post_name        |            1 | post_name    | A         |        1772 |     NULL | NULL   |      | BTREE      |         |               |
| …

“Working with empirical genomic data and modern computational models, the laboratory addresses questions relevant to how genetics and the environment influence the frequency and severity of diseases in human populations” –Thibault de Malliard. Big Data for Genomic Sequencing. On this subject, I have interviewed Thibault de Malliard, researcher at the University of Montreal’s Philip Awadalla [...]