You're comparing apples to oranges -- your where clauses are
different. The first query uses the primary key because you're
actually comparing 'account' to something. The second query doesn't
because you're using the 'street' field -- what does SHOW INDEXES
FROM account; show? any indexes on 'street', in 4.1 that didn't get
moved to 5.0?
-Sheeri
On 5/5/06, Juri Shimon <[EMAIL PROTECTED]> wrote:
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
