Awsome answers! I'll clear a few things up where appropriate
Don Read wrote:
>
> 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).
>
Yeah, sorry, I cut the joins out, they're about like you imagined...
It needs to be done like this, because even if status/dept/source_host/
etc are unknown we need to display the records...
It's a security reporting app, so even if someone with an unknown
bid/department scans a badge we need to log it and include it. That's
why the left join, then we just set the default of
badge_history_resolved.department to 'Unknown' or whatever, and make it
not null. The logic of the query is correct (I believe) and it's being
driven by an import program coming from Informix written in c, so
asthetics are not so important...
> > 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 ?
4 rows, and I've indexed them every way I can think of (unique on id,
unique on (id, source_host) and still can't get it down to an eq_ref,
which it should be. It's a simple table, with just id description
source_host fields and 4 rows or so.
>
> 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) ?
yes, unique on id, source host, rather than 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.
>
Thought so, that's one of the reasons it ran so slowly...
> >
> > 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 ?
Since this is a port, the types must match as much as possible, the
logic of the c program sets the type based on the Informix type coming
out...
>
> 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 ?
No duplicates, I know that for sure, and not just this query, I get it
at different places. Is there a limit with the c api as to how many
change xxx yyy zzz, change...., change..., .... you can have with the c
api, there appears to be...
The thing is, these lines show up in the error log, and I cut and past
them into the command line client, and they work, this is why I"m
stumped...
>
> >
> > 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)
depends on the my.cnf file, I've given it enough memory so that it swaps
some of hte stuff it's not using out, but mysql is not swapping. Also,
it only has 1 hdd, how much of a factor is this.
Thanks, that was extremely helpful...
MWB.
>
> 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.
--
*********************************
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
