MySQL Table Design – Optimized Structure

database-designMySQL

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?

Best Answer

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

Related Question