gistfile1.txt

#Entities table:


+----+------------+-------------------+--------------------------+----------------------+
| id | dataset_id | twitter_id        | name                     | value                |
+----+------------+-------------------+--------------------------+----------------------+
|  1 |          1 | 60129190850211840 | hashtag                  | goblue               |
|  2 |          1 | 60129190850211840 | user_mention_id          | 71023061             |
|  3 |          1 | 60129190850211840 | user_mention_screen_name | CollegeGameDay       |
|  4 |          1 | 60129190799876096 | user_mention_id          | 138672856            |
|  5 |          1 | 60129190799876096 | user_mention_screen_name | SASAPY666            |
|  6 |          1 | 60129190984417280 | url                      | http://bit.ly/hORxXH |
|  7 |          1 | 60129190976028672 | hashtag                  | syria                |
|  8 |          1 | 60129190976028672 | hashtag                  | homs                 |
|  9 |          1 | 60129190976028672 | user_mention_id          | 268769345            |
| 10 |          1 | 60129190976028672 | user_mention_screen_name | ahmad_Ja             |
| 11 |          1 | 60129191026364416 | hashtag                  | VWBeetle             |
| 12 |          1 | 60129190455939072 | user_mention_id          | 16040961             |
| 13 |          1 | 60129190455939072 | user_mention_screen_name | YUCKYBOT             |
| 14 |          1 | 60129190414004224 | hashtag                  | DWTS                 |
| 15 |          1 | 60129190414004224 | hashtag                  | teamkanenball        |
| 16 |          1 | 60129190414004224 | user_mention_id          | 268414482            |
| 17 |          1 | 60129190414004224 | user_mention_screen_name | MileyCyrus           |
| 18 |          1 | 60129190414004224 | user_mention_id          | 27592116             |
| 19 |          1 | 60129190414004224 | user_mention_screen_name | ChelseaKane          |
| 20 |          1 | 60129190414004224 | user_mention_id          | 1357141              |
+----+------------+-------------------+--------------------------+----------------------+


+----+-------------------+-----------+-------------------------------------+----------+-------------+----------------------+-----------------------+---------------------+-----------+-------------------------+---------------------+---------------+------+------+---------------------------------------------------------------------+-----------+------------+------------+---------------+---------------+--------------+
| id | twitter_id        | user_id   | text                                | language | screen_name | location             | in_reply_to_status_id | in_reply_to_user_id | truncated | in_reply_to_screen_name | created_at          | retweet_count | lat  | lon  | source                                                              | retweeted | dataset_id | link_count | hashtag_count | mention_count | entity_total |
+----+-------------------+-----------+-------------------------------------+----------+-------------+----------------------+-----------------------+---------------------+-----------+-------------------------+---------------------+---------------+------+------+---------------------------------------------------------------------+-----------+------------+------------+---------------+---------------+--------------+
|  1 | 60129190850211840 | 237717002 | @CollegeGameDay OHIO STATE. #goblue | NULL     | Jstephans51 | Brochester, Michigan |     60128793708343296 |            71023061 |         0 | CollegeGameDay          | 2011-04-18 23:54:50 |             0 | NULL | NULL | <a href="http://www.tweetcaster.com" rel="nofollow">TweetCaster</a> |         0 |          1 |          0 |             1 |             1 |            2 |
+----+-------------------+-----------+-------------------------------------+----------+-------------+----------------------+-----------------------+---------------------+-----------+-------------------------+---------------------+---------------+------+------+---------------------------------------------------------------------+-----------+------------+------------+---------------+---------------+--------------+

Eventually, I'd see the query turning out to be something like: 


select tweets.*,count(distinct entities_l.id) as links_count,count(distinct entities_m.id) as mentions_count,count(distinct entities_h.id) as hashtag_count,count(distinct entities_a.id) as all_entities_count
from tweets 
left join entities as entities_l on entities_l.twitter_id = tweets.twitter_id 
left join entities as entities_m on entities_m.twitter_id = tweets.twitter_id 
left join entities as entities_h on entities_h.twitter_id = tweets.twitter_id 
left join entities as entities_a on entities_a.twitter_id = tweets.twitter_id 
where 
  (entities_l.name="url" or entities_l.name is null) and 
  (entities_m.name="user_mention_screen_name" or entities_m.name is null) and 
  (entities_h.name="hashtag" or entities_h.name is null) and 
  entities_a.name in ("url", "user_mention_screen_name", "hashtag") and 
  tweets.dataset_id = 1 group by tweets.id limit 1\G


### This returns the following record:

*************************** 1. row ***************************
                     id: 233
             twitter_id: 60129190720192512
                user_id: 75136923
                   text: False pretense for war in #Libya ? http://t.co/NFvZe4P @BarackObama lied and waisted millions in #Oil war.  #us #france #uk #nato #UN #Cuba
               language: NULL
            screen_name: DJSabroso
               location: ?
  in_reply_to_status_id: NULL
    in_reply_to_user_id: NULL
              truncated: 0
in_reply_to_screen_name: NULL
             created_at: 2011-04-18 23:54:50
          retweet_count: 0
                    lat: NULL
                    lon: NULL
                 source: web
              retweeted: 0
             dataset_id: 1
            links_count: 1
         mentions_count: 1
          hashtag_count: 8
     all_entities_count: 10
1 row in set (22.51 sec)

When clearly, it is 8 hashtags, 1 link, and 1 mention, for sum of 10.

But that is only going to return tweets where there are links and mentions and hashtags present - ideally, I would just get counts of zero if they weren't present, but instead it just skips those records entirely.