Documentation on IN optimization

MariaDB 09:35 employees> analyze select COUNT(*) FROM employees WHERE first_name IN ('Yuqun') AND hire_date > '1990-01-01';+------+-------------+-----------+-------+---------------------+---------------------+---------+------+------+--------+----------+------------+--------------------------+

| id   | select_type | table     | type  | possible_keys       | key                 | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra                    |

+------+-------------+-----------+-------+---------------------+---------------------+---------+------+------+--------+----------+------------+--------------------------+

|    1 | SIMPLE      | employees | range | first_name_hireDate | first_name_hireDate | 19      | NULL |  115 | 115.00 |   100.00 |     100.00 | Using where; Using index |

+------+-------------+-----------+-------+---------------------+---------------------+---------+------+------+--------+----------+------------+--------------------------+

1 row in set (0.00 sec)

MariaDB 09:38 employees> analyze select COUNT(*) FROM employees WHERE first_name IN ('Yuqun', 'Ziyad', 'Nikolaus') AND hire_date > '1990-01-01';+------+-------------+-----------+-------+---------------------+---------------------+---------+------+------+--------+----------+------------+--------------------------+

| id   | select_type | table     | type  | possible_keys       | key                 | key_len | ref  | rows | r_rows | filtered | r_filtered | Extra                    |

+------+-------------+-----------+-------+---------------------+---------------------+---------+------+------+--------+----------+------------+--------------------------+

|    1 | SIMPLE      | employees | range | first_name_hireDate | first_name_hireDate | 19      | NULL |  328 | 329.00 |   100.00 |     100.00 | Using where; Using index |

+------+-------------+-----------+-------+---------------------+---------------------+---------+------+------+--------+----------+------------+--------------------------+

1 row in set (0.00 sec)

MariaDB 09:39 employees> analyze select COUNT(*) FROM employees WHERE first_name IN ("Aamer","Aamod","Abdelaziz","Abdelghani","Abdelkader","Abdelwaheb","Abdulah","Abdulla","Achilleas","Adam","Adamantios","Adas","Adel","Adhemar","Adib","Adil","Adin","Adit","Adly","Aemilian","Aiichiro","Aiman","Aimee","Akeel","Akemi","Akeno","Akhilish","Aksel","Alagu","Alain","Alair","Alassane","Alejandra","Alejandro","Aleksandar","Aleksander","Alenka","Alexius","Almudena","Alois","Aloke","Along","Aloys","Alper","Alselm","Aluzio","Amabile","Amalendu","Amstein","Anestis","Angel","Angus","Anneke","Anneli","Annemarie","Anoosh","Anwar","Anyuan","Apostol","Aral","Aram","Aran","Arch","Arco","Arfst","Aria","Arie","Arif","Aris","Arlette","Armond","Arnd","Arne","Arno","Arnould","Aron","Arra","Arto","Arumugam","Arun","Aruna","Arunachalam","Arunas","Arup","Arve","Arvin","Arvind","Ashish","Atilio","Atreye","Atreyi","Augustine","Avishai","Ayakannu","Ayonca","Babette","Bader","Badri","Bangqing","Baocai","Baoqiu","Barna","Barry","Bartek","Barton","Baruch","Barun","Basant","Basem","Basil","Basim","Baziley","Becky","Bedir","Bedrich","Behnaam","Berhard","Berna","Bernardo","Bernd","Berni","Bernice","Bernt","Berry","Berthier","Berto","Beshir","Bezalel","Bikash","Bilhanan","Billie","Billur","Bingning","Bluma","Boalin","Boaz","Bodh","Bodo","Bogdan","Boguslaw","Bojan","Bokyung","Boriana","Bouchung","Boutros","Bowen","Branimir","Breannda","Brendon","Brewster","Brigham","Byong","Candida","Candido","Caolyn","Carrsten","Cathie","Cedric","Chaitali","Chandrasekaran","Changho","Chanjung","Chaosheng","Chaoyi","Charlene","Charmane","Chenxi","Chenye","Chenyi","Cheong","Chikako","Chikara","Chinho","Chinhyun","Chinya","Chiradeep","Chiranjit","Chirstian","Chrisa","Chriss","Christ","Christfried","Christoper","Christoph","Christophe","Chuanti","Chuanyi","Claude","Claudi","Clyde","Collette","Conal","Conor","Conrado","Constantijn","Constantine","Constantino","Cordelia","Cristinel","Cullen","DAIDA","Danae","Danai","Danco","Danel","Danil","Danny","Dannz","Dante","Dayanand","Debaprosad","Debatosh","Deborah","DeForest","Dekang","Demin","Demos","Denis","Deniz","Denny","Dharmaraja","Dhritiman","Diederik","Dietrich","Dinah","Dines","Dinkar","Dipankar","Dipayan","Divier","Divine","Djelloul","Djenana","Dmitri","Dmitry","Domenick","Domenico","Doohun","Doowon","Douadi","Duangkaew","Duke","Dulce","Dung","Duri","Duro","Dzung","Ebbe","Eben","Eberhardt","Ebru","Eckart","Edwin","Eirik","Eishiro","Ekawit","Eldridge","Elgin","Elliott","Elrique","Elvia","Elvis","Elzbieta","Emdad","Emran","Ennio","Eran","Erez","Eric","Erzsebet","Etsuo","Evgueni","Ewing","Fabrizio","Fai","Faiza","Falguni","Fan","Fatemeh","Fay","Fayez","Fei","Feipei","Feixiong","Feiyu","Felicidad","Fen","Ferdinand","Filipe","Filipp","Flemming","Florian","Florina","Foong","Fox","Francesca","Francoise","Franziska","Frazer","Frederique","Freyja","Froduald","Fumiko","Fumino","Fumitaka","Fumitake","Fumiya","Fumiyo","Fun","Fuqing","Fusako","Gad","Gadiel","Gaetan","Gal","Gao","Garnet","Garnik","Gaurav","Gay","Gen","Genevieve","Genki","Gennadi","Gennady","Genta","Geoff","Geoffrey","Geoffry","Georg","George","Georgi","Georgy","Ger","Geraldo","Gererd","Ghassan","Gian","Gianluca","Giao","Giap","Gift","Gil","Gila","Gilbert","Gill","Gina","Gino","Gio","Giordano","Giri","Girolamo","Gita","Gladys","Goa","Godehard","Goetz","Goh","Golgen","Goncalo","Gonzalo","Goo","Gopalakrishnan","Gor","Gou","Greger","Gregory","Gretta","Gritta","Gro","Guadalupe","Guangming","Gudjon","Guenter","Guiseppe","Gully","Gunilla","Guoxiang","Guozhong","Gurbir","Guther","Guttorm","Hailing","Hairong","Haldon","Haldun","Halsur","Harjit","Harngdar","Hatem","Hatim","Hatsukazu","Hauke","Hausi","Heather","Heejo","Heeju","Heekeun","Hein","Heng","Henk","Heon","Heping","Herb","Herbert","Hercules","Hidde","Hidefumi","Hidekazu","Hideo","Hideyuki","Hilari","Hilary","Hinrich","Hirochika","Hironobu","Hironoby","Hiroyasu","Hisao","Hitofumi","Hitomi","Holgard","Honesty","Honglan","Hongzhu","Hongzue","Horward","Houman","Howell","Hsiangchu","Huai","Huan","Huei","Hugh","Hugo","Huican","Huiqun","Hyuckchul","Hyuncheol","Iara","Ibibia","Idoia","IEEE","Inderjeet","Indrajit","Ingemar","Ingmar","Insup","Ioana","Ipke","Irena","Irene","Isaac","Isamu","Ishfaq","Itzchak","Izaskun","Jaana","Jaber","Jackson","Jacopo","Jacqueline","Jaewon","Jaewoo","Jagoda","Jahangir","Jaideep","Jaihie","Jamaludin","Janalee","Janche","Jaques","Jaroslava","Jasminko","Jayesh","Jayson","Jeane","Jeanna","Jeanne","Jenwei","Jeong","Jessie","Jiafu","Jiakeng","Jiang","Jianhao","Jianhua","Jianhui","Jiann","Jianwen","Jingling","Jinpo","Jinxi","Jinya","Jiong","Jixiang","JiYoung","JoAnna","JoAnne","Jolita","Jongsuk","Jordanka","Josyula","Jouko","Jouni","Jovan","Joydip","Juichirou","Jungsoon","Junichi","Junsik","Juyoung","Kagan","Kaijung","Kamakshi","Kamran","Kankanahalli","Kannan","Karlis","Karoline","Karsten","Kasidit","Kasturi","Katsuo","Katsuyuki","Kauko","KayLiang","Kayoko","Kazuhide","Kazuhiko","Kazuhira","Kazuhiro","Kazuhisa","Kazuhito","Kazunori","Kazuyasu","Keiichiro","Kellie","Kellyn","Kendra","Kenroku","Kensei","Kensyu","Kenton","Kerhong","Kerryn","Kersti","Keung","Khaled","Khalid","Khalil","Khatoun","Khedija","Khoa","Khosrow","Khue","Khun","Kiam","Kien","King","Kinh","Kirk","Kish","Kitty","Kiyokazu","Kiyomitsu","Kiyotoshi","Koichi","Kokou","Kolar","Koldo","Kotesh","Kousuke","Krassimir","Krisda","Kristen","Krister","Kristian","Kristin","Kristina","Kristine","Kristinn","Kristof","Kshitij","Kwan","Kwangho","Kwangjo","Kwangsub","Kwangyoen","Kwee","Kwok","Kwun","Kyoichi","Lakshmi","Lalit","Lansing","Larisa","Laurentiu","Leaf","Leah","Leandro","Lech","Leen","Leif","Lein","Lena","Lene","Leni","Lenore","Leon","Leszek","Leucio","Licheng","Lidong","Lihong","Lijia","Lijie","Lillian","LiMin","Limsoon","Lobel","Lokesh","Lorien","Lubomir","Lucian","Lucien","Luerbio","Luigi","Luisa","Luise","Lunjin","Mabhin","Mabo","Mabry","Macha","Mack","Mads","Magdalena","Magy","Maha","Mahendra","Mahmut","Maia","Maik","Maja","Makato","Make","Malu","Mamdouh","Mana","Mandell","Mang","Mani","Manibrata","Manohar","Mansur","Mantis","Manton","Manu","Mara","Marc","Marco","Marek","Maren","Margareta","Margo","Mari","Maria","Mariangiola","Marie","Marin","Mario","Maris","Marit","Mariusz","Marjo","Mark","Marke","Marko","Marl","Marla","Marlo","Marsal","Marsha","Marsja","Mart","Martial","Martien","Martijn","Martina","Martine","Martins","Mary","Masadi","Masaki","Masako","Masali","Masami","Masamitsu","Masanao","Masaru","Masasuke","Masato","Masoud","Matk","Mats","Matt","Maya","Mayuko","Mayumi","Mayuri","Mechthild","Menkae","Michaela","Mihalis","Mikhail","Mingdong","Minghong","Mingsen","Mingzeng","Miomir","Miquel","Mircea","Mitchel","Mitsuyuki","Miyeon","Mizuhito","Mohamadou","Mohammad","Mohammed","Mohan","Mohd","Moheb","Mohit","Mohua","Moie","Mokhtar","Mona","Mong","Moni","Monique","Mooi","Moon","Mori","Moriyoshi","Morrie","Morris","Moss","Mostafa","Moti","Moto","Mototsugu","Moty","Mountaz","Mrinalini","Munehiko","Munehiro","Munenori","Muneo","Munir","Murthy","Muzhong","Nadjib","Naftali","Naftaly","Nagui","Nahid","Nahla","Nahum","Nakhoon","Nalini","Naraig","Narain","Nathalie","Nathan","Navid","Navin","Nechama","Nectarios","Neelam","Neven","Nevin","Nevio","Niclas","Nidapan","Nigel","Nikolaos","Nikolaus","Nimmagadda","Niranjan","Nirmal","Nishit","Niteen","Nitsan","Nobuyoshi","Noritoshi","Oddvar","Odinaldo","Odoardo","Odysseas","Oguz","Ohad","Oksana","Oldrich","Olivera","Ortrud","Ortrun","Oscal","Oscar","Otilia","Otmar","Ottavia","Owen","Paddy","Padma","Palash","Paloma","Panayotis","Pantung","Paraskevi","Paris","Parke","Parto","Parviz","Pascal","Pasqua","Patricia","Patricio","Peer","Percy","Perla","Perry","Persi","Peternela","Petter","Phillip","Phule","Phuoc","Pintsang","Piyawadee","Piyush","Pohua","Poorav","Poornachandra","Pradeep","Pragnesh","Pramod","Pranas","Pranav","Pranay","Pranjal","Prasadram","Premal","Premsyl","Prodip","Przemyslawa","Qingxiang","Qiwen","Quingbo","Qunsheng","Radhakrishnan","Radhia","Radhika","Radoslaw","Rafael","Rafail","Rafols","Raimond","Rajmohan","Ramachenga","Ramalingam","Ramya","Ramzi","Ranan","Rance","Randi","Randy","Ranga","Rasiah","Rasikan","Ravishankar","Rayond","Reinhard","Reinhold","Remco","Remko","Remmert","Remzi","Renee","Renny","Reuven","Richara","Richard","Rildo","Rimli","Rimon","Roddy","Roded","Ronghao","Ronnie","Rosalie","Rosalyn","Rosella","Roselyn","Rosine","Rosita","Rosli","Rutger","Sachar","Sachem","Sachin","Sachio","Saddek","Sadegh","Sadok","Sadun","Saeed","Saeko","Sahrah","Sailaja","Sajjad","Sajjan","Sakthirel","Salvador","Sampalli","Samphel","Sandeepan","Sangeeta","Saniya","Sanjai","Sanjay","Sanjeeva","Sanjit","Sanjiv","Sanjoy","Sariel","Sarita","Sasan","Sashi","Sastry","Satoru","Satosi","Satyanarayana","Sedat","Selwyn","Seongbae","Seongbin","Serap","Serenella","Serge","Sergi","Serif","Seshu","Shahab","Shahaf","Shahar","Shahid","Shai","Shaibal","Shakhar","Shaleah","Shalesh","Sham","Shan","Shao","Shaowei","Shaowen","Shaunak","Shaw","Shawna","Shay","Shaz","Shem","Shen","Shichao","Shietung","Shigeaki","Shigehiro","Shigehito","Shigeichiro","Shigenori","Shigeo","Shigeu","Shih","Shim","Shimshon","Shin","Shir","Shirish","Shiv","Shmuel","Shooichi","Shounak","Shridhar","Shrikanth","Shugo","Shuho","Shuichi","Shuji","Shuky","Shunichi","Shushma","Shuzo","Siamak","Sibyl","Siddarth","Sigeru","Sivanarayana","Slavian","Snehasis","Soenke","Somnath","Sorina","Souichi","Spyrose","Sreekrishna","Sreenivas","Srinidhi","Stabislas","Stafford","Stamatina","Steen","Stein","Stella","Stepehn","Steve","Stevo","Subbu","Subhada","Subhankar","Subhash","Subhrajyoti","Subir","Sudhanshu","Sudharsan","Suebskul","Sugwoo","Sukumar","Sumali","Sumant","Sungwon","Supot","Surveyors","Susanna","Susanne","Susanta","Sushant","Susuma","Susumu","Suvo","Suzette","Sven","Sverrir","Syozo","Szabolcs","Tadahiko","Tadahiro","Tadanori","Taegyun","Taiji","Taisook","Taizo","Takahira","Takahiro","Takahito","Takanari","Takushi","Tamiya","Tayeb","Teiji","Temple","Terresa","Teruyuki","Tesuro","Tesuya","Tetsurou","Tetsushi","Teunis","Thanasis","Theirry","Theron","Thodoros","Tianruo","Tiina","Tokuyasu","Tommaso","Tomofumi","Tomokazu","Tomoyuki","Toney","Tonia","Tonny","Toong","Tooru","Torsten","Toshiki","Toshiko","Toshimi","Toshimitsu","Toshimo","Toshimori","Tran","Tremaine","Tristan","Troy","Tru","True","Trygve","Tse","Tsuneo","Tsutomu","Tsvetan","Tsz","Tua","Turgut","Tuval","Tze","Tzu","Tzvetan","Udaiprakash","Udi","Udo","Ugo","Uinam","Ulf","Uli","Ulises","Ult","Uma","Unal","Ung","Uno","Uri","Uriel","Urs","Uta","Ute","Utpal","Utz","Uwe","Uzi","Val","Valdiodio","Valeri","Valery","Valeska","Valter","Van","Vasilii","Vasilis","Vasiliy","Vatsa","Venkatesan","Vesna","Vidar","Vidya","Vincent","Vishu","Vishv","Vishwani","Visit","Vitali","Vitaly","Vivian","Vojin","Volkmar","Vugranam","Waiman","Wanqing","Wayne","Weicheng","Weidon","Weijing","Weiru","Weiwu","Weiye","Weiyi","Werner","Willard","Wilmer","Wojceich","Wonhee","Woody","Wuxu","Xiadong","Xiahua","Xianlong","Xiaobin","Xiaocheng","Xiaoheng","Xiaopeng","Xiaoqiang","Xiaoqiu","Xiaoshan","Xinan","Xinglin","Xinyu","Xiong","Xiping","Xudong","Xuedong","Xuejia","Xuejun","Xumin","Yaghout","Yagil","Yahiko","Yakichi","Yakkov","Yannik","Yannis","Yechezkel","Yechiam","Yefim","Yifei","Yinghua","Yinlin","Yishai","Yishay","Ymte","Yolla","Yongdong","Yonghoan","Yonghong","Yongmao","Yongmin","Yongqiao","Yoshimitsu","Yoshinari","Youjian","Youngkon","Younwoo","Youpyo","Youssef","Yucai","Yucel","Yuchang","Yuguang","Yuichiro","Yunming","Yuping","Yuqun","Yurii","Yurij","Yuriy","Yuuichi","Zdislav","Zejun","Zeljko","Zengping","Zeydy","Zhanqiu","Zhaofang","Zhenbing","Zhenhua","Zhensheng","Zhigen","Zhiguo","Zhilian","Zhiwei","Zhonghua","Zhonghui","Zhongwei","Zijian","Zine","Zissis","Zita","Zito","Ziva","Ziya","Ziyad","Zongyan","Zorica","Zsolt","Zvonko") AND hire_date > '1990-01-01';

+------+-------------+-----------+-------+---------------------+---------------------+---------+------+--------+-----------+----------+------------+--------------------------+

| id   | select_type | table     | type  | possible_keys       | key                 | key_len | ref  | rows   | r_rows    | filtered | r_filtered | Extra                    |

+------+-------------+-----------+-------+---------------------+---------------------+---------+------+--------+-----------+----------+------------+--------------------------+

|    1 | SIMPLE      | employees | range | first_name_hireDate | first_name_hireDate | 19      | NULL | 134946 | 135112.00 |   100.00 |     100.00 | Using where; Using index |

+------+-------------+-----------+-------+---------------------+---------------------+---------+------+--------+-----------+----------+------------+--------------------------+

1 row in set (0.07 sec)