Re[2]: innodb and use of indices
Hello Heikki, Wednesday, November 21, 2001, 8:34:13 PM, you wrote: I see some more users are interested in optimize table to work. Why don't you just map OPTIMIZE TABLE to ALTER TABLE for INNODB tables so user may not care about table type, there currently one should check the table type and use OPTIMIZE table or alter table depending on its type. HT> Hi! >>On Wed, 2001-11-21 at 05:19, Heikki Tuuri wrote: >>> The way to defragment InnoDB tables, or tables in any database, is from time >>> to time to dump and reimport them. That can give a significant performance >>> boost. >>> >> >>That is actually not entirely true. For MyISAM tables, one simply needs >>to run "OPTIMIZE TABLE table_name" from time to time. I think the end >>result is the same, but it's atomic. >> >>Does OPTIMIZE TABLE work for InnoDB? HT> Sorry, no. ALTER TABLE can be used to rebuild the table, but essentially it HT> is equivalent to a dump and reimport. >>Steve HT> Regards, HT> Heikki HT> -- HT> Order commercial MySQL/InnoDB support at https://order.mysql.com/ HT> - HT> Before posting, please check: HT>http://www.mysql.com/manual.php (the manual) HT>http://lists.mysql.com/ (the list archive) HT> To request this thread, e-mail <[EMAIL PROTECTED]> HT> To unsubscribe, e-mail <[EMAIL PROTECTED]> HT> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Best regards, Petermailto:[EMAIL PROTECTED] - 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: innodb and use of indices
Hi! >On Wed, 2001-11-21 at 05:19, Heikki Tuuri wrote: >> The way to defragment InnoDB tables, or tables in any database, is from time >> to time to dump and reimport them. That can give a significant performance >> boost. >> > >That is actually not entirely true. For MyISAM tables, one simply needs >to run "OPTIMIZE TABLE table_name" from time to time. I think the end >result is the same, but it's atomic. > >Does OPTIMIZE TABLE work for InnoDB? Sorry, no. ALTER TABLE can be used to rebuild the table, but essentially it is equivalent to a dump and reimport. >Steve Regards, Heikki -- Order commercial MySQL/InnoDB support at https://order.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: innodb and use of indices
On Wed, 2001-11-21 at 05:19, Heikki Tuuri wrote: > The way to defragment InnoDB tables, or tables in any database, is from time > to time to dump and reimport them. That can give a significant performance > boost. > That is actually not entirely true. For MyISAM tables, one simply needs to run "OPTIMIZE TABLE table_name" from time to time. I think the end result is the same, but it's atomic. Does OPTIMIZE TABLE work for InnoDB? Steve - 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: innodb and use of indices
Hi! I forwarded the email to Monty if he has time to look at index usage. The speedup .43 -> .44 may be due to better optimization of SQL queries, or due to the table dumps and imports you made. The way to defragment InnoDB tables, or tables in any database, is from time to time to dump and reimport them. That can give a significant performance boost. Regards, Heikki -Original Message- From: nsabbi <[EMAIL PROTECTED]> To: Heikki Tuuri <[EMAIL PROTECTED]> Cc: [EMAIL PROTECTED] <[EMAIL PROTECTED]> Date: Wednesday, November 21, 2001 11:37 AM Subject: innodb and use of indices > >HI, >I have two problems: > > >1) >I have the following table: > >desc users; >+--+--+--+-+-+---+ >| Field| Type | Null | Key | Default | Extra | >+--+--+--+-+-+---+ >| username | varchar(128) | | PRI | | | >| password | varchar(128) | YES | | NULL| | >| email| varchar(128) | | | | | >| usa | int(11) | YES | | 0 | | >+--+--+--+-+-+---+ >4 rows in set (0.00 sec) > >mysql> show keys from users; >+---++--+--+-+---+- +--++-+ >| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | >Cardinality | Sub_part | Packed | Comment | >+---++--+--+-+---+- +--++-+ >| users | 0 | PRIMARY |1 | username| A | >14 | NULL | NULL | | >+---++--+--+-+---+- +--++-+ >1 row in set (0.00 sec) > > >filled with 1579 records. > >when I launch > >explain select * from users order by username asc; >+---+--+---+--+-+--+--+ + >| table | type | possible_keys | key | key_len | ref | rows | Extra >| >+---+--+---+--+-+--+--+ + >| users | ALL | NULL | NULL |NULL | NULL | 14 | Using >filesort | >+---+--+---+--+-+--+--+ + >1 row in set (0.00 sec) > >mysql says it doesn't use any index, contrarily to what stated in the >manual: > >"Indexes are used to: ... >Sort or group a table if the sorting or grouping is done on a leftmost >prefix of a usable key (for example, ORDER BY key_part_1,key_part_2 )..." > > >Is it a bug or a decision of mysql to ignore the PK? > >I realized of this problem because it took ages to make such a simple >select on such a little table. > >I remember there was a command to force the use of an index in a query, >but I don't remember the syntax, any help? > > >2) >reinstalling 3.23.44 from scratch (erasing 3.23.43 and the directories of >data and >logs) gave me an enormous speed boost (1/3 the time to make a select on >500K records). > >I wonder if this is only due to the upgrade from .43 to .44 or if >a periodical cleanse will always give me these results. > >I'd like to know if the cleaning of logs is beneficial because of >the way Innodb is designed, and what other tricks can help me, if any. > >Thanks, > > Nico > - 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
innodb and use of indices
HI, I have two problems: 1) I have the following table: desc users; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | username | varchar(128) | | PRI | | | | password | varchar(128) | YES | | NULL| | | email| varchar(128) | | | | | | usa | int(11) | YES | | 0 | | +--+--+--+-+-+---+ 4 rows in set (0.00 sec) mysql> show keys from users; +---++--+--+-+---+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---++--+--+-+---+-+--++-+ | users | 0 | PRIMARY |1 | username| A | 14 | NULL | NULL | | +---++--+--+-+---+-+--++-+ 1 row in set (0.00 sec) filled with 1579 records. when I launch explain select * from users order by username asc; +---+--+---+--+-+--+--++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--++ | users | ALL | NULL | NULL |NULL | NULL | 14 | Using filesort | +---+--+---+--+-+--+--++ 1 row in set (0.00 sec) mysql says it doesn't use any index, contrarily to what stated in the manual: "Indexes are used to: ... Sort or group a table if the sorting or grouping is done on a leftmost prefix of a usable key (for example, ORDER BY key_part_1,key_part_2 )..." Is it a bug or a decision of mysql to ignore the PK? I realized of this problem because it took ages to make such a simple select on such a little table. I remember there was a command to force the use of an index in a query, but I don't remember the syntax, any help? 2) reinstalling 3.23.44 from scratch (erasing 3.23.43 and the directories of data and logs) gave me an enormous speed boost (1/3 the time to make a select on 500K records). I wonder if this is only due to the upgrade from .43 to .44 or if a periodical cleanse will always give me these results. I'd like to know if the cleaning of logs is beneficial because of the way Innodb is designed, and what other tricks can help me, if any. Thanks, Nico - 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
