How to make multiple updates using a single query in MySQL

How to make multiple updates using a single query in MySQL
April 20, 2008 - Posted by Indy


As you might know it’s quite easy to make multiple INSERTs in a single query, like this:

INSERT INTO mytable
(id, title)
VALUES
('1', 'Lord of the Rings'),
('2', 'Harry Potter');

However, for some strange reason you can’t do multiple changes to a table in a single UPDATE query like this:

UPDATE mytable
SET (title='Great Expectations' WHERE id='1'),
(title='War and Peace' WHERE id='2');

However, you can do a very interesting trick. You can combine an UPDATE with a CASE like this:

UPDATE mytable SET title = CASE
WHEN id = 1 THEN 'Great Expectations';
WHEN id = 2 THEN 'War and Peace';
ELSE title
END;

The ELSE title is very important, otherwise you will overwrite the rest of the table with NULL.

You might wonder why on earth you’d want to make multiple updates in a single query. Here’s an example that might not be so obvious:

Let’s say you want to make a page view counter for your shop, but you want to implement caching for your pages and running an SQL UPDATE query for each page is out of the question. An efficient solution would be to make a logfile with each view as a new line appended in a file.

Here’s an example of logfile, with each view logged on a single line using this format: IP | PRODUCT_ID

78.32.43.2|3
54.133.87.54|2
85.83.93.91|4

The append part is most important, since it’s the only file writing mode that can be used with multiple processes (remember that you have MANY viewers at the same time, and if only 2 of them try to write a file at the same time your file will be pretty much screwed – no, you don’t lose the file itself, you just lose the content, which is even more important).

An alternative would be to use file locking, but it’s really really inefficient when you have lots of visitors (digg effect anyone?). However, the append mode is optimized by the operating system and stores the content in a buffer, making sure the content is write properly.

So, the best solution would be to make a logfile and then process this file with a cron job. It’s very easy to do that, but the challenge lies in updating the database. Here’s where the multiple updates in a single query trick comes into play.

You can just create a long query to update the database and run it only once instead of hundreds of small queries (which in case you didn’t figure it out, would bring your database to its knees in many cases).

So we can make a script to parse our logfile like this:

$logfile = 'logfile.txt';

$views = array();
$ip_db = array();

$lines = file($logfile);

$cnt = count($lines);
for ($i=0; $i<$cnt; $i++)
{
    if (preg_match('/([0-9.]+)\|([0-9]+)/', $lines[$i], $regs))
    {
        $ip = $regs[1];
        $id = $regs[2];

        if (!isset($ip_db[$ip]))
        {
            $ip_db[$ip] = 1;
            $views[$id]++;
        }
    }
}

if (empty($views))
{
    exit;
}

$query = "UPDATE products SET views = CASE ";

$idlist = '';

reset($views);
while (list($id, $val) = each($views))
{
    $query .= " WHEN id = ".$id." THEN views + ".$val." ";
    $idlist .= $id.',';
}

$idlist = substr($idlist, 0, -1);

$query .= "
END
WHERE id IN (".$idlist.")";

// run $query
Simple and efficient. And did I mention it’s also free?

Download link: parse_logfile.php

Important update:
Just as Jesper pointed out using transactions instead of CASE can make a huge difference.

Here are the results of some tests made on a 316,878 records database using both MyISAM and InnoDB storage engine.

InnoDB

Rows updated    Transactions time
(in seconds)    CASE time
(in seconds)
400    6s    11s
1000    20s    17s
30000    (too long)    (too long)
MyISAM

Rows updated    Consequent queries time
(in seconds)    CASE time
(in seconds)
400    0s    6s
1000    0s    13s
30000    10    (too long)
As you can see the results are very interesting. You can clearly see the difference of both storage engine and transactions (at least for MyISAM). In other words, if you use MyISAM (and many people do), use transactions for multiple updates. If you use InnoDB switch to MyISAM and them use transactions.

The CASE method seems to be efficient only for few updates (so you can make a cron job to run updates more frequently), but overall you can see clearly that you can still use transactions and get better results.

I hope the method described in this post helped you or at least gave you more ideas on how to use it for any of your projects, and if you didn’t know about transactions, maybe you should give it more attention (I sure will from now on).

Second (and hopefully final) update:
After a bit of research I figured out MyISAM doesn’t support transactions (yes, silly me), so the tests above were done using simple consequent queries (modified up there as well). However, it seems MySQL does some internal optimizations and runs them very efficiently. If you have a busy database it’s a good idea to do a LOCK TABLE query before the batch update though (and of course don’t forget to UNLOCK the table when done).

Also after a few suggestions I got to a much faster version (read the comments below – Thanks, guys).

So here’s the final CASE version:

UPDATE mytable SET title = CASE
WHEN id = 1 THEN ‘Great Expectations’
WHEN id = 2 THEN ‘War and Peace’
...
END
WHERE id IN (1,2,...)

Although it doesn’t beat the speed of normal queries run in a row, it can still get close enough.

Also, since many people asked why bother that much when you can just run the normal queries, you can consider this an experiment to find alternatives. Also, keep in mind that in order to gain advantage of MySQL optimizations all those UPDATE queries need to be run in a batch. Running each UPDATE query when the page is viewed for example is not the same thing (and that’s pretty much the problem I wanted to solve as efficiently as possible).

Also, as a final note, you should always run an OPTIMIZE TABLE query after all these updates.

Huge List of the Best Drupal SEO & Social Media Modules

If you're managing one or many Drupal websites you probably use at least one or a few Drupal modules to achieve some website optimization. Search engine friendly URLs, Twitter integration, optimized page titles, and so on are just a few of the most common enhancements performed by Drupal users.

There are many beneficial modules that will improve the indexation and page ranking of your Drupal website, not to mention bring in more visitors, increase social networking, and better align user actions such as purchasing a product or filling out a membership form. Also, the better the relevance of your website content, the better search engines will generally rank your website's pages.

Last, many Drupal theme customers will ask us what modules are the best or how to add social media icons and links to their Drupal website. The list I've added below is meant to be on ongoing resource that will be updated for our Drupal theme customers and anyone else wanting to get the most out of their Drupal installation. This list is currently over 30 modules long and we've used all of the SEO or social media modules in this list in one or more Drupal projects.

Most Popular Drupal SEO Modules

1. Page Title - The page title module improves the HTML header tag which shows up in the <title> location. This title tag is used within search results as the title of the page. This is one of the single most important "front-facing" search engine improvments one can do to their Drupal website.
2. Nodewords - This SEO module allows you to add meta tags to your Drupal website. Manual and automatic-based settings are available.
3. Nodewords Page Title - This modules provides more customization of nodewords custom pages so that you can better optimize the page title using wildcard paths and global tokens. This SEO module is perfect for "tough-to-reach" page titles in Views, Panels, and other non-node paths.
4. Related Content - The related content module lets site owners easily select on a per-node basis what pages should be displayed along side the page you're on. This helps users select related content and assign it to other related pages. This module is configurable and themeable. The module also supports a powerful API for advanced Drupal users.
5. Pathauto - Probably one of the most popular Drupal SEO modules, the Pathauto module automatically generates new path aliases (or SEO-friendly URLs) for various nodes and taxonomies. Use it for node pages, taxonomies, and user paths, amont other node types added. You can have URls like /category-name/nice-title-here.html instead of /node/262. The aliases you can setup are based upon "pattern" matches which the administrator can manage.
6. Sitemap - The sitemap module gives your Drupal website a clean site map . It can also display your RSS feeds for nodes and categories. Optionally add node counts to RSS feeds.
7. Path Redirect - The Path redirect module allows Drupal users to specify a redirect from one of their node paths (URL) to another path (URL) or to an external URL, using any HTTP redirect option. This nice Drupal redirect module can assist with easy 301 redirections, 302 (temporary redirects), and more.
8. Global Redirect -GlobalRedirect allows a user to check the current URL for an alias. After this, the module will give the ability to do a 301 redirect to or from it if it's not being used. It also performs a check on the current node URL for a trailing slash and removes the slash if present. Also checks if current URL is the same as the site_frontpage and redirects to the frontpage if there is a direct match.
9. Google Analytics Module - Pretty much self-explanatory, the Google Analytics module adds Google's advanced statistics software to your website.
10. XML Sitemap - The XML sitemap module builds a site map that conforms to the currentsitemaps.org sitemap specifications. Search engines can often more intelligently crawl a website and keep their results up to date if the website uses an XML site map. You can automatically submit to XML sitemap to Google, Bing, and Yahoo! search engines. The module also comes with several sub-modules that can add sitemap links for content, menu items, taxonomy terms, and user profiles.

Cool Drupal SEO Modules You May Not Know About!

Want more advanced SEO and tools to make sure yo're website is even more optimized? These can really improve the SEO your Drupal site.

1. Content Optimizer - This content module gives a quick SEO analysis of your Drupal site content and gives you a guide to assure SEO best practices are consistently followed.
2. Glossify Internal Links Auto SEO - This SEO module generates internal node links and on a per node base external http links (crosslinks) automatically - ideal for SEO of your site's pages and partner pages. It is currently node-based and looks for node titles in node bodies and makes them to links. There are plans to extend it to taxonomy terms, too!
3. Scribe SEO - Scribe SEO is a tool that is like a content optimization assistant. It analyzes web pages, blog posts, etc. The tool then tells the site owner how to tweak their content to get more traffic while preserving quality content for readers.
4. SEO Watcher - SEO Watcher searches specified keywords within the major search engines and then checks the rank of your site and competitive sites once a day.
5. QA Checklist - QA Checklist gives a checklist of Drupal best practices. It then provides a checklist to track tasks needing to be completed. Some include modules you may want to install or site reporting to improve crawling.
6. SEO Friend - The SEO Friend modules displays a summary of available Drupal SEO-related modules and if they have been installed and enabled.
7. Related Block - Related blocks performs a search for nodes that are closely related to the title and content of the current node. It then gives the option for placing a block for specific node types. Words in the node titles are given a slightly heavier weight than those in the content.
8. Similar by Terms - Similar to the Related Blocks module, this module provides context for content items by displaying a block with links to other similar content. Content similarity is assigned based on the taxonomy terms chosen to display for certain node pages.

Useful Drupal Social Media Modules

Here is a list of some of our favorite Drupal social media and networking modules. Increasing brand, user-retention, customer support, and Twitter/Facebook or other followers should all be part of the bigger SEO picture!

1. Gigya Social Optimization - Gigya provides a small API function that aggregates authentication and social APIs from popular sites like Facebook Connect, Twitter, and OpenID. Web providers include Google, Yahoo, and AOL.
2. Facebook Social Integration - This module assists in setting up the Like Button, Comments, and Like Box for Facebook based on http://developers.facebook.com/plugins.
3. Twitter Module - The Twitter module gives you API integration with the Twitter microblogging service. You can display tweets in a sidebar block or on a user profile page and even post to individual Twitter accounts.
4. Tweet Module - The Tweet module from Drupal allows links to post pages to twitter in a new pop-up window. The tweet posted can hold the relevant URL, title and anything else you need it to (like hashtags). The URL can also be shortened by choice and integrated with the Shorten URLsmodule.
5. Shorten URLs - This short URL module from Drupal gives users the ability of an API to shorten URLs. Over 25 URL shortener services are available by default.
6. ShURLy - This URL shortener is clean and simple and easy to set up. You can have your own unique URL shortener and each user can track click statistics for his/her URLs.
7. Tweetmeme - This Twitter module provides integration with the popular TweetMeme web site. This module only adds the TweetMeme button to nodes.
8. Twitter Tweet Button - This module adds a Tweet Button to node teaser or page. Configuration is optional for which types of nodes should use the button.
9. Tweet Board - This Twitter module integrates the popular Tweetboard with your Drupal web site. Simple to use!
10. Socialite - This helps to add your favorite external networking sites to a block. This module is clean and uses a simple, styable design! Supports drag and drop ordering, automatic favicons, and uses clean CSS.
11. Facebook Share - This Facebook Share module gives Drupal site owners the ability to add a Facebook Share button to selected content type nodes in their website(s).
12. Service links - Service links allows the ability to integrate many social bookmarking sites like Buzz Yahoo, del.icio.us, Facebook, Google, LinkedIn, and more.
13. ShareThis - Integration with the ShareThis social bookmarking tool on selected node types.
14. AddtoAny Share/Bookmark - This module helps readers share, bookmark, or email web site pages using many services like Facebook, Twitter, or Delicious. Hundreds of syndication sites are available and the services are updated automatically.
15. SexyBookmarks - The last module on our initial list of the top Drupal SEo modules is SexyBookmarks. This module is a port of the popular WordPress plug-in by the same name. This module adds a nicer looking set of social media bookmarking/sharing icons to better entice your readers to use the options.

Like I noted above, this list is over 30 but we'd be glad to check out more cool SEO or social media modules and test them out before adding to the list above. ALl of the modules above are built for Drupal 6 and most are maintained on a regular basis. We'd rather not add modules that are orphaned or maintained to some degree. Do you have any favorites you'd like us to add to the list?