Working around optimizing some queries, I tried to get a better key improvement.
This is what I get:
mysql> explain select ID,reply,nome,email,dataora,titolo,testo,status,thread from board where articolo=50966 AND status<2 and (thread<102741 and thread>101548) ORDER BY ID DESC; +---------+-------+----------------------------+--------+---------+------+------+-----------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+-------+----------------------------+--------+---------+------+------+-----------------------------+ | board15 | range | Status,thread,ast,articolo | thread | 4 | NULL | 2947 | Using where; Using filesort | +---------+-------+----------------------------+--------+---------+------+------+-----------------------------+ 1 row in set (0.01 sec)
mysql> explain select ID,reply,nome,email,dataora,titolo,testo,status,thread from board where articolo=50966 AND status<2 and (thread=101549 or thread=102740 or thread=102387 or thread=101945 or thread=101606) ORDER BY ID DESC; +---------+-------+----------------------------+--------+---------+------+------+-----------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---------+-------+----------------------------+--------+---------+------+------+-----------------------------+ | board15 | range | Status,thread,ast,articolo | thread | 4 | NULL | 19 | Using where; Using filesort | +---------+-------+----------------------------+--------+---------+------+------+-----------------------------+ 1 row in set (0.00 sec)
mysql> desc board; +----------+---------------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------------+------+-----+---------------------+----------------+ | ID | int(11) unsigned | | PRI | NULL | auto_increment | | reply | int(11) unsigned | | MUL | 0 | | | nome | varchar(50) | | | | | | email | varchar(50) | | | | | | dataora | datetime | | | 0000-00-00 00:00:00 | | | titolo | varchar(50) | | | | | | testo | text | | | | | | status | tinyint(4) unsigned | | MUL | 0 | | | thread | int(11) unsigned | | MUL | 0 | | | ip | varchar(64) | | MUL | | | | articolo | varchar(255) | | MUL | | | +----------+---------------------+------+-----+---------------------+----------------+ 11 rows in set (0.00 sec)
mysql> show keys from board; +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ | board15 | 0 | PRIMARY | 1 | ID | A | 102559 | NULL | NULL | | BTREE | | | board15 | 1 | Reply | 1 | reply | A | 34186 | NULL | NULL | | BTREE | | | board15 | 1 | Status | 1 | status | A | 2 | NULL | NULL | | BTREE | | | board15 | 1 | thread | 1 | thread | A | 51279 | NULL | NULL | | BTREE | | | board15 | 1 | ast | 1 | articolo | A | 20511 | NULL | NULL | | BTREE | | | board15 | 1 | ast | 2 | status | A | 20511 | NULL | NULL | | BTREE | | | board15 | 1 | ast | 3 | thread | A | 51279 | NULL | NULL | | BTREE | | | board15 | 1 | articolo | 1 | articolo | A | 20511 | NULL | NULL | | BTREE | | | board15 | 1 | ip | 1 | ip | A | 34186 | NULL | NULL | | BTREE | | +---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+ 9 rows in set (0.00 sec)
As you can see, using "thread=xxx or thread=yyy or ..." instead of "thread>xxx and thread<zzz" seems working better, since it requires to process less rows. I also get the same result when "thread's range" is large and requires many "or" between thread values.
But I can't understand why it doesn't use "ast" key (articolo,status,thread) which has been created since where condiction uses these three fields. In other tables I get such key working fine...
Can anyone explain me this?
Thanks in advance.
Bye, Alberto.
-- "Imagination is more important than knowledge"
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
