Applying indexes
Hello,
1. In which cases the "applying index" phrase should I see in the query plan? I have an old query (the one I've sent here at 06/22/2015) where only for one condition I've seen "applying index", and even this one doesn't appear anymore using a newer (8.2.2) version of BaseX and a larger database. I do see the right properties of the db: Indexes
Up-to-date: true
TEXTINDEX: true
ATTRINDEX: true
2. Only once a value which is mentioned in the query is found in the index, in case of "=" condition?
3. Always, in case of "<" or "<" condition, assuming the specific text/attribute has been indexed?
4. Everything I see as a reply to index:facets is indexed, right?
Hi Menashè,
Quoted text · Menashè Eliezer · 1 line
> 1. In which cases the "applying index" phrase should I see in the query plan?
There is no simple answer to that question. The query optimizer tries to select a query plan that is evaluated faster than others. The following query may illustrate this:
for $p in db:open('db')//person
where $p/country = 'Italy'
and $p/sex = 'female'
return $pThere are at least three ways how this query could e.g. be rewritten:
db:text('db', 'Italy')/parent::country/
parent:person/person[sex = 'female']
db:text('db', 'female')/parent:sex/
parent::person[city = 'Catania']
(db:text('db', 'Italy')/parent::country,
db:text('db', 'female')/parent::sex)/parent::country
In most cases, only one index access will be created will be created for queries of that pattern. The optimizer also checks the number of index entries given for a particular keyword. In the given case, the query would most probably rewritten to the first query plan.
If you have a query with just six conditions, there are more than 50 different ways how your final query plan could look like.
Quoted text · Menashè Eliezer · 2 lines
> 2. Only once a value which is mentioned in the query is found in the index, in > case of "=" condition?
In most cases, exact queries are preferred, because they are usually more selective than other queries.
Quoted text · Menashè Eliezer · 2 lines
> 3. Always, in case of "<" or "<" condition, assuming the specific > text/attribute has been indexed?
Because none of our index structures is particularly suited for range queries, such index-driven requests may be slower than sequential scans. Maybe you remember my last mail: Have you already tried to store latitudes and longitudes in a fixed-size string representation?
Quoted text · Menashè Eliezer · 1 line
> 4. Everything I see as a reply to index:facets is indexed, right?
I'm not sure what you mean by that?
Hi,
So in case of six exact conditions (With different xpaths) should I see the usage of six indexes? Anyway, I'll send the exact query inside the old thread.
Quoted text · Christian Grün · 7 lines
On 07/30/2015 10:21 AM, Christian Grün wrote: > Hi Menashè, > > Because none of our index structures is particularly suited for range > queries, such index-driven requests may be slower than sequential > scans. Maybe you remember my last mail: Have you already tried to > store latitudes and longitudes in a fixed-size string representation?
Sure, I've also responded to it: https://www.mail-archive.com/basex-talk%40mailman.uni-konstanz.de/msg06648.html
Best regards, Menashè
Quoted text · Menashè Eliezer · 2 lines
> So in case of six exact conditions (With different xpaths) should I see the > usage of six indexes?
It always depends on your data. My assumption is that you'll only have one index access in your optimized query.
Quoted text · Menashè Eliezer · 2 lines
> Sure, I've also responded to it: > https://www.mail-archive.com/basex-talk%40mailman.uni-konstanz.de/msg06648.html
What about the next steps? Have you been successful?...
https://www.mail-archive.com/basex-talk%40mailman.uni-konstanz.de/msg06650.html https://www.mail-archive.com/basex-talk%40mailman.uni-konstanz.de/msg06651.html
Quoted text · Christian Grün · 2 lines
On 08/03/2015 02:54 PM, Christian Grün wrote: > https://www.mail-archive.com/basex-talk%40mailman.uni-konstanz.de/msg06651.html
I may try it once I see a good performance for the other parts of the query. Please see the same query without the lat-lon filters.
With kind regards, Menashè
Written for BaseX 8.2. BaseX may have changed since: please check the current documentation.