0

I have a scenario where there is one list with 3 columns : Col1, Col2, Col3

For each value of Col1 there would be 100 unique Col2 values. And for each Col2 values there would be 1000 unique Col3 values.

List has more than 1,000,000 items.

I would like to perform a query that for a given Col1 value, I need to display a list of unique Col2 Values related to Col1 and count of Col3 values.

For example, for a given dataset as shown below

--------------------------------------
|  Col1     |    Col2      |   Col3  |
--------------------------------------
|  Val1     |    Cl2_1     |   Cl3#1 |
|  Val1     |    Cl2_1     |   Cl3#2 |
|  Val1     |    Cl2_2     |   Cl3#3 |
--------------------------------------

My output should be something like :

|  Val1     |
-------------------------
|  Col2     |    Col3   |
-------------------------
|  Cl2_1    |    2      |
|  Cl2_2    |    1      |
-------------------------

What are my possible options?

0

I'm not sure if this helps, since you are using caml (I have only really worked with MYSQL), but I will give you my explanation for that, and hopefully it will help your problem as well (I'm having a hard time finding references for the caml equivalent). What you need to do is include indexing (if CAML allows you to index your queries).

In MySQL, you have are able to run this query:

EXPLAIN SELECT col1,col2,col3 FROM table WHERE col1= 'val1';

This will return

+----------+------+---------------+------+---------+------+------+------------+
| table    | type | possible_keys | key  | key_len | ref  | rows | Extra      |
+----------+------+---------------+------+---------+------+------+------------+
| table    | ALL  | NULL          | NULL |    NULL | NULL |some# | where used |
+----------+------+---------------+------+---------+------+------+------------+

The NULL values in this example are qhy queries run through all possible combinations. If CAML has an equivilant to this, it will just show you keys and references used to query your database. You need to modify these keys to narrow down searches.

Unless you split apart your database into smaller tables, and then decide which tables to query code-side, this is the only option I know of.

If this answer isn't what you are looking for, let me know, and I'll remove it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.