A quiz, in 4 parts:Given the following table definition in the sakila database:

Today I've been checking out a new client environment. My mission is to figure out (cold) some of the characteristics of the queries being run, and particularly if they're "good" or "bad". In my arsenal of "tools I really want to check out" has been Ma'atkit's Query Profiler.They're very different tools. Ma'atkit's query profiler profiles a batch of queries, without granularity (at least not the way I ran it) to see what query is doing what. So I ran this against a production machine:(I ran the query profiler for a while and then hit "enter" (apparently after about 17 minutes))> ./mk-query-profiler --external --host localhost --user <user> --password <password> Press <enter> when the external program is finished +----------------------------------------------------------+ | 1 (1028.2091 sec) |  []

I had so much to say in response to a recent post asking about virtualization from Jennifer Glore that I realized it was long enough to be a blog post.It really depends on what you're looking to do. Many companies don't have the money and staff to have an in-house data center with proper power and network redundancy; others don't want the depreciati

The maximum length of a "VARCHAR" is only restricted by the maximum row length. In most storage engines, the maximum row length is the maximum allowed by MySQL, which is 65,535 bytes. Only the NDB storage engine has a different maximum value.The "VARCHAR" data type has some overhead. If the length of "VARCHAR" is less than 255, one byte per row is used to store the actual length of the string. If the length of "VARCHAR" is greater than 255, the

At the 2008 MySQL Conference and Expo, The Pythian Group gave away "EXPLAIN" cheatsheets. They were very nice, printed in full color and laminated to ensure you can spill your coffee* on it and it will survive.For those not at the conference, or those that want to make more, the file is downloadable as a 136Kb PDF at explain-diagram.pdf* or tea, for

Hi all,I was asked this question in an e-mail -- feel free to ask your questions in the comments, as I will point the original author to this post to answer those questions. There is not a lot of data here, so instead of me asking questions in an e-mail I figured I would open it up to the (MySQL) world. Without further ado, here's the question:Basically when we open more than one connection- we're opening 5, and we do processing in mysql (innodb tables) the server cpu and m

I was asked this question recently, and I thought it was a great little tidbit of knowledge to pass along. The short answer is "no". The slightly longer answer was written up by Jan Kneschke when dealing with a forum post about proxy + connection pooling.From http://forums.mysql.com/read.php?146,169265,169700The clear-text password is _never_ transfered in the authentication phase.On the network we have:

The answers to the last pop quiz are up: http://www.pythian.com/blogs/868/pop-quiz-mysql-clusterSo here's another pop quiz. Given the following:Welcome to the MySQL monitor. Commands end with ; or \\g.Your MySQL connection id is 16450949 to server version: 4.1.14-standard-logType 'help;' or '\\h' for help. Type '\\c' to clear the buffer.mysql> select count(*),length(password) from mysql.user group by l

While doing a standard audit for a new client, I recommended a few changes to get better performance. Because I had several changes, I used the documentation at http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.htmland found that "innodb_flush_log_at_trx_commit" is a dynamic variable. I was surprised, because most operations dealing with file sizes and operations are not dynamic.

A broken VIEW was caused by the view's definer being non-existent. In this particular system, backups are done by replicating all the machines (production, development, test, etc) to one server and doing cold physical backups off that server, which currently has 12 instances running.So in order to find on what machine the user might still be defined, I went to the backup server. All the data directories are in one path, ie:instance 1 has a datadir of /data/mysql/instance1instance 2 has a datadir of /data/mysql/instance2Now, the unix tool "strings" can be used against many types of files. In particular, though, you can use "strings" on the mysql/user.MYD file to see the username, host, and password hash.

I was writing up some notes about a client's status variables, and I'd had to remind myself of Handler_read_first. Handler_read_first is a counter that increments when the first entry in the index is read, which may indicate that a full index scan is being used. With this particular client, I ran a SHOW STATUS, waited 100 seconds, and ran it again. Handler_read_first jumped by about 53. So I wrote down in my notes:first in the index...every other second. Lots of table scans?As s

Today's trivial MySQL system variable:old_alter_tableThe interesting bit is that this is a system variable, and shows up in SHOW GLOBAL VARIABLES, but is not documented on the Server System Variables manual page.Instead, it is documented on the manual page for Server options.Unfortunately, that documentation is v

A short time ago I posted how I was Using XtraDB Backup to backup InnoDB. Overall, the blog post was positive, but experiences that others have had (and commented to on that blog post) have made me want to put out another short article about using XtraDB backup.The first few points remain the same -- the backup process is stable, we were able to use the binaries without compiling, and using Innobackupex as the wrapper script, analogous to Innobackup.pl. However, we did figure out why Xtrabackup had to be run as the mysql user:Xtrabackup writes to the data dictionary file (ibdata1, for example). We have not examined if it also writes to the data and index files (still ibdata1 by default, or the .ibd files when using innodb_file_per_table). [EDIT: The authors of Xtrabackup have commented below as to why the write occurs:xtrabackup is kind of very small and restricted instance of InnoDB, and reuses a lot of InnoDB code.InnoDB by  []

At the November MySQL User Group, Patrick Galbraith ran into a problem where "binlog-do-db" was duplicated. It manifests itself like this:(copied from http://lists.mysql.com/replication/607) master my.cnf:binlog-do-db=db1slave my.cnf:replicate-do-db=db1;Relevant show slave status outputReplicate_Do_DB: db1;,db1;When db1 is modified on master, Read_Master_Log_Pos and Relay_Log_Pos dochanges, also

A friend asked for this, so I thought it'd be helpful:All tables with no primary key:use INFORMATION_SCHEMA;select CONCAT(t.table_name,".",t.table_schema) as tbl,from TABLES AS t LEFT JOIN KEY_COLUMN_USAGE AS c ON (t.TABLE_NAME=c.TABLE_NAME AND c.CONSTRAINT_SCHEMA=t.TABLE_SCHEMA AND constraint_name='PRIMARY') WHERE t.table_schema!="information_schema" AND constraint_name IS NULL;All tables and their primary keys, if exist:use INFORMATION_SCHEMA

In Spotting the Wolf in Sheep's Clothing, Frank Mash writes about a specific person who is spreading fear, uncertainty and doubt about MySQL.Now, this always gets me, especially with MySQL. For how long will MySQL be the bastard stepchild of the database world? Because really, it's been a full-fledged DBMS for at least 5 years. 10 years ago there were no transactions, but....that was 10 years ago! 10 years ago everyone made $100,000 per year, took 3 hour lunch breaks, played foosball in the office, coveting their IT stock and sat in massage chairs with Apple Cinema displays.How much have *you* changed in 10 years? Your digital world? 10 years ago we paid for text messages by the character, which is why we came up with rlly abbr wrds like kthxbye! and OMG, LOL, ROFL and my favorite, ROFLPMP -- because it makes me giggle whenever I meet a professional Project Manager who has PMP as a suffix.Whoops, mini-rant aside....don't hate  []

MySQL's manual page for InnoDB's adaptive hash states: If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes. InnoDB has a mechanism that monitors index searches made to the indexes defined for a table. If InnoDB notices that queries could benefit building a hash index, it does so automatically.Note that the hash index is always built base

A friend of mine asked me:I'm hoping you can help me out with something -- I'm trying to optimize a search feature. Since it uses a MySQL database, the search already uses the "LIKE" statement to get matches for a search query, we might be needing something more flexible. I found mention on MySQL's website about something called the "SOUNDS LIKE" expression that can be more flexible than "LIKE". Do you know anything about this? If you do, can you point me a direction where I might be able to learn more about it? Thanks in advance for your help!My response:

The listing of Dynamic System Variables on the MySQL Reference Manual's page is supposed to show those variables you can change on-the-fly."innodb_data_home_dir" is listed there as a dynamic variable, but it is not one, and trying to change it on-the-fly doesn't work: mysql> set global innodb_data_home_dir="/data/mysql/data";ERROR 1193 (HY000): Unknown system variable 'innodb_data_home_

By "Good SQL Querying", I am not referring to "how to make your queries more perfomant." I am about to go on a mini-rant about how to make readable and self-documenting SQL queries.One practice that will get me instantly going on a rant is using a comma join. There is NO reason to do the following:-- uses the sakila sample databaseSELECT first_name, last_name, addressFROM customer,address;What kind of join did the original author intend? A CROSS JOIN? Or did they really want an INNER JOIN and forget the WHERE clause?The answer: you do not know for sure; you can only guess. Had the query beenSELECT first_name,last_name,addressFROM customer INNER JOIN address;you would know that the author intended an INNER JOIN; had the query beenSELECT first_name,last_name,addressFROM customer CROSS JOIN address;you would know that the author intended a CROSS JOIN. I advocate using INNER JOIN instead of JOIN because like  []