End Your If

Category: Database

  • Dynamic COUNT() Versus Static COUNT()

    I’ll continue today with another good database programming article.  If you missed yesterdays, you may view it here.

    Today’s article will discuss when to use a static COUNT() versus a dynamic COUNT().  On a regular basis we are tasked with creating a message center or a photo album and we need to display a count of new messages or pictures in the album.

    The obvious solution is to do a simple query: SELECT COUNT(*) FROM pictures WHERE album_id = 1 and display the result beside our album name.  Now, let’s assume that we allow multiple albums our actual query would be closer to this: SELECT albums.*, COUNT(pictures.*) as picture_count FROM albums INNER JOIN pictures ON albums.id = pictures.album_id WHERE albums.id = 1.  This will of course work, but let me show you a better, more practical approach that will improve performance significantly on large databases. (more…)

  • Why You Should Avoid ORDER BY RAND() OR NEWID()

    Quite often we get a complaint from a client that their homepage is too slow.  The first thing we do is load the homepage up and see for ourselves.  Yep, it looks slow, so the next step is too see what is being done.  Quite often you’ll see some random data being displayed on the homepage.  Whether it’s random news articles, photos, videos, etc…If this is not the case, you may find my article on improving slow loading web pages more useful.

    If the homepage is slow because of random data, you can lay a bet with high certainty that those 10 random articles are being pulled with an ORDER BY RAND() OR NEWID() slowing the site to a crawl.  In case you are curious the difference, RAND() is the MySQL way and NEWID() is the SQL Server way.  In this article, I’ll show you why you should avoid these functions and several different solutions to help you overcome this issue. (more…)

  • Why You Should Always Backup

    Man, what a week it has been.  As you may have noticed it’s been a few days too many since my last post.  The reason for that is a hectic week at work has prevented me from having time.

    Now that the week is over and it was hectic because of my own stupidity, I mine as well write an article about it to save you some time. (more…)

  • SQL Tips – Why LEFT JOINS are bad

    When you do SQL queries do you find yourself always, or almost always, using left joins?  If you’ve answered yes to this question, I would suggest to continue to read and understand some downfalls that you might be getting yourself into.

    Let’s start by looking at a standard query that has a left join:

    [code]
    SELECT * FROM users u LEFT JOIN countries c ON c.id = u.country_id WHERE u.id = 5
    [/code]

    The following query will return all fields from the users table and all fields from the countries table.  Now you’re thinking, “Yeah, looks good, so what’s wrong with this?”.  Well, I’m glad you asked. (more…)