Hello All, I have a next problem. After upgrade from 4.1 branch to 5.0, the productivity of our applications has decreases too much.
Below is a tipical case. Table 'account' has primary(department,account) and 40777 records. Table 'payment' primary(department,account,year,month) and 3831797 records. In this case all ok ('where' uses primary key of table 'account'): > desc select * from > account a inner join payment p using(department,account) > where a.department='1' and a.account=1000 +----+-------------+-------+------+-----------------+---------+---------+------------------------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------+---------+---------+------------------------+-------+-------------+ | 1 | SIMPLE | a | ref | PRIMARY,account | account | 4 | const | 1 | | | 1 | SIMPLE | p | ref | PRIMARY | PRIMARY | 10 | HAS.a.department,const | 28738 | Using where | +----+-------------+-------+------+-----------------+---------+---------+------------------------+-------+-------------+ This case not working in 5.0.20 ('where' uses index of 'account' with 10 resulting rows): > desc select * from > account a inner join payment p using(department,account) > where a.street=10 +----+-------------+-------+------+-----------------+---------+---------+------------------------------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------+---------+---------+------------------------------------+---------+-------------+ | 1 | SIMPLE | p | ALL | PRIMARY | | | | 3831797 | | | 1 | SIMPLE | a | ref | PRIMARY,account | PRIMARY | 10 | HAS.p.department,HASVODA.p.account | 1 | Using where | +----+-------------+-------+------+-----------------+---------+---------+------------------------------------+---------+-------------+ I don't think that selecting for fullscan of table with 3.5 million rows (instead of table with 40 thousends) is a good idea. Is this a known bug? WBR! Juri. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]