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

Displaying posts with tag: myisam (reset)

Using the Sphinx Search Engine with MySQL
+6 Vote Up -0Vote Down

MySQL Full Text Search Limitations

Suppose you have a MyISAM table containing a column with a full text index. This table starts to grow to a significant size (millions of rows) and gets updated fairly frequently. Chances are that you’ll start to see some bottlenecks when accessing this table, since without row level locking, the reading and writing operations will be blocking each other.

A solution that many people would suggest right away is to use the master for writes and a slave for reads, but this only masks the problem, and it won’t take long before enough read traffic on the slave starts causing slave lags.

Why Sphinx?

The main difference between the Sphinx search engine and other alternatives is its close integration with MySQL. For example, it can be used as a

  [Read more...]
Importing times in MySQL
+0 Vote Up -0Vote Down

One of the ways to import data into MySQL is using the LOAD DATA INFILE. It is a faster method than recovering from a dump, as it’s raw data instead of SQL sentences.

The import time depends on the table engine, for example, MyISAM can be 40 times faster than Innodb. Let’s benchmark this:

Preparation

I’m gonna make some benchmarking using MySQL 5.1.36 (64 bits MacOS X). I’ll need a big table, so I’ll take City from the World Database and create a huge table called “city_huge”:

CREATE TABLE city_huge LIKE CITY;

INSERT INTO city_huge 
    SELECT NULL, name, CountryCode, District, Population FROM city;
# Run this sentence 100 times,
# so city_huge table will be 100 times bigger than city.
# Tip: use a script, temporary table,
  [Read more...]
MyISAM quote of the day
+1 Vote Up -0Vote Down

Seen in #maatkit on Freenode:

I never realized just how terrible recovering MyISAM from a crash can be

Sad but true — it can be pretty painful. This is one of the reasons I pretty much recommend InnoDB (okay, okay, XtraDB) for most data unless it’s read-only.

Related posts:

  • Hindsight on a scalable replacement for InnoDB A while ag
  • What is the scalable replacement for InnoDB? A while ba
  • Xtrabackup is for InnoDB tables too, not just XtraDB Just thoug
  • Related posts brought to you by Yet Another Related Posts Plugin.

    Getting annoyed with MyISAM multiple key caches.
    +2 Vote Up -4Vote Down

    As I've wrote few times using multiple key caches is a great way to get CPU scalability if you're using MyISAM. It is however very annoying - this feature really looks half baked to me.

    The problem with multiple key caches and mapping of tables to the different files is - there is no way to see the existing key caches, their mapping and stats. The only thing you can access is key cache parameters - via structured variables

    In particular I would like to:

    See the list of created caches Right now I can create key caches with random names causing invisible resource consumption. It is possible to make an error in key

      [Read more...]
    Selectivity threshold for a non-covering index
    +0 Vote Up -0Vote Down

    Assume you have a table with about 300 000 rows, and an indexed column ‘col1′ with only 9 distinct values. Now you got a query like ’select * from t1 where col1 = const’. The questions are

    - when the index is faster to full table scan and vice versa?
    - does MySQL use the optimal plan by default?

    These questions became very relevant now that QOT got server access and is able to gather various table metrics including selectivity. Besides index selectivity the threshold value obviously depends on the storage engine used, so for me it is also interesting to see how our PBXT engine compares to others in this aspect. Namely to InnoDB - an engine with similar transactional properties and MyISAM - a very fast engine for read-only scenarios.

    For the test I took


      [Read more...]
    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. [...]
    10 Newer Entries Showing entries 61 to 70 of 96 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.