Grab N rows

mysqldump has a nice feature '-w' that I used today. Whilst working with a customer to migrate their 200G database from 4.1 to 5.1, we decided to grab a subset of the database to do initial tests with.
Using this:

mysqldump -w 'true LIMIT 1000'

I created a dump of 1000 rows of each table.

From:

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
--where='where_condition', -w 'where_condition'

Dump only rows selected by the given WHERE condition. Quotes around the condition are mandatory if it contains spaces or other characters that are special to your command interpreter.

procedure analyse has its limits

Today whilst attempting to use procedure analyse against a customers tables I discovered that it doesn't work whilst using LIMIT in the SELECT query.
SELECT * FROM tablename LIMIT 10000 PROCEDURE ANALYSE()\G

The work around is to create a temporary table.

CREATE TEMPORART TABLE tmp_tablename from SELECT * FROM tablename LIMIT 10000;
SELECT * FROM tmp_tablename PROCEDURE ANALYSE()\G
DROP TABLE tmp_tablename;

MySQL replication filtering

This from a question on #mysql, how can you filter whats replicated between master and slave.
Now i was aware of the Database and Table level filtering such as using
--replicate-do-db=db_name
--replicate-do-table=table_name
--replicate-ignore-db=db_name
--replicate-ignore-table=table_name

etc

What i didn't know about was this:


http://dev.mysql.com/doc/refman/5.0/en/set-sql-log-bin.html
SET SQL_LOG_BIN = {0|1}

Disables or enables binary logging for the current connection (SQL_LOG_BIN is a session variable) if the client has the SUPER privilege. The statement is refused with an error if the client does not have that privilege.

Thats kind of kewl.

Blog Title

After seeing the discussions in http://groups.google.com/group/oursql-conference i thought about the idea of combining a user conference with a music festival.

Optimizing table join order

Something i should have known (and didn't):

The join optimizer calculates the order in which tables should be joined. The table read order forced by LEFT JOIN or STRAIGHT_JOIN helps the join optimizer do its work much more quickly, because there are fewer table permutations to check.

http://dev.mysql.com/doc/refman/5.0/en/left-join-optimization.html