In Phoenix if you select any column apart from indexed column it will perform a full scan to get the resut. But you can include required columns in the same index created. If you don't want to include column in the index table due to space utilization or dynamic query , you can perform a work arouond for this. Get the primary key from indexed table and using that key query the main table to get required columns. once phoenix supports subquery we can achive the same in one subquery. Thanks & Regards Job M Thomas
________________________________ From: yixuan geng [mailto:[email protected]] Sent: Sat 5/3/2014 1:03 PM To: [email protected] Subject: Secondary index is not used Hi all, I am using the great secondary index feature on an existing immutable table. I was able to successfully create the index and actually saw the index table in hbase. However, when I do "explain {query}", I always get " x-way full scan" which I believe means the index is not actually used. I know people have asked similar questions and the root cause turned out to be that the columns used in the query is not a subset of the columns defined/included in the index. But I am pretty sure my index has covered all the columns in the query. Here is the example: I have an existing table in hbase named "metadata_test", it has only one column family "info". There are already some data rows in the table. 1. Since the table is "write once, append only", I did: ALTER TABLE \"sgtrack_metadata_test\" SET IMMUTABLE_ROWS=true 2. Then I created an index (info.appid, info.counterid, info.time) on this table as following: create index \"test_index\" on \"metadata_test\" ( \"info\".\"appid\",\"info\".\"counterid\", \"info\".\"time\")"; 3. It took some time to create the index. Once its done, I saw a TEST_INDEX table in hbase. So I ran a query to see if the index is actually used explain select \"info\".\"appid\" from \"metadata_test\" where \"info\".\"appid\" = 'test_app' and \"info\".\"counterid\" = 'test_counter'; Please note in the query only two columns (info.appid and info.counterid) are used 4. The result I got from "explain" is CLIENT PARALLEL 1-WAY FULL SCAN OVER sgtrack_metadata_test SERVER FILTER BY (info.appid = 'test_app' AND info.counterid = 'test_counter'') I assume the explanation means the index is not used for the query. I don't understand why. The only reason I can think of is my test dataset is small so Phoenix did not bother to use the index. I tested with 50K rows and 3 million rows, both return the same full scan result. I am using phoenix 2.2.2 client/server and hbase 0.94.16. Anyone has any idea please? Thanks, Yixuan
