Category Archives: Mysql

MySQL – Case insensitive ORDER BY

March 3, 2010 1:37 pm /

MySQL by default will make the ORDER BY option in queries case sensitive. This means that rows with the same starting letters (but different case) may be ordered non-alphabetically. There are three solutions to this. One is to force all database entries to be forced to upper-case or lower-case when inserting. Another is to use the collate option. The other solution is to change the query itself.

Option 2 (Thanks to eremit)
mysql_query(“SELECT * FROM names ORDER BY name COLLATE ‘latin1_general_ci’);

See: MySQL Reference – Case Sensitivity in String Searches and MySQL Reference – Character Set Support

Option 3:

The MySQL query for case insensitive ORDER BY is to add the LOWER() to your field name. For instance:

mysql_query(“SELECT * FROM names ORDER BY lastname”);

will be: mysql_query(“SELECT * FROM names ORDER BY LOWER(lastname)“);

Posted in: MySQL / Tagged:

MySQL Tutorial: Fetch last 10 rows without reversing order

January 5, 2010 4:57 pm /

Here’s the quickest way to sort data in MySQL after retrieving the last 10 entries with the LIMIT and ORDER BY DESC commands. If you simply do a ORDER BY DESC LIMIT 10, this will return the last 10 results in reverse order (newest first). Adding an inline SELECT command will sort the results after retrieving the last 10 items. (oldest first)

SQL Command

$sql = "SELECT * FROM (SELECT * FROM [TABLE] WHERE ORDER BY modified_date DESC LIMIT 10) AS tbl ORDER BY tbl.modified_date"

This will retrieve the last 10 rows while sorting by date chronologically. I used this SQL command for the graphing API I was using (LibChart). This allowed my graph to display the most current data on the right side as opposed to the left.

Posted in: MySQL / Tagged: , , ,

MySQL: Avoiding the Mysql:Too many connections error

July 17, 2009 2:37 pm /

 If your site experiences the infamous “Mysql : Too many connections” error, you will need to tune your MySQL server to handle the load of a heavily used website. 

 There are some configuration variables that you can adjust to make your site handle more connections.

  • An easy way to identify which variables need to be changed can be done through phpMyAdmin. Login to your phpMyAdmin control panel and search for the “Show MySQL runtime information” module. Scroll down the page and look for entries that are in red. These are suggested areas in your MySQL configuration that need to be looked at.

  • The wait_timeout default in MySQL is 28840 seconds (8 hours), which can seriously limit the number of open connections you have on your page. I adjusted mine to 60 seconds. This frees up the thread after idle timeout, which allows for more connections to connect. The 8 hour timeframe is fine if you only have a few connections to the server. For popular websites, its best to make this value as low as possible. Set this variable to the maximum time your page typically runs. For instance, if you have a page that needs to retrieve some data processed from another server (60-80 seconds) and you only use one mysql_connect, you may lose the mysql connection for processing the remaining parts of the page if your wait_timeout is too low. If the processing takes much longer, a workaround is to use multiple mysql_connects to prevent an idle connection from disconnecting.

  • DNS issues. If your DNS suddenly stops working, this will consume all your MySQL connections. MySQL tries to resolve the IP address for every connection it gets, so this will start creating many more threads than released if it DNS is down. In Linux, edit the /etc/hosts file and manually add in your web server IPs/names to this (if you have static IPs) This will speed up performance by resolving IPs locally as opposed to through DNS. You can identify DNS issues or connection attempts through “Failed attempts” section in phpMyAdmin runtime page. A significant number of failed attempts can mean either someone is trying to break into your MySQL box or your DNS isn’t working right.

  • Increase the key_buffer value (for MyISAM tables) This helps handle indexes for temporary tables. If you have lots of key misses, its important to increase the size of the key_buffer. You can do this by comparing the key_reads vs. key_read_requests value.

  • Increase the innodb_buffer_pool_size (for innodb tables)

  • Increase the table_cache value. If you look at your phpMyAdmin runtime “Opened_tables” field and see a large number (over 1,000) you will need to increase your table_cache. This will keep tables in memory to speed up peformance. Default is only 64. I’ve upped mine to over 2,000 since I have many databases and tables running on my server.

  • For more information, check out these resources:
    http://www.ibm.com/developerworks/linux/library/l-tune-lamp-3.html 
    http://dev.mysql.com/tech-resources/presentations/presentation-oscon2000-20000719/index.html

 

Posted in: MySQL / Tagged: , , ,

Quick tuning MySQL performance

June 11, 2009 8:32 pm /

I’ve been working on tuning my Apache/MySQL/PHP configuration lately to see how to improve the performance. Lately, I’ve been noticing some crawlers (same IPs all the time) that have been ignoring my robots.txt file and hitting my server really hard with several page requests every second.

So, I decided to run some tests to see where I could speed up my website in the code.

One test was significant. I commented one update statement in my code and that sped up my page 3 times. Page display time went down from 0.15 second to 0.045 second! It seems as though my VMWARE server has slow write access times in comparison to reads, so this is a source of the cause. I decided to use this to my advantage.  So, basically create an array with the blacklisted IPs (but you can’t block them entirely since I need certain crawlers to index websites/etc. for searches)

$is_crawler = 0;
$crawler_array = array("[CRAWLER IP]","[CRAWLER IP2]");
foreach($crawler_array as $crawler_item)
{
if ($crawler_item == $current_ip)
{
$is_crawler = 1;
break;
}
}

In using this, only run the update query if the source IP isn’t a crawler… so something like this:

if ($is_crawler==0)
{
// Run MySQL Update command
} 

Other ways to speed up your MySQL performance.

  • Only use ORDER BY when necessary. Ordering is costly with large datasets
  • Joins are costly as well. Design a database table cache that has the union set if performance is a concern
  • Memcache is a viable option as well for pages that use many tables to generate the page.
Posted in: MySQL, PHP / Tagged: , , ,

WordPress MU 2.7 Review

March 17, 2009 7:42 pm /

Wordpress Mu Control Panel

Wordpress MU Control Panel

WordPress introduced a new version of its multi-user blog software in January 2009. I have been using version 2.6 for the past few months and it was working well, although the site management was very confusing to use. Version 2.7 fixes this problem by making the Site Admin menu more uniform with the main dashboard.

The Site Admin is now located on the left hand menu, which is much easier to access than 2.6. (previously on the right side of the control panel. There are six options: Admin, Blogs, Users, Themes, Options, and Upgrade. Pretty straight-forward.

Admin: Create new blogs
Blogs: View each of the blogs, its users, links to edit/de-activate
Users: Manage what each of the users can see/edit/role
Themes: Themes available for blogs.
Site Options: Security/Global settings for WordPress
Upgrade: This is used to upgrade the WordPress version on the individual blogs. You still need to manually copy the latest version of WordPress MU into the folder when a new version comes out. In contrast, the standalone WordPress has a button that will automatically fetch the source and install the latest version for you.

What I like about the new version:
A lot of the things I like about WordPress 2.7, I like it on WordPress MU. Major Upgrades with 2.7 vs. 2.6 include: QuickPress publishing features for quick posting, 1 click upgrade, and all new navigation system on the dashboard.

Site Themes Control Panel

Site Themes Control Panel

 

Quick Post Feature

Quick Post Feature

There is also redundant menu options on the top menu in WordPress MU. This allows you to quickly switch between different blogs. So, switching between blogs is a one click process vs. a multiple click previously in 2.6. 

Top Toolbar for WordPress MU

Top Toolbar for WordPress MU

Verdict:
WordPress MU 2.7 is definitely worth considering with the new dashboard layout. The main advantage of having MU is that you have the ability to control the settings of all blogs. You can control what themes can be used, ban certain email addresses from registering, control permissions, and set the default welcome email. There is a built-in quota manager for each blog, so you can limit the disk space usage of all blogs. You can also limit the types of files that a user can upload on a blog — such as limiting attachments to PDF, Word Documents, or images. Another advantage of the MU is that you only need to upgrade the blog software in 1 place for several blogs. User management is good, but there still needs to be a way to add a user to multiple blogs in one screen. 

Overall, WordPress MU 2.7 is great resource if you need to support several blogs (blog network) and are a fan of the standalone WordPress software.
WordPress MU Support Forums
http://mu.wordpress.org/forums/

WordPress MU Download
http://mu.wordpress.org/download/

Posted in: MySQL, PHP / Tagged: ,