Вверх ↑
Ответов: 655
Рейтинг: 18
#1: 2016-02-11 15:35:28 ЛС | профиль | цитата
Вот пример, БД с создается БД с 1 000 000 записей вида 'аытвйновыфлвйшвй' с использованием уникальных индексов, при запросе select count(*) from data where mydata like '%ф%а%ы%п' занимает в районе секунды-двух (на глаз)

http://forum.hiasm.net/forum_serv.php?q=56&id=4209


Схема:
Add(MainForm,2953706,21,105)
{
 Width=645
 Height=458
 link(onCreate,3330765:doOpenF,[])
}
Add(SQLite_Query,9645187,217,224)
{
 link(onQuery,3898437:doStr,[])
 link(dbHandle,7689960:Var2,[])
}
Add(SQLite_DB,3330765,217,119)
{
 FileName="test.db"
}
Add(Memo,281889,77,154)
{
 Left=10
 Top=5
 Width=345
 Height=50
 Strings=#24:PRAGMA encoding="UTF-8";|70:create table data (id integer primary key autoincrement, mydata text);|43:create unique index d1 on data (id,mydata);|
}
Add(DoData,13177178,77,224)
{
 link(onEventData,10843461:doExec,[])
 link(Data,281889:Text,[])
}
Add(Button,4197836,21,224)
{
 Left=10
 Top=55
 Width=80
 Caption="create_table"
 link(onClick,13177178:doData,[])
}
Add(StrList,9834666,441,301)
{
 Strings=#1:й|1:ц|1:у|1:к|1:е|1:р|1:н|1:г|1:ш|1:щ|1:з|1:х|1:ъ|1:ф|1:ы|1:в|1:а|1:п|1:р|1:о|1:л|1:д|1:ж|1:э|1:я|1:ч|1:с|1:м|1:и|1:т|1:ь|1:б|1:ю|
 Point(doGetString)
 Point(onGetString)
 link(onGetString,3328643:doAdd,[])
}
Add(Random,1578549,392,343)
{
 Max=32
 link(onRandom,9834666:doGetString,[])
}
Add(For,14472687,315,343)
{
 End=10
 link(onEvent,1578549:doRandom,[])
 link(onStop,625045:doData,[(379,356)(379,384)])
}
Add(For,15043235,252,343)
{
 End=1000000
 link(onEvent,14472687:doFor,[])
 link(onStop,9212208:doString,[(305,356)(305,426)])
}
Add(StrList,3328643,511,308)
{
}
Add(Replace,2537379,511,378)
{
 SubStr="\r\n"
 link(onReplace,14405146:doDelete,[])
 link(Str,3328643:Text,[])
}
Add(Delete,14405146,560,378)
{
 Count=1
 Direction=1
 link(onDelete,10316750:doEvent1,[])
}
Add(DoData,625045,462,378)
{
 link(onEventData,2537379:doReplace,[])
}
Add(Button,13927473,174,343)
{
 Left=10
 Top=90
 Width=80
 Caption="create_data"
 link(onClick,15043235:doFor,[])
}
Add(FormatStr,647888,651,378)
{
 DataCount=1
 Mask="insert into data values (NULL,'%1');"
 link(onFString,253376:doCharset,[])
}
Add(Hub,10316750,609,378)
{
 link(onEvent1,647888:doString,[])
 link(onEvent2,3328643:doClear,[(635,391)(635,362)(498,362)(498,321)])
}
Add(MT_String,3898437,273,224)
{
 link(onResult,10038553:doCharset,[])
}
Add(Memo,15620192,385,210)
{
 Left=15
 Top=130
 Width=595
 Height=105
 Enabled=1
 ScrollBars=2
}
Add(Memo,15104376,501,158)
{
 Left=15
 Top=260
 Width=595
 Height=105
 Strings=#56:select count(*) from data where mydata like '%ф%ц%ы%х%';|
 ScrollBars=2
}
Add(DoData,6067671,501,220)
{
 link(onEventData,6109644:doCharset,[])
 link(Data,15104376:Text,[])
}
Add(Button,2885158,445,220)
{
 Left=15
 Top=380
 Width=80
 Caption="query"
 link(onClick,6067671:doData,[])
}
Add(SQLite_Exec,10843461,119,224)
{
 link(dbHandle,7689960:Var1,[(125,173)])
}
Add(GetDataEx,7689960,217,168)
{
 link(Data,3330765:dbHandle,[])
}
Add(Charset,6109644,566,220)
{
 Type=6
 link(onCharset,37820:doWork1,[(697,226)])
}
Add(Charset,253376,710,378)
{
 Type=6
 link(onCharset,2188896:doAdd,[(763,384)(763,349)])
}
Add(Charset,10038553,330,224)
{
 Type=7
 link(onCharset,15620192:doAdd,[(370,230)(370,216)])
}
Add(HubEx,37820,693,271)
{
 Angle=2
 link(onEvent,15689310:doEvent1,[(172,277)(172,223)])
}
Add(Hub,15689310,189,217)
{
 link(onEvent1,15620192:doClear,[])
 link(onEvent2,9645187:doQuery,[])
}
Add(StrList,2188896,784,343)
{
}
Add(FormatStr,9212208,784,420)
{
 DataCount=1
 Mask="BEGIN;\r\n%1\r\nEND;"
 link(onFString,9574265:doEvent1,[])
 link(Str1,2188896:Text,[])
}
Add(Hub,9574265,833,420)
{
 OutCount=3
 link(onEvent1,37820:doWork2,[(859,426)(859,277)])
 link(onEvent2,2188896:doClear,[(853,433)(853,398)(772,398)(772,356)])
 link(onEvent3,3686493:doMessage,[])
}
Add(Message,3686493,875,434)
{
 Message="OK"
 Caption="OK"
}
карма: 0

0