tips for MySQL newbies

Are you a brand-newbie to MySQL? Coming from another database (or databases)? If so, here are some concepts to read up on right away to ease your confusion down the road. These are the top things I wish someone had told me about before I did anything else with MySQL.

(I’ve had a lot of questions along these lines, and have posted on all of each of these items somewhere, but thought it’d be more helpful to gather the points together into one place.)

MY TOP 2 “BEFORE YOU BEGIN” TOPICS

#1: storage engines. Before you do any big planning, if you don’t know what a “storage engine” is, find out!
I can’t think of a good analogy for what a storage engine is. The best I can come up with is, you know how with Oracle you choose your index types (btree? bitmap? etc)? In MySQL, you choose your table types (and with them, the index types). Your choice determines a lot. Each storage engine (table type) has various features and limitations. You need to know them. For instance, do you want ACID-compliant transactions or foreign-key constraints? You’ll most likely be using the InnoDB storage engine then. In which case, clustering is not supported – that would require the NDB storage engine (which, by the way, is an in-memory storage engine – ie, all your tables must fit into memory).
You can read more in my post on the subject, which includes further links for even more information.

#2: scale-out vs scale-up: plan your strategy (it’ll affect your schema and a lot more)
Plan your scaling strategy. Not only is it crucial to do, it might affect your schema design and much more! (Eg, if you “scale out”, you’ll need to plan your schema accordingly.)
You can read more in my post on that subject, which includes further linds for even more information.

Before you roll anything into production, you’ll need to read up on these topics (among others):

TWO VERY IMPORTANT THINGS THAT ARE DIFFERENT THAN YOU’RE USED TO

#1 backups
– read the documentation (on mysql.com) on mysqldump. It’s something like export, but you can use it with archived binary logs for point-in-time recovery, too.
– or use LVM if you have linux, to take snapshots
– or buy add-ons like MySQLHotCopy (?)
– or all of the above!
more notes with further links in this post.

#2 permissions
there are no roles; permissions are based on a userid and (if desired) the host or hosts they connect from. It’s a bit laborious, and you’ll want to read up on it and play with it. (I got snagged on it a few times; see this for an example.)

AND 5 OTHER THINGS I THINK YOU SHOULD KNOW ABOUT RIGHT OFF THE BAT

#1 collations – by default, if you compare strings, it’s not case sensitive (‘a’=’A’). Read up on collations and character sets.
#2 autocommit is turned on by default. If you want to change this, read up on init_connect. Or see this post for more.
#3 the optimizer is not as developed/fine-tuned/complex be sure to test and QA and “analyze” your statements as much as possible. Use the slow query log to find longer-running queries and analyze them. Use “force index” in your select statements where needed (hopefully you know your data better than the optimizer).
#4: SQL_MODE. If you’re concerned about strict data integrity, you should read up on SQL_MODE. MySQL can insert “bad” data silently by default… (0’s for null dates, etc)
#5: isolation levels. If you’re going to use transactions (or even if you’re not), read up on isolation levels. This can affect locking, transactional integrity, etc. The default setting is not quite like Oracle’s.

where to get further info:
– download the software from mysql.com
– dev.mysql.com has the documentation, papers, etc
– planetmysql.com is a blog aggregator for MySQL-related blogs
– do lots of google’ing
– recommended books: Pro MySQL, MySQL 5.0 Certification Study Guide, MySQL Internals (and more…)

– buy MySQL support (it’s actually very good! Not at all like Oracle’s support. Best software support I’ve ever experienced by far, personally.)