On 11-Jul-01 Mike W. Baranski wrote:
> Awsome answers! I'll clear a few things up where appropriate
>
> Don Read wrote:
<snip>
>>
>>
>> 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...
>
Ok, understand; one the cases where the generalization doesn't hold.
> 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.
>
That should be perfect, and prolly won't hit the disk (get the answer out
of the index cache).
Are the id & source_host declaration exactly the same as history ?
Otherwise there could be some type-cast shuffle going on behind the scenes.
>>
>> 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...
>
That'll do it.
>> >
>> > 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...
>
? 'splain please.
<snip>
>>
>> Example bid(s) please.
>>
<snip>
> 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...
>
max_packet_size
and your C compiler my have some limit on string size ...
> 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...
>
Weird, but withou the error code, might as well check the tarot cards.
>>
>> >
>> > 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.
>
If IDE :
might stuff-up your swap some, it'll help to get another controller &
drive for swap & tmp tho ...
> Thanks, that was extremely helpful...
You're welcome.
--
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
