----- Original Message -----
From: "tedd" <tedd@xxxxxxxxxxxx>
To: <php-general@xxxxxxxxxxxxx>
Cc: "Gustav Wiberg" <gustav@xxxxxxxxxxxxxx>; "Robert Cummings"
<robert@xxxxxxxxxxxxx>
Sent: Sunday, March 05, 2006 12:58 AM
Subject: Re: Mysql Rows
Hi:
Gustav said:
No, maybe not when it's a small db, but when you try to delete 50.000
posts I have a strong feeling this would be very much slower then if you
don't alter table after each deletion.
First, I'm not deleting 50,000 records -- I dropping a table and
renumbering it.
In any event, I just tested your claim on my host and I was able to
renumber 50,000 records in less than 1/2 second. Even though I tried it
several times, the results were never above 0.47 seconds. If I was dealing
with a database that was accessible to others, then I would either lock
tables or use transaction and then renumber -- but in either case the
difference in time is less than additional 1/10 of a second.
Ok, it wasn't really a claim, it was more a "feeling", but I couldn't say it
for sure, so I applogize for misleading you there! :-)
Half a second or 0.47 seconds may not be a big issue, but as I see it this
could be "unessescary time", if you could achieve the same
functionality (without doing some alter-statements) faster and without have
to lock the db. Have you tested without the alter-table statement? *just
curious*
Aha... I missed the part ...
"I was dealing with a database that was accessible to others"
Only YOU are using the db? Ok, then it would not be a problem.
I don't know if 1/2 second is a big deal in your world, or not, but it
seems a bit slow to me. I wrote splay binary tree search routine that
would perform 100,000 searches in a two million record dB in less than one
second on my Mac. And if you know what a splay algorithm is, then you also
know that it not only preforms a search but then reorders the tree each
time a search is successful and thus is very laborious. Yet the time it
took to preform 100,000 searches and reorders was still less than one
second.
Perhaps my host is running something slower -- after all, I'm only paying
$7.00 per year for the service. But with all things considered, a half
second is not that significant with a small 50,000 record dB. That's
probably less than the majority of web sites that use MySQL, don't you
think?
Ok, I got your point! :-) But there is another side to it , and that is
scalability. Ok, you don't have so many posts in your database.
Ok, it does take less then a second...
What if you had do make the db visible to other users? Then this link that
might intrest you: (it's about pitfalls with transactions as may see in the
link)
http://www.onlamp.com/pub/a/php/2003/12/18/transaction_pitfalls.htmlhttp://www.onlamp.com/pub/a/php/2003/12/18/transaction_pitfalls.html
But what If you suddenly hade to change host for some reason... The new host
server maybe wouldn't be as fast as yours is today.
And as someone mentioned, what about if your db was growing to include a
couple of million posts.
Of course, if you don't think your db will grow so much, and it works fine,
then of course you could contiuning doing the ALTER-statement thing, but
there's always two sides of the story... ;-)
"That's probably less than the majority of web sites that use MySQL, don't
you think?"
I really have no clue, I could only guess... :-)
Rod said:
*LOL* I knew those MySQL people shouldn't have made the ALTER TABLE
syntax available to just anyone. Gun --> foot --> *BLAM*. I hope to God
you never get your hands on a real database with millions of entries.
I'm glad that you were amused. Considering that I was talking about a flat
dB, then you have already shot yourself in the foot if your "real
database" is in the millions of entries and is flat. I hope to God that
normalization may be something you consider in your next database design.
In any event, it's interesting that I posted a question here and I
expected some ribbing, but I also expected something of value.
If the ALTER TABLE statement is prone to error, then I would like to know
that and why. However, I suspect that claim isn't true, it's just that
it's misuse has generated an urban myth of "Don't do that! That's
dangerous!" without any real substance other than for programer error.
*LOL* I have to say, that I agree with you on this one. I've searched on
issues/regarding ALTER TABLE, and could only find errors regarding this when
Microsoft was involved ;-)
Of course, I've run into windozes programmers who
accept the occasional crash and burn as "it comes with the territory", but
that's unfortunate to apply this "apprehension" to MySQL.
:-)
In my previous post I pleaded for someone to point out the error of my
ways and to give me an alternative, but that hasn't happened yet -- so,
does anyone want to tell me why I should not renumber a flat database and
give me an alternative? I'm all ears...
I have thought of this...
...but isn't there a function in MySQL or PHP to retrieve the absolute
recordnr of a db???
...if there is not, then your code IS good, if you absolutelty need the
functionality! :-)
/G
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
