Fast AdHoc Queries

0
votes
  • (Requirements)
  • We need a fast and scalable solution.
  • We need faster way of doing adhoc queries;

I am thinking about seperating my 10 column table into 10 tables. The table has got 10 Billion rows.

Original Table ID,VALUE1,VALUE2,VALUE3,VALUE4,VALUE5,VALUE6,VALUE7,VALUE8,VALUE9,VALUE10

into 10 tables

  • ID,VALUE1 (indexed on value1)
  • ID,VALUE2 (indexed on value2)
  • ID,VALUE3 (indexed on value3)
  • ID,VALUE4 (indexed on value4)
  • ID,VALUE5 (indexed on value5)
  • ID,VALUE6 (indexed on value6)
  • ID,VALUE7 (indexed on value7)
  • ID,VALUE8 (indexed on value8)
  • ID,VALUE9 (indexed on value9)
  • ID,VALUE10 (indexed on value10)

My plan is if i got a 5 clauses in my WHERE condition , go to 5 tables and asked them than use a hashset to set a subset of ID's.

My Question is i am reinventing the wheel ?is this "Column Store DB By RDBMS" or something else?

1
sqlkey-valueadhoccolumnstore
What database software are you using? - Codesleuth
What do your queries look like? What is the frequency of each of the values? What is the selectivity (how many rows are selected) of a typical query? How frequently is the data updated? - Gordon Linoff

1 Answers

0
votes

You don't need 10 tables. 1 table with 10 indexes will have the same result. For the performance it depends on the clause. If your where clause is an AND expression like

select * from table 
where value1 = x
and value3 = y

then splitting the query won't help.

If your where clause is an OR expression like

select * from table 
where value1 = x
or value3 = y
or value5 = z

then maybe rephrasing the one query to a union would be faster. 

select * from table 
where value1 = x
union select * from table 
where value3 = y
union select * from table 
where value5 = z

But performance always depends on the database engine, and its optimizer.