If you are using FULLTEXT indexes in MySQL and plan to switch from MyISAM to InnoDB then you should review the reference manual section on Fine-Tuning MySQL Full-Text Search to see what configuration changes may be required. As I mentioned in yesterday's post when comparing query results on my database with FULLTEXT indexes in MyISAM versus InnoDB I got different results. Specifically, the InnoDB tables were returning fewer results for certain queries with short FULLTEXT search terms. Here's an example of a query that returned fewer results on InnoDB:

select id from flite.ad_index where match(name,description,keywords) against('+v1*' IN BOOLEAN MODE);

The issue was that all of the fine tuning I …

I have found myself using UNION in MySQL more and more lately. In this example, I am using it to speed up queries that are using IN clauses. MySQL handles the IN clause like a big OR operation. Recently, I created what looks like a very crazy query using UNION, that in fact helped our MySQL servers perform much better.

With any technology you use, you have to ask yourself, "What is this tech good at doing?" For me, MySQL has always been excelent at running lots of small queries that use primary, unique, or well defined covering indexes. I guess most databases are good at that. Perhaps that is the bare minimum for any database. MySQL seems to excel at doing this however. We had a query that looked like this:

select category_id, count(*) from some_table

where

    article_id in (1,2,3,4,5,6,7,8,9) and

    category_id in (11,22,33,44,55,66,77,88,99) and

    some_date_time > now() - interval 30 day
 …

This comes from an issue that I worked on recently, wherein a customer reported that their application was working fine under stock MySQL 5.6 but producing erroneous results when they tried running it on Amazon RDS 5.6. They had a table which, on the working server, contained two TIMESTAMP columns, one which defaulted to CURRENT_TIMESTAMP and the other which defaulted to ’0000-00-00 00:00:00′, like so:

CREATE TABLE mysql56 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ts1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ts2 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
);

However, under Amazon RDS, the same table looked like this:

CREATE TABLE rds56 ( 
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT NULL, 
);

They mentioned that their schema contains TIMESTAMP column definitions without any modifiers for nullability or …

There are a lot of holy wars between programming language advocates in the industry.

I use Python.

Why?

Because I have found that programs written in Python are significantly more likely to Work Correctly. They more often run correctly the first time, therefore I spend less time debugging.

There are lots of other reasons, but it's mainly the "It just works".

A few years ago, I had 10+ years of industry experience of Perl, but only 1 year of Python. I already found that my Python programs initially worked correctly FAR MORE OFTEN.

This is comparing a language that I'd been using commercially, most days, for 10 years, with something that I'd only just picked up.

That's why I use Python.

---
Other stuff?

DEBUGGING: Stack traces. In Perl, it's possible to get a stack trace from an exception, but you …

MaxScale is a modular proxy application, the modules can be considered as the building blocks of your proxy implementation within your MySQL database environment. It is important to know what building blocks you have at your disposal.  The release of version 1.0 as a beta means that the number of available modules has grown once again. Normally I post about the incremental changes in what is available, but I thought that maybe it was a good time to post a short summary of all the modules. This is not designed to be comprehensive documentation for the functionality available, merely as a summary of what is available.Routing ModulesRouters are perhaps the most important modules within MaxScale, since they make the decisions as to where to send requests. However they are incapable of functioning autonomously and require monitor and protocol modules in order to fulfil a useful function.
There are two classes of router within MaxScale, …

A few weeks ago we announced the availability of the MySQL Central @ OpenWorld content catalog.

We're now pleased to announce additional keynotes delivered by MySQL power users. They will follow the "State of The Dolphin" keynote address by Oracle's Chief Corporate Architect Edward Screven and VP of MySQL Engineering Tomas Ulin, and include:

High Speed Event Logging at Booking.com
Nicolai Plum, Senior Systems Architect, will provide an overview of Booking.com's MySQL-based event logging application, recording data from all customer interactions in order to develop the best customer …

As I prepare to convert some MySQL tables with FULLTEXT indexes from MyISAM to InnoDB I want to verify that running a standard production query set against the tables will return the same results with InnoDB that it did with MyISAM. Since I read Matt Lord's blog post about the document relevancy rankings used for InnoDB full-text searches I knew to expect some differences when sorting by relevancy, so I want to focus on getting the same set of rows back, mostly ignoring the order in which the rows are returned.

Percona toolkit has a tool called pt-upgrade that works well for this purpose. I used 2 test servers with a copy of my production database. On one of the servers I left the tables in MyISAM, and on the other I converted the tables to InnoDB. I …

While there are many graphing tools out there and we’ve used Munin for a while now.

The MySQL plugin for Munin had fallen out of date and the show engine innodb status output changed in 5.5 making some bits of the plugin simply not work any more. Also the show global status has some extra variables so there was a need to create new graphs.

All of these are now in the 2.1.8+ development releases of Munin.

Here are samples of the new/updated graphs.

Tables

Table …

#DBHangOps 07/10/14 -- MySQL Index Types and More!

Check out the recording below!

Hello everybody!

Join in #DBHangOps this Thursday, July, 10, 2014 at 11:00am pacific (18:00 GMT), to participate in the discussion about:

• Indexes in MySQL

  • PRIMARY, UNIQUE, FULLTEXT and more
  • Geo-spatial
  • Hash indexes
  • Fractal Indexes (TokuDB)

Be sure to check out the #DBHangOps twitter search, the @DBHangOps twitter feed, or this blog post to get a link for the google hangout on Thursday!

See all of you on Thursday!

Show notes

Indexes

Spatial index resources

• …

It was a year ago, on a nice Sunday night of the English Summer (apologies for the oxymoron), that Mark Riddoch came to see me and together we headed to the Vansittart Arms, our local family pub round the corner. A pint of London Pride on one side and a Honey Dew on the other were the perfect add-on to Mark’s MacBook Pro, on which Mark was showing me the 0.1 version of MaxScale. It was the result of the joint efforts of Mark’s team, Massimiliano and Vilho, who had worked hard to bring to life the first version of something that I believe will be a natural addition to clusters of MySQL/Percona/MariaDB servers in the near future.

A year ago, Mark showed me a …