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
