From Stack Overflow:

When I run an SQL command like the one below, it takes more than 15 seconds:

SELECT  *
FROM    news
WHERE   cat_id = 4
ORDER BY
        id DESC
LIMIT   150000, 10

EXPLAIN shows that its using where and the index on (cat_id, id)

LIMIT 20, 10 on the same query only takes several milliseconds.

This task can be reformulated like this: take the last 150,010 rows in id order and return the first 10 of them

It means that though we only need 10 records we still need to count off the first 150,000.

The table has an index which keeps the records ordered. This allows us not to use a filesort. …

This week the Log Buffer is a little more challenging for two reasons: a) Oracle Open World 2009 and b) the controversy around Monty Widenius‘ opposition to Oracle owning MySQL due to the Sun acquisition, so let’s go straight to the articles.

Oracle – Oracle Open World 2009

There is so much material about OOW09, that I’m giving a full subtitle to it.

Let’s start with a quick recap of the keynotes by Scott McNealy and Larry Elison in this article by Andrew Clarke: The return of The Scott And Larry Show. The recap suggests that the presentations aimed to show how …

It's time to announce our plans for Percona Performance Conference 2010. We will hold the event on April 12th and 13th in Santa Clara, California. In the months to come we will announce the theme, the exact location (we have it reserved, but it's subject to change), the exact dates (we may add days), and we'll open up registration and a Call For Proposals. Stay tuned!

Entry posted by Baron Schwartz | 7 comments

Add to: | | …

Given the announcement earlier this week of an updated release of the actively developed MySQL Workbench 5.2, I thought I'd give it a quick look...

Install on Windows went without a hitch.

One thing I noticed, which I don't remember being previously available in MySQL GUI tools, is the ability to connect to a remote MySQL server via SSH tunneling.

Below is a screen shot:



Well done! A small, but significant addition for folks that require SSH to connect to their systems.

I did notice that the design in Workbench involves selecting "Standard TCP/IP over SSH" from the drop down and then providing credentials in a more "form" oriented layout. An "SSH" tab like we see in Navicat or SQLyog …

The 3rd of November I will have a webinar on Cluster Performance Tuning.

For more information:

http://www.mysql.com/news-and-events/web-seminars/display-453.html

In addition to what is mentioned in the Agenda I will also present numbers on typical operations, how to design requests, explain how the optimizer works with MySQL Cluster etc etc.

See you there!

-johan

Maatkit is a pretty useful set of utilities for MySQL. From their site:

You can use Maatkit to prove replication is working correctly, fix corrupted data, automate repetitive tasks, speed up your servers, and much, much more.

One of the first things you can do after installing the toolkit (which may already be installed if you are running CentOS or Debian) is to run the mk-audit utility. It will give you a nice summary of your server, as well as point out potential problems in your configuration.

Here's a list of all the utilities included in Maatkit:

• mk-archiver Archive rows from a MySQL table into another table or a file.
• mk-audit Analyze, summarize and report on MySQL config, schema and operation
• mk-checksum-filter Filter checksums from …

Sometimes, as Sergei rightly mentioned, I can be, well, "righteously indignant" about what I perceive to be a hack.

In this case, after Sergei repeatedly tried to set me straight about what was going on "under the covers" during a REPLACE operation, I was still arguing that he was incorrect.

Doh.

I then realized that Sarah Sproenhle's original comment about my test table not having a primary key was the reason that I was seeing the behaviour that I had been seeing.

My original test case was failing, expecting to see a DELETE + an INSERT, when a REPLACE INTO was issued against a table. When I placed the PRIMARY KEY on the table in my test case and re-ran the test case, it still …

This blog was started to coincide with the Open Source release of Calpont's InfiniDB Data Warehouse engine for MySQL. You should be able to find the source in SourceForge and Launchpad by now, as well as tar files containing a source distribution as well as 32- and 64-bit bit binary tar files.

As the Software Architect for the InfiniDB product, I will be using this blog to discuss the lower-level features of how and why we did what we did.

If you want to get started as quickly as possible, I would download one of the binary tar files and follow the directions in README.

After that, you may want to visit http://www.infinidb.org/ and browse the available documentation.

Sometimes you forget about what you did years ago, for example the ability of mysqlnd write back traces of slow queries into the PHP error log . Regardless if you use mysqlnd with ext/mysql, ext/mysqli or PDO_MYSQL, mysqlnd is able to tell you which of your code has executed a slow query. To enable the feature, you need to compile PHP with CFLAGS="-D A0". A0? Yeah, we must have had some doubts about the usefulness.

Slow queries

The MySQL server is capable of monitoring queries and recording slow queries in a slow query log. MySQL can log the following types of queries:

• SQL statements that took more than long_query_time seconds to execute
• SQL statement that required at least min_examined_row_limit rows to be examined (MySQL 5.1.21+)
• SQL statement …

Sometimes you forget about what you did years ago, for example the ability of mysqlnd write back traces of slow queries into the PHP error log . Regardless if you use mysqlnd with ext/mysql, ext/mysqli or PDO_MYSQL, mysqlnd is able to tell you which of your code has executed a slow query. To enable the feature, you need to compile PHP with CFLAGS="-D A0". A0? Yeah, we must have had some doubts about the usefulness.

Slow queries

The MySQL server is capable of monitoring queries and recording slow queries in a slow query log. MySQL can log the following types of queries:

• SQL statements that took more than long_query_time seconds to execute
• SQL statement that required at least min_examined_row_limit rows to be examined (MySQL 5.1.21+)
• SQL statement …