Folks,
I am with Security Managment Consulting, and we have a liscensed copy of
mysql. I don't have the lisc. # on hand.
We have a very large database in mysql, and one of the tables (call it
badge_history) has about 4,000,000 records, with the potiental to get
much bigger...
Now, we are using PHP/mysql to run web queries on this database, and
some of the queries are taking up to 1 hour. Here is an example query:
******************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
