Hello sheeri, Friday, May 5, 2006, 9:25:18 PM, you wrote:
sk> You're comparing apples to oranges -- your where clauses are sk> different. The first query uses the primary key because you're sk> actually comparing 'account' to something. The second query doesn't sk> because you're using the 'street' field -- what does SHOW INDEXES sk> FROM account; show? any indexes on 'street', in 4.1 that didn't get sk> moved to 5.0? May be, my question wasn't clear. I want to ask: 1. Two tables are linked by primary keys. 2. First has 40,000 rows, and second - 3,500,000. 3. Any subselect of first table will by less then 40,000. Why optimizer 5.0 now selects for FULL scan the biggest table (and forgets about primary key)? Wy I get the +----+-------------+-----------+------+-----------------+---------+ | id | select_type | rows | type | possible_keys | key | +----+-------------+-----------+------+-----------------+---------+ | 1 | SIMPLE | 3,500,000 | ALL | PRIMARY | | | 2 | SIMPLE | 40,000 | ref | PRIMARY,account | PRIMARY | +----+-------------+-----------+------+-----------------+---------+ But not the +----+-------------+-----------+------+-----------------+---------+ | 1 | SIMPLE | 40,000 | ALL | PRIMARY,account | | | 2 | SIMPLE | 3,500,000 | ref | PRIMARY | PRIMARY | +----+-------------+-----------+------+-----------------+---------+ ? Tables on 5.0 and 4.1 were created by same dumpfile. -- Best regards, Juri mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
