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? …

  [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 …
  [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:

  1. Hindsight on a scalable replacement for InnoDB A while ag
  2. What is the scalable replacement for …
  [Read more...]
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 …

  [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 …


  [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 …

  [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 …
  [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 …

  [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.