Read the original article at 3 Ways to Optimize for Paging in MySQL

Join 6100 others and follow Sean Hull on twitter @hullsean. Lots and lots of web applications need to page through information. From customer records, to the albums in your itunes collection. So as web developers and architects, it’s important that we do all this efficiently. Start by looking at how you’re fetching information from your [...]

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

Read the original article at How to Optimize MySQL UNION For High Speed

Join 6100 others and follow Sean Hull on twitter @hullsean. There are two ways to speedup UNIONs in a MySQL database. First use UNION ALL if at all possible, and second try to push down your conditions. 1. UNION ALL is much faster than UNION How does a UNION work? Imagine you have two tables [...]

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

Are you a Django user? There’s an upcoming Django conference in Chicago in a few months, and I know they’re looking for speakers with MySQL experience in particular. One suggestion the organizers have floated is a talk on MySQL:

I’m looking for someone to give at least one MySQL talk there. In particular, I would love a (friendly but vigorous) “Why you should use MySQL instead of PostgreSQL talk”, as PostgreSQL tends to get a lot of love and attention at Django events, and MySQL not so much.

Take a look at it and see if you are interested. Presenting at a conference is one of the best things you can do for your career, your company, and your community of open-source software. I highly encourage it if you haven’t tried it.

Original images from Flickr user jenniferwilliams

One of our clients, for various historical reasons, runs both MySQL and PostgreSQL to support their website. Information for user login lives in one database, but their customer activity lives in the other. The eventual plan is to consolidate these databases, but thus far, other concerns have been more pressing. So when they needed a report combining user account information and customer activity, the involvement of two separate databases became a significant complicating factor.

In similar situations in the past, using earlier

Last week Tokutek announced that they’re open-sourcing their TokuDB storage engine for MySQL. If you’re not familiar with TokuDB, it’s an ACID-compliant storage engine with a high-performance index technology known as fractal tree indexing. Fractal trees have a number of nice characteristics, but perhaps the most interesting is that they deliver consistently high performance under varying conditions, such as when data grows much larger than memory or is updated frequently. B-tree indexes tend to get fragmented over time, and exhibit a performance cliff when data doesn’t fit in memory anymore.

The MySQL community is excited about having access to TokuDB’s source code, and rightly so. TokuDB is, broadly

Years ago I complained bitterly about MySQL’s backwards development and release model, which made guinea pigs out of the paying customers. I think I’d be remiss if I didn’t say it’s been fixed for years. And it’s really fixed right in my opinion — much better than what I proposed.

Congratulations, and thanks, to the MySQL team for superhuman software engineering, release engineering, documentation, bug triage and analysis, and doing a million things right — in other words, making a damn good database, which is hard. You know I still have gripes occasionally, and so do most people, but in the scheme of things — wow. MySQL is awesome.

We’re ironing out a kink that’s preventing Planet MySQL from aggregating VividCortex’s blog feed, so while that’s in progress, I’ll post a quick note on what we’re up to at VividCortex, for the Planet MySQL readers.

VividCortex is a monitoring and analysis product for MySQL, provided as Software-As-A-Service, with agents that run in your systems and report back to our APIs. The agents are super-efficient and non-obtrusive (you’ve probably noticed my posts about Go recently). They gather high-resolution data about your systems and our web application helps you make sense of it.

VividCortex is shockingly easy to install — if you’re slow at the keyboard, it takes 30 seconds. In less than a minute you can get insight into what your MySQL servers are doing. We are in closed

If you are importing large CSV or SQL dumps to MySQL, chances are you were looking for ways to see how far the import has gone. If you know how many rows there are from the file being imported, you can do a SELECT COUNT(*) but that would take sometime for the query to finish especially on really big imports.

Using lsof, you can monitor the current file offset to which a process is reading from using the -o option. Knowing the size of the file and some snapshots of the offset, you can get a somewhat rough idea of how fast the import goes. Note though that this is only file-read-pace not actual import speed as MySQL import can vary depending on a number of conditions i.e. table growth, secondary indexes, etc.

Let’s say I am importing a 1.1G CSV file into a table.

[revin@forge msb_5_5_300]$ ls -al

While I tried to deflect how you perform SQL Injection attacks against a MySQL procedure, my students requested that I post examples of what to do to avoid SQL injection, and what not to do to invite attacks. The best practice to avoid SQL injection attacks is too always bind inputs to data types, and avoid providing completely dynamic WHERE clauses.

Here’s the correct way to dynamically generate a result from a MySQL Stored Procedure:

CREATE PROCEDURE hello (IN pv_input VARCHAR(50))
BEGIN
  SELECT sample_id
  ,      sample_name
  FROM   sample
  WHERE  sample_name = pv_input;
END;
$$

A call to this hello procedure will only return the row or rows where the pv_input value matches the sample_name column value. Any attempt to exploit it like the one below fails.

CALL

MySQL 5.6 through the eyes of a custom storage engine MySQL plugin

MySQL is famous for its pluggable storage engine architecture which allows a DBA or an application developer to choose the right engine for the task. An application uses MySQL API and is isolated from all of the low-level implementation details at the storage level. As an example, the Cloud Storage Engine (ClouSE) enables existing MySQL applications to use cloud storage such as Amazon S3 or Google Cloud Storage to store its data. The application doesn’t need to be changed or even redeployed: with ClouSE, remote cloud storage will look like a better (ultra-scalable, durable, always-on) alternative to the local storage.

As you may already know, ClouSE now supports

I’m really looking forward to Percona Live this year. I will be there, along with Kyle Redinger, my co-founder at VividCortex. I feel that this year the conference has come full circle. 2007 was my first year at the conference, and it was amazing. This year is tremendously exciting for me because it feels like we’re back on the 2007 trajectory.

Those were the golden days. Things were a little sad in 2010 and 2011 as the MySQL community tried to figure out the new landscape and O’Reilly decided to stop running the event, but then in 2012 we all said “we’re back!” with the exception of Oracle, who instead arranged MySQL Connect, as part of Oracle Open World. I don’t think that was a huge success. I don’t know if that’s why

High Performance MySQL, 3rd Edition has been selling very well. It’s translated into many languages. O’Reilly sends me a hard-copy of the translations, and I have a whole section on my bookshelf dedicated to them. It’s really satisfying to look at it.

Today I’m happy to announce that we’re moving forward with a new batch of translations. Demand has been so strong that we want to make the book accessible to as wide an audience as possible. Plus, I get a fat check every time O’Reilly sells the translation rights.

The new languages will include Australian, l337 (“Leet”), Jive, Ebonics, Elmer Fudd, Blissymbols, and Esperanto. Here’s a sample before-and-after paragraph:

Isolating

Last night at the Golang-DC meetup I spoke about building (MySQL) database applications with Go. The meetup was well attended and people were very enthusiastic about Go. I spent a few minutes talking about Go in general, how VividCortex uses Go (we’ve built our agents, API servers, and all backend processes with Go), why we like it, some of the nice things it enables like making it easy to build very resilient programs, and then I gave the presentation, which I’ve embedded below.

Afterwards the discussion ranged to a lot of related topics. This was the best part of the evening for me. There were really great questions on a variety of topics, and insightful answers from everyone.

I really don’t like running my database in “I Love Garbage” mode, so I set the following SQL_MODE:

STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO, NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE, NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY

Guess what WordPress does with that? It doesn’t install. If you set the SQL_MODE to empty and install WordPress, then restore the SQL_MODE, WordPress will run, but if you try to create a post you’ll see an error page that says “You are not allowed to edit this post.”

This problem was reported to WordPress at least 7 years ago. Lessons learned:

I am currently finishing some features to make a program highly resilient to occasional crashing bugs. A particular function was found to crash on queries of the form WHERE x IN(NULL), and that crashed the entire program. Now we have a framework for intelligently recovering from arbitrary crashes. I will write more on this in the future, because I think it’s a very interesting thing to share.

In this episode, I want to focus on a related topic: how do you test a program that is supposed to be resilient to bugs you can’t predict? Many new problems are caused by writing clever code that is supposed to detect, avoid, or recover from problems, even known problems. Unknown problems are even riskier.

The approach that has given me a great deal of

One of the most rewarding things you can do is help someone get a great job or hire a great person for the position they need to fill. I have traveled a lot, written books, done a bunch of consulting, and spoken widely on MySQL, other databases, open source, and so forth. I’ve gotten to know a lot of people, some I’d call good friends, and many of them are leading large organizations. I think this is both a privilege and a serious responsibility.

It’s a privilege because I can ask some of these people for help or introductions or advice sometimes. It’s a responsibility because I need to be ready to do something for them, too. In many cases it’s a pay-it-forward kind of readiness.

Many, many people contact me looking for people to hire. I keep a list. When someone tells me they are on the job market, I try to

I was a guest on the Food Fight Show last week, along with a bevy of really smart people asking and answering tough questions on fault detection. We didn’t talk a lot about MySQL, but given that VividCortex is focusing on MySQL initially, pretty much all of my experience with zero-threshold, zero-configuration fault detection is MySQL-based.

It’s a fun conversation with a lot of insights into the industry, what’s wrong with current monitoring tools, and where monitoring is going. Also, it’s sold out now, but Monitorama is a conference you might be interested in if you’re doing monitoring (and who isn’t?)

Here is a collection of upcoming events that are interesting to me as a MySQL user (in some cases because I’m speaking). I think some of them are must-see events. I am sure I am missing a lot of events, but some of these are only publicized in specific channels, and I wanted to mention them here to help spread the word.

Upcoming Meetups

This coming Thursday, Charlottesville’s Neon Guild tech group will co-host a Meetup with a noted Kanban expert.

I’m joining two Meetups soon to talk about building database (MySQL) applications with the Go programming language:

• Wednesday, March 13, 2013 in Charlottesville Virginia

I was discussing how to avoid surprising users and someone pointed out that what seems intuitive and rational to one person is often complete insanity for others. The mental gap between a developer and a user can often be a chasm far too wide to cross. Of all the bug reports I’ve filed against MySQL, here is my all-time favorite:

select * from t where a >= 1.0order by a;

Does not cause an error. I believe it should, because there should be a whitespace before ORDER BY.

Similar syntax errors such as “select 1e0from dual” were also accepted as valid SQL. Much soul-searching later, the official reply from MySQL’s development team:

The server behaves properly here:
- “1″

Most of the time, when people say “scalability” they mean any of dozens of things. Most of the time, when I say it I mean exactly one precisely defined thing. However, I don’t claim that’s the only correct use of “scalability.” There is another, in particular, that I think is very important to understand: the inherent limitations of the system. This second one doesn’t have a single mathematical definition, but it’s vital nonetheless.

I’ll frame the discussion by asking this: how scalable is your database?

Using the two definitions I like to use the most, I answer the question in this way.

Had a case recently where I had to merge data from two identically structured tables containing nearly identical data.

"Nearly identical" meaning most table data is identical in both; sometimes a row is missing from one of the tables; sometimes same row (according to PK) appears in both, but some columns are NULL is one tables (while others can be NULL in the second).

Otherwise no contradicting data: it was not possible for some data to be "3" in one table and "4" in the other.

How do you create a merge of the tables, such that all missing rows are completed, and NULLs replaced by actual values when possible?

pt-table-sync comes to mind: one can do a bidirectional syncing of two tables, and actually stating how to resolve ambiguities (like "greater value

I’m really looking forward to this year’s Percona Live MySQL Conference. This is always THE event of the year for me in the MySQL conference circuit. It’s also the first year I haven’t been a speaker! I’ve been a speaker since 2007 but this year things were too uncertain for me to submit a proposal in time.

As usual, the real highlight of the conference is seeing and talking to everyone. Technical sessions are also great, but honestly I can usually study up on technical things without going to a conference. However, nothing can replace the benefit of meeting all the dedicated MySQL community members in the hallways and at meals, and talking to MySQL-related businesses in the expo hall. Year after year, this conference has been what makes things happen:

SELECT id FROM foo ORDER BY id LIMIT 10 OFFSET 1000;

SELECT id FROM foo WHERE id > 100000  ORDER BY id LIMIT 10;

Teradata results. Funding for DataXu. The chemistry of data. And more.

For 451 Research clients: Oracle launches major update to MySQL open source database bit.ly/TSONAt

— Matt Aslett (@maslett) February 8, 2013

For 451 clients: Analyzing the chemistry of data bit.ly/TSOV2R By @451wendy Treating sensitive data like dangerous chemicals

— Matt Aslett (@maslett) February 8, 2013

Teradata: Q4 net income $112m on revenue up 10% to $740m, FY net income $419m on revenue up 13% to $2.7bn. bit.ly/14FNS8L

[mysqld]
log-bin=/path/to/binlogs/mysql-binlog

I wonder if the MySQL archaeologists out there would be willing to unearth some (presumably ancient) history for me. Why does the logging configuration merit special mention in the version_comment variable?

The more I think about this, the more bizarre it seems. I enabled logging. The version reported by the server changed. No, really, is my server somehow a different version of MySQL now?

130203 15:39:55 [Note] ./bin/mysqld: ready for connections.
Version: '5.6.7-rc-log'

I assume there’s a good story behind this somewhere. I’m thinking a priest, a rabbi, and Monty walk into a bar, and black vodka is probably involved at some point too :-)

Somebody ran into a problem after reading about the MySQL CREATE statement and the AUTO_INCREMENT option. They couldn’t get a CREATE statement to work with an AUTO_INCREMENT value other than the default of 1. The problem was they were using this incorrect syntax:

CREATE TABLE elvira
( elvira_id    int unsigned PRIMARY KEY AUTO_INCREMENT=1001
, movie_title  varchar(60))
  ENGINE=InnoDB
  CHARSET=utf8;

It raises this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=1001
, movie_title  varchar(60))