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.
mike stone <[EMAIL PROTECTED]> 'net geek..
been there, done that, have network, will travel.
____________________________________________________________________
--------------------------------------------------------------------
Join The NEW Web Consultants Association FORUMS and CHAT:
Register Today at: http://just4u.com/forums/
Web Consultants Web Site : http://just4u.com/webconsultants
Give the Gift of Life This Year...
Just4U Stop Smoking Support forum - helping smokers for
over three years-tell a friend: http://just4u.com/forums/
To get 500 Banner Ads for FREE
go to http://www.linkbuddies.com/start.go?id=111261
---------------------------------------------------------------------
