So I have two tables:
1
CREATE TABLE `adstable` (
`adid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`adbudget` decimal(14,7) unsigned NOT NULL DEFAULT '0.0000000',
`targetdesktop` tinyint(1) NOT NULL DEFAULT '1',
`adactive` tinyint(1) NOT NULL,
`user` bigint(20) unsigned NOT NULL,
`approved` tinyint(1) NOT NULL,
`imp_today` int(10) unsigned NOT NULL DEFAULT '0',
`targetwindows` tinyint(1) NOT NULL,
PRIMARY KEY (`adid`),
KEY `user_index` (`user`),
KEY `budget_index` (`adbudget`) USING BTREE,
KEY `imp_today_index` (`imp_today`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=719102 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
2
CREATE TABLE `userstable` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`balance` decimal(14,7) unsigned NOT NULL DEFAULT '0.0000000',
PRIMARY KEY (`id`),
KEY `balance_index` (`balance`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
and I have two MySQL queries:
1
SELECT adstable.adid FROM adstable INNER JOIN userstable ON (adstable.user = userstable.id )
WHERE
adstable.adactive=1 AND adstable.approved = 1 AND
adstable.targetdesktop = 1 AND adstable.targetwindows = 1 and
adstable.adbudget > 0.02 AND userstable.balance > 0.02
ORDER BY adstable.imp_today ASC limit 1
2
SELECT adstable.adid FROM adstable INNER JOIN userstable ON (adstable.user = userstable.id )
WHERE
adstable.adactive=1 AND adstable.approved = 1 AND
adstable.adbudget > 0.02 AND userstable.balance > 0.02
ORDER BY adstable.imp_today ASC limit 1
The 1st query has an extra condition in the where clause: adstable.targetdesktop = 1 AND adstable.targetwindows = 1
However the thing that I can't understand is why the first query takes 2-3 seconds to run, while the second query takes 2-3 seconds.
Notes:
- adstable has around 700k rows
- the second & first query both return the same 2 rows (but the extra condition in the 1st query makes it so much slower)
- I ran query 1 by removing adstable.adactive=1 AND adstable.approved = 1 instead of adstable.targetdesktop = 1 AND adstable.targetwindows = 1, and the query ran in 0.001 seconds.
Does anyone know why the second query is so much faster than the first, even though the second returns the same # and type of rows the 1st does?