2

I need to store statistics of my website.

I need these

1: I can query how many hits in day, week month, last month, etc based on a category. 2: Then I can sort stats according to countries, day, pages etc.

I know these way to do it

Table all_stats

| category | page_url | country | date |....... | x | index.php | US | timestamp |....... | y | index2.php | UK | timestamp |....... . .

Then I can query as 

SELECT * FROM all_stats WHERE category=x AND date<=today AND date>=last week This works fine but problem with this is as database increase query takes a lot of time to execute.

To solve query speed store daily country based stats from above table in other table with 256 countries and island etc like

Table daily_stats

| category | page_url | US | UK |.......| date | | x | index.php | 1000 | 1500 |.......| 1st | | y | index2.php | 1500 | 2000 |.......| 2nd | . .

Then I can query like

SELECT SUM(US) AS us SUM(Uk) AS uk . . //all 256 countries and island etc . . WHERE category=x AND date<=today AND date>=last week But this will be a big table with many columns

Another approch I see is

Creating table smart_stats 

| category | page_url | country_array .......| date | | x | index.php | US=>1000, UK=1500 .......| 1st | | y | index2.php | US=>1500, UK=2000 .......| 2nd | . . This can be smart approach if I can add all country stats in some way

Please suggest a way to make possible query from table smart_stats country_array that I can sort in any way I like

Or suggest what you think will be the best approach to this

Thanks

Improve this question
3
  • Add index on DATE column Commented Mar 13, 2016 at 13:49
  • Please include the results of the query when using EXPlAIN Commented Mar 13, 2016 at 13:50
  • do you view the results in the workbench, sqlcommand line or you have a gui? better make a gui with pagination to reduce fetch time. also add an AI primary key.. will be efficient that way Commented Mar 13, 2016 at 13:52

2 Answers 2

Reset to default
1

Your all_stats structure is probably fine. This query:

SELECT * FROM all_stats WHERE category = x AND date <= today AND date >= last week

can benefit from an index. I would actually recommend two: (category, date) and (date). The first should speed up the query considerably. The second handles queries that are not looking for a specific category.

If you generally want all countries at the same time, you might find that pivoting the countries helps. It actually reduces the absolute size of the table as well as the number of rows -- if all or most countries are represented each day. The problem arise in querying a table with hundreds of columns, as well as maintaining the table for new countries (and MySQL has a limit on the number of columns in a table, so what works for countries might not work at the province/state/city level).

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Will the index work if rows are more than 1 million in a given month
@TallboY . . . It should. Tables with a millions of rows are easily handled. You might want to also consider partitioning the table if you know that all (or most) queries will include date ranges.
I don't know enough about partitioning but Ill learn, my concern is after partitioning table can I query from partitioned part of table.
@TallboY . . . Yes, of course. You get the benefits of partitioning when you are using the partitioning key in the where clause of a query, and the most natural partitioning key is the date.
Ok, Ill try index with partitioning.
0

You can add the following index:

ALTER TABLE `all_stats` ADD INDEX `categoryDate` (`category` ASC, `date` ASC) COMMENT '';
Improve this answer

5 Comments

Will the index work if rows are more than 1 million in a given month
It would. But there are more solutions in this case (like table partitioning)
I don't know enough about partitioning but Ill learn, my concern is after partitioning table can I query from partitioned part of table.
Yes you can, but read some tutorials before you attempt to add partitions to the table, to make sure you're doing it right
Ok, Ill try index with partitioning, I want to accept your answer as well but cant. Thanks for the link.