Feb 09, 2006 12:48 pm eh, i just put in an 'ORDER BY' instead. problem solved.
Seems funny, as soon as I post a question about something, the answer pops in my head. heh.
Thanks =)
perplexing MySQL problem.
Posted on Feb 09, 2006 12:42 pm
pjk
Prince CZAR-ming
Member Since: Apr 08, 2004
for those into mysql (dB, i'm looking your way . . .)
i'm using a html/php form to add records to a simple db, vendors.
I can run the sql insert command and all is well, but the record doesn't always end up at the end of the table. sometimes it goes at first record, sometimes at end.
I'm thinking there's something I can do to make it always end up as the last record, but for the life of me, I can't find it. The few pages I can find regarding this says it adds records to the end of the table. ugh.
Any advice?
[ Back to Top ]
Feb 09, 2006 12:48 pm eh, i just put in an 'ORDER BY' instead. problem solved.
Seems funny, as soon as I post a question about something, the answer pops in my head. heh.
Thanks =)
Feb 09, 2006 12:49 pm Well, this is an optimization issue...much like fragmented hard drives, tables fragment.
As you insert and delete rows things get goofy, if you delete, say, row two of a ten row table, adding the eleventh record will put that row into the space row two was deleted from...or something like that, I have noticed the behavior myself.
You can view these rows in whatever order your want and make it appear at the end...what you need to do is mark each table row with an auto_increment primary key or a time stamp (I suggest the auto_increment primary key), then, when displaying the rows, regardless of where MySQL puts them, simply use the ORDER BY clause to order them by the primary key or timestamp...
Feb 09, 2006 12:49 pm Bah, OK, you already figured it out...good on ya.
Feb 09, 2006 12:59 pm i use the primary key, and auto-increment, and do most of my navigation with the row pointer.
I was thinking right along with the same thing you've mentioned. If I delete a record, then there's more of a chance that new new record will appear at the top. If i keep adding records, they sometimes end up at the bottom.
This would make me think of the overhead in sorting (ordering) large tables, but for me, now, it'll only be a few dozen or so, so no big deal.
Thanks for the input.
Feb 09, 2006 01:03 pm MySQL won't suffer performance issues on simple tables like that for many thousands of rows, I typically start seeing problems when rowcount gets up over a half a million or so...
Also, maybe try never "deleting" a row, but just flaging it as inactive...if you are running relational tables in any way, deleting is never cool, just deactivating and adding that flag to the query...if that table is being ralted, to say products you carry, and you delete the vendor, the product queries can break...
WHERE active=1
or something like that...just making it a binary true/false or 1/0 type of thing. That will keep queries from breaking and keep rows being inserted at the end...win/win situation :-)
never "delete" data :-)
Feb 09, 2006 01:18 pm cool beans, i'll definately keep that in mind.
(now that i've already gotten my delete script to work =) )
actually, i don't think it'll take much to change from delete to deactivate.
Thanks for the tips.
|
Related Forum Topics: |
If you would like to participate in the forum discussions, feel free to register for your free membership.