I was wondering if anyone had any suggestions on the following problem. I have a
table with about 7 million rows, and I'm using the following join:CREATE TABLE
badge_history_resolved SELECT badge_history.id AS id, badge_history.xact_date AS
xact_date, badge_history.xact_time AS xact_time, badge_history.last_name AS last_name,
badge.first_name AS first_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,
badge_history.floor_accessed AS floor_accessed, badge_history.kp_alarm_resp AS
kp_alarm_resp, badge_history.sequence AS sequence, badge_history.nbadge AS nbadge,
badge_history.reader_type_phy AS reader_type_phy, badge_history.reader_type_log AS
reader_type_log, badge_history.shunt AS shunt, badge_history.duress AS duress,
badge_history.swipe_show AS swipe_show, badge_type.description AS personnel_type,
badge_history.employee AS employee, badge_history.source_host AS source_host FROM
badge_history LEFT JOIN badge_type ON badge_history.personnel_type=badge_type.id AND
badge_history.source_host = badge_type.source_host LEFT JOIN badge ON
badge_history.bid = badge.bid LEFT JOIN badgests ON badge_history.status = badgests.id
AND badge_history.source_host = badgests.source_host LEFT JOIN area ON
badge_history.area = area.id AND badge_history.source_host = area.source_host LEFT
JOIN department on badge_history.dept = department.id;
The describe statement looks like:
+---------------+--------+---------------+---------+---------+-----+
| table | type | possible_keys | key | key_len | ref
| | rows | Extra |
+---------------+--------+---------------+---------+---------+-----+
| badge_history | ALL | NULL | NULL | NULL | NULL| 7073329
| badge_type | ALL | PRIMARY| NULL | NULL | NULL | 4 |
| badge | ref | bid | bid | 4 | badge_history.bid |1
| badgests | eq_ref | PRIMARY| PRIMARY | 68 |
|badge_history.status,badge_history.source_host | 1
| area | eq_ref | PRIMARY| PRIMARY |68 |
|badge_history.area,badge_history.source_host | 1 |
| department | eq_ref | PRIMARY,id| id | 4 | badge_history.dept|1 |
+---------------+--------+---------------+---------+---------+-
This join takes over 24 hours to run, and as you can see I'm using indexes and things.
Does anyone have any suggestions on how to speed this up?
Is it faster if I create the badge_history_resolved table, set up the indexes, and
then do the inserts, or should I create the table, do the inserts, and then add all of
hte indexs at the end?
Also, a somewhat related problem, I seem to behaving some trouble with the c API.
This program ports a large database from Informix, and some of the queries (creating
indexes mostly) return errors, and it seems tor eally botch the mysql thread. Example:
string query = "alter table badge change bid bid varchar(18) not null, add
unique(bid)"
Sometimes this returns an error, sometimes not... Is this a bug, or what?
Thirdly, Could someone suggest some memory sizes in teh my.conf file for the above
join? What are the best settings?
This is a 2 Proc RedHat 7.1 Machine w/ 256 MB of RAM, pretty much dedicated to this
app.
Thanks for the help!
MIke B.
---------------------------------------------------------------------
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
