Re: Which is faster when deleting rows? In() or Separate Delete stmts?
Hi mos wrote: If I have a large table with 20 million rows, is it going to be faster to use one delete statement like: delete from mytable where rcdid in(20,300,423, 9) to delete 10-100 random records using the primary index "RcdId" or should I use separate delete statements for each RcdId as in: delete from mytable where rcdid = 20; delete from mytable where rcdid = 300; delete from mytable where rcdid = 423; ... delete from mytable where rcdid = 9; I'm concerned the IN() clause will cause a full table scan and that will take quite a while to find the rows even though I'm using the primary key RcdId. You can convert it into an equivalent SELECT and use EXPLAIN to see if it's using a table scan. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Which is faster when deleting rows? In() or Separate Delete stmts?
If I have a large table with 20 million rows, is it going to be faster to use one delete statement like: delete from mytable where rcdid in(20,300,423, 9) to delete 10-100 random records using the primary index "RcdId" or should I use separate delete statements for each RcdId as in: delete from mytable where rcdid = 20; delete from mytable where rcdid = 300; delete from mytable where rcdid = 423; ... delete from mytable where rcdid = 9; I'm concerned the IN() clause will cause a full table scan and that will take quite a while to find the rows even though I'm using the primary key RcdId. TIA Mike MySQL 5.0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Static or Dynamic rows -- which is faster?
On 11 Feb 2002, at 18:27, Jeremy Zawodny wrote: > Nope, not for MyISAM it hasn't changed. With fixed rows, tables can > be checked, repaired, and accessed more quickly. If you know the row > size, you know how to get to row 500,123 quickly. It's just > multiplication. But if the row sizes are all different, you have to > scan the rows before it. I don't understand. Why would you want to get to row 500,123 in the first place? Wouldn't you normally either be finding the record through an index (in which case you'd have the byte position of the record) or scanning through the table anyway? Under what conditions would you be looking for a row number? Filter fodder: sql,query -- Keith C. Ivey <[EMAIL PROTECTED]> Washington, DC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Static or Dynamic rows -- which is faster?
Whoops, originally sent this to just Heikki. > On Monday, February 11, 2002, at 12:12 PM, Heikki Tuuri wrote: > >> for InnoDB 'dynamic rows', that is, rows where you define char columns as >> VARCHAR, are faster because tables and indexes fit in smaller space. > > Is there reasoning specific to InnoDB? Dynamic tables would be smaller > regardless of the table type, I would think. In Kaj Arno's talk on > optimizing MySQL at the O'Reilly Open Source Convention last year, he said > that fixed tables are preferable and that if you must have dynamic columns, > you should try to put them in a table separate from your static columns > (slides 67 and 68 for those who have them). Has this changed? David Felio Software Developer Information Network of Arkansas http://www.AccessArkansas.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Static or Dynamic rows -- which is faster?
Eric, for InnoDB 'dynamic rows', that is, rows where you define char columns as VARCHAR, are faster because tables and indexes fit in smaller space. Only in some rare cases where you want to avoid fragmentation caused by updates which change a column length, a fixed-length CHAR(...) column can be a better option. I guess also for MyISAM dynamic rows are usually faster, because you win in smaller disk i/o. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com "Eric Mayers" wrote in message ... >I have a large data set (15 mil rows) consisting of a datetime column >and a char(255) column. I seem to recall seeing something about >performance benefits for using static length rows with MyISAM tables, >and I've heard some argument for using dynamic length rows (e.g., use >varchar rather than char) in InnoDB tables. So for performance sake >(assuming no index can be used), which is better for these table types? > > >I can imagine that using dynamic length would be better in the case of >an IO bottleneck, but maybe there is an efficiency gain from knowing >where the records start and stop ahead of time..? > >Thanks, >Eric > >uugh. sql. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Static or Dynamic rows -- which is faster?
I have a large data set (15 mil rows) consisting of a datetime column and a char(255) column. I seem to recall seeing something about performance benefits for using static length rows with MyISAM tables, and I've heard some argument for using dynamic length rows (e.g., use varchar rather than char) in InnoDB tables. So for performance sake (assuming no index can be used), which is better for these table types? I can imagine that using dynamic length would be better in the case of an IO bottleneck, but maybe there is an efficiency gain from knowing where the records start and stop ahead of time..? Thanks,=20 Eric uugh. sql. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Static or Dynamic rows -- which is faster?
Hi! -Original Message- From: David Felio <[EMAIL PROTECTED]> To: Heikki Tuuri <[EMAIL PROTECTED]> Date: Tuesday, February 12, 2002 12:47 AM Subject: Re: Static or Dynamic rows -- which is faster? >On Monday, February 11, 2002, at 12:12 PM, Heikki Tuuri wrote: > >> for InnoDB 'dynamic rows', that is, rows where you define char columns as >> VARCHAR, are faster because tables and indexes fit in smaller space. > >Is there reasoning specific to InnoDB? Dynamic tables would be smaller >regardless of the table type, I would think. In Kaj Arno's talk on >optimizing MySQL at the O'Reilly Open Source Convention last year, he said >that fixed tables are preferable and that if you must have dynamic columns, > you should try to put them in a table separate from your static columns >(slides 67 and 68 for those who have them). Has this changed? I am no expert on MyISAM, but I guess also there it is better to define VARCHAR(255) rather than CHAR(255), because in big tables disk i/o is often the bottleneck. I have understood MyISAM searches are faster if the row format is 'fixed length', but I doubt that this offsets the increased i/o. http://www.mysql.com/doc/D/y/Dynamic_format.html : " You can use OPTIMIZE table or myisamchk to defragment a table. If you have static data that you access/change a lot in the same table as some VARCHAR or BLOB columns, it might be a good idea to move the dynamic columns to other tables just to avoid fragmentation: " For InnoDB, VARCHAR is almost always better than CHAR, except in rare cases where you want to avoid fragmentation caused by updates which change a field length. >David Felio >Software Developer >Information Network of Arkansas >http://www.AccessArkansas.org Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Which is Faster
Dear Hayan, > Which of the following SQL queries is faster and better > "select thefield from thetable group by thetable" > Or > "select distinct thefield from thetable"? > and WHY? =if you use the MySQL command line to issue a query, a summary report follows any output giving number of rows affected and the time taken. =which of the two is quicker? =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Static or Dynamic rows -- which is faster?
On Mon, Feb 11, 2002 at 04:48:10PM -0600, David Felio wrote: > Whoops, originally sent this to just Heikki. > > On Monday, February 11, 2002, at 12:12 PM, Heikki Tuuri wrote: > >> for InnoDB 'dynamic rows', that is, rows where you define char columns as >> VARCHAR, are faster because tables and indexes fit in smaller space. > > Is there reasoning specific to InnoDB? Sort of. It's because InnoDB stores the data and indexes together. (BDB does something similar.) > Dynamic tables would be smaller regardless of the table type, I > would think. True. > In Kaj Arno's talk on optimizing MySQL at the O'Reilly Open Source > Convention last year, he said that fixed tables are preferable and > that if you must have dynamic columns, you should try to put them in > a table separate from your static columns (slides 67 and 68 for > those who have them). Has this changed? Nope, not for MyISAM it hasn't changed. With fixed rows, tables can be checked, repaired, and accessed more quickly. If you know the row size, you know how to get to row 500,123 quickly. It's just multiplication. But if the row sizes are all different, you have to scan the rows before it. (This is an over-simplification, but hopefully gets the point across.) Plus, if you have fixed rows and no "holes" in the table, you can INSERT and SELECT at the same time. :-) So, Kaj's talk is right on. Going to the conference again this year? There's gonna be some cool MySQL stuff again this year. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.47-max: up 4 days, processed 162,876,748 queries (418/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Which is Faster
At 04:21 AM 2/5/2002 , you wrote: >Dear all, >Which of the following SQL queries is faster and better >"select thefield from thetable group by thetable" >Or >"select distinct thefield from thetable"? >and WHY? > >Best Regards >Hayan Hayan, I believe the "Select Distinct.." gets translated into a "Group By" so they should both be identical. Brent - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Which is Faster
Dear Hayan, > Which of the following SQL queries is faster and better > "select thefield from thetable group by thetable" > Or > "select distinct thefield from thetable"? > and WHY? =if you use the MySQL command line to issue a query, a summary report follows any output giving number of rows affected and the time taken. =which of the two is quicker? =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Which is Faster
Dear all, Which of the following SQL queries is faster and better "select thefield from thetable group by thetable" Or "select distinct thefield from thetable"? and WHY? Best Regards Hayan Get your own "800" number Voicemail, fax, email, and a lot more http://www.ureach.com/reg/tag - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Which is faster VarChar(255) or Text?
On Fri, Jan 25, 2002 at 09:42:07AM -0600, BD wrote: > > Jeremy, > > Thanks, I hadn't thought of TinyText. With flat file type > databases that I used to use, if I put something in a memo field, it > takes longer to retrieve the data because it is stored in a separate > physical file. There is a noticeable lag on slow machines. Right, I've seen that too. > I was wondering if using a Text (or TinyText) in MySQL exhibited > noticeably slower record retrieval when hundreds of users are > querying the database. Or is it too small to notice? I'd expect it to be rather small, since the data is all stored in the same place. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 25 days, processed 552,420,629 queries (253/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Which is faster VarChar(255) or Text?
I belive the type of table you use here will be the only thing that will speed things up. If you use MyISAM tables and have 100's of users calling things from the DB and 100's of users INSERTING things into the same table, then it will be very slow due to MyISAM's table level locking. Using InnoDB tables will speed up access time due to ROW level locking. -My 2c - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 -Original Message- From: BD [mailto:[EMAIL PROTECTED]] Sent: Friday, January 25, 2002 9:42 AM To: [EMAIL PROTECTED] Subject: Re: Which is faster VarChar(255) or Text? At 02:31 AM 1/25/2002 , you wrote: >On Thu, Jan 24, 2002 at 05:27:18PM -0600, BD wrote: > > > I will be putting variable length text into a field (up to 255 > > characters but typically around 60 characters) and wonder what makes > > for faster retrieval? Or does it matter? A field defined as > > Varchar(255) or Text? > >Do you mean VARCHAR(255) or TINYTEXT? > >As seen here: > > http://www.mysql.com/doc/n/o/node_366.html > >TEXT columns can be much larger than 255. But they require an extra >byte for the length portion of the record. So VARCHAR(255) will be >ever so slightly faster than TEXT. > >Jeremy Jeremy, Thanks, I hadn't thought of TinyText. With flat file type databases that I used to use, if I put something in a memo field, it takes longer to retrieve the data because it is stored in a separate physical file. There is a noticeable lag on slow machines. I was wondering if using a Text (or TinyText) in MySQL exhibited noticeably slower record retrieval when hundreds of users are querying the database. Or is it too small to notice? Brent >-- >Jeremy D. Zawodny, <[EMAIL PROTECTED]> >Technical Yahoo - Yahoo Finance >Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 > >MySQL 3.23.41-max: up 22 days, processed 514,632,289 queries (266/sec. avg) > >- >Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Which is faster VarChar(255) or Text?
At 02:31 AM 1/25/2002 , you wrote: >On Thu, Jan 24, 2002 at 05:27:18PM -0600, BD wrote: > > > I will be putting variable length text into a field (up to 255 > > characters but typically around 60 characters) and wonder what makes > > for faster retrieval? Or does it matter? A field defined as > > Varchar(255) or Text? > >Do you mean VARCHAR(255) or TINYTEXT? > >As seen here: > > http://www.mysql.com/doc/n/o/node_366.html > >TEXT columns can be much larger than 255. But they require an extra >byte for the length portion of the record. So VARCHAR(255) will be >ever so slightly faster than TEXT. > >Jeremy Jeremy, Thanks, I hadn't thought of TinyText. With flat file type databases that I used to use, if I put something in a memo field, it takes longer to retrieve the data because it is stored in a separate physical file. There is a noticeable lag on slow machines. I was wondering if using a Text (or TinyText) in MySQL exhibited noticeably slower record retrieval when hundreds of users are querying the database. Or is it too small to notice? Brent >-- >Jeremy D. Zawodny, <[EMAIL PROTECTED]> >Technical Yahoo - Yahoo Finance >Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 > >MySQL 3.23.41-max: up 22 days, processed 514,632,289 queries (266/sec. avg) > >- >Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail <[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Which is faster VarChar(255) or Text?
On Thu, Jan 24, 2002 at 05:27:18PM -0600, BD wrote: > I will be putting variable length text into a field (up to 255 > characters but typically around 60 characters) and wonder what makes > for faster retrieval? Or does it matter? A field defined as > Varchar(255) or Text? Do you mean VARCHAR(255) or TINYTEXT? As seen here: http://www.mysql.com/doc/n/o/node_366.html TEXT columns can be much larger than 255. But they require an extra byte for the length portion of the record. So VARCHAR(255) will be ever so slightly faster than TEXT. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 22 days, processed 514,632,289 queries (266/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Which is faster VarChar(255) or Text?
I will be putting variable length text into a field (up to 255 characters but typically around 60 characters) and wonder what makes for faster retrieval? Or does it matter? A field defined as Varchar(255) or Text? It is unlikely this field will be used for searching and it will not be indexed. This table will be part of a web application so a lot of people will be doing queries on the table and this field will be displayed as a grid field in the browser. The user queries will only return10-100 records at a time but there will be a lot of simultaneous connections to the database. Any opinions on which is better? VarChar(255) or Text? TIA Brent - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
