0

Let's say there is a table that looks like this:

name | date | value1 | value2 | value3 | ... | value100 varchar | date | float | float | float | float ...

I can do SELECT statments with this, and use WHERE value1 > n, or do WHERE value1 > n AND value2 = n.

However, what if I wanted to include queries that included up to 100 conditions to filter the results in the table based on values in every column?

Is this possible? If it is, how could you go about indexing this since the maximum number of columns in an index is 16? The results of any given combination of WHERE conditions I will need within 10 seconds.

Thanks for the insight.

  • 1
    If you really have a table with 100+ columns I might say that this by itself is bad database design. – Tim Biegeleisen May 01 '18 at 02:28
  • even if you did index that all; it would never help you as you would need to ensure your where was utilizing each index in order. – Napoli May 01 '18 at 02:30
  • 1
    What indexes you put on a table are determine by what queries yo run against the table, how frequently those queries are run and the distribution of the data. Asking the question properly with a concrete example and the right supporting information exceeds the scope of a question here on SO. Asking in the abstract is way too broad. – symcbean May 01 '18 at 12:06
  • Thanks for the comments. Makes sense. –  May 01 '18 at 13:23

1 Answers1

0

See my answer to the following question https://stackoverflow.com/a/45611294/2350861

As mentioned in your question's comments, it isn't a good idea to have a table with so many columns. The best way to handle this necessity for attribute filtering is to have a separate values table with a many to one relational mapping. Then you can filter on the desired values like so:

SELECT * FROM table a where conditional = X 
AND a.PrimaryId IN 
(SELECT P_Id FROM values WHERE ValueType = "value1" AND ValueValue = "x" AND P_Id = a.PrimaryId)
AND a.PrimaryId IN 
(SELECT P_Id FROM values WHERE ValueType = "value2" AND ValueValue = "y" AND P_Id = a.PrimaryId)
.... ;

You can also use INNER JOINS instead

SELECT * from table a where conditional = X
INNER JOIN values b
INNER JOIN values c
WHERE a.P_Id = b.P_Id AND a.P_Id = c.P_Id ...
AND b.ValueType = "value1" AND b.ValueValue = "x"
AND c.ValueType = "value2" AND c.ValueValue = "y"
... ;

Although better than a table with hundreds of columns, this could still get pretty ugly as the number of "values" grows. I would consider using an external free-text/attribute search engine or indexer.

MarCPlusPlus
  • 366
  • 1
  • 5