Вверх ↑
Ответов: 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