为什么在添加更多条件时，这个MySQL查询会变慢？

Question

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?

Answer 1

You didn't post output from EXPLAIN so this is guesswork. But it seems possible the conditions of your first query are somehow forcing MySQL's query planner to do a full table scan of your adstable. It's not a small table, so the scan will take a while.

Try creating a compound index on (adactive, approved, adbudget, user) on that table. That's a covering index for your query. You should read about covering indexes.

 ALTER TABLE adactive 
   ADD INDEX act_appr_bud_user (adactive, approved, adbudget, user);

That should allow the query planner to random-access and the partially scan an index to satisfy your query. It will likely speed things up.

Notice (as of early 2017) MySQL's query planner ordinarily can use only one index per table to satisfy a query. So lots of indexes on single columns can't help as much as compound indexes chosen for particular queries.