Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
10 Newer Entries Showing entries 31 to 40 of 58 10 Older Entries

Displaying posts with tag: indexing (reset)

Thoughts and ideas for Online Schema Change
+6 Vote Up -0Vote Down

Here’s a few thoughts on current status and further possibilities for Facebook’s Online Schema Change (OSC) tool. I’ve had these thoughts for months now, pondering over improving oak-online-alter-table but haven’t got around to implement them nor even write them down. Better late than never.

The tool has some limitations. Some cannot be lifted, some could. Quoting from the announcement and looking at the code, I add a few comments. I conclude with a general opinion on the tool’s abilities.

“The original table must have PK. Otherwise an error is returned.”

  [Read more...]
How often should you use OPTIMIZE TABLE? – followup
+3 Vote Up -2Vote Down

This post follows up on Baron’s How often should you use OPTIMIZE TABLE?. I had the opportunity of doing some massive purging of data from large tables, and was interested to see the impact of the OPTIMIZE operation on table’s indexes. I worked on some production data I was authorized to provide as example.

The use case

I’ll present a single use case here. The table at hand is a compressed InnoDB table used for logs. I’ve rewritten some column names for privacy:

mysql> show create table logs \G

Create Table: CREATE TABLE `logs` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(20) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
 `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
  [Read more...]
A review of Relational Database Design and the Optimizers by Lahdenmaki and Leach
+2 Vote Up -0Vote Down

Relational Database Index Design and the Optimizers

Relational Database Index Design and the Optimizers. By Tapio Lahdenmaki and Mike Leach, Wiley 2005. (Here’s a link to the publisher’s site).

I picked this book up on the advice of an Oracle expert, and after one of my colleagues had read it and mentioned it to me. The focus is on how to design indexes that will produce the best performance for various types of queries. It goes into quite a bit of detail on how databases execute

  [Read more...]
Databases: Normalization or Denormalization. Which is the better technique?
+1 Vote Up -0Vote Down
This has really been a long debate as to which approach is more performance orientated, normalized databases or denormalized databases. So this article is a step on my part to figure out the right strategy, because neither one of these approaches can be rejected outright. I will start of by discussing the pros and cons of both the approaches. Pros and Cons of a Normalized database design. Normalized databases fair very well under conditions where the applications are write-intensive and the write-load is more than the read-load. This is because of the following reasons: Normalized tables are usually smaller and...
Table refactoring & application version upgrades, Part II
+0 Vote Up -1Vote Down

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 the same as in life.

DROP COLUMN

A column turns to be redundant, unused. Before it is dropped from the database, we must ensure no one is using it anymore. The steps are:

  • App: V1 -> V2. Remove all references to column; make sure no queries use said column.
  • DB:
  •   [Read more...]
    Table refactoring & application version upgrades, Part I
    +2 Vote Up -0Vote Down

    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 understand the need for refactoring and will dictate the steps towards a successful upgrade.

    Reader prerequisites

    I will assume MySQL to be the underlying database. To take a major component out of the equation: we may need to deal with very large tables, for which an ALTER command may take long hours. I will assume familiarity with Master-Master (Active-Passive) replication, with possible use of MMM for MySQL.


      [Read more...]
    SQL: forcing single row tables integrity
    +2 Vote Up -0Vote Down

    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 to have just one row?

    The half-baked solution

    The common solution is to create a PRIMARY KEY and always use the same value for that key. In addition, using REPLACE or INSERT INTO ON DUPLICATE KEY UPDATE helps out in updating the row. For example:

    CREATE TABLE heartbeat (
     id int NOT NULL PRIMARY KEY,
     ts datetime NOT NULL
     );
    

    The above table definition is taken from

      [Read more...]
    Reducing locks by narrowing primary key
    +2 Vote Up -0Vote Down

    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 PRIMARY KEY on the combination of all 5 columns. And in both cases, there was no clear explanation as for why the PRIMARY KEY was chosen as such.

    Choosing a proper PRIMARY KEY

    Especially with InnoDB, which uses clustered index structure, the PRIMARY KEY is of particular importance. Besides the fact that a bloated PRIMARY KEY bloats the entire clustered index and secondary keys (see:

      [Read more...]
    Monotonic functions, SQL and MySQL
    +2 Vote Up -0Vote Down

    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 f(x) < f(y).

    So, if we follow values in some order, we say that f is monotonic increasing if f’s value never decreases (it either increases or stays the same), and we say that f is strictly increasing if f’s value is always changes “upwards”.

    Monotonic functions play an important role in SQL. To discuss monotonic functions

      [Read more...]
    Beware of implicit casting
    +1 Vote Up -0Vote Down

    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 on customers’ databases, and this begs for a short introduction.

    MySQL doesn’t support index functions

    Let’s assume the following table:

    CREATE TABLE `person` (
     `id` bigint(20) NOT NULL AUTO_INCREMENT,
     `first_name` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
     `last_name` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
     `driver_license_registration` bigint(20) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `last_name` (`last_name`),
     KEY `driver_license_registration` (`driver_license_registration`)
    )
    
      [Read more...]
    10 Newer Entries Showing entries 31 to 40 of 58 10 Older Entries

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.