This chapter starts as:

Chapter 4. Miscellaneous.

There are cases when wrong output is just symptom of wrong input made before.

For example, you start to receive wrong data at particular step of the
scenario. After analysis of the SELECT query (or queries) is clear what
queries are correct and return exactly same data which exists in the
table (or tables).

This means wrong data was inserted at the earlier step.

How to know when it happened?

Start from the step of the scenario which exists just before
step which does output, check every query as was described in earlier
chapters. If everything works correctly, examine earlier step, repeat
until you find the error.

MySQL's SHOW STATUS command has two counters that are often confusing and result in "what does that mean?" questions:

1. Handler_read_rnd
2. Handler_read_rnd_next

As I understand it, there is some historical context to the choice of names here, hearkening back to before I was involved with MySQL, way back when it was a wrapper around ISAM tables -- or even earlier, I don't know. (Unireg, anyone?) In any case, these two variables deserve a little explanation.

Both counters indicate the number of times the corresponding storage engine API function has been called. In olden times, the storage engine API was called the handler API, which is why the variables begin with Handler_.

Handler_read_rnd counts the number of times the handler::rnd_pos() method is called. This method fetches a row from a table based on a "fixed position," i.e. a random-read. What this actually means varies between storage …

I have introduced some new people to MySQL recently and had to back track the years to figure out how I learned what I learned about MySQL indexes.  A quick way to get up to speed about MySQL indexes is these three podcasts by Sheeri Cabral.

1. OurSQL Episode 13: The Nitty Gritty of Indexes
2. OurSQL Episode 17: Hashing it out
3. OurSQL Episode 18: De-myth-tifying Indexes

Those three episodes do a good job of explaining how indexes work so that you have a better understanding of how MySQL …

While many people are familiar with the MySQL EXPLAIN command, fewer people are familiar with "extended explain" which was added in MySQL 4.1

EXPLAIN EXTENDED can show you what the MySQL optimizer does to your query. You might not know this, but MySQL can dramatically change your query before it actually executes it. This process is called query rewriting, and it is a part of any good SQL optimizer. EXPLAIN EXTENDED adds a warning message to the EXPLAIN output which displays additional information, including the rewritten query.

To take a look at EXPLAIN EXTENDED, I'll start with three empty tables. It is important to note that the tables are empty because the MySQL optimizer treats empty tables (and incidentally, tables with only one row) differently than tables which contain more than one row. More about that in a bit.

PLAIN TEXT SQL:

…

Anybody have a few millions lines of Apache Log files they can share? I am working on a lab/demo on InfiniDB and need a few millions lines of an Apache HTTPD log file. I no longer run any large websites and would rather use real data over creating something. I will sanitize your URL so you will be anonymous, so www.YourCompanyHere.com will end up as www.abc123.com or something similar.

The demo/lab will show how to load data into the columnar InfiniDB storage engine and run some analytics against the data. Please let me know if you can help.

Are you doing some datetime manipulation or maybe you are migrating from some database technology to MySQL or possibly using milliseconds?
Here is an example on how to go about it:

Say you have the following date: MAR 16 2008 09:12:51:893AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893AM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 2008031691251.893000


What if its PM rather than AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893PM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 20080316211251.893000


Ok so this is just simple string manipulation where:
%M is the month name
%d is day number
%Y is the year
%h is the hour
%i is the minute
%s is the second
%f is the microsecond
%p is the period: ante or post meridiem
In the DATE_FORMAT part we se a %k which is …

Hi,

We are pleased to announce the release of SQLyog – MySQL GUI 8.5 GA. Below are some pointers:

• As the title suggests this release comes with tabbed interface for different connections. We need not have to emphasize the usability of a tabbed interface vs. a windowed one. All web browsers have already demonstrated it to us. Now in SQLyog, you can switch between different connections in a snap! We didn’t forget to add keyboard shortcuts to jump from one tab to other. Most – if not all – shortcuts for tabs of a standard web browser would work with SQLyog. Multi-coloring of connection tabs & object browser pane adds to the ease of use. Chances of one executing queries in one server than the other by mistake would be rare as the brain would easily grasp the connection by it’s color. Demoed below is a typical setup of multi-colored tabs in a Web Developer’s PC:

…

In this month’s article we discuss Maatkit again, following up on our previous article last month.  There are a whole host of backup and administrative tools including some to dump in parallel, search for tables, search for queries based on criteria and more.

Even More Maatkit at Database Journal

Database schema's need version control. Here you can find some guidelines help you control your changes.

(Note: The review was done as part of our consulting practice, but is totally independent and fully reflects our opinion)

In my talk on MySQL Conference and Expo 2010 "An Overview of Flash Storage for Databases" I mentioned that most likely there are other players coming soon. I actually was not aware about any real names at that time, it was just a guess, as PCI-E market is really attractive so FusionIO can't stay alone for long time. So I am not surprised to see new card provided by Virident and I was lucky enough to test a pre-production sample Virident tachIOn 400GB SLC card.

I think it will be fair to say that Virident targets where right now FusionIO has a monopoly, and it will finally bring some competition to the market, which I believe is good for …