Will,
First of all, if I sounded condescending, it was not my intent. I apologize
if that's how you (or anyone else) took it. I will, however, respond to
your smoking response to mine:
> > I think no-one's replied because the answer is really simple
>
> Oh I don't know Jay, I've seen some threads 4-5 responses deep to
> issues far simpler than this.
That does not make those questions and answers on-topic and appropriate or
your question any more complex.
> I understand that you could use REPLACE with this newly created
> recordset to simulate the UPDATE w/JOIN, but your "it's so simple"
response
> glosses over the details of what to do with the delete. The only way I've
conceived is
> to carry an "isdeleted"...
Perhaps you're using the wrong scripting language. Just because you have a
hammer does not make everything a nail.
> Well I'm happy for you but did you ever stop and consider that what you do
> with your database may not necessarily be representative of what everyone
> does?
Let's see - I read records, I do something with them, I update and delete
them... No, I never stopped to think that you (or anyone) might be doing
something different.
> In my situation, one that is certainly not unique, I have
> tables with 100+ fields and many million rows. The SELECT INTO/REPLACE
FROM
> and SELECT INTO/REPLACE FROM/DELETE WHERE approaches to UPDATE FROM and
> DELETE FROM are an order of magnitude less efficient.
> This inefficiency coupled with the
> need (using myisam) to lock the entire table during updates makes for some
> not-so-simple design challenges.
So you feel that forcing a lock while you do a huge join/delete is more
efficient than:
* Doing a select (using joins) against your properly indexed table (which
should NOT require a lock in that you only want the record ID's)
* Using the method of your choosing, deleting or updating the records so
identified? I don't know the benchmarking which compares a WHERE IN()
versus issuing individual updates/deletes while the table is open, but it
would be easy enough to test.
> In closing, I offer a couple rhetorical questions:
> 1. Who is the bigger idiot... The person who asks a naive question or the
> person who, upon hearing a question he knows the answer to, assumes the
> asker is an idiot?
Never did I assume that you were an idiot. What I assumed (and still do) is
that you are overcomplicating the question.
> 2. Does your condescending attitude win you as many points with your users
> as it did with me?
>
> Will French
>
I haven't had a complaint from my users yet. Again, If I sounded
condescending, it was not my intent.
Jay Fesco
---------------------------------------------------------------------
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
