Query Optimization

At the 2008 MySQL Conference and Expo, Ronald Bradford delivered "The Top 20 Design Tips for Enterprise Data Architects". See the slides on the Forge at http://forge.mysql.com/wiki/MySQLConf2008ThursdayNotes#Top_20_DB_Design_Tips_Every_Architect_Needs_to_Know

At the 2008 MySQL Conference and Expo, there was a panel discussion on "Addressing Challenges of Data Warehousing - a Panel Discussion" including: Robin Schumacher (Sun/MySQL) (moderator) Brian Miezejewski (MySQL), Charles Hooper (Pro Relational Systems), Paul Whittington (NitroSecurity, Inc.), Raj Cherabuddi (Kickfire), Victoria Eastwood (InfoBright Inc.)

At the 2008 MySQL User Conference and Expo, Farhan Mashraqi spoke about "Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community". Download the slides, see people's notes, and more on the MySQL Forge Wiki at http://forge.mysql.com/wiki/MySQLConf2008ThursdayNotes#Optimizing_MySQL_and_InnoDB_on_Solaris_10_for_World.27s_Largest_Photo_Blogging_Community

At the 2008 MySQL User Conference and Expo, Baron Schwartz spoke on "The MySQL Query Cache". Download the slides, see people's notes, and more on the MySQL Forge Wiki at http://forge.mysql.com/wiki/MySQLConf2008WednesdayNotes#The_MySQL_Query_Cache

I have finally managed to watch and slightly edit the September 2007 Boston MySQL User Group presentation I did on the MySQL Proxy. It's geared towards beginners, and has lots of examples, including explaining some of the examples that come bundled with the MySQL Proxy. Direct Play Download video (.wmv file, 612 Mb) Download video (.wmv file, 76.10 Mb) Enjoy! Some resources: Presentation Slides PowerPoint (ppt) or PDF or Flash (swf)
Official Documentation for MySQL Proxy
Proxy information on the MySQL Forge Wiki, including getting started guide, link to FAQ, forums, and lots more articles.
MySQL Proxy Download
Articles about MySQL Proxy written by Giuseppe Maxia Giuseppe Maxia will be leading Introducing Lua for MySQL Proxy scripting for the [free] MySQL University on Thursday, December 13th at 16:00 CET (15:00 URC, 10:00 EDT, 07:00 PST). Make sure to sign up and read Instructions for Attendees. (From his blog, "MySQL University is a series of online expert lessons that you can join for free and attend from the comfort of your home or office. The slides are provided in either PDF of wiki pages, the audio is an ogg stream, and you can interact with the lecturer via IRC. If you have heard of MySQL Proxy but haven't got the time of getting involved with it yet, this session is for you. If you were interested but you thought that another scripting langiage would be too difficult, give this session a chance.") Note: the wishlist at the end includes startup scripts and integraton with my.cnf, my.cnf, which I am informed will be possible with the chassis currently under development for version 0.70.

I started this as a response to Keith Murphy’s post at http://www.paragon-cs.com/wordpress/?p=54, but it got long, so it deserves its own post. The basic context is figuring out how not to cause duplicate information if a large INSERT statement fails before finishing.
Firstly, the surefire way to make sure there are no duplicates if you have a unique (or primary) key is to use INSERT IGNORE INTO.
Secondly, I just experimented with adding an index to an InnoDB table that had 1 million rows, and here’s what I got (please note, this is one experience only, the plural of “anecdote” is *not* “data”; also I did this in this particular order, so there may have been caching taking place):

So, O’Reilly’s ONLamp.com has published the “Top 10 MySQL Best Practices” at http://www.onlamp.com/pub/a/onlamp/2002/07/11/MySQLtips.html. Sadly, I find most “best practice” list do not thoroughly explain the “why” enough so that people can make their own decisions.
For instance, #3 is “Protect the MySQL installation directory from access by other users.” I was intrigued at what they would consider the “installation” directory. By reading the tip, they actually mean the data directory. They say nothing of the log directory, nor that innodb data files may be in different places than the standard myisam data directories.

aka…..”when good queries go bad!”
So, today the developers were debugging why a script was running much longer than expected. They were doing text database inserts, and got to the point where they realized that double the amount of text meant the queries took double the amount of time.
You see, they were doing similar text inserts over and over, instead of using connection pooling and/or batching them. Apparently the other DBA explained that it was a limitation of MySQL, but either the developers didn’t convey what they were doing well, or the DBA didn’t think to mention batching.