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
