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
