Hot answers tagged

54

The only way MyISAM can be faster that InnoDB would be under this unique circumstance MyISAM When read, a MyISAM table's indexes can be read once from the .MYI file and loaded in the MyISAM Key Cache (as sized by key_buffer_size). How can you make a MyISAM table's .MYD faster to read? With this: ALTER TABLE mytable ROW_FORMAT=Fixed; I wrote about this ...


20

Looking at the feature availability list at http://dev.mysql.com/doc/refman/5.1/en/memory-storage-engine.html two possible problems jump out: No transaction or FK support, meaning you will have to manage transactional integrity and referential integrity in your own code were needed (which could end up being a lot less efficient than letting the DB do this ...


16

There's nothing wrong with using multiple storage engines on the same physical machine, as long as you understand the pros and cons of each. There are performance considerations, feature limitations and use cases for all the plugin storage types. For instance, if you have a small table that's 90% writes, you might choose MyISAM. If the data can be ...


15

Oracle is not like MySQL in this. Like most other RDBMSs it comes with it's own built-in storage engine that cannot be exchanged for another.


14

In a simple world, MyISAM is faster for reads, InnoDB is faster for writes. Once you start introducing mixed read/writes, InnoDB will be faster for reads as well, thanks to its Row locking mechanism. I wrote a comparison of MySQL storage engines a few years ago, that still holds true to this day, outlining the unique differences between MyISAM and InnoDB. ...


14

Is this even true? No, it is an implementation detail. A database may implement a suitable update in place if it so chooses. What are the benefits? Splitting an update into a delete followed by an insert generally makes the implementation simpler. Potential side benefits include the ability to avoid transient key violations in a unique index, by ...


13

Personally, I prefer the mmapv1 storage engine as of now for three reasons. Reason 1: Maturity It isn't that WiredTiger is immature. But mmapv1 is well understood and battle tested all way up and down, back and forth and above and beyond. WiredTiger has had some serious issues (see http://jira.mongodb.com for details) fairly recently, and I am not willing ...


11

There are plenty of cases not to use the memory storage engine - and when InnoDB will be faster. You just need to think about concurrency and not trivial single threaded tests. If you have a large enough buffer pool, then InnoDB will become entirely memory resident for read operations as well. Databases have caches. They warm themselves up! Also - do ...


10

It is very interesting this question would come up because a similar question was asked back in January 2011 ( When is the right time to use MariaDB instead of MySQL, and Why? ). Giving thought to that 16-month-old question and how I answered it in April 2011, here are my answers to your immediate questions: Why still using MyISAM ? MyISAM still has ...


9

Let me start by saying, I hate ALTER. It's evil, IMHO. Say, this is your current table schema - CREATE TABLE my_table_of_love ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, my_value VARCHAR(40), date_created DATE, PRIMARY KEY(id) ) ENGINE=MyISAM CHARSET=utf8; Here's the path I recommend - Create a new table object that will replace the old one: CREATE ...


9

You said I heard that we can retrieve the data by creating a new table same as the old table with storage engine as innodb or myisam Whoever told you that should have told you to enable binary logs as @jynus commented. The BLACKHOLE Storage Engine does not store data at all. It is a special storage engine used in very meticulous setups. EXAMPLE #1 : ...


7

I can't tell you if this is a common practice. I can say about my own experience. I always use the best tool for the job, so I mix engines all the time. Most of my projects use MyISAM as the default engine. When I need special features just available on InnoDB, I go for it. When a table is mostly read-only, I choose Archive engine before I could blink. ...


7

1) Loss protection is a function of paranoia. Always make a backup. If you're really paranoid, make a backup then restore from the backup. 2) This page of the MySQL manual has instructions to convert table types. The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table. That is, use ALTER TABLE ... ENGINE=INNODB, or ...


7

To add to the responses here covering the mechanical differences between the two engines, I present an empirical speed comparison study. In terms of pure speed, it is not always the case that MyISAM is faster than InnoDB but in my experience it tends to be faster for PURE READ working environments by a factor of about 2.0-2.5 times. Clearly this isn't ...


7

Nonclustered indexes always include a row locator. For a heap this will be an 8 byte RID (File:Page:Slot). On a table with a clustered index it will be the clustered index key column(s). And it will always be the copied values not a pointer to the values. This duplication of CI key values into all non clustered indexes is why it is often recommended that ...


6

Where pages are used for internal purposes like sort runs, the maximum row size is 8094 bytes. For data pages, the maximum in-row size including internal row overhead is 8060 bytes. Internal row overhead can expand significantly if certain engine features are in use. For example, using sparse columns reduces the user-accessible data size to 8019 bytes. ...


6

'page split' and 'heap' should not be used in the same sentence. Do you mean why wasn't the row of size 393+change fit in the free space of the first page of the heap? That is because the PFS free space info has not high enough resolution, see Managing Extent Allocations and Free Space: The PFS has one byte for each page, recording whether the page is ...


6

To be honest, with a low traffic environment running on 5.1, you shouldn't have any problems with MyISAM. The biggest downfall of MyISAM is table-locking WRITES, IMO. But with limited data in the tables, this won't be noticeable. MyISAM is probably easier to manage from a DBA standpoint with recovery and repair. And in 5.1, I think InnoDB's benchmarks aren'...


6

The write I/O was a result of repeated DROP TABLE/CREATE TABLE statements. I replaced those with TRUNCATE TABLE and the problem is gone.


6

One valid reason to move to another MySQL flavour is performance. The latest MariaDB versions 5.3 and 5.5 (which are drop in replacemants of MySQL 5.1 and 5.5 respectively) included major improvements in query optimization. You can read more on their site: What is MariaDB 5.3 Examples on improvements/features are "Index Condition Pushdown", "Cost-based ...


6

You can only use a single storage engine type per mongod instance. There is only a single dbPath setting for a mongod deployment, and you cannot mix & match data files from different storage engines within the same data path. However, you can use different storage engine configurations within members of a replica set (for example, mmap primary and ...


5

You may want to watch how many MEMORY tables you are using. Even with a MEMORY table, there must be an open file handle to the .frm of the MEMORY table. Check your open_files_limit. You may need to increase that as well. You also have a great disparity between read_buffer_size and read_rnd_buffer_size. You have read_buffer_size at 64M and ...


5

Pros If there is no Sphinx API for the language you are using SphinxSE allows you to do searches through MySQL. If you are doing background processing and storing the search results in your database SphinxSE allows you to move the data from the searchd service directly into MySQL instead of going through an application layer. Cons You have to compile ...


5

Both works with b-tree index, but MyISAM can work with FULLTEXT index also. MySQL 5.6 is supposed to support FULLTEXT indexing as well. Look for it when it goes GA. InnoDB is more strict in data integrity, while MyISAM is loose InnoDB has transactions while MyISAM does not. InnoDB has foreign keys and relationship contraints while MyISAM does ...


5

For completeness, whilst Jack's answer is technically true, it is possible to use Data Cartridges to expand on vanilla Oracle offerings. In fact, a company called CopperEye released a new indexing method (patented, now not available) utilizing this functionality. See this old press release. The patent makes for fascinating reading.


5

InnoDB supports compression as of MySQL 5.1 (with InnoDB Plugin), or natively in MySQL 5.5. The level of compression really depends on your table content, but I see 75% reduction in size as common. Reads and writes to compressed tables involve more CPU. InnoDB will support your foreign keys as well as any indexes. I would say it is a fairly good out-of-...


4

I actually wrote a post back in July 2011 ( Modify DEFINER on Many Views ) about how to access views for modification. It is a fact that when the storage engine is NULL, it is always a View. The actual definition of the View is MySQL 5.1/5.5 mysql> desc information_schema.views; +----------------------+--------------+------+-----+---------+-------+ | ...


4

When it comes to InnoDB, you should never use big primary keys. Why? InnoDB has an internal Clustered Index called gen_clust_index. When you are doing INSERTs into an InnoDB table with just a Primary Key, you are building up the gen_clust_index. I would expect this table to bloat very quickly. Your goal should be to record the URL and index it in such a way ...


4

In general, MariaDB is a drop-in replacement for MySQL. It says so on their home page. :-) Your applications won't notice a difference. For specialized applications and edge cases, there may be difference. But these are going to be tuned to use special features of the newer versions of MySQL. You may also want to look at Percona Server as it tracks the ...


4

There's no need to check for disk space when allocating a new extent to an object. SQL Server already owns that space on the disk. It knows which pages within it's data file are allocated and which aren't, so there's no need to verify that we own the pages within the extent as we know for a fact that we already do. It will simply allocate space in the ...



Only top voted, non community-wiki answers of a minimum length are eligible