'Indexing' Tag

  • Table refactoring & application version upgrades, Part II

    August 12, 2010

    Continuing Table refactoring & application version upgrades, Part I, we now discuss code & database upgrades which require DROP operations. As before, we break apart the upgrade process into sequential steps, each involving either the application or the database, but not both. As I’ll show, DROP operations are significantly simpler than creation operations. Interestingly, it’s [...]

  • Table refactoring & application version upgrades, Part I

    August 10, 2010

    A developer’s major concern is: How do I do application & database upgrades with minimal downtime? How do I synchronize between a DB’s version upgrade and an application’s version upgrade? I will break down the discussion into types of database refactoring operations, and I will limit to single table refactoring. The discussion will try to [...]

  • SQL: forcing single row tables integrity

    June 22, 2010

    Single row tables are used in various cases. Such tables can be used for “preferences” or “settings”; for managing counters (e.g. summary tables), for general-purpose administration tasks (e.g. heartbeat table) etc. The problem with single row tables is that, well, they must have s single row. And the question is: how can you force them [...]

  • Reducing locks by narrowing primary key

    May 4, 2010

    In a period of two weeks, I had two cases with the exact same symptoms. Database users were experiencing low responsiveness. DBAs were seeing locks occurring on seemingly normal tables. In particular, looking at Innotop, it seemed that INSERTs were causing the locks. In both cases, tables were InnoDB. In both cases, there was a [...]

  • Monotonic functions, SQL and MySQL

    February 9, 2010

    In mathematics, a monotonic function (or monotone function) is a function which preserves the given order. [Wikipedia] To be more precise, a function f is monotonic increasing, if for every x ≤ y it holds that f(x) ≤ f(y). f is said to be strictly monotonic increasing is for every x < y it holds that [...]

  • Beware of implicit casting

    February 2, 2010

    Ever so often a query provides a “bad” execution plan. Adding a missing index can many times solve the problem. However, not everything can be solved with an index. I wish to highlight the point of having an implicit cast, which negates the use of an index on MySQL. I see this happening a lot [...]

  • Useful temporal functions & queries

    December 8, 2009

    Here’s a complication of some common and useful time & date calculations and equations. Some, though very simple, are often misunderstood, leading to inefficient or incorrect implementations. There are many ways to solve such problems. I’ll present my favorites. Querying for time difference Given two timestamps: ts1 (older) and ts2 (newer), how much time has [...]

  • SQL: finding a user’s country/region based on IP

    May 26, 2009

    I’ve encountered the same problem twice for different customers, so I guess it’s worth a discussion. A common task for web applications is to find out the country/region of a user, based on her IP address, as can be detected in the HTTP request. Depending on the country of origin, the website can translate dates [...]

  • MySQL not being able to utilize a compound index?

    May 7, 2009

    I came today upon a very strange issue. It seems like MySQL is unable to utilize a compound index when evaluating a plan for a query with a range condition. I’m looking for an explanation. I’ll appreciate any insight on this.

  • The depth of an index: primer

    April 9, 2009

    InnoDB and MyISAM use B+ and B trees for indexes (InnoDB also has internal hash index). In both these structures, the depth of the index is an important factor. When looking for an indexed row, a search is made on the index, from root to leaves. Assuming the index is not in memory, the depth [...]

 
Powered by Wordpress and MySQL. Theme by openark.org