This question often comes up, and the general answer given appears to be "CREATE TABLE ... SELECT ..."
But actually, that does not do what you might expect, as this statement creates a table structure based on the resultset of the select, so the column types may differ from your original table, and the table will not have indexes either.
The syntax does allow you to add and override pretty much everything, but since we were talking about copying, let's look at another way:

CREATE TABLE bar LIKE foo;
INSERT INTO bar SELECT * FROM foo;

This produces an exact copy of the original table, both structure and data, indexes and everything.
No, you can't combine these two into a single statement. Sorry ;-)

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

This wonderful quiz from Vladimir Kolesnikov is one of those that should make you stop and think for a moment…

Given this table and data:

mysql> SELECT * FROM t;
+------+------+
| i1   | i2   |
+------+------+
|    1 |    2 |
|    2 |    1 |
|    4 |    3 |
|    3 |    4 |
+------+------+
4 rows in set (0.00 sec)

What is the result of the following three statements?

SELECT * FROM t ORDER BY 1

SELECT * FROM t ORDER BY 2

SELECT * FROM t ORDER BY 1+1

(more…)

Well these days we see a lot of post for and against (more, more) using of MySQL and DRBD as a high availability practice.

I personally think DRBD has its place but there are far more cases when other techniques would work much better for variety of reasons.
First let me start with Florian's comments on the issue as I think they are most interested ones.

First lets get to the point what we're comparing here - it is mainly DRBD to MySQL Replication based techniques (lets …

(If you want $100, you will have to read the entire blog post. Sorry for the tease, but I did not want folks to miss out on the opportunity to win!)

By now it is no surprise that I won one of the three 2008 MySQL Community Member of the Year awards. And folks may know that I won the same award last year.

One interesting fact you may not know: during the 2006 MySQL Awards Ceremony, where Giuseppe Maxia, Roland Bouman, Markus Popp and …

In my post on estimating query completion time, I wrote about how I measured the performance on a join between a few tables in a typical star schema data warehousing scenario.

In short, a query that could take several days to run with one join order takes an hour with another, and the optimizer chose the poorer of the two join orders. Why is one join order so much slower than the other, and why did the optimizer not choose the faster one? That's what this post is about.

Let's start with the MySQL query optimizer. The optimizer tries to choose the best join order based on its cost metric; it tries to estimate the cost for a query, then choose the query plan that has the lowest cost. The unit of cost for the MySQL query optimizer is a single random 4k data page read. In general, it's a pretty …

I have already blogged about this keynote at http://www.pythian.com/blogs/948/liveblogging-who-is-the-dick-on-my-site.

If you are interested in actually seeing the video, the 286 Mb .wmv file can be downloaded at http://technocation.org/videos/original/mysqlconf2008/2008_04_17_panelDick.wmv and played through your browser by clicking the “play” link at http://tinyurl.com/55c5ps. This is not to be missed!

I have already blogged about this keynote at http://www.pythian.com/blogs/948/liveblogging-who-is-the-dick-on-my-site.

If you are interested in actually seeing the video, the 286 Mb .wmv file can be downloaded at http://technocation.org/videos/original/mysqlconf2008/2008_04_17_panelDick.wmv and played through your browser by clicking the "play" link here. This is not to be missed!

Discussion on community builds, patches, adding features, etc using GPL version of MySQL source code. It's also a public group, and if you're interested in hacking MySQL source code, please join.


oursql-sources

It's been mentioned before, but I'll mention it again. There's been talk of a community conference, not to compete with but augmenting the Sun/MySQL one. We're here to discuss such an event, its potential, dates/location, and get it going! It's a public group, please blog & tell others about it! Sheeri suggested OurSQL, like her podcast. Created and managed by Arjen Lentz

oursql-conferenceMySQL DBA & Programming Blog by Mark Schoonover

Discussion on community builds, patches, adding features, etc using GPL version of MySQL source code. It's also a public group, and if you're interested in hacking MySQL source code, please join.


oursql-sources

It's been mentioned before, but I'll mention it again. There's been talk of a community conference, not to compete with but augmenting the Sun/MySQL one. We're here to discuss such an event, its potential, dates/location, and get it going! It's a public group, please blog & tell others about it! Sheeri suggested OurSQL, like her podcast. Created and managed by Arjen Lentz

oursql-conferenceMySQL DBA & Programming Blog by Mark Schoonover

The weekly run-down of MFotD

(See this post for background information on MySQL Function of the Day)

Week Seven:

• Monday, April 21st, 2008: ROUND()
• Tuesday, April 22nd, 2008: TRUNCATE
• Wednesday, April 23rd, 2008: LOG()

(more…)