On 11-Jul-01 Mike Baranski wrote:
> 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
<snip>
Oh yuck,
Use some aliases (& some formatting) to make sense out of that:
-----
SELECT hist.id AS id, hist.xact_date AS xact_date,
hist.xact_time AS xact_time, hist.last_name AS last_name,
badge.first_name AS first_name, hist.bid AS bid,
badgests.cond_desc AS status, department.description AS department,
hist.reader_desc AS reader_desc, area.description AS area,
badge.expired_date AS expired, hist.xact_type AS xact_type,
hist.tzcorr AS tzcorr, hist.floor_accessed AS floor_accessed,
hist.kp_alarm_resp AS kp_alarm_resp, hist.sequence AS sequence,
hist.nbadge AS nbadge, hist.reader_type_phy AS reader_type_phy,
hist.reader_type_log AS reader_type_log, hist.shunt AS shunt,
hist.duress AS duress, hist.swipe_show AS swipe_show,
btype.description AS personnel_type, hist.employee AS employee,
hist.source_host AS source_host
FROM badge_history AS hist
LEFT JOIN badge_type AS btype ON
hist.personnel_type=btype.id AND hist.source_host=btype.source_host
LEFT JOIN badge USING(bid)
LEFT JOIN badgests ON
hist.status=badgests.id AND hist.source_host=badgests.source_host
LEFT JOIN area ON
hist.area=area.id AND hist.source_host=area.source_host
LEFT JOIN department ON hist.dept=department.id;
---
(sorry folks, i usually put the condition on the same line following 'ON',
but xfmail doesn't do SQL worth a damn)
Why the LEFT JOINS ?
They're (mostly) used for finding set membership; whenever you see
'LEFT JOIN b' without s following 'b IS [NOT] NULL' clause 'tis the
clue that you prolly should be using something else.
Perhaps STRAIGHT_JOIN, or SELECT DISTINCT ... JOIN
is what you want (or at least give the same result).
> 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 |
> +---------------+--------+---------------+---------+---------+-
>
How many rows in badge_type ?
Since you are running a full scan on the 7 million rows in badge_history,
that first JOIN is pretty important.
Do you have a key on badge_type (source_host,id) ?
> 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?
>
7,000,000 x foo is going to be a fairly large number.
Reducing foo :
Break out the first JOIN (badge_history,badge_type); save resultset
in a temp table.
Complete the final table by selecting on the temp and the other
tables (which look to be well indexed).
Reducing 7,000,000 :
# put key on the department, if not already indexed
$qry="ALTER TABLE history_badge ADD KEY idx_d (dept)";
SQLQuery($qry);
foreach $dept (1..25) { # 25 departments in this example
$qry="INSERT INTO final_table
SELECT ...
FROM history_badge AS hist ... JOIN ... JOIN ...
WHERE hist.dept=$dept";
SQLQuery($qry);
}
> 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?
Add INDEX afterwards.
>
> 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)"
>
what is bid before this ?
Are these are numeric ?
BIGINT UNSIGNED might be a better choice (and save 10 bytes per).
Example bid(s) please.
> Sometimes this returns an error, sometimes not... Is this a bug, or what?
>
A 'what'. Most likely duplicate bid, whats the error code/msg ?
>
> Thirdly, Could someone suggest some memory sizes in teh my.conf file for the
> above join? What are the best settings?
>
Fix query => tune index(s) => tune buffers.
> This is a 2 Proc RedHat 7.1 Machine w/ 256 MB of RAM, pretty much dedicated
> to this app.
... little light on the RAM, methinks, what does 'top' say ?
(a cold beverage sez you're deep in the weeds)
Regards,
--
Don Read [EMAIL PROTECTED]
-- It's always darkest before the dawn. So if you are going to
steal the neighbor's newspaper, that's the time to do it.
---------------------------------------------------------------------
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
