Thanks all for the replies.
I will illustrate using a simplified schema and query. Please note I am not
using any Phoenix indexes as there are further complexities that are not
illustrated by this simplified example that preclude using that Phoenix
capability.
I have created two tables and two sample queries of the join I will paste
below.
Perhaps I am missing something, but I am unclear as to why a full table scan is
needed on the clicks table given the key fields in the query for both tables.
Any further insight is very appreciated in advance.
CREATE TABLE IF NOT EXISTS clicks (
sha_key VARCHAR(64) NOT NULL PRIMARY KEY,
user_id VARCHAR(40),
product_id VARCHAR(40),
zip_code VARCHAR(40))
COMPRESSION=SNAPPY, DISABLE_WAL=true, IMMUTABLE_ROWS=true;
CREATE TABLE IF NOT EXISTS products (
product_id VARCHAR(40) NOT NULL,
sha_key VARCHAR(64) NOT NULL,
zip_code VARCHAR(64) NOT NULL
CONSTRAINT pk PRIMARY KEY (product_id, sha_key, zip_code) )
COMPRESSION=SNAPPY, DISABLE_WAL=true, IMMUTABLE_ROWS=true, SALT_BUCKETS=256;
explain
select clicks.*
from
clicks, products
where
clicks.sha_key = products.sha_key and
products.product_id in ('w8kfc1','wxdfe8','fmlwl6') and
products.zip_code in ('90210','55511','81811')
+--------------------------------------------------------------------------------------------------------------------------+
| PLAN
|
+--------------------------------------------------------------------------------------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER CLICKS
|
| PARALLEL INNER-JOIN TABLE 0 (SKIP MERGE)
|
| CLIENT 256-CHUNK PARALLEL 256-WAY ROUND ROBIN SKIP SCAN ON 768 KEYS
OVER PRODUCTS [0,'fmlwl6'] - [255,'wxdfe8'] |
| SERVER FILTER BY FIRST KEY ONLY AND ZIP_CODE IN
('55511','81811','90210') |
| DYNAMIC SERVER FILTER BY CLICKS.SHA_KEY IN (PRODUCTS.SHA_KEY)
|
+--------------------------------------------------------------------------------------------------------------------------+
explain
select clicks.*
from
clicks
where
clicks.sha_key in (
select products.sha_key
from
products
where
products.product_id in ('w8kfc1','wxdfe8','fmlwl6') and
products.zip_code in ('90210','55511','81811'))
+--------------------------------------------------------------------------------------------------------------+
| PLAN
|
+--------------------------------------------------------------------------------------------------------------+
| CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER CLICKS
|
| SKIP-SCAN-JOIN TABLE 0
|
| CLIENT 256-CHUNK PARALLEL 256-WAY SKIP SCAN ON 768 KEYS OVER PRODUCTS
[0,'fmlwl6'] - [255,'wxdfe8'] |
| SERVER FILTER BY FIRST KEY ONLY AND ZIP_CODE IN
('55511','81811','90210') |
| SERVER AGGREGATE INTO DISTINCT ROWS BY [SHA_KEY]
|
| CLIENT MERGE SORT
|
| DYNAMIC SERVER FILTER BY CLICKS.SHA_KEY IN ($5.$7)
|
+--------------------------------------------------------------------------------------------------------------+
____________________________________________________________
Affordable Wireless Plans
Set up is easy. Get online in minutes.
Starting at only $14.95 per month!
www.netzero.net?refcd=nzmem0216
