CODEWITHSUNDEEP
mysqloptimization
php_nub_qq
php_nub_qq

Reputation: 16055

Optimize frequently ran query

I have added advertisements to my website which have quite some conditions to meet before delivering to a browsing user. Here's a detailed explanation:

These are the fields that require explaining:

start is by default '0000-00-00' and it indicates whether the ad has been yet paid or not. When an ad payment is accepted start is set to the day after, or any date the customer choses.

impresssions is respectively the remaining impressions of the advertisement

impressions_total and impressions_perday are self explanatory

and the other fields used in the query are just fields that validate whether the user falls into the specifications of the advertisement's auditory

An advertisement has to be paid to start displaying in the first place, however it can be set to start on a future date so the start value will be set but the ad shouldn't show up before it is time to. Then since customers can limit impressions per day I need to pick up only advertisements that have enough impressions for the day in progress. For example if an advertisement is started in 30/08/2013 with 10,000 impressions and 2,000 impressions per day then it shouldn't be able to show up today (31/08/2013) if it has less than 6,000 impressions because it's the second day of the campaign. As well as if the term period is say 5 days, and 5 days have passed, the advertisement has to be shown regardless of remaining impressions. Then there are those other comparisons to validate that the user is fit for this ad to display and the whole thing gets so complicated.

I am not quite good with mysql, although I have managed to construct a working query I am very concerned about optimizing it. I am most certain that the methods I have used are highly inefficient but I couldn't find a better way online. That's why I'm asking this question here, if anyone can help me improve the performance of this query?

SELECT `fields`,
FROM `ads` 
WHERE (`impressions`>0 && `start`!='0000-00-00')
AND `start`<CURDATE() AND 
    (
        `impressions`>(`impressions_total`-(CONVERT(CURDATE()-date(`start`), UNSIGNED)*`impressions_perday`)) 
        OR (`impressions_total`/`impressions_perday` < CURDATE()-date(`start`))

        -- this is the part where I validate the impressions for the day
        -- and am most concerned that I haven't built correctly
    )
AND
(
    (
        (YEAR(NOW())-YEAR("user's birthday") BETWEEN `ageMIN` AND `ageMax`) 
        AND (`sex`=2 OR `sex`="user's gender")
        AND (`country`='' OR `country`="user's country")
    ) OR `applyToUnregistered` = 1
)
ORDER BY $random_order -- Generate random order pattern

Schema:

CREATE TABLE `ads` (  
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `headline` varchar(25) NOT NULL,
  `text` varchar(90) NOT NULL,
  `url` varchar(50) NOT NULL,
  `country` varchar(2) DEFAULT '0',
  `ageMIN` tinyint(2) unsigned NOT NULL,
  `ageMax` tinyint(2) unsigned NOT NULL,
  `sex` tinyint(1) unsigned NOT NULL DEFAULT '2',
  `applyToUnregistered` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `creator` int(10) unsigned NOT NULL,
  `created` int(10) unsigned NOT NULL,
  `start` date NOT NULL,
  `impressions_total` int(10) unsigned NOT NULL,
  `impressions_perday` mediumint(8) unsigned NOT NULL,
  `impressions` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=utf8

Upvotes: 0

Views: 64

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You have a very complicated query from an optimization perspective.

The only indexes that can be used on the where clause are on ads(impressions) or ads(start). Because you use inequalities, you cannot combine them.

Can you modify the table structure to have an ImpressionsFlag? This would be 1 if there are any impressions and 0 otherwise. If so, then you can try an index on ads(ImpressionsFlag, Start).

If that helps with performance, the next step would be to break up the query into separate subqueries and bring them together using union all. The purpose is to design indexes to optimize the underlying queries.

Upvotes: 1

Related Questions