Is my WHERE clause wrong? Or my index? Or something else?

I have imported the wikibase data into a bucket (I’m using full ejection if that matters).

I run this query (which uses the primary index):

SELECT w.* FROM `wikibase` w LIMIT 1;

And it returns:

[
  {
    "aliases": { ... },
    "claims" : { ... },
    "datatype": "commonsMedia",
    "descriptions" : { ... },
    "id": "P10",
    "labels": {
      ...
      "en": {
        "language": "en",
        "value": "video"
      },
      ...
    },
    "type": "property"
  }
]

Let me focus in on a part that I’m interested in, the English label. I run this query:

SELECT w.labels.en FROM `wikibase` w LIMIT 1;

And as I would expect, I get:

[
  {
    "en": {
      "language": "en",
      "value": "video"
    }
  }
]

I plan to query on that value field a lot, so I created an index:

CREATE INDEX ix_labelenglishvalue ON `wikibase` (w.labels.en.`value`);

After the indexing finished, I ran this query:

SELECT w.labels.en.`value`
FROM `wikibase` w
WHERE w.labels.en.`value` = 'video'
LIMIT 1;

But that returns nothing (“results” : []). I ran an EXPLAIN on it, and it’s hitting the index I created (ix_labelenglishvalue). I would expect it to return at least one result. What am I doing wrong?

Remove w from your index.

1 Like

Doh! Thanks @geraldss

1 Like