--0016e68fa734c1dbbb048b7abee3
Content-Type: text/plain; charset=ISO-8859-1
Hi,
I am newbie to mysql can i know the advantages of myisam storage engine
and some of its special features.
--
Regards
Karthik.P.R
karthik@mafiree.com
--0016e68fa734c1dbbb048b7abee3--
--000feae9ac769f3ace048b7c660c
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable
Hi,
Much more conservative approach to disk space management - each MyISAM tabl=
e
is stored in a separate file, which could be compressed then with myisamchk
if needed. With InnoDB the tables are stored in tablespace, and not much
further optimization is possible. All data except for TEXT and BLOB can
occupy 8,000 bytes at most. No full text indexing is available for InnoDB.
TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.
MyISAM tables have the following characteristics:
=B7 All data values are stored with the low byte first. This makes =
the
data machine and *operating system* independent. The only requirements for
binary portability are that the machine uses two's-complement signed
integers and IEEE floating-point format. These requirements are widely used
among mainstream machines. Binary compatibility might not be applicable to
embedded systems, which sometimes have peculiar processors.
There is no significant speed penalty for storing data low byte first; the
bytes in a table row normally are unaligned and it takes little more
processing to read an unaligned byte in order than in reverse order. Also,
the code in the server that fetches column values is not time critical
compared to other code.
=B7 All numeric key values are stored with the high byte first to
allow better index compression.
=B7 Large files (up to 63-bit file length) are supported on
filesystems and operating systems that support large files.
=B7 The maximum number of indexes per MyISAM table is 64. This can =
be
changed by recompiling. Beginning with MySQL 5.1.4, you can configure the
build by invoking *configure* with the --with-max-indexes=3D*N* option, whe=
re
*N* is the maximum number of indexes to permit per MyISAM table. *N* must b=
e
less thann or equal to 128. Before MySQL 5.1.4, you must change the source.
The maximum number of columns per index is 16.
=B7 The maximum key length is 1000 bytes. This can also be changed =
by
changing the source and recompiling. For the case of a key longer than 250
bytes, a larger key block size than the default of 1024 bytes is used.
=B7 When rows are inserted in sorted order (as when you are using a=
n
AUTO_INCREMENT column), the index tree is split so that the high node only
contains one key. This improves space utilization in the index tree.
=B7 Internal handling of one AUTO_INCREMENT column per table is
supported. MyISAM automatically updates this column for INSERT and
UPDATEoperations. This makes
AUTO_INCREMENT columns faster (at least 10%). Values at the top of the
sequence are not reused after being deleted. (When an AUTO_INCREMENT column
is defined as the last column of a multiple-column index, reuse of values
deleted from the top of a sequence does occur.) The AUTO_INCREMENT value ca=
n
be reset with ALTER TABLE or *myisamchk*.
=B7 Dynamic-sized rows are much less fragmented when mixing deletes
with updates and inserts. This is done by automatically combining adjacent
deleted blocks and by extending blocks if the next block is deleted.
=B7 If a table has no free blocks in the middle of the data file, y=
ou
can INSERT new rows into it at the same time that other threads are reading
from the table. (These are known as concurrent inserts.) A free block can
occur as a result of deleting rows or an update of a dynamic length row wit=
h
more data than its current contents. When all free blocks are used up
(filled in), future inserts become concurrent again. See Section 7.3.3,
=93Concurrent Inserts=94
=B7 You can put the data file and index file on different directori=
es
to get more speed with the DATA DIRECTORY and INDEX DIRECTORY table options
to CREATE TABLE. See Section 13.1.5, =93CREATE TABLE
Syntax=94
=B7 BLOB and TEXT columns can be indexed.
=B7 NULL values are allowed in indexed columns. This takes 0=961 by=
tes
per key.
=B7 Each character column can have a different character set.
See Chapter 10,
*Character Set Support*
=B7 There is a flag in the MyISAM index file that indicates whether
the table was closed correctly. If *mysqld* is started with the
--myisam-recover option, MyISAM tables are automatically checked when
opened, and are repaired if the table wasn't closed properly.
=B7 *myisamchk* marks tables as checked if you run it with the
--update-state option. *myisamchk --fast* checks only those tables that
don't have this mark.
=B7 *myisamchk --analyze* stores statistics for portions of keys, a=
s
well as for entire keys.
=B7 *myisampack* can pack BLOB and VARCHAR columns.
*MyISAM also supports the following features: *
=B7 Support for a true VARCHAR type; a VARCHAR column starts with a
length stored in one or two bytes.
=B7 Tables with VARCHAR columns may have fixed or dynamic row lengt=
h.
=B7 The sum of the lengths of the VARCHAR and CHAR columns in a tab=
le
may be up to 64KB.
=B7 A hashed computed index can be used for UNIQUE. This allows you=
to
have UNIQUE on any combination of columns in a table. (However, you cannot
search on a UNIQUE computed index.)
On Fri, Jul 16, 2010 at 11:16 AM, P.R.Karthik
> Hi,
>
> I am newbie to mysql can i know the advantages of myisam storage engine
> and some of its special features.
>
> --
> Regards
> Karthik.P.R
> karthik@mafiree.com
>
--=20
Best Regards,
Prabhat Kumar
MySQL DBA
My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat
--000feae9ac769f3ace048b7c660c--
> From: "P.R.Karthik"
>
> I am newbie to mysql can i know the advantages of myisam storage
> engine
> and some of its special features.
Works better with file-based incremental backup systems.
With InnoDB, you end up backing up a humongous file of all your InnoDB
tables, even if only one byte in one field of one table of one
database was touched.
----------------
There are only two ways to look at life: One is as if nothing is a
miracle. The other is as if everything is a miracle. -- Albert Einstein
:::: Jan Steinman, EcoReality Co-op ::::
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
--000e0cd53776503d8c048bbde9b8
Content-Type: text/plain; charset=ISO-8859-1
Since everyone keeps bringing up Innodb's shared tablespace, I will point
out that Innodb has a "file-per-table" option where each table has it own
tablespace. This helps with management issues. While there is still a
central datafile it doesn't contain table data and is much smaller than if
you used a centralized table space.
keith
On Sat, Jul 17, 2010 at 12:37 PM, Jan Steinman
> From: "P.R.Karthik"
>>
>>
>> I am newbie to mysql can i know the advantages of myisam storage engine
>> and some of its special features.
>>
>
> Works better with file-based incremental backup systems.
>
> With InnoDB, you end up backing up a humongous file of all your InnoDB
> tables, even if only one byte in one field of one table of one database was
> touched.
>
> ----------------
> There are only two ways to look at life: One is as if nothing is a miracle.
> The other is as if everything is a miracle. -- Albert Einstein
> :::: Jan Steinman, EcoReality Co-op ::::
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=bmurphy@paragon-cs.com
>
>
--
Chief Training Officer
Paragon Consulting Services
850-637-3877
--000e0cd53776503d8c048bbde9b8--
In addition, the binlogs function as incremental backups. With a full backu=
p + binlogs, you can do point-in-time recovery to any moment you choose.
-----Original Message-----
From: Keith Murphy [mailto:bmurphy@paragon-cs.com]
Sent: Monday, July 19, 2010 6:55 AM
To: Jan Steinman
Cc: mysql@lists.mysql.com
Subject: Re: Myisam advantages
Since everyone keeps bringing up Innodb's shared tablespace, I will point
out that Innodb has a "file-per-table" option where each table has it own
tablespace. This helps with management issues. While there is still a
central datafile it doesn't contain table data and is much smaller than if
you used a centralized table space.
keith
On Sat, Jul 17, 2010 at 12:37 PM, Jan Steinman
> From: "P.R.Karthik"
>>
>>
>> I am newbie to mysql can i know the advantages of myisam storage engine
>> and some of its special features.
>>
>
> Works better with file-based incremental backup systems.
>
> With InnoDB, you end up backing up a humongous file of all your InnoDB
> tables, even if only one byte in one field of one table of one database w=
as
> touched.
>
> ----------------
> There are only two ways to look at life: One is as if nothing is a miracl=
e.
> The other is as if everything is a miracle. -- Albert Einstein
> :::: Jan Steinman, EcoReality Co-op ::::
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dbmurphy@paragon-cs.com
>
>
--
Chief Training Officer
Paragon Consulting Services
850-637-3877
This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
On Thu, Jul 15, 2010 at 10:46 PM, P.R.Karthik
> =A0Hi,
>
> =A0I am newbie to mysql can i know the advantages of myisam storage engin=
e
> and some of its special features.
>
> --
> Regards
> Karthik.P.R
> karthik@mafiree.com
Special features:
1. Not atomic.
2. No consistency.
3. Horrible isolation
4. Durable on good days.
In short it can easily eat your data. If you love your data and treat
it well it will love you back. There are very few valid uses for
MyISAM (full text and spatial are not valid, use sphinx and PostGIS
respectively)
--=20
Rob Wultsch
wultsch@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg