List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:January 12 2000 3:13am
Subject:Re: why isn't this key used
View as plain text  
Hi!

>>>>> "Sasha" == Sasha Pachev <sasha@stripped> writes:

Sasha> Mike Wexler wrote:
<cut>

Sasha> I am forwarding this to Monty and the list for further suggestions, as
Sasha> 4000 heads are better than one :-)

<cut>

>> SELECT accounts.storeId, TO_DAYS(NOW())-TO_DAYS(accounts.enteredStamp),
>> tums.mail_aliases.address,
>> count(*), sum(if(inventory.qty=0,1,inventory.qty) * inventory.price),
>> firstName, lastName, workPhone, homePhone, city, state,
>> salesContact, salesStatus,
>> accounts.enteredStamp, activeStamp,
>> IFNULL(MAX(accountReview.reviewEnd),activeStamp),
>> businessName
>> FROM accounts
>> LEFT JOIN inventory ON accounts.storeId = inventory.storeId
>> LEFT JOIN accountReview ON accounts.storeId = accountReview.storeId
>> LEFT JOIN inventory.status_defs AS s ON inventory.status=s.statusId
>> LEFT JOIN category
>> ON inventory.storeId=category.storeId AND inventory.catId=category.catId
>> LEFT JOIN tums.mail_aliases ON tums.mail_aliases.alias =
>> tias.accounts.loginName
>> WHERE active=1
>> AND (s.showItems=1 OR s.showItems IS NULL)
>> AND   (inventory.catId IS NULL and category.catId IS NULL OR
>> inventory.catId IS NOT NULL and category.catId IS NOT NULL)
>> GROUP BY accounts.storeId
>> ORDER BY 16
>> ...
>> 282 rows in set (25.18 sec)
>> 
>> The bad part about this query is is locks the database and prevents many
>> other requests from running. I've been tempted to generate the account list
>> and then iterate through it in Perl code and request the other information
>> for each store.
>> 
>> EXPLAIN ...
>> +---------------+--------+---------------+---------+---------+--------------
>> ---------------------+------+------------+
>> | table         | type   | possible_keys | key     | key_len |
>> ref                               | rows | Extra      |
>> +---------------+--------+---------------+---------+---------+--------------
>> ---------------------+------+------------+
>> | accounts      | ALL    | active        | NULL    |    NULL |
>> NULL                              |  799 | where used |
>> | inventory     | ref    | PRIMARY       | PRIMARY |      64 |
>> accounts.storeId                  | 5095 | where used |
>> | accountReview | eq_ref | PRIMARY       | PRIMARY |      64 |
>> accounts.storeId                  |    1 |            |
>> | s             | eq_ref | PRIMARY       | PRIMARY |      64 |
>> inventory.status                  |    1 | where used |
>> | category      | eq_ref | PRIMARY       | PRIMARY |     128 |
>> inventory.storeId,inventory.catId |    1 | where used |
>> | mail_aliases  | eq_ref | PRIMARY       | PRIMARY |      64 |
>> accounts.loginName                |    1 |            |
>> +---------------+--------+---------------+---------+---------+--------------
>> ---------------------+------+------------+
>> 6 rows in set (0.01 sec)

Try doing in your shell:

mysqladmin flush-tables
isamchk -a mysql-database-directory/*.ISM

And try again the above explain?

The problem here is that the ref primary key matches too many rows.

Anyway, are you sure that you want to force accounts as your first
table and not inventory?  (LEFT JOIN forces the tables to be used in
the given order).

Regards,
Monty

Thread
why isn't this key usedMike Wexler11 Jan
  • Re: why isn't this key usedJim Faucette11 Jan
    • Re: why isn't this key usedMike Wexler12 Jan
  • Re: why isn't this key usedSasha Pachev12 Jan
  • Re: why isn't this key usedSasha Pachev12 Jan
    • Re: why isn't this key usedMichael Widenius12 Jan
      • Re: why isn't this key usedMike Wexler12 Jan
        • Re: why isn't this key usedBenjamin Pflugmann12 Jan
    • image map queryChris Mason14 Mar
      • Re: image map querySergei A. Golubchik14 Mar