Question

我有一个拥有谷歌分析数据的MySQL表:

CREATE TABLE IF NOT EXISTS `analytics_data` (
  `ga_profile_id` int(11) NOT NULL,
  `page` varchar(200) NOT NULL,
  `source` varchar(150) NOT NULL,
  `medium` varchar(50) NOT NULL,
  `keyword` varchar(200) NOT NULL,
  `bounces` int(11) NOT NULL,
  `entrances` int(11) NOT NULL,
  `exits` int(11) NOT NULL,
  `new_visits` int(11) NOT NULL,
  `page_views` int(11) NOT NULL,
  `unique_page_views` int(11) NOT NULL,
  `time_on_page` int(11) NOT NULL,
  `visits` int(11) NOT NULL,
  `date` date NOT NULL,
  KEY `ga_profile_id` (`ga_profile_id`,`source`,`medium`,`date`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

我有点问,在一定时期内根据权宜分析仪表征(ga_profile_id)计算来访者人数:

SELECT 
    SUM( `visits` ), ( UNIX_TIMESTAMP( `date` ) - 21600 ) * 1000 AS date 
FROM `analytics_data` 
WHERE 
    `date` >=  2011-05-09  AND `date` <=  2011-06-08  AND `ga_profile_id` = [...]
GROUP BY `date`

We have 4.5 million records or so.

<>Index Data:

Type: BTREE
Fields/Cardinality:
ga_profile_id / 100
source / 10196
medium / 10196
date / 149893

EXPLAIN SELECT
- id: 1
- select_type: SIMPLE
- table: analytics_data
- type: ref
- possible_keys: ga_profile_id
- key: ga_profile_id
- ref: const
- rows: 219555
- extra: Using where; Using temporary; Using filesort

Average time for execution: 1 second.

We are on a virtual private server and most queries get executed in .0003 - 0.03 seconds. LONG queries (that I was going to optimize at some point) are generally .3 seconds.

I have tried adjusting the keys, ignoring some, changing some values and nothing seems to be affecting it in a positive way. Considering this is 1 of many queries on a page.

我期待着改变我的记忆——欢迎任何想法。

Answer 1

您需要在这一具体顺序中制定综合指数ga_profile_id +date/code>。你们会得到最好的答案。



进一步优化是按期预先计算访问量,并用于快速计算。

Answer 2

我有点问,在一定时期内,根据google analytics Profile ID(ga_profile_id)计算来访者的人数。

It seems pretty optimized already... In your question at the time of writing this answer, you ve stripped out the most interesting part of your query (the actual clause on ga_profile_id), which is the most selective in all likelihood -- hence the current index usage.

At the very best, you d manage to leverage an index on date if you place it in a multicolumn index, e.g. (date, ga_profile_id) or the other way around depending on your usage pattern and table statistics.

See indexes dos and donts.

Answer 3

Running indexes will be the first and easiest option but if that doesn t help I would suggest to look more into some fundamental DB management strategies like Table Partitioning.

Answer 4

页: 1 他提议的唯一候补成员是,如果在同一时期内采取行动的其他人,将纳入贵国的指数。

此外,“Bohemian”组别到第二点是一个强点......你可能希望根据全职/专栏结果的DATE ONLY部分进行分类。

Answer 5

如果你在问询中有一个典型日期,你可考虑横向分割你的表格。或许,如果你的大部分数据是“过时”的,而且你只需要一个或多个分治的“新”数据,而且所有这些旧数据都是在另一个分界线上。

友情链接