Did you get an answer to this problem. I'm experiencing the same behavior.
Mike Wexler wrote:
> When I do
>
> EXPLAIN
> SELECT status.itemKey, status.auctionUrl,
> status.hideItem, status.auctionId, status.action,
> status.auctionHouse
> FROM auction.status, inventory.thisItem
> WHERE status.itemKey=thisItem.itemKey
> AND (status.closeDate>=NOW() OR
> (status.closeDate IS NULL AND action="queued"))
> AND status.action<>'build'
>
> I get
>
>
>+----------+--------+------------------------------------+-----------+---------+------+-------+------------+
> | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
>
>+----------+--------+------------------------------------+-----------+---------+------+-------+------------+
> | thisItem | system | NULL | NULL |
> NULL | NULL | 1 | |
> | status | range | itemKey,itemKey_2,closeDate,action | closeDate |
> 9 | NULL | 23417 | where used |
>
>+----------+--------+------------------------------------+-----------+---------+------+-------+------------+
> 2 rows in set (0.01 sec)
>
> Note that the second table matches 23417 rows.
>
> Here is inventory.thisItem:
>
> mysql> show fields from inventory.thisItem;
> +---------+------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +---------+------------+------+-----+---------+-------+
> | itemKey | bigint(10) | | | 0 | |
> +---------+------------+------+-----+---------+-------+
>
> and auction.status:
>
> mysql> show fields from auction.status;
>
>+-----------------+-------------------------------------------------------------+------+-----+---------+-------+
> | Field | Type
> | Null | Key | Default | Extra |
>
>+-----------------+-------------------------------------------------------------+------+-----+---------+-------+
> | action |
> enum('atTIAS','build','uploaded','sold','queued','unknown') | | MUL
> | atTIAS | |
> | actionDate | timestamp(14)
> | YES | | NULL | |
> | auctionHouse | varchar(10)
> | | | | |
> | batchNum | int(11) unsigned
> | | MUL | 0 | |
> | auctionId | varchar(64)
> | | MUL | | |
> | auctionUrl | varchar(255)
> | YES | | NULL | |
> | minimumBid | decimal(7,2)
> | YES | | NULL | |
> | reserve | decimal(7,2)
> | YES | | NULL | |
> | finalBid | decimal(7,2)
> | YES | | NULL | |
> | closeDate | datetime
> | YES | MUL | NULL | |
> | durationHrs | int(11)
> | YES | | NULL | |
> | buyerEmail | varchar(128)
> | YES | | NULL | |
> | hideItem | enum('Link','Hide','Ignore','NoAuction')
> | | | Link | |
> | buyerName | varchar(64)
> | YES | | NULL | |
> | title | varchar(128)
> | YES | | NULL | |
> | description | text
> | YES | | NULL | |
> | invoiced | enum('no','yes')
> | | | no | |
> | uploadKey | varchar(128)
> | YES | | NULL | |
> | qty | int(11)
> | | | 0 | |
> | uploadFee | decimal(7,2)
> | YES | | NULL | |
> | bold | varchar(32)
> | | | no | |
> | private | varchar(32)
> | | | no | |
> | feature1 | varchar(32)
> | | | no | |
> | feature2 | varchar(32)
> | | | no | |
> | feature3 | varchar(32)
> | | | no | |
> | feature4 | varchar(32)
> | | | no | |
> | feature5 | varchar(32)
> | | | no | |
> | feature6 | varchar(32)
> | | | no | |
> | feature7 | varchar(32)
> | | | no | |
> | feature8 | varchar(32)
> | | | no | |
> | imageUrl0 | varchar(255)
> | YES | | NULL | |
> | imageUrl1 | varchar(255)
> | YES | | NULL | |
> | imageUrl2 | varchar(255)
> | YES | | NULL | |
> | imageUrl3 | varchar(255)
> | YES | | NULL | |
> | imageUrl4 | varchar(255)
> | YES | | NULL | |
> | imageUrl5 | varchar(255)
> | YES | | NULL | |
> | imageUrl6 | varchar(255)
> | YES | | NULL | |
> | imageUrl7 | varchar(255)
> | YES | | NULL | |
> | imageUrl8 | varchar(255)
> | YES | | NULL | |
> | takePrice | decimal(7,2)
> | | | 0.00 | |
> | cityState | varchar(128)
> | YES | | NULL | |
> | zip | varchar(10)
> | YES | | NULL | |
> | relisted | enum('no','yes')
> | | | no | |
> | exchangeId | varchar(64)
> | YES | | NULL | |
> | exchangeItemid | varchar(64)
> | YES | | NULL | |
> | shipOpts | varchar(64)
> | YES | | NULL | |
> | payOpts | varchar(64)
> | YES | | NULL | |
> | weight | decimal(7,2)
> | | | 0.00 | |
> | startDate | varchar(32)
> | YES | | NULL | |
> | endDate | varchar(32)
> | YES | | NULL | |
> | auctionCat | varchar(64)
> | YES | | NULL | |
> | deleteOnClose | tinyint(1)
> | | | 0 | |
> | itemKey | int(8) unsigned
> | YES | MUL | NULL | |
> | oldBatchNum | int(11) unsigned
> | YES | | NULL | |
> | uploadStartTime | datetime
> | YES | | NULL | |
> | uploadEndTime | datetime
> | YES | | NULL | |
>
>+-----------------+-------------------------------------------------------------+------+-----+---------+-------+
>
> mysql> show keys from auction.status;
>
>+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
> Collation | Cardinality | Sub_part | Packed | Comment |
>
>+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+
> | status | 1 | auctionId | 1 | auctionId | A
> | 245831 | NULL | NULL | |
> | status | 1 | itemKey | 1 | itemKey | A
> | 122915 | NULL | NULL | |
> | status | 1 | itemKey_2 | 1 | itemKey | A
> | 122915 | NULL | NULL | |
> | status | 1 | itemKey_2 | 2 | auctionId | A
> | 245831 | NULL | NULL | |
> | status | 1 | batchNum | 1 | batchNum | A
> | 81943 | NULL | NULL | |
> | status | 1 | closeDate | 1 | closeDate | A
> | 122915 | NULL | NULL | |
> | status | 1 | action | 1 | action | A
> | 5 | NULL | NULL | |
>
>+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+
> 7 rows in set (0.00 sec)
>
> I think it should have used the itemKey index in auction.status.
>
> Under 4.0 and 3.23.42 it does.
>
> Here is the same explain on 4.0:
>
>
>+----------+--------+------------------------------------+---------+---------+-------+------+------------+
> | table | type | possible_keys | key |
> key_len | ref | rows | Extra |
>
>+----------+--------+------------------------------------+---------+---------+-------+------+------------+
> | thisItem | system | NULL | NULL |
> NULL | NULL | 1 | |
> | status | ref | itemKey,itemKey_2,closeDate,action | itemKey |
> 5 | const | 2 | where used |
>
>+----------+--------+------------------------------------+---------+---------+-------+------+------------+
>
> Why idd this change in 3.23.44. How can I get it to use the itemKey
> index instead of the closeDate index?
>
> ---------------------------------------------------------------------
> 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
---------------------------------------------------------------------
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
