[
https://issues.apache.org/jira/browse/PHOENIX-2548?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15546191#comment-15546191
]
James Taylor commented on PHOENIX-2548:
---------------------------------------
I tried the above in 4.8.0 release and got the following explain plan:
{code}
0: jdbc:phoenix:localhost> explain select devicename from devicedim_type1 where
tenant_id = 'ccd' and devicename = 'abc' and macaddress = 'afg';
+--------------------------------------------------------------------------------------------------+
| PLAN
|
+--------------------------------------------------------------------------------------------------+
| CLIENT 4-CHUNK PARALLEL 4-WAY ROUND ROBIN RANGE SCAN OVER DEVICEDIM_TYPE1
[1,'ccd','abc','afg'] |
| SERVER FILTER BY FIRST KEY ONLY
|
+--------------------------------------------------------------------------------------------------+
2 rows selected (0.047 seconds)
{code}
Although somewhat confusing (see PHOENIX-3344), the local index is being used
in this case. The local index resides in the same table as the data, so you'll
see the range scan over the data table. You'll notice the [1 in the range being
scanned over - that refers to the index of the local index (i.e. this is the
first one).
If you're using local indexes, I'd definitely recommend using Phoenix 4.8 or
above.
> Local Indexing Not Looks Like Working As Expected
> -------------------------------------------------
>
> Key: PHOENIX-2548
> URL: https://issues.apache.org/jira/browse/PHOENIX-2548
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.5.2
> Reporter: Gokhan Cagrici
> Assignee: Rajeshbabu Chintaguntla
> Priority: Critical
> Labels: verify
> Fix For: 4.9.0
>
>
> Hi,
> We are accessing this table using a variety of different WHERE clauses and
> for each of them, we are trying to create an appropriate index to avoid full
> table scan. Since there is going to be almost 20 indexes, we tried to proceed
> with local indexing since there will be a lot of writes.
> Here is the table definition:
> CREATE TABLE DEVICEDIM_TYPE1 (
> TENANT_ID VARCHAR NOT NULL,
> DEVICE_TYPE1_KEY BIGINT NOT NULL,
> CLASSNAME VARCHAR(64),
> DAY_IN_MONTH SMALLINT,
> MONTH_NUMBER SMALLINT,
> QUARTER_NUMBER SMALLINT,
> YEAR SMALLINT,
> WEEK_NUMBER SMALLINT,
> YEAR_FOR_WEEK SMALLINT,
> HOUR SMALLINT,
> MINUTE SMALLINT,
> IPADDRESS VARCHAR(50),
> DEVICENAME VARCHAR(255),
> MACADDRESS VARCHAR(30),
> CONSTRAINT PK PRIMARY KEY (TENANT_ID, DEVICE_TYPE1_KEY)
> ) SALT_BUCKETS=4, COMPRESSION='GZ', VERSIONS=1, MULTI_TENANT=TRUE;
> And here is the index:
> create local index gokhan_ix2 on devicedim_type1 (devicename, macaddress)
> Now if I execute this:
> explain select devicename from devicedim_type1 where tenant_id = 'ccd' and
> devicename = 'abc' and macaddress = 'afg'
> Here is the output:
> CLIENT 4-CHUNK PARALLEL 4-WAY RANGE SCAN OVER DEVICEDIM_TYPE1 [0,'ccd']
> SERVER FILTER BY (DEVICENAME = 'abc' AND MACADDRESS = 'afg')
> SERVER 100 ROW LIMIT
> CLIENT 100 ROW LIMIT
> I was expecting the index to be used. Am I wrong?
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
