speeding up my sql

Hi guys,
not sure if this should go in this forum or a (my)sql one, but damn it i'm using php, anyways..

I've written a (simple) hits tracker for my site, it records the Date, VisitorIP, Page Hit and Page Refered From, this is all stored in one big table:
hit_id int(10) unsigned MUL auto_increment
hit_time timestamp(14)
hit_host varchar(255)
hit_page varchar(255)
hit_refer varchar(255)
hit_ip varchar(15)
(not idea whi hit_id is MUL not PRI, anyone understand this and is it significant)
Everypage that is hit puts the appropriate data in to the database, then it coes to the viewing!
The page that views all the hits is very slow, sometimes 3 or 4 seconds (for about 1200 records), all it does is selects this data from the database and runs through a while loop, displaying it in a lovely table, with a total number of hits and time taken for the script to execute.

The SQL i am using is 'SELECT * FROM php_hits WHERE hit_time > ' . $date . ' ORDER BY hit_time ASC' where $date is the date to limit it all by. i.e. you want all hits since december $date = 20031201000000 (i think).

I also play with the data (a little) in the while loop, converting timestams to readable messages (28 December 2003 - 16:22 for example), ripping the page from the hit url, taking of http://www. from all referring pages and the like, could this be the biggie slowing me down? I am using my own fuctions for this not any of PHPs and there may be a better way of doing this.

My current thinking is either, there is a better way to do the tables / SQL that i'm not aware of, my functions in the loop are slowing it all down (when they are executed 1000+x), and i should probably do something about that, like ripping the page from the url when put INTO the db not taken OUT OF it! (i'm kinda lazy and that sounds challenging!)

So if anyone has any suggestions on how to make it quicker (not using go faster stripes pls!) then it would be much appriciated.

Sorry about the long post but i have just improved my code quite a bit but am v. sure the are ways to make it 100x better,
Cheers,

Matt

I'd say you'd probably want hit_id to be a primary key. How it became a MUL, I can't guess. Did you create it that way?

You might try indexing the hit_time field. I don't know that that'd give you a big performance increase, though.

It might help you out to use mysql's DATE_FORMAT function to format some of the dates, and I would suggest stripping the page from the URL on the way into the database. Perhaps you could try using getenv("REQUEST_URI") to insert into the database -- that should give you everything after the domain name and require no up-front (or back-end) processing.

yeah i was sure i made hit_id the primary key, but i guess not!

I think you are on a winner with the inserting without domains and just displaying it, should take a chunk of processing out of it, especially with it being in the loop and all, i'll give it a go, and have a look at DATE_FORMAT

what does indexing the hit_time field do? how would i do it?

Indexing the hit_time field may speed up queries on that field. I'm no expert on indexing, so I can't certify that it will or anything. I never can recall the syntax for adding an index off the top of my head. Check out mysql.com and search on either the alter table syntax or just index.

If you create indexes in your database with the fields you most call you will speed up a lot the mysql acces and triggers.

Daniel.
____________________________
Webmaster of Hotel Netguide & Barcelona Guide Sites.