[SOLVED] Mysql query with multiple selects results in high CPU load

Mysql query with multiple selects results in high CPU load

I'm trying to do a link exchange script and run into a bit of trouble. Each link can be visited by an IP address a number of x times (frequency in links table). Each visit costs a number of credits (spend limit given in limit in links table)

I've got the following tables:

CREATE TABLE IF NOT EXISTS `contor` (
`key` varchar(25) NOT NULL,
`uniqueHandler` varchar(30) DEFAULT NULL,
`uniqueLink` varchar(30) DEFAULT NULL,
`uniqueUser` varchar(30) DEFAULT NULL,
`owner` varchar(50) NOT NULL,
`ip` varchar(15) DEFAULT NULL,
`credits` float NOT NULL,
`tstamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`key`),
KEY `uniqueLink` (`uniqueLink`),
KEY `uniqueHandler` (`uniqueHandler`),
KEY `uniqueUser` (`uniqueUser`),
KEY `owner` (`owner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `links` (
`unique` varchar(30) NOT NULL DEFAULT '',
`url` varchar(1000) DEFAULT NULL,
`frequency` varchar(5) DEFAULT NULL,
`limit` float NOT NULL DEFAULT '0',
PRIMARY KEY (`unique`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I've got the following query:

$link = MYSQL_QUERY("
    SELECT * 
    FROM `links` 
    WHERE (SELECT count(key) FROM contor WHERE ip = '$ip' AND contor.uniqueLink = links.unique) <= `frequency` 
    AND (SELECT sum(credits) as cost FROM contor WHERE contor.uniqueLink = links.unique) <= `limit`")

There are 20 rows in the table links.

The problem is that whenever there are about 200k rows in the table contor the CPU load is huge.

After applying the solution provided by @Barmar: Added composite index on (uniqueLink, ip) and droping all other indexes except PRIMARY, EXPLAIN gives me this:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     l   ALL     NULL    NULL    NULL    NULL    18  
1   PRIMARY     <derived2>  ALL     NULL    NULL    NULL    NULL    15  
2   DERIVED     pop_contor  index   NULL    contor_IX1  141     NULL    206122

Solution

Try using a join rather than a correlated subquery.

SELECT l.*
FROM links AS l
LEFT JOIN (
    SELECT uniqueLink, SUM(ip = '$ip') AS ip_visits, SUM(credits) AS total_credits
    FROM contor
    GROUP BY uniqueLink
) AS c
ON c.uniqueLink = l.unique AND ip_visits <= frequency AND total_credits <= limit

If this doesn't help, try adding an index on contor.ip.