上一篇文章 說到 LiteDB 製作索引(index) ，百萬資料測試查詢篇，後來我有測到一個東西
就是我對屬性是 List<Object> 中的 某一屬性進行查詢，是不是可以做索引 (index) ，
答案是可以的，但是要直接做到 List<Object> 中要被查詢的該屬性..
一樣先 show 一下我的 Data Model
<![CDATA[
//Friend Model
public class Friend
{
public string Name { get; set; }
public string _id { get; set; }
public DateTime CreateDate { get; set; }
public List<Phone> Phones { get; set; }
public bool IsActive { get; set; }
public decimal Salary { get; set; }
public Friend()
{
Phones = new List<Phone>();
}
public class Phone
{
public string Num { get; set; }
public string Type { get; set; }
}
}
]]>
這時候我原本天真的以為是要這樣做索引
<![CDATA[
//製作index
//Error way
using (var db = new LiteDatabase(AppDomain.CurrentDomain.BaseDirectory + "sample.db"))
{
var userColleciton = db.GetCollection<Friend>("FRIENDS");
var result = userColleciton.EnsureIndex(x => x.Phones);
Console.WriteLine("Make Index Result:" + result);
Console.WriteLine("Cost time:" + sw.Elapsed);
}
Console.WriteLine("Cost time:" + sw.Elapsed);
]]>
但是測試之後發現，效果並不好，應該說就是沒有變快
後來我改成這樣下索引
<![CDATA[
using (var db = new LiteDatabase(AppDomain.CurrentDomain.BaseDirectory + "sample.db"))
{
var userColleciton = db.GetCollection<Friend>("FRIENDS");
//要精準下到 Phones 中的 N
var result = userColleciton.EnsureIndex(x => x.Phones.Select(y => y.Num));
Console.WriteLine("Make Index Result:" + result);
Console.WriteLine("Cost time:" + sw.Elapsed);
}
]]>
查詢結果
<![CDATA[
for (var i = 1; i <= 10; i++)
{
using (var db = new LiteDatabase(AppDomain.CurrentDomain.BaseDirectory + "sample.db"))
{
var userColleciton = db.GetCollection<Friend>("FRIENDS");
sw.Restart();
var data1 = db.Execute("select $ from FRIENDS include Phones where $.Phones[*].Num any LIKE '%099999%'").ToList();
if (data1 != null)
{
sw.Stop();
Console.WriteLine("Cost time:" + sw.Elapsed);
Console.WriteLine("Count:" + data1.Count());
}
}
}
//RESULT
/*
Hello, World!
Cost time:00:00:02.0611174
Count:111
Cost time:00:00:01.0423100
Count:111
Cost time:00:00:01.0894045
Count:111
Cost time:00:00:01.0413027
Count:111
Cost time:00:00:01.1151590
Count:111
Cost time:00:00:01.0695749
Count:111
Cost time:00:00:01.0112895
Count:111
Cost time:00:00:01.0733385
Count:111
Cost time:00:00:01.1731811
Count:111
Cost time:00:00:01.1073664
Count:111
*/
]]>
果然快非常多，所以 如果你要針對 Friends[].Phones.Num 進行查詢 所以索引就要下到
x => x.Phones.Select(y=>y.Num) ，而不是只針對 Phones 做索引是無效的
