MyISAM is a non-transactional storage engine for MySQL. It provides high-speed storage and retrieval, as well as fulltext searching capabilities. Also, is the default storage engine type for version prior to 5.5.
50
votes
8answers
23k views
What are the main differences between InnoDB and MyISAM?
What are the main differences between InnoDB and MyISAM?
11
votes
1answer
11k views
How can I change the default storage engine in phpmyadmin?
I use InnoDB almost exclusively in my applications. However, if I'm not careful when setting up the table, I forget to change it and phpmyadmin sticks me with MyISAM. Is there a way to change the ...
10
votes
2answers
3k views
Rows missing after online conversion from MyISAM to InnoDB
We have a reasonably small database that we wanted to convert from MyISAM to InnoDB. Being database noobs, we just converted (using alter table) without even taking the site down.
Now that the ...
9
votes
2answers
2k views
Is it common practice to mix InnoDB and MyISAM tables on same server?
I've got a single database of about 4.5GB running on a server with 8GB RAM. The vast majority of the tables are MyIsam (about 4.3GB), but I'm soon going to be converting some of them to InnoDB. (It's ...
9
votes
1answer
842 views
MySQL table with 100,000 records queried often
I have a single database of about 100 tables to store various kinds of information.
The most important table is our order table which is used to store customers orders and is over 100000 records as ...
8
votes
3answers
17k views
Which is faster, InnoDB or MyISAM?
How can MyISAM be "faster" than InnoDB if
MyISAM needs to do disk reads for the data?
InnoDB uses the buffer pool for indexes and data, and MyISAM just for the index?
6
votes
1answer
2k views
When to switch from MyISAM to InnoDB?
We have a MySQL 5.0 server running all tables as MyISAM. We have two slaves that, in the last month, we have upgraded to MySQL 5.5. Their tables are also still MyISAM.
My original plan was to upgrade ...
6
votes
2answers
891 views
BigInt or Varchar for a large number I won't ever sort / calculate on
I need to store users' Facebook IDs in a column in my DB.
The ID always consists of only digits, and can be as large as a number in excess of 500 million. Considering that I won't ever need to do ...
6
votes
1answer
182 views
MySQL Hanging Completely when `ALTER TABLE… ENABLE KEYS`
I know very little about database administration but I have to deal with some very large tables on my site.
This server has 64GB of RAM and Intel Core i7-3820 (4 x 3600 MHz). Most of everything it ...
6
votes
1answer
218 views
LOAD DATA INFILE blocks on concurrent use against MyISAM tables
I'm having trouble loading data into MyISAM table(s) concurrently using LOAD DATA INFILE. The loading operation works fine in isolation. Trying to do LOAD DATA INFILE operations concurrently, blocks ...
6
votes
2answers
1k views
Why does MyISAM support FULLTEXT Searching and InnoDB does not?
I know the basic differences between the MyISAM and InnoDB engines. In MySQL 5.6 beta, InnoDB will support fulltext search. But, I wonder why previous versions of InnobDB didn't support FTS? My guess ...
5
votes
1answer
350 views
Can I have an InnoDB master and MyISAM slaves with Full-Text for searching?
I'd like to use InnoDB on the master, for its transactional ability, but MyISAM on the slaves for the full-text search ability. Is this possible?
5
votes
2answers
1k views
Any gotchas at all with converting from MyISAM to InnoDB?
I'm ready to move from MyISAM to InnoDB but wanted to know if there was a full list of things to look for? For example, I haven't seen any list mention that running DISABLE KEYS on an InnoDB table ...
5
votes
2answers
390 views
Choosing MyISAM over InnoDB for these project requirements; and long term options
Sorry for the long post, but I had to give as much info as possible to make this very vague question more specific.
My project's aim is to let users search a (huge) database of variety of products.
...
5
votes
3answers
312 views
MyISAM Performance: Join Decomposition
in High Performance MySQL on page 159 they talk about breaking up complex queries into simple ones:
Converting
SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON ...
5
votes
1answer
97 views
Start/Stop MySQL
I'm seeking help to understand what happens when the following command line is executed:
root@prodn$ service mysqld stop
Yes, it shuts down the MySQL server so access to it is no longer available ...
5
votes
2answers
223 views
Recommended structure? %70 write %30 read. 10M row. 200 query/sec. Select, update, insert, search
Followings are what I need to do on my project:
1- If a search term is new and unique, then add the search term
2- Show lastest searched 1000 search terms (I don't need to store dates of all search ...
4
votes
3answers
596 views
MySQL tables/databases into different disks
I am aware that there is a possibility to have a single database server with many tables/databases, and you can separate some of those tables/databases into separate disks.
Lets say you have 5 ...
4
votes
4answers
1k views
Benefits of table level locking
What are the benefits of table level locking which is used by the MyISAM storage engine? Row level locking has lots of benefits like concurrent updates and reads that do not lock the table.
Edit
Its ...
4
votes
1answer
310 views
Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?
There's a production database I'm working on, whose 4 largest tables contain between 4 million and 10 million rows each and about 15 fields each, with indexes on different field types (numbers, ...
4
votes
2answers
3k views
Using multiple cores for single MySQL queries on Debian
I am running a MySQL server for tests on a VM (VMWare) with Debian as guest OS. The guest has four emulated CPU cores, so I set thread_concurrency to four.
I am doing expensive joins on large tables, ...
4
votes
1answer
124 views
Which MySQL storage engine is the most similar to ARCHIVE when that is not available?
I have a table that contains a log of events that is written to often and read very infrequently by comparison. I would love to use the ARCHIVE engine, because it is basically designed for exactly ...
4
votes
3answers
127 views
Index before or after bulk load using load infile?
I have a database with over 1B rows and two columns that are indexed (in addition to the PK).
Is it better to have the index pre-defined in the table before the load infile or better to index after ...
4
votes
2answers
4k views
Why is MySQL is creating so many temporary tables on disk?
Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows
Current max_heap_table_size = 200 M
Current tmp_table_size = 200 M
Of 17158 temp tables, 30% were ...
4
votes
2answers
276 views
Less RAM than Index_length MyISAM
I'm working on a database to store "time series" data (the value of X was Y at this time). The rows themselves are very small and static in size, with the primary key consisting of two smallint ...
4
votes
2answers
5k views
way to prevent queries from waiting for table level lock
We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in ...
4
votes
1answer
230 views
How much does table size affect performance?
Lets say I have a MyISAM table (amongst other tables) with 2938347 rows. This table is merely being used for logging and doesn't get emptied very often.
By not regularly archiving or deleting these ...
4
votes
1answer
124 views
Can I partially invalidate the MySQL table cache?
I want to set a fairly large table to MyISAM mode to keep it cached proactively. The table file is approximately 3GB.
After several hours, MySQL invalidates the entire table from cache, while < ...
4
votes
1answer
604 views
Not enough space for MySQL optimize; do new rows overwrite existing data?
My MyISAM table consumed most of my disk space. I deleted a significant portion of the table, but the optimize command (to clean out the deleted data) does not have sufficient space.
Is there any ...
3
votes
3answers
213 views
How can I select using the same fields from an undetermined number of tables using MySQL?
I'm dealing with a MySQL database where I have an undetermined amount of identically structured tables that look like this:
foo_reference1
foo_reference2
foo_referencea
foo_referenceb
....
...
3
votes
2answers
310 views
Why innodb doesn't store geospatial data?
I am very curious.
I have business tables. Now I think I will have to create a separate table, location table. That separate table should be myisam.
But why would I do so?
Why can't innodb store ...
3
votes
2answers
1k views
Performance settings for MyISAM tables - keep everything in memory
I have a 8-core MySQL server with 32 GB of RAM, holding 40 MyISAM tables (total: ~4GB of data).
Since the databases size is several times smaller than the available memory, how can I make full usage ...
3
votes
3answers
3k views
Best of MyISAM and InnoDB
Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
3
votes
3answers
333 views
Dynamically changeable database
We have a database and some data is stored in a Trucrypt folder, my question is how can I include this trucrypt folder. The data type is MyISAM. I saw there is a symbolic link, but not really clear ...
3
votes
2answers
290 views
Preordering the table by a specified column
I have a table and I am always getting the data by ordering a column. In my case, I order by datenum. However, the selection is so slow because of the ordering.
Is it possible to order the table ...
3
votes
2answers
526 views
Should I move to InnoDB during a planned migration?
I'm shortly migrating a Debian Lenny webserver to another host and Debian Squeeze. The standard MySQL installation even on Squeeze is 5.1 and therefore would default to MyISAM - I'm inclined to stick ...
3
votes
1answer
346 views
Proper tuning for 30GB InnoDB table on server with 48GB RAM
I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the ...
3
votes
2answers
216 views
Writing transactions to .MYD file to take Backup
I'm trying to backup MySQL DB by archiving the data directory of required databases.
I'm doing FLUSH TABLES WITH READ LOCK before taking the backup, but even then some of the transactions were not ...
3
votes
1answer
1k views
Consistent logical backup of databases that use MyISAM and InnoDB engines
I have a question regarding the logical backup of MySQL databases
that use both MyISAM and InnoDB.
The mysqldump utility supports these two options:
--single-transaction - Creates a consistent ...
3
votes
2answers
122 views
Using MyISAM for reading and InnoDB for writing data
I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data.
I want to use two structure like ...
3
votes
4answers
2k views
mysql insert into indexed table taking long time after few million records
I have a table like this
CREATE TABLE IF NOT EXISTS `dnddata` (
`numbers` varchar(10) NOT NULL,
`opstype` char(1) NOT NULL,
PRIMARY KEY (`numbers`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
...
3
votes
1answer
293 views
Which mysql storage engine to choose?
I'm confused about which mysql engine should I choose (lets talk about the most used : MyISAM and InnoDB).
The theories say:
Both work with BTree indexes, but MyISAM can work with FULLTEXT index ...
3
votes
1answer
458 views
How to backup a MySQL database without slowing the application?
I'm an application developer and wondering about our database backup strategy. Right now our sysadmins backup our database every 8 hours. I don't really know how the do it, but while the backup is ...
3
votes
1answer
593 views
Why full-text-search returns less rows than LIKE
I don't get full-text-search working as I want it to, and I don't understand the differences in the resultlists.
Example statements:
SELECT `meldungstext`
FROM `artikel`
WHERE `meldungstext` LIKE ...
3
votes
3answers
564 views
InnoDB vs MyISAM with many indexes
I am not sure if there is a right or wrong answer to this question, as I suppose it severely depends on the situation, but for the sake of a mental exercise, I'll go ahead and ask it anyway:
I have a ...
3
votes
1answer
572 views
MySQL ignoring index, index cardinality is the same as number of records in the table (?!)
Everyone, I could use an extra set of eyes on an issue we've come across. Here's what we're seeing:
We're writing some code to migrate our messages to a new table called
communications. We're ...
3
votes
2answers
529 views
Quick MySQL Backup
I have a database which is grown to 3GB and it has 3 MyISAM tables. And I have 4GB RAM. What will be the best (quick) way to backup this database without any performance lack.
Or mysqldump will work ...
3
votes
1answer
1k views
Is Percona xtrabackup the right choice for my 500GB MySQL backup?
I have a MySQL db with 500GB, consisting mainly of Innodb tables, and one big (200GB) MyISAM table. My current backup strategy is copying the db files to an external harddrive. This causes a downtime ...
3
votes
1answer
100 views
Can I use mysql to serve 100's of millions of small image files?
I need to serve 300,000,000 small image files totaling 1.5TB. Trying to unpack these files to individual files on the file system is next to impossible (1 MByte/sec throughput rates to create the ...
3
votes
1answer
80 views
Which MySQL table to use for read-only, bulk-loaded tables?
So I have a workload on 2 key tables in my application with the following characteristics:
READ-ONLY DATA
The data is bulk-loaded in a fully consistent manner from a single process on a schedule, ...
