Using MySQL + an ETL tool to parse large amounts of XML. I end up with a table that holds variables and values of those variables that are found in the XML. I already have an index on the Variable column, but wondering if there is a way to optimize queries on the VariableValue column. The problem is that based on the 'Variable' name, the 'VariableValue' value may or may not contain a value that would otherwise be good to index on. For example:
Address 123 Whatever Street
In the table above, I think it would make sense to want to index State (will hold 1 of 50 values) and Customer Type (will hold 1 of 2 values), but Address values, and Order Number values will always hold 1 of infinite values and it wouldn't make much sense to index those.
Is there a good way to tackle these besides building new tables/fields and doing some kind of weird mapping? I wonder if indexing on the first X bytes of VariableValue would make sense. I'd love your advice.