Chad,
I'm still running v1.2 b3 and felt that it would be appropriate to mention that I added an index on the "pl_datetime" field of the "PageLog" table.
Since all the queries are dealing with that information, I thought it could improve the performance a bit.
With a database with roughly 10,000 records, I was able to see improvement, so the odds are that with larger databases, the people would benifit too.
I'd appreciate of people would be able to do the same, and report improvements, if any, before concluding this is the way to go.
Database Indexes
Isn't there a disadvantage to this, when it takes longer to add an entry to the table because the index must be recalculated each time? That would slow pages down a bit. I think I'd prefer to take extra time in calculating stats just for me than slow the site down for everybody. I wouldn't expect the slowdown to be annoying until 50,000 records or so, though.
Database Indexes
quote:Originally posted by b.e.wilson
Isn't there a disadvantage to this, when it takes longer to add an entry to the table because the index must be recalculated each time? That would slow pages down a bit. I think I'd prefer to take extra time in calculating stats just for me than slow the site down for everybody. I wouldn't expect the slowdown to be annoying until 50,000 records or so, though.
You're right, updating the indexes when an INSERT is performed can consume some resources. How much, this is difficult to calculate.
However, I splitted the reporting section and the logging logic into 2 different files, although I kept the same class name.
By the time the overhead induced by the index update will be as meaningfull as the overhead induced on the server to load the 200KB script to log an entry in the log, the odds are that it will be time to remove records from the DB anyhow.
I sincerely doubt I'll keep as many as 50,000 records in the db at any point of time, however since I changed the structure quite a bit, the DB wouldn't be too large when reaching that quantity.
I'm actually implementing a process that will be invoked perhaps on a weekly or bi-weekly basis, which will extract the "aged" records and save them into a "csv" file which can be created and downloaded on the fly. The corresponding records will then be removed from the live DB upon confirmation, and if the DB is an Access file, the "admin" will be prompted to compact it as well. The compaction process will automatically de-activate the logging while compacting and re-enable it upon completion.
That "csv" file will then be appended to a local database used as an archival repository for further analysis. What will be done with the data used locally is yet to be determined.
People have different needs and ways I guess.
Regards.