2

I am building an advertisements system for websites ( a plugin for a framework ). I am not quite sure how I should set up the table in terms of indexes.

Basically there are two options for billing - by impressions and by clicks, so I guess the table fields ( initially, without customization ) would be

this may not be valid code, just a representation

`id` int(10) unsigned AUTO_INCREMENT,
`headline` VARCHAR(100),
`text` VARCHAR(255),
`url` VARCHAR(255),
`impressions` int(10) unsigned NULL,
`clicks` int(10) unsigned NULL,
`threshold` int(10) unsigned

Where threshold will be the amount of impressions/clicks paid for, and respectivelly impressions or clicks will hold the same amount initially, and will then decrease as the advertisement gets attention accordingly.

I'm having my doubts about this design because one of the impressions or clicks columns has to be empty at all times, and this just doesn't seem right. Also my select queries will look a little messy

SELECT (fields) FROM adverts WHERE ( clicks > 0 OR impressions > 0)

And then from the selected advertisements I have to reduce impressions

UPDATE adverts SET impressions = impressions - 1 WHERE id IN (selected previously)

But if the advertisement is paid as clicks, setting NULL to NULL - 1 doesn't make much sense, but seems to work, surprisingly for me.

Also what I'm more concerned about are indexes. Having in mind that most probably there will be an audience selection option, where advertisers will be able to chose their audience by, mainly, age and location. I'd like to ask, how are indexes used in such situations where there are going to be such complex queries and multiple possible columns to validate the record from ( clicks and impressions )? Indexing either of them is pointless because the driver can only use one index and is still going to have to go through all other records. This makes me think that I'm using the wrong schema, is there a better way?

  • Question, is the impressions and clicks holding the max total for the end users campaign? In meaning, if they paid for 1,000 impressions and 1,000 clicks, then the values of 1,000 is held in all 3 fields (clicks, impressions and threshold)? – Glen Swan Sep 25 '14 at 13:47
  • Also, is the impact of impressions being considered here? Click to impression ratio is pretty dramatic as in 5 million impressions versus 100,000 clicks. This could warrant a rethink in the modeling/structure when trying to combine both clicks and impressions into one table. – Glen Swan Sep 25 '14 at 13:56
  • @GlenSwan I think it's better, in terms of simplicity, to only allow one campaign to be either by impressions OR clicks. In other words if the client wants to have both impressions and clicks he would rather have to create two identical campaigns, which can also be added as an additional option to be done automatically, but that's details. Furthermore the threshold field is only holding the initial amount for informational purpose, as you see I'm using decreasing rather than increasing manner. – user48134 Sep 25 '14 at 14:48
  • I'm more concerned about how to index this database, because indexing impressions or clicks is pointless because the driver can only use one index and is still going to have to go through all other records. This makes me think that I'm using the wrong schema, is there a better way? – user48134 Sep 25 '14 at 15:01
  • Have you thought about campaign flags then? CPA, CPC, CPM, Search, Display and ETC? This can help unify the campaign goals, which may be better to query and possibly index on versus checking if clicks or impressions exist. – Glen Swan Sep 25 '14 at 15:23
1

I'll get a answer started for you on the multiple column index questions.

Multiple Column Indexes

CREATE INDEX myindex ON table (clicks, impressions);

MySQL can use multiple-column indexes for queries that test all the columns in the index, or queries that test just the first column, the first two columns, the first three columns, and so on. If you specify the columns in the right order in the index definition, a single composite index can speed up several kinds of queries on the same table.

Example

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

The name index is an index over the last_name and first_name columns. The index can be used for lookups in queries that specify values in a known range for combinations of last_name and first_name values. It can also be used for queries that specify just a last_name value because that column is a leftmost prefix of the index (as described later in this section). Therefore, the name index is used for lookups in the following queries:

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test
  WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius'
  AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test
  WHERE last_name='Widenius'
  AND first_name >='M' AND first_name < 'N';

However, the name index is not used for lookups in the following queries:

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test
  WHERE last_name='Widenius' OR first_name='Michael';

Your Query

This query would not be used in lookups on the index.

SELECT (fields) FROM adverts WHERE clicks > 0 OR impressions > 0;

But this one would.

SELECT (fields) FROM adverts WHERE clicks > 0;

This is why I suggested campaign flags because you can combine the flags in your index to indicate if clicks, impressions OR both are present.

CREATE INDEX myindex ON table (campaign_flag);

To query

SELECT (fields) FROM adverts WHERE campaign_flag = 'Search';  --Clicks Exist
SELECT (fields) FROM adverts WHERE campaign_flag = 'Display'; --Clicks/Impressions Exist

Campaign Flag / Campaign Type Suggestion

If this is for digital marketing, campaign flags or campaign types could be used to further filter down to the clicks and impressions you are really needing.

Let's say there are 1,000 records in the table. If you query looking for both clicks and impressions > 0, then you will search all 1,000 records for a result.

SELECT 
   (fields) 
FROM adverts 
WHERE campaign_flag = 'Display' --Searching for Clicks and Impressions
AND (clicks > 0 OR impressions > 0);

Now let's add campaign_flag field to the table and divide our data up into segments. 500 records are marked as 'Display' and 500 are marked 'Search'. This is a much better approach to index on as opposed to the full 1,000.

SELECT 
   (fields) 
FROM adverts 
WHERE campaign_flag = 'Search' --Searching for just Clicks
AND clicks > 0;

Compensation Models

Compensation models go hand-and-hand with campaign flags or types. Both Search and Display could have a range of different models now and in time. Knowing you have 500 Display records in the previous example, you could also have 250 that are CPC and the other CPM

SELECT 
   (fields) 
FROM adverts 
WHERE campaign_flag = 'Display' --Searching for Clicks and Impressions
AND cmodel = 'CPM' --Further filtering based on type of model
AND clicks > 0;

http://www.basimos.com/web-marketing/internet-marketing-glossary-c.htm

|improve this answer|||||
  • So basically with the setup in the example, would the index be used on SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael';. Aside from that, now I understand what you mean with the concept of flags, however I would still need to set up at least 2 complex indexes, because apart from the flag I also need to select if the advertisement has imperssions / clicks still available, along with age, location and other stuff on joined tables. – user48134 Sep 25 '14 at 16:22
  • I mean that simply by having a flag display for example, doesn't guarantee me that the record's impressions field is greater than 0? – user48134 Sep 25 '14 at 16:24
  • You are correct, but if you further filter by impression > 0 then you can still index on another column that will ensure that only campaigns that COULD have impressions are returned. If not, you're going to query records that never have impressions such as those with paid search where they will likely only have clicks. That could reduce the query time if you begin everything on the campaign type and then further filter from there on specific query needs. Does that make sense? – Glen Swan Sep 25 '14 at 17:39
  • It also occurred to me that doing more research on compensation models for digital marketing campaigns may have you too. You can use those models to further filter your data to give you faster results too. That's where CPC, CPM, CPA and ETC come into play. Paid Search for example would be Cost Per Click (CPC). So searching 'Search' and then further filtering by 'CPC' and Clicks > 0 could be far more efficient going forward. – Glen Swan Sep 25 '14 at 17:55
  • Let me ask you one final question. If I were to do as I initially intended, and allow one campaign to only be made as impressions or clicks ( not both ), would putting two separate indexes on both columns make sense and work in the case of WHERE impressions > 0 OR clicks > 0. If not do you think it would be a good idea to run two queries instead, perhaps with a UNION, in which case I suppose two indexes would be used? Thank you for your guidance! – user48134 Sep 25 '14 at 17:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.