横批：鱼咬羊

大部分web引用统计系统都实现了基于站内的点击行为分析，但是对于点出到其他网站的链接，由于点出链接地址不一定都部署了相通的统计。那么这些点击行为就要在链接所在的页面在点击的时候触发统计了。传统的是中间转向页面，但这样的链接机制非常不利于SEO，给蜘蛛带来了大量的麻烦用于识别真正的目标地址；之前介绍过一些基于鼠标触发机制的页面点出统计方法： 一般是通过在点击的时候触发在当前页面插入一个 img bean，然后统计相应的img 请求实现，现在连这样的点击触发器在Google Analytics统计中也可以自定义实现了： 以下例子在页面点击的时候，会触发Google Analytics生成一个对clickto/TAGET_URL 一个自定义的地址（clickto实际上不存在的）请求访问；

<script type="text/javascript">

var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");

document.write("\<script src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'>\<\/script>" );

</script>

<script type="text/javascript">

var pageTracker = _gat._getTracker("UA-69476-1");

pageTracker._addOrganic("baidu","word");

pageTracker._addOrganic("soso","q");

pageTracker._addOrganic("vnet","kw");

pageTracker._addOrganic("yodao","q");

pageTracker._initData();

pageTracker._trackPageview();


document.onclick = function(e) {

    e = e || event;

    var el = e.target || e.srcElement;

    if ( el.tagName=='A' ) {

        pageTracker._trackPageview("\/clickto/" + window.location.href.replace("http:\/\/www.chedong.com/", "") + 

            el.href.replace("http:\/\/", "\/"));

    }

}

</script>

感谢XD同学，这个点出统计例子从very.cd上学到的；
一个好的例子胜过长篇大论，可以看到very.cd通过对onclick的触发机制，结合站内的cookie等还实现了客户回访率统计等；

When optimizing queries for customers, the first thing I do with a slow query is figure out what it's trying to do. You can't fully optimize a query unless you know how to consider alternative ways to write it, and you can't do that unless you know what the query "means." I frequently run into a situation where I'm forced to stop and ask the developers what they were trying to do with COUNT(). This is database-agnostic, not related to MySQL.

The problem is when the COUNT() contains a column name, like this:

If you know your SQL well, you know COUNT() has two meanings. 1) count the number of rows 2) count the number of values. Sometimes, but not always, these are the same thing. COUNT(*) always counts the number of rows in the result. If you write COUNT(col1) it counts the number of times col1 is not null. If it's never null, the result is the same as the number of rows.

The problem with that is that you don't know by looking at the query whether the developer wanted to count rows or values -- or, quite possibly, the number of distinct values in that column. You see, there's another form for COUNT():

So when I see a query that just does COUNT(col1) I am left with these guesses:

1. You meant to count the number of rows. You should have written COUNT(*) to convey that meaning accurately. If the column is nullable, there's a bug in your query -- it won't do what you think it does.
2. You meant to count the number of distinct values, but you left out the DISTINCT keyword. That's also a bug in your query.
3. You meant to count the number of times the column's value is not null. This in my experience is pretty unlikely -- people rarely do this.

It is impossible to read the developer's mind in these cases, so I always end up getting stalled waiting for them to reply and tell me what the query means so I can optimize it.

A good coding standard can help here -- you can quickly write a tool to grep your source code and search for instances of such constructs, and flag them as errors unless some magic comment is embedded in the code next to them:

Alas, there is a lot of misinformation on the web about COUNT(), so it is not a good place to learn about what it does. 90% of what you can find online is just wrong. So I would advise a good book on the topic, except I can't think of one -- maybe Joe Celko's books address this topic clearly? What's your recommendation?

Entry posted by Baron Schwartz | 7 comments

Add to: | | | |