Searching for format- format vs formats vs approximate formats?

[unboggling]

I've tested these searches and can't notice a difference in speed of results. Maybe my library isn't large enough for the difference to be significant yet. Which would be the fastest search for epub format in a very large library?

1. format:epub
2. formats:epub
3. #formats2:epub
   
   Note: #formats2 is composite column with template {:'approximate_formats()'}

Do #1 and #2 look at the files in the book folders?

Is #3 faster because it doesn't, even though it's composite column?

Is format or formats indexed?

What's the difference between format and formats?

[theducks]

You don't have SQLite database browser (or equiv)?

the format is stored in the 'data' table. That field is also Indexed (formats_idx)

[unboggling]

Originally Posted by theducks

You don't have SQLite database browser (or equiv)?

I was hoping experts would just know the answers off the top of their heads. I do have a database browser. When I use SQLiteStudio I crash frequently. Before asking the question here I upgraded to newest version of SQLiteStudio then crashed again, twice. So I'm leery of using that now and need to find something that works better on OS X 10.9.1. Anybody have any recommendations?

Originally Posted by theducks

the format is stored in the 'data' table. That field is also Indexed (formats_idx)

Before crashing, I noticed formats_idx is based on "format".

The question still is: which search is faster and why?

[Adoby]

I think the main difference is that formats isn't stored between runs, but has to be populated by checking the actual files when used the first time, typically whenever calibre is started. That can be very slow. But then it is fast. Approximate_formats are stored in the database and doesn't cause any delay when starting calibre.

Both are fast to use.

[kovidgoyal]

1 and 2 are exactly the same and they do not check for files. They will be faster than 3. Though given all the optimization and caching that goes on, the speed difference is not going to be very large.

Searching in calibre does not use sql at all, all searching is done on an in memory data structure that stores all metadata, using custom algorithms. SQLITE does not have powerful enough capabilities to power the search in calibre.

The only time actual files are checked is if you use the formats() template function, and even then they are only checked once. Simply searching does not recheck the files.

[unboggling]

Originally Posted by kovidgoyal

1 and 2 are exactly the same and they do not check for files. They will be faster than 3. Though given all the optimization and caching that goes on, the speed difference is not going to be very large.

Searching in calibre does not use sql at all, all searching is done on an in memory data structure that stores all metadata, using custom algorithms. SQLITE does not have powerful enough capabilities to power the search in calibre.

The only time actual files are checked is if you use the formats() template function, and even then they are only checked once. Simply searching does not recheck the files.

Thank you, Kovid. That answered my questions.

So to make sure I understand this, in other words searches are conducted against data structures read into RAM from metadata.db then specially massaged at library startup. So whether a field is indexed or not in the backend doesn't really matter that much after library startup.

[kovidgoyal]

yes, that is correct. Indexing is a legacy from older backends. I made a decision to generally avoid changing database on file format as much as possible to allow people to run multiple versions of calibre against the same database file safely, therefore, there is a lot of legacy cruft in the db schema.

[unboggling]

Originally Posted by unboggling

Thank you, Kovid. That answered my questions.

So to make sure I understand this, in other words searches are conducted against data structures read into RAM from metadata.db then specially massaged at library startup. So whether a field is indexed or not in the backend doesn't really matter that much after library startup.

Originally Posted by kovidgoyal

yes, that is correct. Indexing is a legacy from older backends. I made a decision to generally avoid changing database on file format as much as possible to allow people to run multiple versions of calibre against the same database file safely, therefore, there is a lot of legacy cruft in the db schema.

So, again to make sure I understand: Whether a field is indexed or not in the backend doesn't matter at all unless user uses very old version of calibre.

[kovidgoyal]

yes .

[unboggling]

Originally Posted by kovidgoyal

yes .

Thank you.