Laravel whereIn doesn't use index

Question

I found a whereIn query that generated by Laravel (I guess it was from eager load with

select * from `domain_platforms` where `domain_platforms`.`domain` in ('54.197.198.150', 'aadarsh.in', 'affiliatealmanack.com', 'alexa.chinaz.com', 'allwebsites.net', 'amazonsales.com.ua', 'amazonsales.ru', 'anjana.co.in', 'articleforge.com', 'askdim.com', 'besafe.in', 'besthostingprice.com', 'betalist.com', 'bird.userdetected.com', 'blog.driftly.app', 'brilliantio.net', 'cafemmo.club', 'class.koalix.co.il', 'click.convertkit-mail2.com', 'codetime.io', 'couponsale.in', 'couponseeker.com', 'cupofcopyaudits.com', 'dasar.in', 'digitalmarketingsupermarket.com', 'domain.vsw.jp', 'fatrank.com', 'frequently-asked-questions.info', 'grammar-teacher.com', 'grammarteacher.substack.com', 'hansika.co.in', 'hypeurls.com', 'income.net.in', 'indiehackers.com', 'indiestartups.co', 'infodiscounts.com', 'jasperpieterse.com', 'jaugmente.com', 'jens.marketing', 'kavita.pw', 'ketaki.co.in', 'listit.pw', 'love.net.in', 'lumeaseoppc.ro', 'muchmore.in', 'niche.surf', 'nichepursuits.com', 'nichesurfer.com', 'nichewit.com', 'nitter.namazso.eu', 'offbeatfreelancer.online', 'officialwebsites.in', 'onemanarmy.in', 'onlinefestival.in', 'onlinenewspaper.in', 'ppp.twaino.com', 'priyasha.pw', 'producthunt.com', 'productific.com', 'prousmanhussain.com', 'rankmakers.katlinks.io', 'region.co.in', 'richniches.com', 'rikreviews.com', 'roberto.digital', 'saashub.com', 'seothesis.com', 'seotoolbelt.co', 'seotoolview.com', 'site-checker.org', 'sitescrawl.com', 'skipblast.com', 'soumettre.fr', 'startupscrushing.com', 'surekha.co.in', 'sushila.pw', 'tgstat.ru', 'topdoma.in', 'toplist.co.in', 'topmillion.net', 'trendystartups.com', 'twaino.com', 'twisave.com', 'urldirectory.net', 'urllinking.com', 'webandseo.fr', 'webhorspiste.com', 'webmaster-freelance.net', 'webworker.club', 'wpeagle.com', 'zenspill.com', 'zeroniche.com')

I copy that query and run EXPLAIN on it, but the result showed that no index is used for that query.

It's weird because I have created a lot of index for that table

I think when running that query, the domain_platforms_domain_index should be used but it wasn't. Does anyone know the reason? Thank you!

Answer 1

First please see How MySQL Uses Indexes.

Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.)

Also, you have max_seeks_for_key property in MySQL that you can use for some situations.

please see also Table scan avoidance.

What percentage of rows match your IN clause?