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.
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?
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. 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...
Thanks in advance for any replies. tedd -- -------------------------------------------------------------------------------- http://sperling.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
