Re: MyISAM vs InnoDB - Index choice and Huge performance difference
Tnx for your precious advice. Do you know if there is some documentation somewhere on the net with the most known Innodb performance limitations ? so I can avoid to teast again each query and doing different optimization. I tried the original query with 5.0 and 5.1... same results... Tnx again Edoardo Sebastian Mendel ha scritto: joe schrieb: U might want to try seting you index to calldate, disposition or calldate, day, disposition ... and depending on your MySQL version: (to circumvent possible limitations in InnoDB with your MySQL version) you could try WHERE calldate >= '2007-07-01 00:00:00' AND calldate <= '2007-07-30 23:59:59' or SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM ( SELECT DATE_FORMAT(calldate, '%d') AS day, num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' ) GROUP BY day, disposition; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, November 25, 2007 10:03 PM To: Edoardo Serra Cc: mysql@lists.mysql.com Subject: Re: MyISAM vs InnoDB - Index choice and Huge performance difference just want to take a note on 4Gbytes What kernel u use? 4Gbytes or bigger means nothing on your MySQL, because if your kernel is not compiled using correct patch or simply use CentOS/RHEL, then your MySQl will limited to use up to 2Gbytes only, so 4Gbytes --> 2Gbytes is useless On 11/25/07, Edoardo Serra <[EMAIL PROTECTED]> wrote: Hi everybody, I have a MySQL database with MyISAM tables. As we're experiencing a lot of locking-related problems I decided to migrate to InnoDB. Our database is composed by a lot of small tables (1.000 - 10.000 rows) and a huge table containing 7.000.000 rows, this big table is a sort of a log of our subscriber's phone calls. I have a query I often run on the big table that is performing really poorly on InnoDB (18mins Innodb vs 29secs MyISAM) This is my query SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' GROUP BY day, disposition; Using EXPLAIN I see that the query on the InnoDB table isn't using indexes at all but the one on MyISAM table (same structure, same indexes, same data) is choosing the correct index. Here are my EXPLAIN results MyISAM: id: 1 select_type: SIMPLE table: cdr type: range possible_keys: calldate,date-context-cause key: calldate key_len: 8 ref: NULL rows: 697688 Extra: Using where; Using temporary; Using filesort Innodb: id: 1 select_type: SIMPLE table: cdr_innodb type: ALL possible_keys: calldate,date-context-cause key: NULL key_len: NULL ref: NULL rows: 5035407 Extra: Using where; Using temporary; Using filesort As you can see, Innodb doesn't use the calldate index (which seems to me the correct choice) Probably I can solve this query performance problem with an index on calldate, disposition but I'd like to understand deeper the causes of that to avoide re-analizing every query ad retry to optimize it as I did with MyISAM. I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a config file taken from MySQL sources optimized for innodb and 4G RAM (my-innodb-heavy-4G.cnf) I followed some simple optimization rules as putting InnoDB data dir on a different array of disks on a different channel, etc... Im using MySQL 5.0.32 on a Debian stable. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB - Index choice and Huge performance difference
Yes, you're right, with that index query is flying... then I used DAYOFMONTH(calldate) instead of DATE_FORMAT(calldate, '%d'), it gives an extra performance gain of 5x tnx for help joe ha scritto: U might want to try seting you index to calldate, disposition -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, November 25, 2007 10:03 PM To: Edoardo Serra Cc: mysql@lists.mysql.com Subject: Re: MyISAM vs InnoDB - Index choice and Huge performance difference just want to take a note on 4Gbytes What kernel u use? 4Gbytes or bigger means nothing on your MySQL, because if your kernel is not compiled using correct patch or simply use CentOS/RHEL, then your MySQl will limited to use up to 2Gbytes only, so 4Gbytes --> 2Gbytes is useless On 11/25/07, Edoardo Serra <[EMAIL PROTECTED]> wrote: Hi everybody, I have a MySQL database with MyISAM tables. As we're experiencing a lot of locking-related problems I decided to migrate to InnoDB. Our database is composed by a lot of small tables (1.000 - 10.000 rows) and a huge table containing 7.000.000 rows, this big table is a sort of a log of our subscriber's phone calls. I have a query I often run on the big table that is performing really poorly on InnoDB (18mins Innodb vs 29secs MyISAM) This is my query SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' GROUP BY day, disposition; Using EXPLAIN I see that the query on the InnoDB table isn't using indexes at all but the one on MyISAM table (same structure, same indexes, same data) is choosing the correct index. Here are my EXPLAIN results MyISAM: id: 1 select_type: SIMPLE table: cdr type: range possible_keys: calldate,date-context-cause key: calldate key_len: 8 ref: NULL rows: 697688 Extra: Using where; Using temporary; Using filesort Innodb: id: 1 select_type: SIMPLE table: cdr_innodb type: ALL possible_keys: calldate,date-context-cause key: NULL key_len: NULL ref: NULL rows: 5035407 Extra: Using where; Using temporary; Using filesort As you can see, Innodb doesn't use the calldate index (which seems to me the correct choice) Probably I can solve this query performance problem with an index on calldate, disposition but I'd like to understand deeper the causes of that to avoide re-analizing every query ad retry to optimize it as I did with MyISAM. I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a config file taken from MySQL sources optimized for innodb and 4G RAM (my-innodb-heavy-4G.cnf) I followed some simple optimization rules as putting InnoDB data dir on a different array of disks on a different channel, etc... Im using MySQL 5.0.32 on a Debian stable. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB - Index choice and Huge performance difference
Tnx for your interest # uname -a Linux corona 2.6.18-5-amd64 #1 SMP Thu May 31 23:51:05 UTC 2007 x86_64 GNU/Linux 64 bit shouldn't have problems in using 4gb of ram .. right ? [EMAIL PROTECTED] ha scritto: just want to take a note on 4Gbytes What kernel u use? 4Gbytes or bigger means nothing on your MySQL, because if your kernel is not compiled using correct patch or simply use CentOS/RHEL, then your MySQl will limited to use up to 2Gbytes only, so 4Gbytes --> 2Gbytes is useless On 11/25/07, Edoardo Serra <[EMAIL PROTECTED]> wrote: Hi everybody, I have a MySQL database with MyISAM tables. As we're experiencing a lot of locking-related problems I decided to migrate to InnoDB. Our database is composed by a lot of small tables (1.000 - 10.000 rows) and a huge table containing 7.000.000 rows, this big table is a sort of a log of our subscriber's phone calls. I have a query I often run on the big table that is performing really poorly on InnoDB (18mins Innodb vs 29secs MyISAM) This is my query SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' GROUP BY day, disposition; Using EXPLAIN I see that the query on the InnoDB table isn't using indexes at all but the one on MyISAM table (same structure, same indexes, same data) is choosing the correct index. Here are my EXPLAIN results MyISAM: id: 1 select_type: SIMPLE table: cdr type: range possible_keys: calldate,date-context-cause key: calldate key_len: 8 ref: NULL rows: 697688 Extra: Using where; Using temporary; Using filesort Innodb: id: 1 select_type: SIMPLE table: cdr_innodb type: ALL possible_keys: calldate,date-context-cause key: NULL key_len: NULL ref: NULL rows: 5035407 Extra: Using where; Using temporary; Using filesort As you can see, Innodb doesn't use the calldate index (which seems to me the correct choice) Probably I can solve this query performance problem with an index on calldate, disposition but I'd like to understand deeper the causes of that to avoide re-analizing every query ad retry to optimize it as I did with MyISAM. I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a config file taken from MySQL sources optimized for innodb and 4G RAM (my-innodb-heavy-4G.cnf) I followed some simple optimization rules as putting InnoDB data dir on a different array of disks on a different channel, etc... Im using MySQL 5.0.32 on a Debian stable. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB - Index choice and Huge performance difference
joe schrieb: > U might want to try seting you index to calldate, disposition or calldate, day, disposition ... and depending on your MySQL version: (to circumvent possible limitations in InnoDB with your MySQL version) you could try WHERE calldate >= '2007-07-01 00:00:00' AND calldate <= '2007-07-30 23:59:59' or SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM ( SELECT DATE_FORMAT(calldate, '%d') AS day, num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' ) GROUP BY day, disposition; > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Sunday, November 25, 2007 10:03 PM > To: Edoardo Serra > Cc: mysql@lists.mysql.com > Subject: Re: MyISAM vs InnoDB - Index choice and Huge performance difference > > just want to take a note on 4Gbytes > > What kernel u use? > 4Gbytes or bigger means nothing on your MySQL, because if your kernel is not > compiled using correct patch or simply use CentOS/RHEL, then your MySQl will > limited to use up to 2Gbytes only, so 4Gbytes --> 2Gbytes is useless > > > On 11/25/07, Edoardo Serra <[EMAIL PROTECTED]> wrote: >> Hi everybody, >> I have a MySQL database with MyISAM tables. >> >> As we're experiencing a lot of locking-related problems I decided to >> migrate to InnoDB. >> >> Our database is composed by a lot of small tables (1.000 - 10.000 >> rows) and a huge table containing 7.000.000 rows, this big table is a >> sort of a log of our subscriber's phone calls. >> >> I have a query I often run on the big table that is performing really >> poorly on InnoDB (18mins Innodb vs 29secs MyISAM) >> >> This is my query >> >> SELECT >> DATE_FORMAT(calldate, '%d') AS day, >> count(*) AS num, >> disposition >> FROM cdr >> WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' >> GROUP BY day, disposition; >> >> Using EXPLAIN I see that the query on the InnoDB table isn't using >> indexes at all but the one on MyISAM table (same structure, same >> indexes, same data) is choosing the correct index. >> >> Here are my EXPLAIN results >> >> MyISAM: >> id: 1 >>select_type: SIMPLE >> table: cdr >> type: range >> possible_keys: calldate,date-context-cause >>key: calldate >>key_len: 8 >>ref: NULL >> rows: 697688 >> Extra: Using where; Using temporary; Using filesort >> >> Innodb: >> id: 1 >>select_type: SIMPLE >> table: cdr_innodb >> type: ALL >> possible_keys: calldate,date-context-cause >>key: NULL >>key_len: NULL >>ref: NULL >> rows: 5035407 >> Extra: Using where; Using temporary; Using filesort >> >> As you can see, Innodb doesn't use the calldate index (which seems to >> me the correct choice) >> >> Probably I can solve this query performance problem with an index on >> calldate, disposition but I'd like to understand deeper the causes of >> that to avoide re-analizing every query ad retry to optimize it as I >> did with MyISAM. >> >> I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a >> config file taken from MySQL sources optimized for innodb and 4G RAM >> (my-innodb-heavy-4G.cnf) >> >> I followed some simple optimization rules as putting InnoDB data dir >> on a different array of disks on a different channel, etc... >> >> Im using MySQL 5.0.32 on a Debian stable. >> >> Tnx in advance for help >> >> Regards >> >> Edoardo Serra >> WeBRainstorm S.r.l. >> >> -- >> MySQL General Mailing List >> For list archives: http://lists.mysql.com/mysql To unsubscribe: >> http://lists.mysql.com/[EMAIL PROTECTED] >> >> > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM vs InnoDB - Index choice and Huge performance difference
U might want to try seting you index to calldate, disposition -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, November 25, 2007 10:03 PM To: Edoardo Serra Cc: mysql@lists.mysql.com Subject: Re: MyISAM vs InnoDB - Index choice and Huge performance difference just want to take a note on 4Gbytes What kernel u use? 4Gbytes or bigger means nothing on your MySQL, because if your kernel is not compiled using correct patch or simply use CentOS/RHEL, then your MySQl will limited to use up to 2Gbytes only, so 4Gbytes --> 2Gbytes is useless On 11/25/07, Edoardo Serra <[EMAIL PROTECTED]> wrote: > Hi everybody, > I have a MySQL database with MyISAM tables. > > As we're experiencing a lot of locking-related problems I decided to > migrate to InnoDB. > > Our database is composed by a lot of small tables (1.000 - 10.000 > rows) and a huge table containing 7.000.000 rows, this big table is a > sort of a log of our subscriber's phone calls. > > I have a query I often run on the big table that is performing really > poorly on InnoDB (18mins Innodb vs 29secs MyISAM) > > This is my query > > SELECT > DATE_FORMAT(calldate, '%d') AS day, > count(*) AS num, > disposition > FROM cdr > WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' > GROUP BY day, disposition; > > Using EXPLAIN I see that the query on the InnoDB table isn't using > indexes at all but the one on MyISAM table (same structure, same > indexes, same data) is choosing the correct index. > > Here are my EXPLAIN results > > MyISAM: > id: 1 >select_type: SIMPLE > table: cdr > type: range > possible_keys: calldate,date-context-cause >key: calldate >key_len: 8 >ref: NULL > rows: 697688 > Extra: Using where; Using temporary; Using filesort > > Innodb: > id: 1 >select_type: SIMPLE > table: cdr_innodb > type: ALL > possible_keys: calldate,date-context-cause >key: NULL >key_len: NULL >ref: NULL > rows: 5035407 > Extra: Using where; Using temporary; Using filesort > > As you can see, Innodb doesn't use the calldate index (which seems to > me the correct choice) > > Probably I can solve this query performance problem with an index on > calldate, disposition but I'd like to understand deeper the causes of > that to avoide re-analizing every query ad retry to optimize it as I > did with MyISAM. > > I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a > config file taken from MySQL sources optimized for innodb and 4G RAM > (my-innodb-heavy-4G.cnf) > > I followed some simple optimization rules as putting InnoDB data dir > on a different array of disks on a different channel, etc... > > Im using MySQL 5.0.32 on a Debian stable. > > Tnx in advance for help > > Regards > > Edoardo Serra > WeBRainstorm S.r.l. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- Regards, Ady Wicaksono Email: ady.wicaksono at gmail.com http://adywicaksono.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.6 - Release Date: 11/24/2007 12:00 AM No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.6 - Release Date: 11/24/2007 12:00 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB - Index choice and Huge performance difference
just want to take a note on 4Gbytes What kernel u use? 4Gbytes or bigger means nothing on your MySQL, because if your kernel is not compiled using correct patch or simply use CentOS/RHEL, then your MySQl will limited to use up to 2Gbytes only, so 4Gbytes --> 2Gbytes is useless On 11/25/07, Edoardo Serra <[EMAIL PROTECTED]> wrote: > Hi everybody, > I have a MySQL database with MyISAM tables. > > As we're experiencing a lot of locking-related problems I decided to > migrate to InnoDB. > > Our database is composed by a lot of small tables (1.000 - 10.000 rows) > and a huge table containing 7.000.000 rows, this big table is a sort of > a log of our subscriber's phone calls. > > I have a query I often run on the big table that is performing really > poorly on InnoDB (18mins Innodb vs 29secs MyISAM) > > This is my query > > SELECT > DATE_FORMAT(calldate, '%d') AS day, > count(*) AS num, > disposition > FROM cdr > WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' > GROUP BY day, disposition; > > Using EXPLAIN I see that the query on the InnoDB table isn't using > indexes at all but the one on MyISAM table (same structure, same > indexes, same data) is choosing the correct index. > > Here are my EXPLAIN results > > MyISAM: > id: 1 >select_type: SIMPLE > table: cdr > type: range > possible_keys: calldate,date-context-cause >key: calldate >key_len: 8 >ref: NULL > rows: 697688 > Extra: Using where; Using temporary; Using filesort > > Innodb: > id: 1 >select_type: SIMPLE > table: cdr_innodb > type: ALL > possible_keys: calldate,date-context-cause >key: NULL >key_len: NULL >ref: NULL > rows: 5035407 > Extra: Using where; Using temporary; Using filesort > > As you can see, Innodb doesn't use the calldate index (which seems to me > the correct choice) > > Probably I can solve this query performance problem with an index on > calldate, disposition but I'd like to understand deeper the causes of > that to avoide re-analizing every query ad retry to optimize it as I did > with MyISAM. > > I have got a Xeon quad core with SAS disks and 4 GB of RAM > I'm using a config file taken from MySQL sources optimized for innodb > and 4G RAM (my-innodb-heavy-4G.cnf) > > I followed some simple optimization rules as putting InnoDB data dir on > a different array of disks on a different channel, etc... > > Im using MySQL 5.0.32 on a Debian stable. > > Tnx in advance for help > > Regards > > Edoardo Serra > WeBRainstorm S.r.l. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- Regards, Ady Wicaksono Email: ady.wicaksono at gmail.com http://adywicaksono.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM vs InnoDB - Index choice and Huge performance difference
Hi everybody, I have a MySQL database with MyISAM tables. As we're experiencing a lot of locking-related problems I decided to migrate to InnoDB. Our database is composed by a lot of small tables (1.000 - 10.000 rows) and a huge table containing 7.000.000 rows, this big table is a sort of a log of our subscriber's phone calls. I have a query I often run on the big table that is performing really poorly on InnoDB (18mins Innodb vs 29secs MyISAM) This is my query SELECT DATE_FORMAT(calldate, '%d') AS day, count(*) AS num, disposition FROM cdr WHERE calldate BETWEEN '2007-07-01 00:00:00' AND '2007-07-30 23:59:59' GROUP BY day, disposition; Using EXPLAIN I see that the query on the InnoDB table isn't using indexes at all but the one on MyISAM table (same structure, same indexes, same data) is choosing the correct index. Here are my EXPLAIN results MyISAM: id: 1 select_type: SIMPLE table: cdr type: range possible_keys: calldate,date-context-cause key: calldate key_len: 8 ref: NULL rows: 697688 Extra: Using where; Using temporary; Using filesort Innodb: id: 1 select_type: SIMPLE table: cdr_innodb type: ALL possible_keys: calldate,date-context-cause key: NULL key_len: NULL ref: NULL rows: 5035407 Extra: Using where; Using temporary; Using filesort As you can see, Innodb doesn't use the calldate index (which seems to me the correct choice) Probably I can solve this query performance problem with an index on calldate, disposition but I'd like to understand deeper the causes of that to avoide re-analizing every query ad retry to optimize it as I did with MyISAM. I have got a Xeon quad core with SAS disks and 4 GB of RAM I'm using a config file taken from MySQL sources optimized for innodb and 4G RAM (my-innodb-heavy-4G.cnf) I followed some simple optimization rules as putting InnoDB data dir on a different array of disks on a different channel, etc... Im using MySQL 5.0.32 on a Debian stable. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index usage - MyISAM vs InnoDB
Hi! Comments inline. Edoardo Serra wrote: SELECT sum(usercost) FROM cdr WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59' If I run it on the MyISAM table, MySQL choose the right index (the one on the calldate column) and the query is fast enough If I run it on the InnoDB table, MySQL uses no index even if an EXPLAIN query tells me that 'calldate' is between the available indexes Here are my EXPLAIN results mysql> EXPLAIN SELECT sum(usercost) FROM cdr_innodb WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59'; ++-+---+--+-+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+-+--+-+--+-+-+ | 1 | SIMPLE | cdr | ALL | calldate,date-context-cause | NULL | NULL| NULL | 5016758 | Using where | ++-+---+--+-+--+-+--+-+-+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT sum(usercost) FROM cdr_myisam WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59'; ++-+---+---+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+-+--+-+--++-+ | 1 | SIMPLE | cdr | range | calldate,date-context-cause | calldate | 8 | NULL | 772050 | Using where | ++-+---+---+-+--+-+--++-+ 1 row in set (0.11 sec) Another strange thing is that the EXPLAIN on InnoDB says the table has 5016758 rows but a SELECT count(*) returns 4999347 rows (which is the correct number) The rows returned in EXPLAIN SELECT (and SHOW TABLE STATUS) for InnoDB tables is an estimate. For MyISAM, it is the actual number of rows in the table. This is because InnoDB has to track a version for each row in the table (for transactional isolation), and MyISAM does not, which makes it much easier to just have a simple row count for the table. This estimate of rows returned is what is used by the optimizer to determine what execution plan is optimal for this particular query. In this case, there are approximately 772K out of 5M rows which meet the WHERE condition -- or about 15% of the total number of rows in the table. There is a certain threshold, where above it the optimizer will choose to do a sequential table scan of the data, versus do many random seeks into memory or disk. It seems that you are hovering around the threshold for where the optimizer chooses to do a sequential table scan (InnoDB) vs a range operation on a btree with lookups into the data file for each matched row in the index (MyISAM). The difference in returning an estimate vs. the actual row count *might* be the cause of the difference in execution plans. Or, it could have something to do with the weights that the optimizer chooses to place on bookmark lookups in MyISAM vs a quick table scan in InnoDB. I'd be interested to see what the difference in *performance* is? Also, in *either* engine, if you are executing this particular query a *lot*, the best thing for you to do would be to put the index on (calldate, usercost) so that you have a covering index available to complete the query. Cheers! Jay Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index usage - MyISAM vs InnoDB
Hi guys, I'm moving a database to InnoDB because I need some transaction related features but I'm having big problems with perrformances. I have a big table with 5mln rows on which I need to run some SELECTs. It's the Call Detail Record of a telco, so each record has a 'calldate' field with an index on it (it's a non unique index) I have the same table in InnoDB and MyISAM storage engines. I have this simple query: SELECT sum(usercost) FROM cdr WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59' If I run it on the MyISAM table, MySQL choose the right index (the one on the calldate column) and the query is fast enough If I run it on the InnoDB table, MySQL uses no index even if an EXPLAIN query tells me that 'calldate' is between the available indexes Here are my EXPLAIN results mysql> EXPLAIN SELECT sum(usercost) FROM cdr_innodb WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59'; ++-+---+--+-+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+--+-+--+-+--+-+-+ | 1 | SIMPLE | cdr | ALL | calldate,date-context-cause | NULL | NULL| NULL | 5016758 | Using where | ++-+---+--+-+--+-+--+-+-+ 1 row in set (0.00 sec) mysql> EXPLAIN SELECT sum(usercost) FROM cdr_myisam WHERE calldate BETWEEN '2007-06-01 00:00:00' AND '2007-06-30 23:59:59'; ++-+---+---+-+--+-+--++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+-+--+-+--++-+ | 1 | SIMPLE | cdr | range | calldate,date-context-cause | calldate | 8 | NULL | 772050 | Using where | ++-+---+---+-+--+-+--++-+ 1 row in set (0.11 sec) Another strange thing is that the EXPLAIN on InnoDB says the table has 5016758 rows but a SELECT count(*) returns 4999347 rows (which is the correct number) Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 7 Nov 2006, at 12:35, Jochem van Dieten wrote: On 11/6/06, Leandro Guimarães Faria Corcete DUTRA wrote: Em Thu, 02 Nov 2006 10:22:18 -0800, Jochem van Dieten escreveu: PostgreSQL supports 2 phase commit. IIRC except for transaction interleaving, join and suspend/resume it supports XA. I think that puts it about on par with Ingres and Firebird. I would have to analyze better, but I think you are mistaken, sadly. Please share your analysis with us. Jochem From the Handbook Postgresql 8.1 there are PREPARE TRANSACTION transaction_id COMMIT PREPARED transaction_id ROLLBACK PREPARED transaction_id and it states PREPARE TRANSACTION -- prepare the current transaction for two-phase commit - -- Viele Grüße, Lars Heidieker [EMAIL PROTECTED] http://paradoxon.info - Mystische Erklärungen. Die mystischen Erklärungen gelten für tief; die Wahrheit ist, dass sie noch nicht einmal oberflächlich sind. -- Friedrich Nietzsche -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (Darwin) iD8DBQFFUIXPDAkIK9aNPuIRAjBnAJ92IupcD1/yAcvD88IW2szNieCg0gCgggis CJQvtMAlz6p3EWs2cc/ZstE= =IDBz -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
On 11/6/06, Leandro Guimarães Faria Corcete DUTRA wrote: Em Thu, 02 Nov 2006 10:22:18 -0800, Jochem van Dieten escreveu: PostgreSQL supports 2 phase commit. IIRC except for transaction interleaving, join and suspend/resume it supports XA. I think that puts it about on par with Ingres and Firebird. I would have to analyze better, but I think you are mistaken, sadly. Please share your analysis with us. Jochem
Re: MyISAM vs InnoDB
> > On two-phase commits? I guess it's the IB 6 docs where you have to read > > that, or get a copy of Helen Borries Firebird book. Get a copy of the > > IBPhoenix CD that includes docs. > > > > The Firebird project itself has no full documentation yet - it's being > > worked on. > > Hm, do you mean 2PC are only documented in old IB6 stuff? Eh, no - it's documented in the available Firebird documentation. I'm saying that the available Firebird documentation is either not free (but complete) or not yet complete (but being worked on by the Firebird Documentation sub-project). Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Thu, 02 Nov 2006 10:22:18 -0800, Jochem van Dieten escreveu: > PostgreSQL supports 2 phase commit. IIRC except for transaction > interleaving, join and suspend/resume it supports XA. I think that puts it > about on par with Ingres and Firebird. I would have to analyze better, but I think you are mistaken, sadly. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Fri, 03 Nov 2006 09:18:21 +0100, Martijn Tonies escreveu: > On two-phase commits? I guess it's the IB 6 docs where you have to read > that, or get a copy of Helen Borries Firebird book. Get a copy of the > IBPhoenix CD that includes docs. > > The Firebird project itself has no full documentation yet - it's being > worked on. Hm, do you mean 2PC are only documented in old IB6 stuff? -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
> > InterBase had two-phase commits ages ago, Firebird inherited it. > > > > If there's anything specific you want to know, ask > > I *am* asking — where is the specific piece of documentation? On two-phase commits? I guess it's the IB 6 docs where you have to read that, or get a copy of Helen Borries Firebird book. Get a copy of the IBPhoenix CD that includes docs. The Firebird project itself has no full documentation yet - it's being worked on. > Because if you don’t read MySQL’s documentation attentively, it gives > you the impression everything’s A-OK with XA. And it’s not. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Thu, 02 Nov 2006 17:40:44 +0100, Martijn Tonies escreveu: > InterBase had two-phase commits ages ago, Firebird inherited it. > > If there's anything specific you want to know, ask I *am* asking — where is the specific piece of documentation? Because if you don’t read MySQL’s documentation attentively, it gives you the impression everything’s A-OK with XA. And it’s not. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Thu, 02 Nov 2006 17:30:14 +0100, Martijn Tonies escreveu: > Falcon has a transactional storage engine, including Foreign > Keys (Jim wouldn't do a database without em) Obviouſly. > MGA Ma ze? -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
On 11/2/06, Leandro Guimarães Faria Corcete DUTRA wrote: Em Wed, 01 Nov 2006 09:34:05 -0600, mos escreveu: > Is there a better open source database out there for that amount of data? Several. MySQL's own MaxDB, PostgreSQL, Firebird if you are into Borland stuff, Ingres if you need XA distributed transactions. I usually recommend PostgreSQL, or Ingres if two-phase commits are needed. PostgreSQL supports 2 phase commit. IIRC except for transaction interleaving, join and suspend/resume it supports XA. I think that puts it about on par with Ingres and Firebird. Jochem
Re: MyISAM vs InnoDB
> >> Several. MySQL’s own MaxDB, PostgreSQL, Firebird if you are into > >> Borland stuff, Ingres if you need XA distributed transactions. > > > > Firebird isn't Borland > > Granted. But it is (even more) attractive if you are already a Borland > shop. > > > >> I usually recommend PostgreSQL, or Ingres if two-phase commits are > >> needed. > > > > Firebird has two-phase commits. > > Great to know — do you have any pointers? InterBase had two-phase commits ages ago, Firebird inherited it. If there's anything specific you want to know, ask :-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Thu, 02 Nov 2006 15:32:06 +0100, Martijn Tonies escreveu: >> Several. MySQL’s own MaxDB, PostgreSQL, Firebird if you are into >> Borland stuff, Ingres if you need XA distributed transactions. > > Firebird isn't Borland Granted. But it is (even more) attractive if you are already a Borland shop. >> I usually recommend PostgreSQL, or Ingres if two-phase commits are >> needed. > > Firebird has two-phase commits. Great to know — do you have any pointers? -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
>> > > Is there a better open source database out there for that amount of >>data? >> > >> > Several. MySQLâ?Ts own MaxDB, PostgreSQL, Firebird if you are into >> > Borland stuff, Ingres if you need XA distributed transactions. >> >>Firebird isn't Borland :-) >> >> > I usually recommend PostgreSQL, or Ingres if two-phase commits are >> > needed. >> >>Firebird has two-phase commits. > >Martijin, > Can Firebird store 1TB in a single table? All of FB tables are >stored in a single .GDB file, so is it possible to even split the table >across several drives? You can split a database across multiple drives, but you cannot direct a specific table to be in this or that part of the database. As far as I know, this make it possible that internally, tables are split across drives, but you cannot tell Firebird to do it directly. As for 1TB - I must admit I don't know, there's probably a maximum number of rows, not data though. > There is also the Falcon table engine that is coming out for >MySQL, but MySQL AB claims it is *not* a replacement for InnoDb. So why >introduce it? Falcon will be part of MySQL, unline InnoDB, which is licenses [from Oracle]. I would not agree with the remark that Falcon is not a replacement, as far as I understood, Falcon has a transactional storage engine, including Foreign Keys (Jim wouldn't do a database without em), MGA and more... Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
At 08:32 AM 11/2/2006, you wrote: > >> Always use a DBMS, and MySQL is no (proper) DBMS without a > >> transactional backend. There are InnoDB, which is not completely free (needs > >> a proprietary backup tool); BDB, which is deprecated until further notices; > >> and SolidDB, which is still β. > > > > Ok, so your solution is to use something else? > > Well, this is a MySQL list you can use MySQL with InnoDB, if you are > willing to either have a proprietary backup solution or to use a β backend. > > > > Is there a better open source database out there for that amount of data? > > Several. MySQLâs own MaxDB, PostgreSQL, Firebird if you are into > Borland stuff, Ingres if you need XA distributed transactions. Firebird isn't Borland :-) > I usually recommend PostgreSQL, or Ingres if two-phase commits are > needed. Firebird has two-phase commits. Martijin, Can Firebird store 1TB in a single table? All of FB tables are stored in a single .GDB file, so is it possible to even split the table across several drives? There is also the Falcon table engine that is coming out for MySQL, but MySQL AB claims it is *not* a replacement for InnoDb. So why introduce it? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
> >> Always use a DBMS, and MySQL is no (proper) DBMS without a > >> transactional backend. There are InnoDB, which is not completely free (needs > >> a proprietary backup tool); BDB, which is deprecated until further notices; > >> and SolidDB, which is still β. > > > > Ok, so your solution is to use something else? > > Well, this is a MySQL list… you can use MySQL with InnoDB, if you are > willing to either have a proprietary backup solution or to use a β backend. > > > > Is there a better open source database out there for that amount of data? > > Several. MySQL’s own MaxDB, PostgreSQL, Firebird if you are into > Borland stuff, Ingres if you need XA distributed transactions. Firebird isn't Borland :-) > I usually recommend PostgreSQL, or Ingres if two-phase commits are > needed. Firebird has two-phase commits. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Wed, 01 Nov 2006 09:34:05 -0600, mos escreveu: > At 05:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote: >> >> Always use a DBMS, and MySQL is no (proper) DBMS without a >> transactional backend. There are InnoDB, which is not completely free (needs >> a proprietary backup tool); BDB, which is deprecated until further notices; >> and SolidDB, which is still β. > > Ok, so your solution is to use something else? Well, this is a MySQL list… you can use MySQL with InnoDB, if you are willing to either have a proprietary backup solution or to use a β backend. > Is there a better open source database out there for that amount of data? Several. MySQL’s own MaxDB, PostgreSQL, Firebird if you are into Borland stuff, Ingres if you need XA distributed transactions. I usually recommend PostgreSQL, or Ingres if two-phase commits are needed. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
On 11/1/06, mos wrote: At 02:27 PM 11/1/2006, Jochem van Dieten wrote: What is the big deal of a TB? Now, if you get past 20 TB you might want to team up with one of the commercial PostgreSQL supporters (Fujitsu, EnterpriseDB, Greenplum etc.), but Sun even sells appliances for 100 TB PostgreSQL databases. There is a big difference between a 20 TB database and a 20 TB table! Unless you're storing huge blobs, a table of over 1TB will have hundreds of millions of rows (billions?), and that means huge index trees that need to be maintained. Indexes scale with ln(O). The difference between 100 million rows and 1 billion rows is maybe 10%. And if you are worried about your indexes getting asymmetric use a hash index instead of a B-tree. Though realistically you would partition the data and then your indexes get partitioned too. If PostgreSQL can put 20 TB into a table and still have reasonably fast inserts and queries, then I'll take my hat off to them. It can if you design your queries to make use of the indexes and the partitioning. But first I need to see proof that they can accomplish this. So if you have any sites or white papers you'd like to share, go ahead. Keep in mind we're talking about TB tables here, not databases. Google the PostgreSQL and PostGIS mailinglists. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
At 02:27 PM 11/1/2006, Jochem van Dieten wrote: On 11/1/06, mos wrote: Sure, I've thought of those too. But has anyone gotten Firebird to store 700-800gb tables? Can you split Firebird's .gdb file across drives? The main problem with tables of that size is maintaining the index. My upper limit for MySQL is 100 million rows. After that any new rows that are added will take much longer to add because the index tree has to be maintained. I definitely recommend cramming as much memory in the box as humanly possible because indexes of that size will need it. Probably the simplist solution for MySQL is to use Merge tables. I know some people with MySQL, Oracle and MS SQL have terabyte tables, but I haven't heard of other databases storing tables that large. So if you or anyone else has used FireBird or PostgreSQL to store terabyte tables, I'd certainly would be interested in hearing about it. :) What is the big deal of a TB? Now, if you get past 20 TB you might want to team up with one of the commercial PostgreSQL supporters (Fujitsu, EnterpriseDB, Greenplum etc.), but Sun even sells appliances for 100 TB PostgreSQL databases. Jochem Jochem, There is a big difference between a 20 TB database and a 20 TB table! Unless you're storing huge blobs, a table of over 1TB will have hundreds of millions of rows (billions?), and that means huge index trees that need to be maintained. If PostgreSQL can put 20 TB into a table and still have reasonably fast inserts and queries, then I'll take my hat off to them. But first I need to see proof that they can accomplish this. So if you have any sites or white papers you'd like to share, go ahead. Keep in mind we're talking about TB tables here, not databases. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
On 11/1/06, mos wrote: Sure, I've thought of those too. But has anyone gotten Firebird to store 700-800gb tables? Can you split Firebird's .gdb file across drives? The main problem with tables of that size is maintaining the index. My upper limit for MySQL is 100 million rows. After that any new rows that are added will take much longer to add because the index tree has to be maintained. I definitely recommend cramming as much memory in the box as humanly possible because indexes of that size will need it. Probably the simplist solution for MySQL is to use Merge tables. I know some people with MySQL, Oracle and MS SQL have terabyte tables, but I haven't heard of other databases storing tables that large. So if you or anyone else has used FireBird or PostgreSQL to store terabyte tables, I'd certainly would be interested in hearing about it. :) What is the big deal of a TB? Now, if you get past 20 TB you might want to team up with one of the commercial PostgreSQL supporters (Fujitsu, EnterpriseDB, Greenplum etc.), but Sun even sells appliances for 100 TB PostgreSQL databases. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
At 09:35 AM 11/1/2006, Martijn Tonies wrote: >> > MyISAM vs InnoDB ? What is the best to use >> >> Always use a DBMS, and MySQL is no (proper) DBMS without a >> transactional >>backend. There are InnoDB, which is not completely free (needs a proprietary >>backup tool); BDB, which is deprecated until further notices; and SolidDB, >>which >>is still β. >> >> Choose your evil. > >Ok, so your solution is to use something else? Is there a better open >source database out there for that amount of data? Firebird? PostgreSQL? Both are open source and ALWAYS free for whatever usuage, no dual licensing whatsoever. Martijn, Sure, I've thought of those too. But has anyone gotten Firebird to store 700-800gb tables? Can you split Firebird's .gdb file across drives? The main problem with tables of that size is maintaining the index. My upper limit for MySQL is 100 million rows. After that any new rows that are added will take much longer to add because the index tree has to be maintained. I definitely recommend cramming as much memory in the box as humanly possible because indexes of that size will need it. Probably the simplist solution for MySQL is to use Merge tables. I know some people with MySQL, Oracle and MS SQL have terabyte tables, but I haven't heard of other databases storing tables that large. So if you or anyone else has used FireBird or PostgreSQL to store terabyte tables, I'd certainly would be interested in hearing about it. :) Mike Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Francis wrote: Question about MyISAM vs InnoDB ? What is the best to use, I have a large table contain around 10 millons of records. What is the best for me ? Use MyISAM or InnoDB ? Depends VERY much on your application. If any concurrency and/or durability is required then I would forget about MyISAM, as this is not ACID and integrity of the data is at risk. In fact, if the application is suitable for MyISAM and database could be embedded (runs on same machine as application) then I would probably consider SQLite as that is even faster. If concurrency and scaleability is required then I would go PostgreSQL rather tham MySQL, expecially if a large number of heavy users are on at the same time. For a web-based solution on a machine with a single processor/core then InnoDB is a strong contender. Eddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Francis wrote: Question about MyISAM vs InnoDB ? What is the best to use, I have a large table contain around 10 millons of records. What is the best for me ? Use MyISAM or InnoDB ? Depends VERY much on your application. If any concurrency and/or durability is required then I would forget about MyISAM, as this is not ACID and integrity of the data is at risk. In fact, if the application is suitable for MyISAM and database could be embedded (runs on same machine as application) then I would probably consider SQLite as that is even faster. If concurrency and scaleability is required then I would go PostgreSQL rather tham MySQL, expecially if a large number of heavy users are on at the same time. For a web-based solution on a machine with a single processor/core then InnoDB is a strong contender. Eddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
>> > MyISAM vs InnoDB ? What is the best to use >> >> Always use a DBMS, and MySQL is no (proper) DBMS without a >> transactional >>backend. There are InnoDB, which is not completely free (needs a proprietary >>backup tool); BDB, which is deprecated until further notices; and SolidDB, >>which >>is still β. >> >> Choose your evil. > >Ok, so your solution is to use something else? Is there a better open >source database out there for that amount of data? Firebird? PostgreSQL? Both are open source and ALWAYS free for whatever usuage, no dual licensing whatsoever. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle & MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
At 05:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote: Em Tue, 31 Oct 2006 15:24:44 -0500, Francis escreveu: > MyISAM vs InnoDB ? What is the best to use Always use a DBMS, and MySQL is no (proper) DBMS without a transactional backend. There are InnoDB, which is not completely free (needs a proprietary backup tool); BDB, which is deprecated until further notices; and SolidDB, which is still β. Choose your evil. Ok, so your solution is to use something else? Is there a better open source database out there for that amount of data? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
On Nov 1, 2006, at 12:56 PM, Leandro Guimarães Faria Corcete DUTRA wrote: Em Tue, 31 Oct 2006 15:24:44 -0500, Francis escreveu: MyISAM vs InnoDB ? What is the best to use Always use a DBMS, and MySQL is no (proper) DBMS without a transactional backend. There are InnoDB, which is not completely free (needs a proprietary backup tool); BDB, which is deprecated until further notices; and SolidDB, which is still β. Excuse me, but I have to do some advertising in my own interest :) There is also the PrimeBase XT (PBXT), which is also Beta, but is already available as a pluggable storage engine for 5.1 (besides merged code version for MySQL 4.1.21). More information at: http://www.primebase.com/xt For the latest 5.1 version please check out: http://sourceforge.net/ projects/pbxt Best regards, Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Miles Thompson <[EMAIL PROTECTED]> wrote: > At 07:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote: > > .. further notices; and SolidDB, which > >is still β. > > Help this poor English-speaker - what's the symbol you use to describe > SolidDB? I assume it is a "beta" character, since solidDB for MySQL is indeed in beta. See http://dev.soliddb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
At 07:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote: .. further notices; and SolidDB, which is still β. Choose your evil. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] Leandro, Help this poor English-speaker - what's the symbol you use to describe SolidDB? Cheers - Miles Thompson -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.13.21/509 - Release Date: 10/31/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB
Em Tue, 31 Oct 2006 15:24:44 -0500, Francis escreveu: > MyISAM vs InnoDB ? What is the best to use Always use a DBMS, and MySQL is no (proper) DBMS without a transactional backend. There are InnoDB, which is not completely free (needs a proprietary backup tool); BDB, which is deprecated until further notices; and SolidDB, which is still β. Choose your evil. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM vs InnoDB
Hello, Although the number of records is a consideration to weigh in your decision, there are many other (perhaps more important) factors to consider. For example, do you need foreign keys? transactions? row-level locks?...then InnoDB is your choice. Perhaps with more details concerning the characteristics of the data and your applications requirements, folks may be able to better help you with a design choice. Storage limits, efficiency in how space and memory is used, bulk insert speed, etc. might be other factors to consider. Take a look at: http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html Thanks, Jimmy Guerrero MySQL, Inc > -Original Message- > From: Francis [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 31, 2006 2:25 PM > To: mysql@lists.mysql.com > Subject: MyISAM vs InnoDB > > Hi list, > > Question about MyISAM vs InnoDB ? What is the best to > use, I have a large table contain around 10 millons of > records. What is the best for me ? Use MyISAM or InnoDB ? > > Ty for reply ? > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM vs InnoDB
Hi list, Question about MyISAM vs InnoDB ? What is the best to use, I have a large table contain around 10 millons of records. What is the best for me ? Use MyISAM or InnoDB ? Ty for reply ☺ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyIsam Vs InnoDB
Hello. >innodb_log_file_size=10M >innodb_log_buffer_size=1M These variables have too small values, increase them. Follow other recomendations from: http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html Andrew stolarz <[EMAIL PROTECTED]> wrote: > >hello, here are my current setttings: > ># MySQL Server Instance Configuration File ># -- ># Generated by the MySQL Server Instance Configuration Wizard ># ># ># Installation Instructions ># -- ># ># On Linux you can copy this file to /etc/my.cnf to set global options, ># mysql-data-dir/my.cnf to set server-specific options ># (@localstatedir@ for this installation) or to ># ~/.my.cnf to set user-specific options. ># ># On Windows you should keep this file in the installation directory ># of your server (e.g. C:\Program Files\MySQL\MySQL Server 4.1). To ># make sure the server reads the config file use the startup option ># "--defaults-file". ># ># To run run the server from the command line, execute this in a ># command line shell, e.g. ># mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server 4.1\my.ini" ># ># To install the server as a Windows service manually, execute this in a ># command line shell, e.g. ># mysqld --install MySQL41 --defaults-file="C:\Program Files\MySQL\MySQL >Server 4.1\my.ini" ># ># And then execute this in a command line shell to start the server, e.g. ># net start MySQL41 ># ># ># Guildlines for editing this file ># -- ># ># In this file, you can use all long options that the program supports. ># If you want to know the options a program supports, start the program ># with the "--help" option. ># ># More detailed information about the individual options can also be ># found in the manual. ># ># ># CLIENT SECTION ># -- ># ># The following options will be read by MySQL client applications. ># Note that only client applications shipped by MySQL are guaranteed ># to read this section. If you want your own MySQL client program to ># honor these values, you need to specify it as an option during the ># MySQL client library initialization. ># >[client] > >port=3306 > >[mysql] > >default-character-set=latin1 > > ># SERVER SECTION ># -- ># ># The following options will be read by the MySQL Server. Make sure that ># you have installed the server correctly (see above) so it reads this ># file. ># >[mysqld] > ># The TCP/IP Port the MySQL Server will listen on >port=3306 > > >#Path to installation directory. All paths are usually resolved relative to >this. >basedir="C:/Program Files/MySQL/MySQL Server 5.0/" > >#Path to the database root >datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/" > ># The default character set that will be used when a new schema or table is ># created and no character set is defined >default-character-set=latin1 > ># The default storage engine that will be used when create new tables when >default-storage-engine=innodb > ># Set the SQL mode to strict >sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" > ># The maximum amount of concurrent sessions the MySQL server will ># allow. One of these connections will be reserved for a user with ># SUPER privileges to allow the administrator to login even if the ># connection limit has been reached. >max_connections=5 > ># Query cache is used to cache SELECT results and later return them ># without actual executing the same query once again. Having the query ># cache enabled may result in significant speed improvements, if your ># have a lot of identical queries and rarely changing tables. See the ># "Qcache_lowmem_prunes" status variable to check if the current value ># is high enough for your load. ># Note: In case your tables change very often or if your queries are ># textually different every time, the query cache may result in a ># slowdown instead of a performance improvement. >query_cache_size=0 > ># The number of open tables for all threads. Increasing this value ># increases the number of file descriptors that mysqld requires. ># Therefore you have to make sure to set the amount of open files ># allowed to at least 4096 in the variable "open-files-limit" in ># section [mysqld_safe] >table_cache=256 > ># Maximum size for internal (in-memory) temporary tables. If a table ># grows larger than this value, it is automatically converted to disk ># based table This limitation is for a single table. There can be many ># of them. >tmp_table_size=9M > > ># How many threads we should keep in a cache for reuse. When a client ># disconnects, the client's threads are put
Re: MyIsam Vs InnoDB
Is your database connection auto-commit? MyISAM commits everything at once, where InnoDB you can commit whenever you want. You might want to commit at the end of your batch. Also, look at your indexes - indexes make selects fast, but slow down inserts and deletes, and can slow down updates in some situations. David Andrew stolarz wrote: hello, here are my current setttings: # MySQL Server Instance Configuration File # -- # Generated by the MySQL Server Instance Configuration Wizard # # # Installation Instructions # -- # # On Linux you can copy this file to /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options # (@localstatedir@ for this installation) or to # ~/.my.cnf to set user-specific options. # # On Windows you should keep this file in the installation directory # of your server (e.g. C:\Program Files\MySQL\MySQL Server 4.1). To # make sure the server reads the config file use the startup option # "--defaults-file". # # To run run the server from the command line, execute this in a # command line shell, e.g. # mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server 4.1\my.ini" # # To install the server as a Windows service manually, execute this in a # command line shell, e.g. # mysqld --install MySQL41 --defaults-file="C:\Program Files\MySQL\MySQL Server 4.1\my.ini" # # And then execute this in a command line shell to start the server, e.g. # net start MySQL41 # # # Guildlines for editing this file # -- # # In this file, you can use all long options that the program supports. # If you want to know the options a program supports, start the program # with the "--help" option. # # More detailed information about the individual options can also be # found in the manual. # # # CLIENT SECTION # -- # # The following options will be read by MySQL client applications. # Note that only client applications shipped by MySQL are guaranteed # to read this section. If you want your own MySQL client program to # honor these values, you need to specify it as an option during the # MySQL client library initialization. # [client] port=3306 [mysql] default-character-set=latin1 # SERVER SECTION # -- # # The following options will be read by the MySQL Server. Make sure that # you have installed the server correctly (see above) so it reads this # file. # [mysqld] # The TCP/IP Port the MySQL Server will listen on port=3306 #Path to installation directory. All paths are usually resolved relative to this. basedir="C:/Program Files/MySQL/MySQL Server 5.0/" #Path to the database root datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/" # The default character set that will be used when a new schema or table is # created and no character set is defined default-character-set=latin1 # The default storage engine that will be used when create new tables when default-storage-engine=innodb # Set the SQL mode to strict sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION " # The maximum amount of concurrent sessions the MySQL server will # allow. One of these connections will be reserved for a user with # SUPER privileges to allow the administrator to login even if the # connection limit has been reached. max_connections=5 # Query cache is used to cache SELECT results and later return them # without actual executing the same query once again. Having the query # cache enabled may result in significant speed improvements, if your # have a lot of identical queries and rarely changing tables. See the # "Qcache_lowmem_prunes" status variable to check if the current value # is high enough for your load. # Note: In case your tables change very often or if your queries are # textually different every time, the query cache may result in a # slowdown instead of a performance improvement. query_cache_size=0 # The number of open tables for all threads. Increasing this value # increases the number of file descriptors that mysqld requires. # Therefore you have to make sure to set the amount of open files # allowed to at least 4096 in the variable "open-files-limit" in # section [mysqld_safe] table_cache=256 # Maximum size for internal (in-memory) temporary tables. If a table # grows larger than this value, it is automatically converted to disk # based table This limitation is for a single table. There can be many # of them. tmp_table_size=9M # How many threads we should keep in a cache for reuse. When a client # disconnects, the client's threads are put in the cache if there aren't # more than thread_cache_size threads from before. This greatly reduces # the amount of thread creations needed if you have a lot of new # conn
Re: MyIsam Vs InnoDB
hello, here are my current setttings: # MySQL Server Instance Configuration File # -- # Generated by the MySQL Server Instance Configuration Wizard # # # Installation Instructions # -- # # On Linux you can copy this file to /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options # (@localstatedir@ for this installation) or to # ~/.my.cnf to set user-specific options. # # On Windows you should keep this file in the installation directory # of your server (e.g. C:\Program Files\MySQL\MySQL Server 4.1). To # make sure the server reads the config file use the startup option # "--defaults-file". # # To run run the server from the command line, execute this in a # command line shell, e.g. # mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server 4.1\my.ini" # # To install the server as a Windows service manually, execute this in a # command line shell, e.g. # mysqld --install MySQL41 --defaults-file="C:\Program Files\MySQL\MySQL Server 4.1\my.ini" # # And then execute this in a command line shell to start the server, e.g. # net start MySQL41 # # # Guildlines for editing this file # -- # # In this file, you can use all long options that the program supports. # If you want to know the options a program supports, start the program # with the "--help" option. # # More detailed information about the individual options can also be # found in the manual. # # # CLIENT SECTION # -- # # The following options will be read by MySQL client applications. # Note that only client applications shipped by MySQL are guaranteed # to read this section. If you want your own MySQL client program to # honor these values, you need to specify it as an option during the # MySQL client library initialization. # [client] port=3306 [mysql] default-character-set=latin1 # SERVER SECTION # -- # # The following options will be read by the MySQL Server. Make sure that # you have installed the server correctly (see above) so it reads this # file. # [mysqld] # The TCP/IP Port the MySQL Server will listen on port=3306 #Path to installation directory. All paths are usually resolved relative to this. basedir="C:/Program Files/MySQL/MySQL Server 5.0/" #Path to the database root datadir="C:/Program Files/MySQL/MySQL Server 5.0/Data/" # The default character set that will be used when a new schema or table is # created and no character set is defined default-character-set=latin1 # The default storage engine that will be used when create new tables when default-storage-engine=innodb # Set the SQL mode to strict sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" # The maximum amount of concurrent sessions the MySQL server will # allow. One of these connections will be reserved for a user with # SUPER privileges to allow the administrator to login even if the # connection limit has been reached. max_connections=5 # Query cache is used to cache SELECT results and later return them # without actual executing the same query once again. Having the query # cache enabled may result in significant speed improvements, if your # have a lot of identical queries and rarely changing tables. See the # "Qcache_lowmem_prunes" status variable to check if the current value # is high enough for your load. # Note: In case your tables change very often or if your queries are # textually different every time, the query cache may result in a # slowdown instead of a performance improvement. query_cache_size=0 # The number of open tables for all threads. Increasing this value # increases the number of file descriptors that mysqld requires. # Therefore you have to make sure to set the amount of open files # allowed to at least 4096 in the variable "open-files-limit" in # section [mysqld_safe] table_cache=256 # Maximum size for internal (in-memory) temporary tables. If a table # grows larger than this value, it is automatically converted to disk # based table This limitation is for a single table. There can be many # of them. tmp_table_size=9M # How many threads we should keep in a cache for reuse. When a client # disconnects, the client's threads are put in the cache if there aren't # more than thread_cache_size threads from before. This greatly reduces # the amount of thread creations needed if you have a lot of new # connections. (Normally this doesn't give a notable performance # improvement if you have a good thread implementation.) thread_cache_size=8 #*** MyISAM Specific options # The maximum size of the temporary file MySQL is allowed to use while # recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE. # If the file-size would be bigger than th
Re: MyIsam Vs InnoDB
Hello. Without seeing at least your configuration it is difficult to say what's going on. Please, provide your config file. Andrew stolarz <[EMAIL PROTECTED]> wrote: >When I do a bulk import into a MyIsam engine database, I can reach about 2-3 >thousand records imported per second. > >However when I use the InnoDB engine, I am only importing about 30-50 >records per second? > >Am I missing something here? > >its a P4 3 Ghz machine with 1024mb ram. running MySQL 5.0 > >thanks > >Andrew -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyIsam Vs InnoDB
Hello List, When I do a bulk import into a MyIsam engine database, I can reach about 2-3 thousand records imported per second. However when I use the InnoDB engine, I am only importing about 30-50 records per second? Am I missing something here? its a P4 3 Ghz machine with 1024mb ram. running MySQL 5.0 thanks Andrew
Re: Benchmark of MyISAM vs Innodb vs Innod without FKs?!
Hello. Usually only benchmarks will show a real picture for you. Create foreign keys, perform some tests. Then temporary disable FKs using SET FOREIGN_KEY_CHECKS=0 and repeat the performance measurement. Super Smack is a good tool for such kind of analysis. See: http://dev.mysql.com/doc/mysql/en/custom-benchmarks.html Kevin Burton <[EMAIL PROTECTED]> wrote: >ere's some thing I've been thinking about. > >I want to use INNODB without FKs. I don't need or want referential integrity >in my app (due to a schema and performance issue). > >Basically I just create FKs in my OR layer and my app enforces the rules. >The column is still an _ID column so I visually know a FK when I see one but >INNODB doesn't have to do any runtime checks on insert. > >My question is whether INNODB will be faster without them. If so by how >much. If it's close to the speed of MyISAM then I'll be a happy camper. > >Thoughts? > >Kevin -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Benchmark of MyISAM vs Innodb vs Innod without FKs?!
Here's some thing I've been thinking about. I want to use INNODB without FKs. I don't need or want referential integrity in my app (due to a schema and performance issue). Basically I just create FKs in my OR layer and my app enforces the rules. The column is still an _ID column so I visually know a FK when I see one but INNODB doesn't have to do any runtime checks on insert. My question is whether INNODB will be faster without them. If so by how much. If it's close to the speed of MyISAM then I'll be a happy camper. Thoughts? Kevin -- Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://www.feedblog.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table
Eamon Daly wrote: We have a table containing just one column that we use for unique IDs: CREATE TABLE `id_sequence` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) TYPE=MyISAM Watching 'SHOW FULL PROCESSLIST' and reading the slow query log shows the occasional backlog of locks. Has anyone found significant speed increases or better concurrency by switching over to InnoDB for such a table? I've found that MS Access will produce this behaviour ( among others ) when you get a number of clients connected to MyISAM tables. Converting to InnoDB solves it every time. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table
Hello. If you have lots of concurrent updates and selects on the same table, InnoDB usually has better performance. Use the benchmarks to determine what configuration is preferred. Super-smack for example allows you to write very flexible tests. Be aware of different behavior of AUTO_INCREMENT columns in InnoDB. See: http://dev.mysql.com/doc/mysql/en/innodb-auto-increment-column.html "Eamon Daly" <[EMAIL PROTECTED]> wrote: > We have a table containing just one column that we use for > unique IDs: > > CREATE TABLE `id_sequence` ( > `id` int(10) unsigned NOT NULL auto_increment, > PRIMARY KEY (`id`) > ) TYPE=MyISAM > > Watching 'SHOW FULL PROCESSLIST' and reading the slow query > log shows the occasional backlog of locks. Has anyone found > significant speed increases or better concurrency by > switching over to InnoDB for such a table? > > > Eamon Daly > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table
If you have any deletion gaps in your data, the SQL engine puts a full TABLE LOCK on your table while performing inserts to a MyISAM table. You don't have to do it, the engine does it for you. As to your application design, all I am going to say is that if it works for you, that's fine by me. I probably wouldn't pick that design for myself but you seem to have it working so I will "butt out". As to relieving your locking contentions on this table, switching to InnoDB may help but you should try the OPTIMIZE TABLE command first to collapse out any deletion gaps. The only way you will know for sure is to benchmark both methods. Stick with what works best for your data, on your hardware, and under your loading. Shawn Green Database Administrator Unimin Corporation - Spruce Pine "Eamon Daly" <[EMAIL PROTECTED]> wrote on 08/24/2005 01:49:07 PM: > I'm not doing any explicit locking-- these are just straight > INSERTs, and there are no gaps in the table (This is MySQL > 4.0.20-standard-log, by the way). > > As for why, there are several tables which share this PK, > each of which is heavily UPDATEd, and most make use of > INSERT DELAYED. This can't be entirely uncommon: I'm pretty > sure I've seen a similar example in the Cookbook. > > > Eamon Daly > > > > - Original Message - > From: <[EMAIL PROTECTED]> > To: "Eamon Daly" <[EMAIL PROTECTED]> > Cc: > Sent: Wednesday, August 24, 2005 12:05 PM > Subject: Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table > > > > "Eamon Daly" <[EMAIL PROTECTED]> wrote on 08/24/2005 12:40:55 PM: > > > >> We have a table containing just one column that we use for > >> unique IDs: > >> > >> CREATE TABLE `id_sequence` ( > >> `id` int(10) unsigned NOT NULL auto_increment, > >> PRIMARY KEY (`id`) > >> ) TYPE=MyISAM > >> > >> Watching 'SHOW FULL PROCESSLIST' and reading the slow query > >> log shows the occasional backlog of locks. Has anyone found > >> significant speed increases or better concurrency by > >> switching over to InnoDB for such a table? > >> > >> > >> Eamon Daly > >> > >> > > So long as you do not have any deletion gaps in your data, there > > shouldn't be any read locks on this table even while you are appending new > > records. Are you locking against reads or writes. > > > > BTW - is there a great reason why you are generating auto_incremented ID > > values separately from the actual data they identify? I ask this because I > > don't think many people actually USE that kind of table so you may not get > > any responses from your last question. > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine >
Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table
I'm not doing any explicit locking-- these are just straight INSERTs, and there are no gaps in the table (This is MySQL 4.0.20-standard-log, by the way). As for why, there are several tables which share this PK, each of which is heavily UPDATEd, and most make use of INSERT DELAYED. This can't be entirely uncommon: I'm pretty sure I've seen a similar example in the Cookbook. Eamon Daly - Original Message - From: <[EMAIL PROTECTED]> To: "Eamon Daly" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, August 24, 2005 12:05 PM Subject: Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table "Eamon Daly" <[EMAIL PROTECTED]> wrote on 08/24/2005 12:40:55 PM: We have a table containing just one column that we use for unique IDs: CREATE TABLE `id_sequence` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) TYPE=MyISAM Watching 'SHOW FULL PROCESSLIST' and reading the slow query log shows the occasional backlog of locks. Has anyone found significant speed increases or better concurrency by switching over to InnoDB for such a table? Eamon Daly So long as you do not have any deletion gaps in your data, there shouldn't be any read locks on this table even while you are appending new records. Are you locking against reads or writes. BTW - is there a great reason why you are generating auto_incremented ID values separately from the actual data they identify? I ask this because I don't think many people actually USE that kind of table so you may not get any responses from your last question. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. InnoDB for an AUTO_INCREMENT counter table
"Eamon Daly" <[EMAIL PROTECTED]> wrote on 08/24/2005 12:40:55 PM: > We have a table containing just one column that we use for > unique IDs: > > CREATE TABLE `id_sequence` ( > `id` int(10) unsigned NOT NULL auto_increment, > PRIMARY KEY (`id`) > ) TYPE=MyISAM > > Watching 'SHOW FULL PROCESSLIST' and reading the slow query > log shows the occasional backlog of locks. Has anyone found > significant speed increases or better concurrency by > switching over to InnoDB for such a table? > > > Eamon Daly > > So long as you do not have any deletion gaps in your data, there shouldn't be any read locks on this table even while you are appending new records. Are you locking against reads or writes. BTW - is there a great reason why you are generating auto_incremented ID values separately from the actual data they identify? I ask this because I don't think many people actually USE that kind of table so you may not get any responses from your last question. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
MyISAM vs. InnoDB for an AUTO_INCREMENT counter table
We have a table containing just one column that we use for unique IDs: CREATE TABLE `id_sequence` ( `id` int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (`id`) ) TYPE=MyISAM Watching 'SHOW FULL PROCESSLIST' and reading the slow query log shows the occasional backlog of locks. Has anyone found significant speed increases or better concurrency by switching over to InnoDB for such a table? Eamon Daly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB ----- Incorrect key file for table error
"Praveen KS" <[EMAIL PROTECTED]> writes: > In a table of 20,000 records I am frequented with this error: > > Error 1034: > Incorrect key file for table: ''; try to repair it > > Frequency of this error: Three or four times a week. > I am logging the data it was trying to insert or update. After I, > > repair table > > if, I try to insert the same data.. its accepts without getting > corrupted. Your tables are getting corrupted. You might have a software of hardware error. Since you are running an ALPHA release my guess is that you have a software problem. Upgrade mysql to a production release and see if that takes care of your problem. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB ----- Incorrect key file for table error
Hello. >mysql Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686) You have an old MySQL version which contains lots of bugs (it's an alpha!). I strongly recommend you to upgrade to the latest release (4.1.12 now) and use official binaries. >Hi, > > >In a table of 20,000 records I am frequented with this error: > >Error 1034: >Incorrect key file for table: ''; try to repair it > >Frequency of this error: Three or four times a week. >I am logging the data it was trying to insert or update. After I, > >repair table > >if, I try to insert the same data.. its accepts without getting >corrupted. > > > > > >Has this got something to do with concurrent users transacting with the >database? > >We are using the default type i.e MyISAM. InnoDB provides MySQL with a >transaction-safe >(ACID compliant) storage engine with commit, rollback, and crash >recovery capabilities. >Does a change to InnoDB will get rid of this problem? > >How can we make MyISAM ACID complaint? > >Do we have to write it into our application? > > > > > >MySQL version: > >mysql Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686) > >On Red Hat Linux release 8.0 (Psyche) Linux version 2.4.18-14smp >([EMAIL PROTECTED]) (gcc version 3.2 20020903 (Red Hat Linux 8.0 >3.2-7)) "Praveen KS" <[EMAIL PROTECTED]> wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM vs InnoDB ----- Incorrect key file for table error
Hi, In a table of 20,000 records I am frequented with this error: Error 1034: Incorrect key file for table: ''; try to repair it Frequency of this error: Three or four times a week. I am logging the data it was trying to insert or update. After I, repair table if, I try to insert the same data.. its accepts without getting corrupted. Has this got something to do with concurrent users transacting with the database? We are using the default type i.e MyISAM. InnoDB provides MySQL with a transaction-safe (ACID compliant) storage engine with commit, rollback, and crash recovery capabilities. Does a change to InnoDB will get rid of this problem? How can we make MyISAM ACID complaint? Do we have to write it into our application? MySQL version: mysql Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686) On Red Hat Linux release 8.0 (Psyche) Linux version 2.4.18-14smp ([EMAIL PROTECTED]) (gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)) Regards, Praveen. http://travel.indiainfo.com/img/banner-coolwaters.gif"; width="600" height="40" alt="" border="0" usemap="#banner"> http://travel.indiainfo.com/destination/beaches/goa.html";> http://travel.indiainfo.com/packages/beaches/goa-holidayinn.html";> http://travel.indiainfo.com/packages/beaches/goa-majorda.html";> http://travel.indiainfo.com/packages/beaches/goa-oldanchor.html";> http://travel.indiainfo.com/packages/beaches/goa-kamath.html";> http://travel.indiainfo.com/booknow.html";>
Re: Performance of Joining Tables From Different Storage Engines -- Re: MyISAM vs. InnoDB for heavily-indexed, read-mostly data
Thanks Mike. I think testing ultimately determines how efficient heterogeneous engine joins are. I just wanted to know if someone had issues with them in a heavy-load environment. --- mos <[EMAIL PROTECTED]> wrote: > At 04:00 PM 12/21/2004, Homam S.A. wrote: > >Thanks Mike for the information. Yes, Emmett > mentioned > >the same thing in a private message, and it seems > that > >MyISAM is exactly what I'm looking for: a > >heavily-indexed large table that will be also > indexed > >for full-text search and built off-line -- no > updates > >whatsoever. > > > >However, I will be joining this table with other > >tables that need to be updated frequently, and that > >would require InnoDB for concurrency. > > > >And my concern is: How efficiently MySQL handles > joins > >between MyISAM and InnoDB tables? In other words, > does > >the overhead of joining between a MyISAM table and > an > >InnoDB table outweigh any read performance > advantage I > >get by using MyISAM for the read-mostly table? > >Meaning, would it be more efficient if I just made > all > >the tables InnoDB to improve join performance, or > >there's no particular overhead in heterogeneous > engine > >queries? > > > >I appreciate your feedback, > > > >Homam > > > > Homan, > I can't really say since I've never joined > heterogeneous tables > together. The best thing you can do is create a test > database with the same > table structure. Fill it with 10x the number of rows > you expect to have, > and then execute your expected SQL query statements. > Measure the times to > see how it can be optimized. Then when you have it > optimized for one user, > simulate multiple clients from several machines > doing the same queries to > see how it reacts. > > To fill the text data you can use words > from the dictionary. There > are a few free plain text dictionaries online. See > http://www.translatum.gr/dictionaries/download-english.htm. > Load the words > into a (memory) table and then randomly add words to > your test table's > text/memo fields. I've used this technique to fill > tables with 10 million > rows of random words. Each memo field can have > dozens of words (random > number), and the text fields can have multiple words > as well. Now you can > do full text search of multiple word occurrences, > such as "dog" and "tree" > to see how fast it is. Your client computers can > pull in 1 or 2 words at > random from the dictionary, and then do a fulltext > search on it. You can > then time the results. This should get you going. :) > > Mike > > > > > > >--- mos <[EMAIL PROTECTED]> wrote: > > > > > At 06:37 PM 12/20/2004, you wrote: > > > >I'm new to MySQL and I was wondering which > storage > > > >engine is the best choice for heavily-indexed, > > > >read-mostly data. > > > > > > > > From skimming over the documentation, it seems > > > that > > > >MyISAM is a better choice since it doesn't have > the > > > >transactional overhead. Yet I'm worried that > it's > > > >becoming depricated and won't be supported in > > > future > > > >versions. > > > > > > > >I need the highest read performance possible, > with > > > >many indexes and joins. It has to be able to > cache > > > >query results in memory to service a large > number > > > of > > > >concurrent requests per second. > > > > > > > >Which way to go? What's the pros and cons of > each > > > >engine for my particular situation? > > > > > > > >I appreciate your help. > > > > > > > >-- Homam > > > > > > > > > > Homam, > > > MyISAM will be faster than InnoDb for > > > reading. In my tests, MyISAM > > > was about 10x faster than an untuned InnoDb > > > installation for simple Select > > > statements. InnoDb is capable of faster speeds > but > > > requires a lot of tuning > > > to get the peak performance from it. InnoDb is > great > > > for updates but for > > > reading I prefer MyISAM hands down. > > > > > > MyISAM is the most popular table type > for > > > MySQL and will be around > > > for years to come. It also supports FullText > > > searching which InnoDb does > > > not (perhaps in the future it will). > > > > > > Of course the bottleneck for MyISAM is > its > > > table locking. If you > > > get more than 20 concurrent updates per second > for a > > > table, there may be > > > delays in getting a lock on the table (you will > need > > > to do your own > > > testing). In this case you either have to switch > to > > > InnoDb, get a faster > > > server, or batch the updates or inserts. Keep > in > > > mind every time the table > > > is updated, even if only 1 record is changed, > the > > > query cache is discarded > > > (true for MyISAM and InnoDb). So you really > don't > > > want to be continuously > > > updating the table that a lot of people are > reading > > > from. It is better to > > > update the table every 5-10 minutes. > > > > > > One more thing. If you are just > inserting > > > rows into a MyISAM > > > table, l
Re: Performance of Joining Tables From Different Storage Engines -- Re: MyISAM vs. InnoDB for heavily-indexed, read-mostly data
At 04:00 PM 12/21/2004, Homam S.A. wrote: Thanks Mike for the information. Yes, Emmett mentioned the same thing in a private message, and it seems that MyISAM is exactly what I'm looking for: a heavily-indexed large table that will be also indexed for full-text search and built off-line -- no updates whatsoever. However, I will be joining this table with other tables that need to be updated frequently, and that would require InnoDB for concurrency. And my concern is: How efficiently MySQL handles joins between MyISAM and InnoDB tables? In other words, does the overhead of joining between a MyISAM table and an InnoDB table outweigh any read performance advantage I get by using MyISAM for the read-mostly table? Meaning, would it be more efficient if I just made all the tables InnoDB to improve join performance, or there's no particular overhead in heterogeneous engine queries? I appreciate your feedback, Homam Homan, I can't really say since I've never joined heterogeneous tables together. The best thing you can do is create a test database with the same table structure. Fill it with 10x the number of rows you expect to have, and then execute your expected SQL query statements. Measure the times to see how it can be optimized. Then when you have it optimized for one user, simulate multiple clients from several machines doing the same queries to see how it reacts. To fill the text data you can use words from the dictionary. There are a few free plain text dictionaries online. See http://www.translatum.gr/dictionaries/download-english.htm. Load the words into a (memory) table and then randomly add words to your test table's text/memo fields. I've used this technique to fill tables with 10 million rows of random words. Each memo field can have dozens of words (random number), and the text fields can have multiple words as well. Now you can do full text search of multiple word occurrences, such as "dog" and "tree" to see how fast it is. Your client computers can pull in 1 or 2 words at random from the dictionary, and then do a fulltext search on it. You can then time the results. This should get you going. :) Mike --- mos <[EMAIL PROTECTED]> wrote: > At 06:37 PM 12/20/2004, you wrote: > >I'm new to MySQL and I was wondering which storage > >engine is the best choice for heavily-indexed, > >read-mostly data. > > > > From skimming over the documentation, it seems > that > >MyISAM is a better choice since it doesn't have the > >transactional overhead. Yet I'm worried that it's > >becoming depricated and won't be supported in > future > >versions. > > > >I need the highest read performance possible, with > >many indexes and joins. It has to be able to cache > >query results in memory to service a large number > of > >concurrent requests per second. > > > >Which way to go? What's the pros and cons of each > >engine for my particular situation? > > > >I appreciate your help. > > > >-- Homam > > > > Homam, > MyISAM will be faster than InnoDb for > reading. In my tests, MyISAM > was about 10x faster than an untuned InnoDb > installation for simple Select > statements. InnoDb is capable of faster speeds but > requires a lot of tuning > to get the peak performance from it. InnoDb is great > for updates but for > reading I prefer MyISAM hands down. > > MyISAM is the most popular table type for > MySQL and will be around > for years to come. It also supports FullText > searching which InnoDb does > not (perhaps in the future it will). > > Of course the bottleneck for MyISAM is its > table locking. If you > get more than 20 concurrent updates per second for a > table, there may be > delays in getting a lock on the table (you will need > to do your own > testing). In this case you either have to switch to > InnoDb, get a faster > server, or batch the updates or inserts. Keep in > mind every time the table > is updated, even if only 1 record is changed, the > query cache is discarded > (true for MyISAM and InnoDb). So you really don't > want to be continuously > updating the table that a lot of people are reading > from. It is better to > update the table every 5-10 minutes. > > One more thing. If you are just inserting > rows into a MyISAM > table, locks are not required if the table has been > optimized (holes > created from deleted rows have been removed). So if > you optimize the table > and then do not delete any rows from the table, and > people only insert rows > to the table, locking should not be a problem. You > will of course need to > do your own testing to confirm this. Hope this > helps. > > Mike > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do you Yahoo!? Send a seasonal email greeting and help others. Do good. http://celebrity.mail.yahoo.com -- MySQL General Mailing List For list archives: http://l
Re: MyISAM vs. InnoDB for heavily-indexed, read-mostly data
Homam S.A. wrote: I'm new to MySQL and I was wondering which storage engine is the best choice for heavily-indexed, read-mostly data. From skimming over the documentation, it seems that MyISAM is a better choice since it doesn't have the transactional overhead. Yet I'm worried that it's becoming depricated and won't be supported in future versions. I need the highest read performance possible, with many indexes and joins. It has to be able to cache query results in memory to service a large number of concurrent requests per second. Which way to go? What's the pros and cons of each engine for my particular situation? Homan: I believe in your situation MyISAM is better. I do not expect to see it becoming obsolete for at least another 5 years. If it does at some point in time, you will be able to run ALTER TABLE to covert to a better table type. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance of Joining Tables From Different Storage Engines -- Re: MyISAM vs. InnoDB for heavily-indexed, read-mostly data
Thanks Mike for the information. Yes, Emmett mentioned the same thing in a private message, and it seems that MyISAM is exactly what I'm looking for: a heavily-indexed large table that will be also indexed for full-text search and built off-line -- no updates whatsoever. However, I will be joining this table with other tables that need to be updated frequently, and that would require InnoDB for concurrency. And my concern is: How efficiently MySQL handles joins between MyISAM and InnoDB tables? In other words, does the overhead of joining between a MyISAM table and an InnoDB table outweigh any read performance advantage I get by using MyISAM for the read-mostly table? Meaning, would it be more efficient if I just made all the tables InnoDB to improve join performance, or there's no particular overhead in heterogeneous engine queries? I appreciate your feedback, Homam --- mos <[EMAIL PROTECTED]> wrote: > At 06:37 PM 12/20/2004, you wrote: > >I'm new to MySQL and I was wondering which storage > >engine is the best choice for heavily-indexed, > >read-mostly data. > > > > From skimming over the documentation, it seems > that > >MyISAM is a better choice since it doesn't have the > >transactional overhead. Yet I'm worried that it's > >becoming depricated and won't be supported in > future > >versions. > > > >I need the highest read performance possible, with > >many indexes and joins. It has to be able to cache > >query results in memory to service a large number > of > >concurrent requests per second. > > > >Which way to go? What's the pros and cons of each > >engine for my particular situation? > > > >I appreciate your help. > > > >-- Homam > > > > Homam, > MyISAM will be faster than InnoDb for > reading. In my tests, MyISAM > was about 10x faster than an untuned InnoDb > installation for simple Select > statements. InnoDb is capable of faster speeds but > requires a lot of tuning > to get the peak performance from it. InnoDb is great > for updates but for > reading I prefer MyISAM hands down. > > MyISAM is the most popular table type for > MySQL and will be around > for years to come. It also supports FullText > searching which InnoDb does > not (perhaps in the future it will). > > Of course the bottleneck for MyISAM is its > table locking. If you > get more than 20 concurrent updates per second for a > table, there may be > delays in getting a lock on the table (you will need > to do your own > testing). In this case you either have to switch to > InnoDb, get a faster > server, or batch the updates or inserts. Keep in > mind every time the table > is updated, even if only 1 record is changed, the > query cache is discarded > (true for MyISAM and InnoDb). So you really don't > want to be continuously > updating the table that a lot of people are reading > from. It is better to > update the table every 5-10 minutes. > > One more thing. If you are just inserting > rows into a MyISAM > table, locks are not required if the table has been > optimized (holes > created from deleted rows have been removed). So if > you optimize the table > and then do not delete any rows from the table, and > people only insert rows > to the table, locking should not be a problem. You > will of course need to > do your own testing to confirm this. Hope this > helps. > > Mike > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > __ Do you Yahoo!? Send a seasonal email greeting and help others. Do good. http://celebrity.mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. InnoDB for heavily-indexed, read-mostly data
At 06:37 PM 12/20/2004, you wrote: I'm new to MySQL and I was wondering which storage engine is the best choice for heavily-indexed, read-mostly data. From skimming over the documentation, it seems that MyISAM is a better choice since it doesn't have the transactional overhead. Yet I'm worried that it's becoming depricated and won't be supported in future versions. I need the highest read performance possible, with many indexes and joins. It has to be able to cache query results in memory to service a large number of concurrent requests per second. Which way to go? What's the pros and cons of each engine for my particular situation? I appreciate your help. -- Homam Homam, MyISAM will be faster than InnoDb for reading. In my tests, MyISAM was about 10x faster than an untuned InnoDb installation for simple Select statements. InnoDb is capable of faster speeds but requires a lot of tuning to get the peak performance from it. InnoDb is great for updates but for reading I prefer MyISAM hands down. MyISAM is the most popular table type for MySQL and will be around for years to come. It also supports FullText searching which InnoDb does not (perhaps in the future it will). Of course the bottleneck for MyISAM is its table locking. If you get more than 20 concurrent updates per second for a table, there may be delays in getting a lock on the table (you will need to do your own testing). In this case you either have to switch to InnoDb, get a faster server, or batch the updates or inserts. Keep in mind every time the table is updated, even if only 1 record is changed, the query cache is discarded (true for MyISAM and InnoDb). So you really don't want to be continuously updating the table that a lot of people are reading from. It is better to update the table every 5-10 minutes. One more thing. If you are just inserting rows into a MyISAM table, locks are not required if the table has been optimized (holes created from deleted rows have been removed). So if you optimize the table and then do not delete any rows from the table, and people only insert rows to the table, locking should not be a problem. You will of course need to do your own testing to confirm this. Hope this helps. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM vs. InnoDB for heavily-indexed, read-mostly data
I'm new to MySQL and I was wondering which storage engine is the best choice for heavily-indexed, read-mostly data. >From skimming over the documentation, it seems that MyISAM is a better choice since it doesn't have the transactional overhead. Yet I'm worried that it's becoming depricated and won't be supported in future versions. I need the highest read performance possible, with many indexes and joins. It has to be able to cache query results in memory to service a large number of concurrent requests per second. Which way to go? What's the pros and cons of each engine for my particular situation? I appreciate your help. -- Homam __ Do you Yahoo!? Yahoo! Mail - 250MB free storage. Do more. Manage less. http://info.mail.yahoo.com/mail_250 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB + Foreign Keys
On Thu, 13 May 2004 10:34:37 -0700 (PDT) David Blomstrom <[EMAIL PROTECTED]> wrote: > I thought that only InnoDB tables could be joined - > and only if they had foreign keys. But it sounds like > any kind of table can be joined, and it doesn't need a > foreign key. Exactly, you can do a join with any two or more tables (even the same table joined to itself actually) and on any columns. It's just a matter of whether or not the join makes any sense, for example: SELECT * FROM t1, t2 WHERE t1.city = t2.golfer_id Doesn't make much sense to do this, but the SQL will parse and attempt to execute anyway. > Can someone explain InnoDB, MyISAM and foreign keys in > plain English? If I understand correctly, foreign keys > simply help ensure integrity. To put it another way, > they help weed out errors. InnoDB and MyISAM are table types, or table engines is now the preferred terminology. Different storage implementations basically. MyISAM is so much faster because it doesn't have to deal with the overhead of transactions and foreign keys. An important aspect of foreign keys is the referential action (ON DELETE CASCADE, ON UPDATE SET NULL, etc) which allow you to have the database take care of cascading actions when a parent row is deleted without having to worry about having an application programmer do it and make a coding error or forget to and leave orphaned rows. So if you have a student parent table and an enrollment child table, you can set it to delete rows in the enrollment table for student 123 if student 123 is deleted form the student table (no orphaned rows in enrollment when a student is deleted). You can also set up referential actions to prevent deleting rows from a parent if there are rows in a child table (ON DELETE RESTRICT), it all depends on your situation. Foreign Keys also requires that any rows inserted into the the child table MUST have a value that matches in the parent table. So for the student/enrollment table, if you attempt to insert a row into enrollment for studentId 342, the only way that query will work is if there is indeed a student with studentId 342 in the student table. This is the "referential integrity" part of Foreign Keys. Foreign keys provide a real link between tables to implement an actual relationship between two tables, or even a table to itself. (remember this is a relational database afterall). Without that foreign key, the relationship is only implied and might not be known to anyone unless there is an entity-relationship diagram for the project. This comes in handy when trying to reverse engineer a data model from an existing db application. Data models should always come first though, but we're only human! > For example, when I import data, I often get errors - > something about violations of foreign key restraints. > When I examine my original tables, I often find > discrepancies - like eu* rather than eu in a row for > Turkey, which lies partly in Europe AND Asia. > > I've considered the possibility of creating foreign > keys for quality control, then deleting them after I'm > finished so I can tweak my table - like adding * to > certain elements. However, it sounds like it's very > difficult to delete foreign keys. I tried it in > MySQL-Front or SQLyog and was presented with an > 11-step process, or something like that. ALTER TABLE mytable DROP FOREIGN KEY fk_symbol; (SHOW CREATE TABLE will reveal the fk_symbol) But why even put the foreign key in there in the first place if you're just going to violate it later? If you do that, you should have a "eu*" in the parent table. > My understanding is that MyISAM tables are faster than > InnoDB tables, but the latter offer "row locking," > though I don't really understand what that is. Yes. In addition to foreign keys, InnoDB offers transaction support, which is absolutely critical when dealing with larger OLTP applications. Speed does suffer though because all this Foreign Key / Transaction stuff takes lots of overhead. > Putting it all together, what combination would you > recommend for my project - a series of tables with > data on the world's nations, states, counties and > natural regions? My biggest table at present (Nations) > has about 250 rows and half a dozen columns, but I'll > be creating perhaps a dozen accessory tables, with > data on area, population, economics, government, etc. With rows in the hundreds, InnoDB vs. MyISAM speed should be negligable. Make sure you create Indexes on your foreign key columns and you should be ok. (Indexes will speed up your queries, foreign key columns are almost always the columns used in a join condition, hence the need for an index) > I'm also planning a series of tables focusing on > animals - common and scientific names, diet, habitat, > etc. > > For both of these projects, I think foreign keys would > be a good choice for quality control, which would, in > turn, require the use of InnoDB tables. Am I
Re: MyISAM vs InnoDB + Foreign Keys
> I thought that only InnoDB tables could be joined - > and only if they had foreign keys. But it sounds like > any kind of table can be joined, and it doesn't need a > foreign key. The ability to join a bunch of tables in a query is different from foreign keys. A foreign key is a relationhip between two tables. It says that if you wish to put a value into a column with a foreign key, then that value has to exist in the other table. > Can someone explain InnoDB, MyISAM and foreign keys in > plain English? If I understand correctly, foreign keys > simply help ensure integrity. To put it another way, > they help weed out errors. InnoDB is a storage engine, and so is MyISAM. When you create a table, you specify one of the types. When you add data and indexes to that table, the type of table determins the storage engine used. InnoDB has row level locking. This means that when you are updating a row, only that one row gets locked (which means that another connection to the database cannot modify that row). MyISAM locks the entire table. Only one connection/session at a time can update/insert/delete. InnoDB uses the concept of a tablespace; MyISAM doesn't. A tablespace is where you store your data, and is made up of datafiles. You don't know where your data is stored in those data files. When you create a table in MyISAM, it creates a file of the same name as your table. InnoDB has foreign keys. What you wrote above is correct - you're defining a relationship between tables that the database will enforce. To backup InnoDB, you either have to shut down the database, or buy a hot-backup tool ($500 US, 390 Euros, I think). There is lots more, but those are the basics. MyISAM is easier, InnoDB has more enterprise features. > For example, when I import data, I often get errors - > something about violations of foreign key restraints. > When I examine my original tables, I often find > discrepancies - like eu* rather than eu in a row for > Turkey, which lies partly in Europe AND Asia. Yes, the keys have to be an exact match. You could also allow a country to be in two continents with a one-to-many relationship. > I've considered the possibility of creating foreign > keys for quality control, then deleting them after I'm > finished so I can tweak my table - like adding * to > certain elements. However, it sounds like it's very > difficult to delete foreign keys. I tried it in > MySQL-Front or SQLyog and was presented with an > 11-step process, or something like that. It's not that tough. ALTER TABLE your_table_here ADD CONSTRAINT some_foreign_key_name FOREIGN KEY (column from table) REFERENCES other_table (other column)l ALTER TABLE your_table_here DROP FOREIGN KEY some_foreign_key_name; Note that it depends on the version of MySQL you are using. Some versions of MySQL would not allow you to create foreign keys outside of CREATE-TABLE statements. Others wouldn't allow you to give your foreign key a name. Checkout http://www.innodb.com/ibman.php - it's the InnoDB manual. > My understanding is that MyISAM tables are faster than > InnoDB tables, but the latter offer "row locking," > though I don't really understand what that is. Explained it above. > Putting it all together, what combination would you > recommend for my project - a series of tables with > data on the world's nations, states, counties and > natural regions? My biggest table at present (Nations) > has about 250 rows and half a dozen columns, but I'll > be creating perhaps a dozen accessory tables, with > data on area, population, economics, government, etc. > > I'm also planning a series of tables focusing on > animals - common and scientific names, diet, habitat, > etc. > > For both of these projects, I think foreign keys would > be a good choice for quality control, which would, in > turn, require the use of InnoDB tables. Am I right? Yes, foreign keys would help catch bugs. If there is alot of updating/inserting/deleting, InnoDB can be very helpful as well. Davis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM vs InnoDB + Foreign Keys
I thought that only InnoDB tables could be joined - and only if they had foreign keys. But it sounds like any kind of table can be joined, and it doesn't need a foreign key. Can someone explain InnoDB, MyISAM and foreign keys in plain English? If I understand correctly, foreign keys simply help ensure integrity. To put it another way, they help weed out errors. For example, when I import data, I often get errors - something about violations of foreign key restraints. When I examine my original tables, I often find discrepancies - like eu* rather than eu in a row for Turkey, which lies partly in Europe AND Asia. I've considered the possibility of creating foreign keys for quality control, then deleting them after I'm finished so I can tweak my table - like adding * to certain elements. However, it sounds like it's very difficult to delete foreign keys. I tried it in MySQL-Front or SQLyog and was presented with an 11-step process, or something like that. My understanding is that MyISAM tables are faster than InnoDB tables, but the latter offer "row locking," though I don't really understand what that is. Putting it all together, what combination would you recommend for my project - a series of tables with data on the world's nations, states, counties and natural regions? My biggest table at present (Nations) has about 250 rows and half a dozen columns, but I'll be creating perhaps a dozen accessory tables, with data on area, population, economics, government, etc. I'm also planning a series of tables focusing on animals - common and scientific names, diet, habitat, etc. For both of these projects, I think foreign keys would be a good choice for quality control, which would, in turn, require the use of InnoDB tables. Am I right? Thanks. __ Do you Yahoo!? Yahoo! Movies - Buy advance tickets for 'Shrek 2' http://movies.yahoo.com/showtimes/movie?mid=1808405861 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. INNODB for a single blob table
Hi Alan, > Thanks for that Chris, interesting thoughts. > > For clarification, there is *NO* UPDATEs running on this table. Not a > single one! :) Many more SELECTs than INSERTs If you value your data, and these INSERTs are part of a multi-insert batch of related data, go with the table-type that supports transactions: InnoDB. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. INNODB for a single blob table
Thanks for that Chris, interesting thoughts. For clarification, there is *NO* UPDATEs running on this table. Not a single one! :) Many more SELECTs than INSERTs Chris Nolan wrote: Alan Williamson wrote: A quick question for the hardcore MySQL experts out there. I have a simple table; --- ID varchar (PK) DATA longblob --- This table is a simple persistence cache for one of our servers. It regularly INSERTs and SELECTs into this table data of approximately 2KB - 200KB, although the majority of inserts are around the 2KB mark. No fancy queries are ever performed, merely a single SELECT on a given key and no range queries are ever done. So with that in mind, I just noticed the table was created as a MyISAM. In your experience how does this compare to a table using INNODB? Should it have been created as a INNODB for better performance? Any thoughts, insights, would be listened to intensely! :) thanks How often are DELETE and UPDATE statements executed on this table? MyISAM is damned quick when it comes to workloads that always result in INSERTs ending up at the end of the tablespace. As MyISAM can allow SELECTs to execute while INSERTs are in progress at the end of the table (i.e When no DELETEs have been issued) thanks to it's versioning you'll find that thousands of queries a second is quite doable on modest hardware. That said, InnoDB's speed defies belief. Given that it's multiversioned, transactional and able to lock at the row level the fact that it's even in the same leauge as MyISAM performance-wise for these sorts of loads is impressive. When you have UPDATEs flying around, InnoDB may edge MyISAM out for heavy workloads. Many places have moved to InnoDB due to concurrency issues of that type. In summary, test test test! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs. INNODB for a single blob table
Alan Williamson wrote: A quick question for the hardcore MySQL experts out there. I have a simple table; --- ID varchar (PK) DATA longblob --- This table is a simple persistence cache for one of our servers. It regularly INSERTs and SELECTs into this table data of approximately 2KB - 200KB, although the majority of inserts are around the 2KB mark. No fancy queries are ever performed, merely a single SELECT on a given key and no range queries are ever done. So with that in mind, I just noticed the table was created as a MyISAM. In your experience how does this compare to a table using INNODB? Should it have been created as a INNODB for better performance? Any thoughts, insights, would be listened to intensely! :) thanks How often are DELETE and UPDATE statements executed on this table? MyISAM is damned quick when it comes to workloads that always result in INSERTs ending up at the end of the tablespace. As MyISAM can allow SELECTs to execute while INSERTs are in progress at the end of the table (i.e When no DELETEs have been issued) thanks to it's versioning you'll find that thousands of queries a second is quite doable on modest hardware. That said, InnoDB's speed defies belief. Given that it's multiversioned, transactional and able to lock at the row level the fact that it's even in the same leauge as MyISAM performance-wise for these sorts of loads is impressive. When you have UPDATEs flying around, InnoDB may edge MyISAM out for heavy workloads. Many places have moved to InnoDB due to concurrency issues of that type. In summary, test test test! Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM vs. INNODB for a single blob table
A quick question for the hardcore MySQL experts out there. I have a simple table; --- ID varchar (PK) DATA longblob --- This table is a simple persistence cache for one of our servers. It regularly INSERTs and SELECTs into this table data of approximately 2KB - 200KB, although the majority of inserts are around the 2KB mark. No fancy queries are ever performed, merely a single SELECT on a given key and no range queries are ever done. So with that in mind, I just noticed the table was created as a MyISAM. In your experience how does this compare to a table using INNODB? Should it have been created as a INNODB for better performance? Any thoughts, insights, would be listened to intensely! :) thanks -- Alan Williamson, City Planner w: http://www.BLOG-CITY.com/ e: [EMAIL PROTECTED] b: http://alan.blog-city.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed of MyISAM vs. InnoDB
Simon Green wrote: Is it just not the case that InnoDB table have to do more as they have more functionality and so take more time? Not exactly. InnoDB does indeed support transactions, uses the ultimate in concurrency control (multiversioning) and provides foreign key constraints. That said, MyISAM provides FULLTEXT searches and OpenGIS-style storage in 4.1.x. Having the choice between MyISAM and InnoDB is one of MySQL's primary strengths. In many cases, InnoDB will actually be faster due to many of the cutting edge methods that InnoDB possesses but MyISAM does not. For other workloads, MyISAM will be faster as it doesn't need to worry about rolling things back or having multiple updates from different connections to keep track of the state of to any large degree. Regards, Chris Simon - Original Message - From: "Jiří Matějka" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, February 18, 2004 10:38 AM Subject: Speed of MyISAM vs. InnoDB Hi all, I'm using mysql 4.0.17 and I have this problem with speed of innodb database: I have simple command like select count(*) from table1, or select field1, field2 from table1. The table1 and has more than cca 10.000 rows (most of the fields are integer, only several varchars and several memos) and its type is InnoDB. Then the query lasts too long, at least several seconds, sometimes more than 5. If I convert it to MyISAM then the query lasts usually less then 0.3 second. If the table is small (cca less than 5.000 rows) then there is not big difference... Is it normal, that InnoDB isn't able to access large table as quickly as MyISAM? Or is there any parametr to set to make InnoDB run faster? I need to use InnoDB because it supports transactions and MyISAM not... thx Jiri Matejka [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed of MyISAM vs. InnoDB
Is it just not the case that InnoDB table have to do more as they have more functionality and so take more time? Simon - Original Message - From: "Jiří Matějka" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, February 18, 2004 10:38 AM Subject: Speed of MyISAM vs. InnoDB > Hi all, > I'm using mysql 4.0.17 and I have this problem with speed of innodb > database: > > I have simple command like select count(*) from table1, or select field1, > field2 from table1. The table1 and has more than cca 10.000 rows (most of > the fields are integer, only several varchars and several memos) and its > type is InnoDB. Then the query lasts too long, at least several seconds, > sometimes more than 5. If I convert it to MyISAM then the query lasts > usually less then 0.3 second. If the table is small (cca less than 5.000 > rows) then there is not big difference... > Is it normal, that InnoDB isn't able to access large table as quickly as > MyISAM? Or is there any parametr to set to make InnoDB run faster? I need to > use InnoDB because it supports transactions and MyISAM not... > > thx > > Jiri Matejka > [EMAIL PROTECTED] > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed of MyISAM vs. InnoDB
"Jiri Matejka" <[EMAIL PROTECTED]> wrote: > Now I found one more "strange" thing - if I use show tables to get table > properties, then if table is MyISAM the number of rows is correct and if it > is InnoDB number of rows is around 2000 lower... And the innodb table looks > 8 times bigger than myisam table (field data_length in show table status > query)... It's documented feature: SHOW TABLE STATUS does not give accurate statistics on InnoDB tables, except for the physical size reserved by the table. The row count is only a rough estimate used in SQL optimization. http://www.mysql.com/doc/en/InnoDB_restrictions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed of MyISAM vs. InnoDB
From: "Jiri Matejka" <[EMAIL PROTECTED]> > Now I found one more "strange" thing - if I use show tables to get table > properties, then if table is MyISAM the number of rows is correct and if it > is InnoDB number of rows is around 2000 lower... And the innodb table looks > 8 times bigger than myisam table (field data_length in show table status > query)... Nothing "strange", since it's documented. InnoDB gives an estimate of the number or rows in a table. Since MyISAM keeps a seperate count of the number of records, it can give you accurate numbers. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed of MyISAM vs. InnoDB
Now I found one more "strange" thing - if I use show tables to get table properties, then if table is MyISAM the number of rows is correct and if it is InnoDB number of rows is around 2000 lower... And the innodb table looks 8 times bigger than myisam table (field data_length in show table status query)... Jiri Matejka [EMAIL PROTECTED] ==>Wednesday, February 18, 2004 11:57 AM [EMAIL PROTECTED] wrote: > I think count(*) is a special case: MyISAM holds a record count which > it can access instantly, InnoDB has to count rows. Does the time > difference persist for real queries? > > Alec > > Jiří Matějka <[EMAIL PROTECTED]> wrote on 18/02/2004 10:38:13: > >> Hi all, >> I'm using mysql 4.0.17 and I have this problem with speed of innodb >> database: >> >> I have simple command like select count(*) from table1, or select >> field1, field2 from table1. The table1 and has more than cca 10.000 >> rows (most of the fields are integer, only several varchars and >> several memos) and its type is InnoDB. Then the query lasts too >> long, at least several seconds, sometimes more than 5. If I convert >> it to MyISAM then the query lasts usually less then 0.3 second. If >> the table is small (cca less than 5.000 rows) then there is not big >> difference... >> Is it normal, that InnoDB isn't able to access large table as >> quickly as MyISAM? Or is there any parametr to set to make InnoDB >> run faster? I need to use InnoDB because it supports transactions >> and MyISAM not... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed of MyISAM vs. InnoDB
Unfortunately it persists also for real queries. Eg. query like "select field1 from table1 where field3=xx" and InnoDB is cca 10times slower than MyISAM. I wonder whether there is not some error or problem in my.ini settings, I use following settings: innodb_additional_mem_pool_size 1048576 innodb_buffer_pool_size 8388608 innodb_data_file_path ibdata1:500M:autoextend innodb_data_home_dir innodb_file_io_threads 4 innodb_force_recovery 0 innodb_thread_concurrency 8 innodb_flush_log_at_trx_commit 1 innodb_fast_shutdown ON innodb_flush_method innodb_lock_wait_timeout 50 innodb_log_arch_dir .\ innodb_log_archive OFF innodb_log_buffer_size 1048576 innodb_log_file_size 5242880 innodb_log_files_in_group 2 innodb_log_group_home_dir .\ innodb_mirrored_log_groups 1 innodb_max_dirty_pages_pct 90 Is there anything wrong or unusual? Jiri Matejka [EMAIL PROTECTED] ==>Wednesday, February 18, 2004 11:57 AM [EMAIL PROTECTED] wrote: > I think count(*) is a special case: MyISAM holds a record count which > it can access instantly, InnoDB has to count rows. Does the time > difference persist for real queries? > > Alec > > Jiří Matějka <[EMAIL PROTECTED]> wrote on 18/02/2004 10:38:13: > >> Hi all, >> I'm using mysql 4.0.17 and I have this problem with speed of innodb >> database: >> >> I have simple command like select count(*) from table1, or select >> field1, field2 from table1. The table1 and has more than cca 10.000 >> rows (most of the fields are integer, only several varchars and >> several memos) and its type is InnoDB. Then the query lasts too >> long, at least several seconds, sometimes more than 5. If I convert >> it to MyISAM then the query lasts usually less then 0.3 second. If >> the table is small (cca less than 5.000 rows) then there is not big >> difference... >> Is it normal, that InnoDB isn't able to access large table as >> quickly as MyISAM? Or is there any parametr to set to make InnoDB >> run faster? I need to use InnoDB because it supports transactions >> and MyISAM not... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed of MyISAM vs. InnoDB
COUNT(*) is a special case for MyISAM. However, you'll find that anything that has a WHERE clause that takes advantage of an index is pretty quick for both MyISAM and InnoDB tables. For instance: SELECT COUNT(*) FROM table; Is slow as all buggery on InnoDB, but: SELECT COUNT(id) FROM table WHERE id > 0; Is pretty quick if id is indexed (or a PRIMARY KEY). I use the above query to get a count of rows on an InnoDB table when my PRIMARY KEY is an AUTO_INCREMENT column. Regards, Chris [EMAIL PROTECTED] wrote: I think count(*) is a special case: MyISAM holds a record count which it can access instantly, InnoDB has to count rows. Does the time difference persist for real queries? Alec Jiří Matějka <[EMAIL PROTECTED]> wrote on 18/02/2004 10:38:13: Hi all, I'm using mysql 4.0.17 and I have this problem with speed of innodb database: I have simple command like select count(*) from table1, or select field1, field2 from table1. The table1 and has more than cca 10.000 rows (most of the fields are integer, only several varchars and several memos) and its type is InnoDB. Then the query lasts too long, at least several seconds, sometimes more than 5. If I convert it to MyISAM then the query lasts usually less then 0.3 second. If the table is small (cca less than 5.000 rows) then there is not big difference... Is it normal, that InnoDB isn't able to access large table as quickly as MyISAM? Or is there any parametr to set to make InnoDB run faster? I need to use InnoDB because it supports transactions and MyISAM not... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Speed of MyISAM vs. InnoDB
I think count(*) is a special case: MyISAM holds a record count which it can access instantly, InnoDB has to count rows. Does the time difference persist for real queries? Alec Jiří Matějka <[EMAIL PROTECTED]> wrote on 18/02/2004 10:38:13: > Hi all, > I'm using mysql 4.0.17 and I have this problem with speed of innodb > database: > > I have simple command like select count(*) from table1, or select field1, > field2 from table1. The table1 and has more than cca 10.000 rows (most of > the fields are integer, only several varchars and several memos) and its > type is InnoDB. Then the query lasts too long, at least several seconds, > sometimes more than 5. If I convert it to MyISAM then the query lasts > usually less then 0.3 second. If the table is small (cca less than 5.000 > rows) then there is not big difference... > Is it normal, that InnoDB isn't able to access large table as quickly as > MyISAM? Or is there any parametr to set to make InnoDB run faster? I need to > use InnoDB because it supports transactions and MyISAM not... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Speed of MyISAM vs. InnoDB
Hi all, I'm using mysql 4.0.17 and I have this problem with speed of innodb database: I have simple command like select count(*) from table1, or select field1, field2 from table1. The table1 and has more than cca 10.000 rows (most of the fields are integer, only several varchars and several memos) and its type is InnoDB. Then the query lasts too long, at least several seconds, sometimes more than 5. If I convert it to MyISAM then the query lasts usually less then 0.3 second. If the table is small (cca less than 5.000 rows) then there is not big difference... Is it normal, that InnoDB isn't able to access large table as quickly as MyISAM? Or is there any parametr to set to make InnoDB run faster? I need to use InnoDB because it supports transactions and MyISAM not... thx Jiri Matejka [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Autoincrement in MYISAM vs INNODB
It is my understanding that at least through 4.0.14, INNODB does not support using autoincrement on the last field in a multi field primary key. i.e. if a table has a primary key of three fields like cpny_ID, acct_ID, list_ID in MYISAM you can add the autoincrement attribute to list_ID and it will sequence within the cpny_ID, acct_ID group. Are there any plans to support this in INNODB? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Confused about MyISAM vs InnoDB tabel types
You should find the following informative: http://www.mysql.com/doc/en/Table_types.html InnoDB offers transaction support, and seems to recover better from crashes. You do sacrifice some speed and features such as FULLTEXT index support. Regards, Mike Hillyer www.vbmysql.com > -Original Message- > From: PAUL MENARD [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 24, 2003 2:51 PM > To: [EMAIL PROTECTED] > Subject: Confused about MyISAM vs InnoDB tabel types > > > Can anyone either summarize for me a comparison between the > MyISAM and InnoDB MySQL table type? > > I am getting ready to upgrade from MySQL 3.23.42 to 4.0.13 in > the coming week and started reading the upgrade documents on > the www.mysql.com site. Never had even thought about using > another table type since my current database seems to work > fine. But thought I would ask. > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Confused about MyISAM vs InnoDB tabel types
Can anyone either summarize for me a comparison between the MyISAM and InnoDB MySQL table type? I am getting ready to upgrade from MySQL 3.23.42 to 4.0.13 in the coming week and started reading the upgrade documents on the www.mysql.com site. Never had even thought about using another table type since my current database seems to work fine. But thought I would ask.
