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
