For the following select:
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, badgests, department, area, badge 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
The badge table has 85,000 records, and the badge_history has 4,000,000.
This search takes a very long time to run. What indexes should I have
on these 2 tables to make it run in a reasonable amount of time (5
minutes), or should I just get Oracle or something? I have indexed like
this:
alter table badge_history add index ixBigIx (dept, area, status,
source_host, bid)
This does not help much, it just causes all of the badge table to be
pulled in. There is already an index on the bid col in the badge table.
I really need this to get working, I've been adding indexes all day and
can't get the query to list less than 25 million rows when I describe
it. Any advice is appreciated.
Mike.
---------------------------------------------------------------------
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
