With the correct indexes this query should run in less than a second.
Basically what you should do is make an index for every column that is used
in the where clause. For instance, if you had a query ... WHERE A=X AND B=Y
AND C=Z (A,B,and C are in the same table) you would create an index (A,B,C)
and it would make this query tons faster for large data sets. It is a good
idea to put the most restrictive column first in the index. By restrictive I
mean the column that has the most amount of distinct rows, ie if we had the
index (A,B,C) and the column A is mostly filled with X's, putting A first
wouldnt narrow the search down as much because mysql would have to check
every index that begins with X. Hope that was a clear enough explaination.
ryan
> ******************Example
> 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, 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
> ************************************End Example
> Query**********************************
>
> Now, if we describe the statement:
>
> |badgests |ALL| id|NULL|NULL|NULL|4|Usingtemporary|
>
> |badge_history |ref|
> ixbid,ixarea,ixdept,ixstatus|ixstatus|4|badgests.id|1048640||
>
> |department |eq_ref| ixid|ixid|4|badge_history.dept|1||
>
> |area |eq_ref|
> ixid|ixid|68|badge_history.area,badge_history.source_host|1||
>
> |badge |ref|ixbid| ixbid|18|badge_history.bid|1||
>
> |smccm_user_vs_permitted_department |ref |permitted_department|
> permitted_department
> |4|badge_history.dept|16|whereused;Distinct|
>
> Any ideas on how to make this faster, it needs to run in less than 5
> minutes. The machine is a dual pentiup 8XX Mhz with 900 MB Ram. Is it
> possible to speed this up somehow...
>
> Thanks, and please reply to [EMAIL PROTECTED], as I don't subscribe to
> the list.
>
> --
> *********************************
> Mike W. Baranski
> [EMAIL PROTECTED]
> [EMAIL PROTECTED]
>
> Web : http://www.secmgmt.com
> Phone: 919-788-9200
> Fax : 919-510-0037
>
> P.O. box 30099
> Raleigh, N.C. USA 27622
> *********************************
>
> ---------------------------------------------------------------------
> 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
