One think I can see with people using EXPLAIN is trusting it too much, ie assuming if number of rows is reported by EXPLAIN is large query must be inefficient. It may not be the case.

The question is not only about stats which may be wrong and which is why you may want to profile your queries if you have any hesitations in EXPLAIN accuracy.

The other problem however is EXPLAIN does not take LIMIT into account while estimating number of rows. Basically it gives you the estimates of producing whole result set while it may stop much faster in case LIMIT is used.

Take a look at this simple example:

PLAIN TEXT SQL:

1. mysql> EXPLAIN SELECT * FROM rnd  ORDER BY r DESC LIMIT 1 \G
2. *************************** 1. row ***************************
3.            id: 1
4.   select_type: SIMPLE
5.       …

If you have been using solidDB for MySQL Beta 4 and decided to try out solidDB for MySQL Beta 5, you might have seen the following warning message when you try to start up mysqld: ¶

SOLID Database Warning 1022: The database you are using has been
originally created with a different database block size setting
than your current setting.
To suppress this warning edit the solid.ini file to contain
the following parameter setting:
[IndexFile]BlockSize=8192

The reason for this warning message is that the default block size has changed. Beta 4 was using a default block size of 8K and Beta 5 uses a default block size of 16K. ¶

We're setting new XAMPP versions together and would now ask you to take a look at the beta versions of the upcoming XAMPP release.

New in this version of XAMPP: MySQL (5.0.27), PHP (5.2.0), phpMyAdmin (2.9.1) and a Japanese translation.

XAMPP BETA versions are always for testing purposes only. There will be no upgrade packages from and to beta versions. To all testers: Many thanks in advance!!

[b:2c8kx244]Update 18.11.:[/b:2c8kx244] Since yesterday you also find two new versions of XAMPP for Solaris as XAMPP BETA releases, one for Solaris 10 SPARC, and one for x86. These versions are the last step to finalized releases of XAMPP for Solaris. Both versions are compiled for 64-bit systems.

You find the beta version at our special XAMPP BETA download area.

I am thankful for the plentiful feedback from MySQL Camp, on MySQL Winter of Code.

I started today’s session at MySQL Camp by shortly describing our plans, starting by our insight that more coding happens during wintertime than in summer. Through MySQL Winter of Code, we want to encourage contributions to MySQL in all areas of the server. connectors and GUI tools.

There are three requirements for getting a Winter of Code grant:

1. A signed Contributor License Agreement. Highlights:

• You assign and transfer the copyright of your contribution to MySQL. In return you receive back a broad license to re-use and …

Our first session in Day 2 of the MySQL Camp was the MySQL Winter of Code, as well as an overview of the QA Pilot program and Overview of the Community Doxygen Project by Kaj Arnö and Jay Pipes.

Starting with discussions on Code Contributions & MySQL Winter of Code

Quality Contributer Program

• More coding happens during wintertime then in summer
• MySQL has less contributions than many other Open Source projects
• Contributor License Agreement
  • We want to award contribution more then nominally
  • We want to encourage contributions in all areas
  • We prefer contributions in certain areas (especially encourage them)

Requirements for Winter of Code

• A signed Contributor License Agreement
• A well-formed proposal …

For a long time, whenever I wanted to do paging to browse through a table, I used to run 2 queries. The first would get the results, and the second would be an almost identical query, with a count() instead of fields, and I’d use the result of the second query to figure out how many pages I would need.

Now there’s a better solution, and it’s called found_rows(). Say you have a simple select statement:


select * from sometable LIMIT 10;


You don’t know how many rows are in the table, so you need to do a second query:


select count(1) from sometable;


Now you can calculate the number of pages.

Using found_rows, you write your first query as:


select SQL_CALC_FOUND_ROWS * from sometable LIMIT 10;
select found_rows();


Pagination with MySQL couldn’t be easier.

While this won’t …

Colin, son of Gary is officially the youngest attendee of the MySQL Camp. The new breed of MySQL DBA’s.

Colin really loves the swivel chairs, and now has is own Google shirt courtesy of Leslie!

Also check it out on Gary’s site.

I recently tested installing innotop on Microsoft Windows. There was one slight glitch, but I changed a couple lines of code, and now it runs out of the box under ActivePerl. Version 0.1.156 contains those changes for Windows compatibility.

Upon waking up this morning I was faced with the following question:

"Eat Hotel free breakfast, deal with crappy wireless.... or go to Google, get great food, and have good to great breakfast?"

Easy choice...

Yesterday? Explained daemon plugins, walked through the internals to MySQL and answered questions on refactoring MySQL.

Today?

Much of the same, and Brad and I are going to talk about memcached and ha_memcache (aka the Engine for MySQL). I think I am giving several other talks as well...

Busy, much busy...

Walking around Google and seeing all of the cheap projectors makes me wonder what I could do with a few dozen of these around the house....

It's been a while since I blogged. I've been busier than you can imagine.
Anyways, I finally got the opportunity to start playing with MySQL 5.1 beta and installed 5.1.12-beta on one of our beta database srevers to startt testing functionality. I'm really excited abiut this version, particularly Partitioning with InnoDB. 5.1.11-beta didn;t work at all. I'm about to put it through its tests and really start testing the performance of this new version. Stay tuned!