Page 3 of 3

Posted: Sat Jun 04, 2005 5:10 pm
by timvw
Attention: code is untested... As always: make sure you have a backup :p

Code: Select all

CREATE INDEX ip_idx ON visitors (ip)
CREATE INDEX timestamp_idx ON visitors (timestamp)

Not very certain, but meaby that saving the ip as an integer might increase speed even more...

Code: Select all

ALTER visitors
ADD ipnew INTEGER

UPDATE visitors
SET ipnew= substring(ip, 1, 3) * 256^3 + substring(ip, 5, 3) * 256^2 + substring (ip, 9, 3) * 256 + substring(ip, 13, 3)

ALTER vistors
DELETE ip

ALTER visitors
RENAME ipnew ip

Posted: Sun Jun 05, 2005 9:26 am
by RobertGonzalez
Todd_Z wrote:Timvw, that works perfectly, however, it takes sooo long to do. The load time for the page is unacceptably long. Is there anyway to do this much faster? Would it be better if i just caved and did it in php?
Are you using this SQL in a PHP app? If you are, the you can couple mysql_num_rows with the result, read that into a var and echo that var throughout your app instead of running this query over and over again. Also, what you are looking for might be better selected from an OUTER JOIN instead of an INNER JOIN.

Just a thought.