Subject: RE: CF/SQL Server/NT vs. ?/?/unix



hi Jack - 

> The guys at MySQL say they don't want to put transaction
> handling in due to the performance hit (can't they find a
> workaround?). Again, a point that I am debating: do I need
> transaction handling or not? At this point, and in the foreseen
> future, no, but the key word is "foreseen" ;-) I don't need
> row-level record locking.


i'm afraid there's no way to work around the performance costs for
transaction logging and record locking.   they're both designed to
prevent database corruption and improve your chances for error recovery.  
basically, it's like improving the gas mileage of a car by taking off
all the pieces which are mechanically unnecessary.. like the bumpers,
the firewall, and all the structural steel which protects the passenger
compartment.   the resulting vehicle is much lighter, and therefore goes
farther on a gallon of gas.   you just don't want to hit anything
tougher than another beer can in one.

the elimination of those features makes MySQL almost a read-only
database.   both mechanisms work together to keep two people from trying
to write data to the same field at the same time.   it's a necessity of
life if you want to support multiple, simultaneous writes into the same
database.   the MySQL approach is to assume that only one person will be
storing information at any give time, but that large numbers of people
will want to view that information.

if that matches what you're doing, and you don't foresee a need for
multiple users to alter data (or call operations which cause alteration
of data) simultaneously, you'll be fine.   if you do decide you want to
support simultaneous writes, find something which supports the safety
features.   if you don't, you're just playing russian roulette.


BTW - i heard back from Charles the PHP/DB guru, who says that Sequel
Server is a commercial product which has a free port for linux, and is
derived from the same code base as MySQL.. apparently they were the same
product until version 4.8 or so. he's running that in the Lee production
environment, and seems to be happy with it.



> Do you have any idea of what the basic workarounds are for
> replacing INNER JOIN's? I've done some digging and found other
> people asking the same question, but the answers haven't given
> me a clear path, if there is one.


i just took a look thru the dox for MySQL 3.22, and it looks like they
support the operation, they just use a different (tighter) syntax.

if your Access query looks like so:

    SELECT  * 
    FROM    Table_1 INNER JOIN Table_2
    ON      Table_1.item_X = Table_2.item_Y


you should be able to use either of the following queries under MySQL:


    SELECT  *
    FROM    Table_1 JOIN Table_2
    WHERE   Table_1.item_X = Table_2.item_Y
    
    or more simply:

    SELECT  *
    FROM    Table_1, Table_2
    WHERE   Table_1.item_X = Table_2.item_Y


an inner join simply combines information from rows which meet the
selection criteria in the WHERE condition.   it's the default
relationship between tables.

outer joins combine all the items in one table with information from
only the rows in the other table which meet the matching condition.   a
left outer join bewteen Table_1 and Table_2 would have one row for every
row in Table_1, with additional information from Table_2 appended to
rows which meet the selection condition.   by the same token, a right
outer join bewteen Table_1 and Table_2 would retain all the information
in Table_2, and only add information from Table_1 where appropriate.

there's also a critter known as a cross join, which returns every
possible combination of rows in Table_1 and rows in table_2.

outer joins and cross joins tend to be special-purpose operations, and
aren't used too much.   inner joins are what put the 'relation' in
'relational database', though.   try modifying one of your INNER JOIN
queries as shown above, and feeding it to the engine.