What are MyISAM tables?
In MySQL MyISAM is the default storage engine. MyISAM tables store data values with the low byte first. Even though MyISAM tables are very reliable, corrupted tables can be expected if there is a hardware failure, the pc shuts down unexpectedly. MyISAM tables are reliable because any change made to a table is written before the sql statement returns. Even though MyISAM is the default storage engine it is advisable to specify ENGINE= MYISAM
Explain the difference between MyISAM Static and MyISAM Dynamic.
MyISAM Static tables have fields of fixed width while the MyISAM Dynamic can accommodate variable lengths such as TEXT, BLOB etc. MyISAM allows easier restoration of data as compared to MyISAM dynamic.
What does myisamchk do?
Myisamchk gets is used to fetch information about the database tables. It is also used to check, repair and optimize the tables. From shell, Myisamchk can be invoked by typing:
Shell> myisamchk [options] tbl_name.
Here options explains what is expected from the Myisamchk.
Explain advantages of InnoDB over MyISAM.
1. InnoDb supports locking of rows while MyISAM supports only table locking.
2. Data integrity is more in InnoDB.
3. Transactional nature of InnoDB enables easy and online backups.
Explain advantages of MyISAM over InnoDB.
1. Data is not cached by the MySQL query browser.
2. MyISAM is a simple storage engine.
3. MyISAM provides more optimization.
4. MyISAM has a low relative memory use.
How to use myisamchk to check or repair MyISAM tables?
Myisamchk gets is used to fetch information about the database tables. It is also used to check, repair and optimize the tables. From shell, Myisamchk can be invoked by typing:
Shell> myisamchk [options] tbl_name.
Here options explains what is expected from the Myisamchk.
How to Check MyISAM Tables for Errors?
Using the myisamchk table_name command can be used to find all errors. it cannot find corruption that involves only the data file. The different options are:
Myisamchk –m table_name – checks index entries for errors and calculates checksum. This checksum that is calculated for all key values of rows is verified with the keys in the index tree.
Myisamchk –e table_name – performs and extended check. does a complete and thorough check of all data
Explain the options of myisamchk to improve the performance of a table.
Myisamchk has a couple of options to optimize a table.
1. Using myisamchk –r table_name, runs myisamchk in recovery mode. This option combines the fragmented rows and gets rid of wasted space.
2. --Analyze, -a is used for analyze the distribution of key values.
3. --sort-index, -S – Sorts index tree.
4. --sort-records=index_num, -R index_num – sorts recods according to particularr index.
Discuss about MyISAM Key Cache.
MyISAM keeps a key cache to minimize disk I/O. it keeps the most frequently accessed table blocks in memory. For most frequently accessed index blocks, key cache is used. The key cache is not used for data blocks. Multiple threads can access the cache concurrently. The size of the key cache is restricted using key_buffer_size system variable. If the value of this variable is 0, no key cache is used.
Discuss about MyISAM Index Statistics Collection.
MyISAM’s Index statistics collection is based on a set of rows with the same key prefix value. Here, these set of rows are called as value group. The statistics about the tables is used by the optimizer. The average value of the group size plays an improtant role. The group size is used estimate how many rows must be read for each ref access and how many rows will a particular join will produce. Myisam_stats_method system variable if set as global, affects statistics collection for all tables.
