I was coding out a basic contact form today with the usual name, address, email etc. I was building out the country select menu and noticed I already had code for building it via an array and building it from a database table. So I decided to put the two up against each other and see which one was faster.
If you have a database driven site, like this one, the database will slowly increase in size and the tables will get bigger as your site content and traffic increases. Over time some areas that were fast to load in the browser start to slow down. Why is that? Well one possibility is that the database queries that generate the content on your page are not optimized. As your database keeps growing, these queries get slower and slower until the load time of a page is actually noticeable to one of your visitors. The first step is identifying slow queries.
I read an interesting article about storing IP addresses as ints instead of a string using MySQL’s built-in conversion functions.
Lately I’ve been reading a lot about MySQL optimization and fine tuning. The other day I read a good tip about the size of your primary key. It will definitely save you space as your tables get bigger.
At work, we ran into some MySQL max connections issues that were bringing down our entire site. One highly used table was corrupted which was making all the queries lock, idle or sleep. This would lead to max connections being hit and our whole site going down. We asked our provider to look into it and they turned us on a really cool MySQL optimizer script.