Hi, I have a traffic tracker that logs visit times and IP's which I coded myself. When a user visits my site it automatically records the time as "now()" and their IP Address and it has been working fine for some time now. However I have noticed some irregularities since the 2nd Jan. The records have been inserted into the table with the correct data but not in the correct order. For the few weeks the script has been running (for the first 40k or so inputs), each record has successfully been added to the bottom of the table, and so is in order of the datetime field by default. However on the 2nd Jan for a period of about 8 hours, all the records where entered somewhere else in the table (a few hundred rows above the bottom) before returning to normal. I checked again today and it seems more records have been entered assumingly at random positions in the table. This isn't really causing a problem, as I have just added "ORDERED BY visittime" where ever I am extracting the data, but it has really got me confused! The strangest thing is that I haven't modified the PHP code or MySQL settings when the irregularities happen! It seems to happen randomly for a while and then fix itself randomly. Could it be anything to do with the New Year? Or anything to do with the quantity of records in the table 50k+? Or maybe the rate of input (about 6 to 12 thousand daily)? It would be interesting to see if anyone else has had this problem or what the cause is. --Mike
For the 100th and not final time: Database tables have no order. There is no first record in a table. There is no last record table. There is no 45th record in a table. A table is essentially a bucket of data that you dump rows of data into wily-nily. While the fields of each record stay together, the records themselves get swirled around, mixed up and intertwined. Do not expect to know where specific rows of data are when in your table. Queries are like nets you build to dip in your bucket of data to pull up just the data you want, and if specified, in the order you want. This is why if you want your data to be in a certain order when you retrieve it, you explicitly tell it to be in that order by using a ORDER BY clause.
Although I get your explanation and don't doubt it, if what your saying is completely accurate, then why is over 80% of the records already in order when I select them without an "ORDER BY" clause? I'm sure what you are saying is along the right lines, but it's not exactly a full answer. Thanks for the reply though, it's a good nudge in the right direction. As for the foul attitude, I don't really understand it. DP is a forum for discussion and to get/share help. It's not hard to grasp the concept.
Without sounding like too much, nor too little, of a dick: Order exists in your brain. People see concrete images in clouds, star clusters, tea leaves, etc. That's how our brains work, we see order where none really exists. 80% in order is still not in order. However you still patterns in the data, which is fine, but don't expect them to be there unless you explicitly tell them to be there. Suppose you had a table called 'states' and each record in it had population, state name, date admitted to the union, state capital, square mileage, a unique id and a date it was inserted into the table. If you told someone to 'Put the state table in order' and said nothing else, there are literally hundreds of ways to do that. With that many ordering possibilities even a completely random pull of data would seem to be in some sort of order. To truly find out a full answer to how and why a query without an ORDER BY clause seems to be in order, you are going to have to dig into the code that runs the database you are working with. My guess is you are going to get into memory clusters and disk allocation and other concepts that are beyond the scope of these forums.