CODEWITHSUNDEEP
phpmysql
Kenny
Kenny

Reputation: 1

Modifying mysql database for efficiency

Currently the company I am employed for stores page visits via mysql to give their client daily traffic stats. There table structure is:

visit_id, page_id, ts, ip, uri, site_id

So far there are 85 million rows....when only 2200 accounts exist.

The format it spits the stats to client is:

Total visits: XXXX Last 24 hour visits: XXXX

What is the best way to restructure this while maintaining it's current functionality?

Upvotes: 0

Views: 56

Answers (1)

Parris Varney
Parris Varney

Reputation: 11478

I'm guessing visit date is in a separate visit table, you'll get better throughput if you denormalize that.

Precalculating your results will make your stats query run a lot faster, below is a general idea of what you'd do.

CREATE TABLE stats_table (
,  site_identifier        INT
,  date_visited           DATE
,  visit_count            INT
,  INDEX(site_identifier)
);

Instead inserting into this table for every visit, you'd increment visit count. Then you could query it like:

SELECT site_identifier, visit_count FROM stats_table where date_visited = 'today';

SELECT site_identifier, SUM(visit_count) AS `total_visits` FROM stats_table GROUP BY site_identifier;

You could combine those two queries, but it'll prolly be faster to query them separately.

Upvotes: 1

Related Questions