The explain result (product of rows) is less than 200,000. I'll include it here, but
I don't know if it's readable or not:
+------------------------------------+--------+----------------------------------------+-----------+---------+----------------------------------------------+-------+-----------------------------------+
| table | type | possible_keys
|| key | key_len | ref
| rows | Extra |
+------------------------------------+--------+----------------------------------------+-----------+---------+----------------------------------------------+-------+-----------------------------------+
| badgests | ALL | PRIMARY
|| NULL | NULL | NULL
| 4 | Using temporary |
| badge_history | ref | ixStatus,ixArea,ixDept,ixBid,ixSeveral
|| ixSeveral | 4 | badgests.id
| 41946 | |
| badge | eq_ref | PRIMARY,ixBid
|| ixBid | 18 | badge_history.bid
| 1 | |
| department | eq_ref | PRIMARY
|| PRIMARY | 4 | badge_history.dept
| 1 | |
| area | eq_ref | PRIMARY
|| PRIMARY | 20 | badge_history.area,badge_history.source_host | 1 | where
|used |
| smccm_user_vs_permitted_department | ref | PRIMARY
|| PRIMARY | 4 | badge_history.dept
| 1 | where used; Using index; Distinct |
+------------------------------------+--------+----------------------------------------+-----------+---------+----------------------------------------------+-------+-----------------------------------+
6 rows in set (0.05 sec)
---------- Original Message ----------------------------------
From: Thomas Spahni <[EMAIL PROTECTED]>
Date: Fri, 8 Jun 2001 16:36:18 +0200 (CEST)
>Dear Sir,
AFAICS your query does a FULL join of tables "badge_history, badge,
badgests, department, area" AND an additional LEFT JOIN. Even with indexes
the product may be too big for large tables. What says EXPLAIN SELECT?
Thomas Spahni
On Thu, 7 Jun 2001 [EMAIL PROTECTED] wrote:
> Dear Sirs,
>
> We have a very large database (badge_history has > 4,000,000 rows) and the
> explain for our query looks like this:
>
> By using indexes for a query, the product of the row estimates from an
> explain statement are less than 200,000. The problem is that this query
> times out. There is only one table being scanned, the primary keys are
> being used on the rest of the tables. This is running on a machine with
> 2 8XX processors and almost a gigibyte of ram. Can anyone explain why
> this query is not working? The Query:
>
> SELECT DISTINCT badge_history.xact_date AS xact_date,
> badge_history.xact_time AS xact_time, badge_history.last_name AS
> last_name, badge_history.bid AS bid, badgests.cond_desc AS status,
> department.description AS department,
> badge_history.reader_desc AS reader_desc, area.description AS area,
> badge.expired_date AS expired, badge_history.xact_type AS
> xact_type, badge_history.tzcorr AS tzcorr FROM badge_history, badge,
> badgests, department, area LEFT JOIN
> smccm_user_vs_permitted_department ON badge_history.dept =
> smccm_user_vs_permitted_department.permitted_department
> WHERE smccm_user_vs_permitted_department.smccm_user = '1' AND
> badge_history.dept = department.id AND
> badge_history.area = area.id AND badge_history.status = badgests.id AND
> badge_history.source_host = area.source_host AND
> badge_history.bid = badge.bid
>
> This should not take 20 minutes to run, any ideas on how to speed it up?
> The indexes are about optimal (I think)
>
> Mysql version: 3.23.36
>
> ---------------------------------------------------------------------
> 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
