Query optimization or better table design?
Posted by: Bradley Proctor
Date: May 02, 2009 10:32PM

A website that I am working on needs to be able to keep track of every time an advertisement is displayed. I have two tables

# Each ad view gets a record in this table
ad_views:
ad_id INT UNSIGNED
track_id INT UNSIGNED

# Each page hit gets a record in this table
tracking:
track_id INT UNSIGNED AUTO_INCREMENT
domain_id INT UNSIGNED
... (several more fields)


My query to find the number of ads viewed on a particular domain is...

SELECT COUNT(*) FROM ad_views a
LEFT JOIN tracking t ON a.track_id = t.track_id
WHERE t.domain_id="3"

This is works fine except that by the end of the year we expect to have ad_views containing at least 3 million records and tracking at least 500,000.

My tests show that this query takes about 60 seconds. I've tried changing the indexes around every which way I can think of, but I can't get better that about 60 seconds, which is about 59.9 seconds slower than what I need.

I'm sure there must be a simple solution to this. Possibly a better way to store the data?

Options: ReplyQuote


Subject
Written By
Posted
Query optimization or better table design?
May 02, 2009 10:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.