MySQL two-part selection

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
Post Reply