mysql partial index or other way to decrease index size without splitting table

46 Views Asked by At

I have a table with raw json objects and a type column. The json can contain person information, company infos or any other object. Most of the json properties are the same for the same type. E.g. Each json of type person will have a firstname and lastname property

id, type, data
1, person, {firstname: jon, lastname: doe, location: us}
2, person, {firstname: bill, lastname: gates, gender: male}
3, company, {name: acme, domain: acme.com}

They are all in the same table because I do not know all object types in advance and the json of a type is also not always 100% the same structure.

I now want to index some properties to search for them. e.g. I want to search for companies by domain:

SELECT * FROM objects WHERE type = 'company' AND data-->domain = 'acme.com'

I would therefore set an index on type,data.domain However, this is a huge waste of space since the index on data.domain is only required for the rows that have type company.

In postgres I can use partial indexes that solve this problem and make the index much smaller.

Is there a similar solution in mysql other than having to create a separate table for each object type?

0

There are 0 best solutions below