The latest from the MySQL community

When we use a relational database system, we should know some important things. Numeric data type is very essential for a table. Here I describe some important points of Numeric Data type in MySQL:

MySQL has 9 numeric data types

• Integer:            TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
• Floating Point:  FLOAT, DOUBLE
• Fixed Point:      DECIMAL
• Other:              BIT, (ENUM maybe)‫‏‬

Some poor design:

• INT(1)‫‏‬
• BIGINT AUTO_INCREMENT
• no UNSIGNED used
• DECIMAL(31,0)‫‏‬

INT(1) – 1 does not mean 1 digit.   (1) represents client output display format only. INT is 4 Bytes. TINYINT is 1 Byte …

When dealing with replicated setups with two or more slaves sharing a master, it appears as if a lot of people overlook the obvious. You don't need to take your master down to resync a slave. I was hoping I wouldn't need to post about this, but I see people taking down their masters when they have perfectly healthy slaves way too often to let it slip.

So, how does one gather statistics on indexes? With InnoDB it’s one thing, with MyISAM it’s another thing. This post however, will focus on MyISAM. There are various ways, each having their own drawbacks and positive sides.

What about ANALYZE TABLE? It’s great — very fast in InnoDB. But with MyISAM a lock occurs while scanning the table.

OPTIMIZE TABLE? Still relatively quick with MyISAM, unless the table in question is huge. InnoDB requires an ALTER TABLE — it might take forever, depending on the server configuration, as OPTIMIZE TABLE for InnoDB maps to a ALTER TABLE tableName ENGINE=InnoDB

We all know how ANALYZE TABLE, REPAIR TABLE, and OPTIMIZE TABLE work with MyISAM. However, there’s a less explored way, for MyISAM — the myisam_stats_method.

…

My copy of High Performance MySQL arrived this week! It's also Independance Day here in the States, so I have a long weekend after all the fireworks to get through my stack of reading, plus get some hill rides in to prepare for a 200K brevet July 19th. Oh, tomorrow also starts the Tour de France! What a weekend!!

MySQL DBA & Programming Blog by Mark Schoonover …

My copy of High Performance MySQL arrived this week! It's also Independance Day here in the States, so I have a long weekend after all the fireworks to get through my stack of reading, plus get some hill rides in to prepare for a 200K brevet July 19th. Oh, tomorrow also starts the Tour de France! What a weekend!!

MySQL DBA & Programming Blog by Mark Schoonover …

Andrew Clarke has published to 104th edition of Log Buffer, the weekly review of database blogs, on Radio Free Tooting, marking LB’s second year. Happy Birthday, LB!

Log Buffer always needs editors, so if you you’d like to present your view of the week that was in DB blogs, contact me, the Log Buffer coordinator. You’ll be joining some of the best bloggers around, and making yourself and your blog a little better known to readers around the world.

And now, here’s Andrew Clarke’s …

MySQL Proxy is very flexible and can be used for many different use-cases

• Load Balancing
• Failover management
• synchonized Replication
• Caching
• Query Filtering, Rewriting, ...

What shall we implement first, where should our priority ?

Cast you vote at http://dev.mysql.com/tech-resources/quickpolls/

MySQLs multiple-queries feature makes it possible to run two or more SQL commands with one function call: SQL injections made easy. The PDO design is said to be optimized for security. For security reasons PDO has gone the stony road of prepared statements. If security matters, why does PDO_MYSQL explicitly enable multiple-queries? If portability matters, why do you use a MySQL specific feature?

Mighty multiple-queries feature: performance

Multi-statement is yet another example of how optimization works: trade in one feature (security) for another (performance). Sending several SQL command with one function call saves communication round-trips. You need only one round-trip for executing n > 1 SQL …

MySQLs multiple-queries feature makes it possible to run two or more SQL commands with one function call: SQL injections made easy. The PDO design is said to be optimized for security. For security reasons PDO has gone the stony road of prepared statements. If security matters, why does PDO_MYSQL explicitly enable multiple-queries? If portability matters, why do you use a MySQL specific feature?

Mighty multiple-queries feature: performance

Multi-statement is yet another example of how optimization works: trade in one feature (security) for another (performance). Sending several SQL command with one function call saves communication round-trips. You need only one round-trip for executing n > 1 SQL …