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

Displaying posts with tag: myisam (reset)

The depth of an index: primer
+1 Vote Up -0Vote Down

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 of the index represents the minimal cost (in I/O operation) for an index based lookup. Of course, most of the time we expect large portions of the indexes to be cached in memory. Even so, the depth of the index is an important factor. The deeper the index is, the worse it performs: there are simply more lookups on index nodes.

What affects the depth of an index?

There are quite a few structural issues, but it boils down to two important factors:

  • The number of rows in the table: obviously, more rows leads to larger index, larger indexes grow in depth.
  •   [Read more...]
    LOCK TABLES in MyISAM is NOT a poor man’s tranactions substitute
    +0 Vote Up -0Vote Down

    I get to hear that a lot: that LOCK TABLES with MyISAM is some sort of replacement for transactions; some model we can work with which gives us ‘transactional flavor’.

    It isn’t, and here’s why.

    When we speak of a transactional database/engine, we check out its ACID compliance. Let’s break out the ACID and see what LOCK TABLES provides us with:

    • A: Atomicity. MyISAM does not provide atomicity.  If we have LOCK TABLES followed by two statements, then closed by UNLOCK TABLES, then it follows that a crash between the two statements will have the first one applied, the second one not not applied. No mechanism ensures an “all or nothing” behavior.
    • C: Consistency. An error in a statement would roll back the entire transaction in a transactional
      [Read more...]
    MySQL User Group Meetings in Israel
    +0 Vote Up -0Vote Down

    This is a short note that the MySQL User Group Meetings in Israel are established (well, re-established after a very long period).

    Thanks to Eddy Resnick from Sun Microsystems Israel who has set up the meetings. So far, we’ve had 2 successful meetings, and we intend to have more! First one was in Sun’s offices in Herzlia; second one, held last week, was at Interbit (a MySQL training center) in Ramat Gan. We hope to hold these meetings on a monthly basis, and the next ones are expected to be held at Interbit.

    A new (blessed) law in Israel forbids us from sending invitations for these meetings via email without prior consent of the recepient (this law has passed as means of stopping spam). We do realize there are many users out there who would be interested in these meeting. For those users: please stay tuned to

      [Read more...]
    2/3 myisam_suggest: an AutoComplete tool for MySQL fulltext indices
    +0 Vote Up -0Vote Down
    As I’ve written in my previous post “1/3 Implementing an AutoSuggest feature using MySQL fulltext indices”, it’s possible to use the MySQL/MyISAM full-text index to extract search words for an AutoSuggest feature with great performance (because the index tree is used actually). This tool, called myisam_suggest, is my first implementation of this. Download Here: myisam_suggest.c [...]
    1/3 Implementing an AutoSuggest feature using MySQL fulltext indices
    +0 Vote Up -0Vote Down
    The MySQL full-text index Current MySQL versions provide a full-text index (FTI) which is generally used to index and search MyISAM (the default storage engine in MySQL) tables like this: SELECT id, content FROM documents WHERE MATCH(content) AGAINST ("tes*" IN BOOLEAN MODE) Internally, every indexed (text) column of a row is splitted into its words. [...]
    What is the scalable replacement for InnoDB?
    +0 Vote Up -0Vote Down
    A while back a Sun engineer posted an article claiming that the best way to scale MySQL is to shard your database in many instances on a single server, each of which runs in threads that individually have low performance. The Sun way has always been to get high throughput with high latency. [...]
    Should you move from MyISAM to Innodb ?
    +1 Vote Up -0Vote Down

    There is significant portion of customers which are still using MyISAM when they come to us, so one of the big questions is when it is feasible to move to Innodb and when staying on MyISAM is preferred ?

    I generally prefer to see Innodb as the main storage engine because it makes life much simpler in the end for most users - you do not get to deal with recovering tables on the crash or partially executed statements. Table locks is no more problem, hot backups are easy, though there are some important things which we have to consider on case by case basics before recommending the move.

    Is MyISAM used as default or as a choice ? This is the most important question to ask upfront. Sometimes MyISAM is there just because it is default, in other cases this is deliberate choice with system being optimized to deal with MyISAM limits,

      [Read more...]
    Recovering CREATE TABLE statement from .frm file
    +0 Vote Up -0Vote Down

    So lets say you have .frm file for the table and you need to recover CREATE TABLE statement for this table. In particular when we do Innodb Recovery we often get .frm files and some mess in the Innodb tablespace from which we have to get data from. Of course we could relay on old backups (and we do ask for them for a different reason anyway) but there is never guaranty there were no schema changes in between.

    So how to recover CREATE TABLE from .frm file ?

    Recovering from .frm for Innodb Table

    If we simply copy .frm file back to the database we will see the following MySQL creative error message:

    PLAIN TEXT SQL:
  •   [Read more...]
    Less known SQL syntax and functions in MySQL
    +0 Vote Up -0Vote Down

    “Standard SQL” is something you read about. All popular databases have modified version of SQL. Each database adds its own flavor and features to the standard. MySQL is no different.

    Some deviations are storage engine dependent. Others are more general. Many, such as INSERT IGNORE, are commonly used. Here’s a list of some MySQL deviations to SQL, which are not so well known.

    I’ll be using MySQL’s world database for demonstration.

    GROUP_CONCAT

    Assume the following query: SELECT CountryCode, COUNT(*) FROM City GROUP BY CountryCode, which selects the number of cities per country, using MySQL’s world database. It is possible to get a name for one “sample” city per country using standard SQL: SELECT

      [Read more...]
    Two storage engines; different plans, Part II
    +0 Vote Up -0Vote Down
    In Part I of this article, we have seen how the internal structure of the storage engine’s index can affect an execution plan. We’ve seen that some plans are inherent to the way engines are implemented. We wish to present a second scenario in which execution plans vary for different storage engines. Again, we will consider [...]
    10 Newer Entries Showing entries 61 to 70 of 91 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.