Tag Info

New answers tagged

3

The main configuration settings are in my memory blog. Most important is 20% of RAM for key_buffer_size. (However, it does not need to be more than the sum total of Index_length of all the SHOW TABLE STATUS. This will also do the arithmetic: SELECT ENGINE, ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB", ROUND(SUM(index_length)/1024/1024, 1) ...


1

They do the same thing, but there is a difference and a grave danger. Here is the difference: OPTIMIZE TABLE is done in the mysql client or in a mysql session. myisamchk is a utility, not a client. Thus, there are no table locking safeguards. Therefore, you should never run myisamchk -r -a -S in a live system. Otherwise, you can quickly (in fact, ...


1

If you sort the records according to the PRIMARY KEY before loading them, it will run faster. If you are "replacing" an existing table, do this to avoid essentially all downtime: CREATE TABLE new LIKE real; -- with all PRIMARY & UNIQUE keys Load new with data ALTER TABLE new ADD INDEX ...; -- add all secondary indexes Optionally sanity check the data ...


2

Unfortunately, no there isn't because ALTER TABLE ... DISABLE KEYS disables nonunique indexes For MyISAM tables, key updating can be controlled explicitly. Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. Then use ALTER TABLE ... ENABLE KEYS to re-create missing indexes. MyISAM does this with a special algorithm that is ...


1

Based on the suggestion I gave in the chat room, here is my answer CREATE TABLE cash_op LIKE cash_operation; Goto the datadir in your Windows server rename cash_op.MYD cash_op.MYX copy cash_operation.MYD cash_op.MYD login to mysql and run REPAIR TABLE cash_op; ALTER TABLE cash_operation RENAME cash_op_old; ALTER TABLE cash_op RENAME cash_operation; Go ...


3

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


0

Yes, there are many differences. The biggest for me is that InnoDB supports foreign keys MyISAM does not. Some will argue that MyIsam is faster for reads, but if you want to take advantage of compression for example you need Innodb. My default position is to start with InnoDB whenever possible, then if you cannot use this for example when using spatial ...


0

MyISAM only -- Normally a record is a continuous stream of bytes in the .MYD file. This includes TEXT and BLOB columns. An index has a byte offset (or record number) to point into the .MYD file. After row(s) have been DELETEd, there can be holes in the .MYD. MyISAM prefers to fill in the holes before appending to the .MYD. However the hole(s) may not be ...


0

"Compressed MyISAM" -- what is that? Maybe "Archive"? Compression is not needed; the tables are too small, and will quickly be fully cached. FOREIGN KEYS -- Why? You have debugged you code, haven't you? There will be no dangling things to check. MEMORY is not unreasonable, if you write 'reload' code that executes whenever you bring up the server, and any ...


0

Yes, increase table_open_cache. Are you using Windows? Or *nix? (You are in an area where the OS matters.) Keep in mind that each MyISAM table involves 3 files. What is Max_used_connections? That should give you a clue of what max_connections can be lowered to.


3

In MyISAM records are stored in blocks. There are like 20 different block types, some of can be really large. Those are used for BLOB/TEXT values. I never saw that MyISAM record stores a pointer to a BLOB (but I would not insist). In InnoDB BLOB/TEXT values are also a part of the record and stored in-page as long as total record size not more than ~7k. ...


1

TEXT fields have what the MySQL Documentation (Compressing BLOB, VARCHAR and TEXT Columns) calls "off-column storage" In a clustered index, BLOB, VARCHAR and TEXT columns that are not part of the primary key may be stored on separately allocated (“overflow”) pages. We call these “off-page columns” whose values are stored on singly-linked lists of ...



Top 50 recent answers are included