I have a table like so:
CREATE TABLE IF NOT EXISTS BEACONING_ACTIVITY (
EVENT_TIME VARCHAR NOT NULL,
C_IP VARCHAR NOT NULL,
CS_HOST VARCHAR NOT NULL,
REQUEST_METHOD VARCHAR NOT NULL,
USER_AGENT VARCHAR NOT NULL,
PERIOD_SECONDS INTEGER NOT NULL,
SLD VARCHAR,
CONFIDENCE DOUBLE NOT NULL,
RISK DOUBLE NOT NULL,
INTERVAL DOUBLE NOT NULL
CONSTRAINT PK PRIMARY KEY (EVENT_TIME, C_IP, CS_HOST, REQUEST_METHOD,
USER_AGENT, PERIOD_SECONDS)
) IMMUTABLE_ROWS=true;
I run this query:
select EVENT_TIME, SLD, C_IP, CS_HOST, REQUEST_METHOD, USER_AGENT,
PERIOD_SECONDS, CONFIDENCE, RISK, INTERVAL from BEACONING_ACTIVITY where
EVENT_TIME >= ? and EVENT_TIME <= ? and PERIOD_SECONDS = ?"
So I need this index:
CREATE INDEX BEACON_TIME_SERIES_INDEX ON BEACONING_ACTIVITY (EVENT_TIME,
PERIOD_SECONDS) INCLUDE (SLD, C_IP, CS_HOST, REQUEST_METHOD, USER_AGENT,
CONFIDENCE, RISK, INTERVAL);
But I get this error when I create it:
Error: ERROR 1007 (42K01): A key/value column may not be declared as not
null. columnName=_0._0:SLD (state=42K01,code=1007)
What is the correct thing to do in this situation? How can I optimize my
query?
Thanks!
--
Russell Jurney twitter.com/rjurney [email protected] datasyndrome.com
