Mysql – Getting total number of counts for distinct entrys for the same id

MySQLPHP

I have a table which is like this.

CREATE TABLE `ad_analytics` (
  `id` int(90) NOT NULL,
  `ad_id` int(90) NOT NULL,
  `advertiser_id` int(90) NOT NULL,
  `publisher_id` int(90) NOT NULL,
  `visitor_ip` varchar(250) NOT NULL,
  `type_ad` varchar(90) NOT NULL,
  `impression` int(90) NOT NULL,
  `view` int(90) NOT NULL,
  `clicks` int(90) NOT NULL,
  `date_event` date NOT NULL,
  `placed_date` date NOT NULL,
  `ending_date` date NOT NULL,
  `cpc` int(60) NOT NULL,
  `cpv` int(60) NOT NULL,
  `cpi` int(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I want to count the number of impressions, views, and clicks for a particular id for distinct IP Aka if there are four impressions from the same IP then it should be counted as one impression for a particular IP. How can I do that?

Sample data.

INSERT INTO `ad_analytics` (`id`, `ad_id`, `advertiser_id`, `publisher_id`,
    `visitor_ip`, `type_ad`, `impression`, `view`, `clicks`,
    `date_event`, `placed_date`, `ending_date`, `cpc`, `cpv`, `cpi`)
VALUES
(34, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(35, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(36, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(37, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(38, 50, 113, 109, '::1', 'Video', 0, 0, 1, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0),
(38, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-04-16', '0000-00-00', '0000-00-00', 0, 0, 0);

I am expecting the result like this.

ad_id  Impression views click
49       1         0      1
50       0         0      1

Best Answer

First you need to group by ad_id and then group by visitor_ip then select max for impression, view and click

so your query would look like

SELECT visitor_ip, ad_id,MAX(impression), MAX(view), MAX(clicks)
FROM ad_analytics
GROUP BY ad_id, visitor_ip;

NOTE : You should avoid using reserved keywords as column name.

YOUR QUERY

SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;

At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506.

REALITY CHECK

No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them can fight to one enemy they all have in common : The InnoDB Architecture.

InnoDB Architecture

CLUSTERED INDEX

Please keep in mind that the each threadid entry has a primary key attached. This means that when you read from the index, it must do a primary key lookup within the ClusteredIndex (internally named gen_clust_index). In the ClusteredIndex, each InnoDB page contains both data and PRIMARY KEY index info. See my post Best of MyISAM and InnoDB for more info.

REDUNDANT INDEXES

You have a lot of clutter in the table because some indexes have the same leading columns. MySQL and InnoDB has to navigate through the index clutter to get to needed BTREE nodes. You should reduced that clutter by running the following:

ALTER TABLE newbb_innopost
    DROP INDEX threadid,
    DROP INDEX threadid_2,
    DROP INDEX threadid_visible_dateline,
    ADD INDEX threadid_visible_dateline_index (`threadid`,`visible`,`dateline`,`userid`)
;

Why strip down these indexes ?

The first three indexes start with threadid
threadid_2 and threadid_visible_dateline start with the same three columns
threadid_visible_dateline does not need postid since it's the PRIMARY KEY and it's embedded

BUFFER CACHING

The InnoDB Buffer Pool caches data and index pages. MyISAM only caches index pages.

Just in this area alone, MyISAM does not waste time caching data. That's because it's not designed to cache data. InnoDB caches every data page and index page (and its grandmother) it touches. If your InnoDB Buffer Pool is too small, you could be caching pages, invalidating pages, and removing pages all in one query.

TABLE LAYOUT

You could shave of some space from the row by considering importthreadid and importpostid. You have them as BIGINTs. They take up 16 bytes in the ClusteredIndex per row.

You should run this

SELECT importthreadid,importpostid FROM newbb_innopost PROCEDURE ANALYSE();

This will recommend what data types these columns should be for the given dataset.

CONCLUSION

MyISAM has a lot less to contend with than InnoDB, especially in the area of caching.

While you revealed the amount of RAM (32GB) and the version of MySQL (Server version: 10.0.12-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4002), there are still other pieces to this puzzle you have not revealed

The InnoDB settings
The Number of Cores
Other settings from my.cnf

If you can add these things to the question, I can further elaborate.

UPDATE 2014-08-28 11:27 EDT

You should increase threading

innodb_read_io_threads = 64
innodb_write_io_threads = 16
innodb_log_buffer_size = 256M

I would consider disabling the query cache (See my recent post Why query_cache_type is disabled by default start from MySQL 5.6?)

query_cache_size = 0

I would preserve the Buffer Pool

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

Increase purge threads (if you do DML on multiple tables)

innodb_purge_threads = 4

GIVE IT A TRY !!!

MySQL : Avoid Temporary/Filesort Caused by GROUP BY Clause in Views

OBSERVATION #1

The MySQL Documentation gives you the Restrictions on Views

It is not possible to create an index on a view.

Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).

Subqueries cannot be used in the FROM clause of a view.

Therefore

The result set of a View would be some temp table used for row-by-row retrieval
There is no existing mechanism to index the result set coming from a view

Creating indexes only help the underlying base tables within the JOIN a the View, not the View itself.

OBSERVATION #2

You mentioned this

Some suggest to do aggregation in subquery before join which is not possible as MySQL doesn't allow subquery in FROM clause.

That's not true. I have a post in StackOverflow (Fetching a Single Row from Join Table) that clearly demonstrates doing an aggregation in a subquery followed by all kinds of JOINs to that subquery.