SQLite
Artifact Content
Not logged in

Artifact 4be6b8e81ec382c55a534489a2ffc43a2419fb0c:

Ticket change [4be6b8e81e] - New ticket [7c6a01c6c5] Multi-column index is not used with IN operator. by anonymous 2013-01-15 08:18:41.
D 2013-01-15T08:18:41.935
J foundin 3.7.15.2
J icomment Having\san\sindex\son\s3\sinteger\scolumns\s(column1,\scolumn2,\scolumn3),\sthe\sanalyse\scommand\sgenerates\sthe\sstat1\sand\sstat3\stables.\sI\ssee\sthe\sfollowing\sstatistics\sfor\sthis\sindex\sin\sthe\ssqlite_stat1\stable:\r\n\r\n"4600132\s1289\s1275\s1"\r\n\r\nWhen\sI\sexecute\sthe\sfollowing\sSQL\squery,\sthis\sindex\sis\snot\sused\sbut\sthe\squery\soptimizer\sprefers\sto\suse\sthe\sprimary\sindex\swhich\sis\son\sthe\scolumn3,\swhich\sis\sabout\s60\stimes\sslower\sthan\susing\sthe\sindex.\s(\s20000\sms\svs\s300\sms)\r\n\r\n"select\scolumn1,\scolumn2,\scolumn3\sfrom\stable\swhere\scolumn1\sIN\s(\sinteger1,\sinteger2,\s...,\sinteger30\s)\sand\scolumn2\s=\s1\sorder\sby\scolumn3\slimit\s0,200"\r\n\r\nWhen\sI\sput\sless\sinteger\svalues\sin\sthe\sIN\soperator\sOR\sremove\sthe\s"order\sby"\spart\sfrom\sthe\squery,\smy\sindex\sis\sused.\s\r\n\r\nOR\sIf\sI\screate\san\sindex\sonly\son\sthe\scolumn1,\sthen\sthis\sindex\sis\sused\sfor\sthe\soriginal\squery\sI\swrote\sabove,\swhich\sis\salso\svery\sefficient.\r\n\r\nThe\sSQLite\sversion\s3.6\sdoes\snot\shave\sthe\ssame\sissue.\r\n\r\nThe\ssame\sproblem\shad\sbeen\sdiscussed\sa\swhile\sago:\shttp://www.mail-archive.com/sqlite-users%40sqlite.org/msg74412.html
J login nobody
J mimetype text/plain
J private_contact 14cddfb49aadf411bc30f51537baea148d33c31a
J severity Critical
J status Open
J title Multi-column\sindex\sis\snot\sused\swith\sIN\soperator
J type Performance_Issue
K 7c6a01c6c5ae77de91822d812409c568e78d180e
U 
Z 88b78001bdf9ccacb79d629d09bf810b