« « Public Terasen Hearings The View » »

Left Join Fun

Haven’t blogged much lately, but not from lack of things to talk about. Part of it is that there’s just so much a person can think about the goings-on down south and the other is just lack of wanting to put any brainpower to it. The grey matter has been devoted to integrating some mailing list software into the a web site, and so far so good. The mailing list part of it is working great, just working on pulling the data from four different tables of the master list, to be used for displaying info elsewhere on the site. The left join to do it all that I’ve have been working got too big of a result set at first, and working on paring that down to just what I need. This would be a good time to have a machine to work on as fast as the servers, it’s amazing how big the table can get with a join, as the software that originally created the database has made oodles of tables with just a few columns but gazillions of rows, and with just the wrong query things can get quite large. hmm, the other pages are public and get lots of hits, maybe it’s not worth the overhead of all this query work and just link the few dozen fields that I do need to a whole new table. Then cache those results as html ready for even less of a server hit, hmm again…
update: got the join working, but it’s huge:
SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
Did a test last night, and a series of selects is much faster than the join above in this case.

This entry was posted on Monday, October 3rd, 2005 at 6:34 am and is filed under Daily Life, Web Development. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.

2 Responses to “Left Join Fun”

  1. rudy Says:
    October 7th, 2005 at 7:53 am

    series of joins will almost always be slower

    i guess it depends on whether the relationships (t1-t2, t1-t3, t1-t4) are one-to-many or many-to-one

    if more than one of these relationships is one-to-many (one t1 to many tn), then you shouldn’t be joining them anyway, because of the cross join effects

    also, never mix JOIN syntax with comma-delimited tables, that’s just asking for optimizer trouble…

  2. admin Says:
    October 7th, 2005 at 8:19 am

    it’s one to many, and the effect was quite spectacular fer sure! :-)

    Going over again what data I *actually* need working backwards, it’s not going to be a very big table echo on the web page, was pulling extra stuff from the db for needless joining.

    Four simple selects with all the data needed gets served up very quick regardless of the db size, (good key index I assume by the person that created the db structure originally), and there’s only one variable that gets chosen by the web site visitor that’s inserted into one of the selects. An elegant solution would seem to be to create a simple TEMP table to echo for the web page.

    gonna test that out this evening - thanks for the advice Rudy!