phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Perko, Ralph J" <Ralph.Pe...@pnnl.gov>
Subject indexed query question
Date Mon, 19 Jan 2015 18:25:59 GMT
Hi

I have a question about the most efficient way to query many indexed columns.  Here is the
scenario:

Say I have a table with 100 fields

Table {f1,f2,f3,f4,…f100}

The first 10 fields are core fields and the client wishes to query them in any combination.

This is too many fields to create a secondary index for every combination so I create just
10, one for each core field.

Then when a query is submitted, I create a single query for each secondary index and return
just the PK, thus taking advantage to the index:

Example – for each indexed field included in the query:

SELECT PK FROM Table WHERE <indexed field> = <some value>’;

Then once I have a list of all PK values from all the index queries I will either get the
combination or intersection of all PKs, depending on the query logic (and/or)and then run
a final select:

SELECT * FROM Table WHERE pk IN(…);

Does this sound like a reasonable approach?

Thanks,
Ralph


Mime
View raw message