Our sysadmin has a nice blog post with a few tips for using mysqldump, especially if your database is used for more than a basic site, or if you have stored procedures and/or triggers.
mysql
- A while ago when I started my first job in LA, I was using MSSQL. I was told to never use count(), and rather to use count(1). Several people insisted this performs better than count(), and since I really didn’t care to argue or look into it, I just started doing it. Fast forward to today, where I am still writing count(1), but on MySQL. I’m at a different company now, but working with a lot of the same people.
- REPLACE INTO will actually perform a delete and then an insert, while INSERT … ON DUPLCIATE KEY UPDATE will perform an update (as the name suggests). I would think the latter would be faster. I have not done any performance testing between the two, but it only seems logical the update would be faster than the delete/insert. Please correct me if I’m wrong. Since the two statements both end up with the same result, I’m not sure yet what the benefit of REPLACE into it.
- For a long time, whenever I wanted to do paging to browse through a table, I used to run 2 queries. The first would get the results, and the second would be an almost identical query, with a count() instead of fields, and I’d use the result of the second query to figure out how many pages I would need. Now there’s a better solution, and it’s called found_rows(). Say you have a simple select statement:
- I do not think you can use the result of a stored procedure in an ad-hoc subquery. On my social network, LetsGetNuts.com, I have a Friend table. This is the structure: mysql> describe Friend; +—————-+—————+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +—————-+—————+——+—–+———+——-+ | fkUser | int(11) | YES | MUL | NULL | | | fkFriend | int(11) | YES | MUL | NULL | | | confirmed | enum(‘Y’,‘N’) | YES | MUL | NULL | | | confirmed_date | date | YES | | NULL | | +—————-+—————+——+—–+———+——-+ 4 rows in set (0.
- Maybe not breaking news, but I think it’s interesting enough of a point, and I didn’t really find anything about the topic when I googled it. If you do any addition, subtraction, multiplication, or division (and probably a lot more mathematical functions for that matter) and NULL is one of your values, the entire expression will evaluate to NULL. For example, this statement returns NULL: mysql> select 4 + NULL; +----------+ | 4 + NULL | +----------+ | NULL | +----------+ Normally you wouldn’t do the above in such a simple way, for instance, you might do some addition in a subquery.
- If you’ve worked with any of the commerical databases other than MySQL, you may have already had some experience with triggers. I’m glad that the MySQL developers finally managed to squeeze in this extremely important feature. I no longer dread trying to defend MySQL from the Microsoft and Oracle developers who have had triggers for so many years. I’ll use the example off the MySQL website (explained below in detail). I’ll assume you know how to create a table.