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
