Just a shot in the dark but I don't think non-pk columns can be non-null. So try removing the "NOT NULL" from CONFIDENCE, RISK, and INTERVAL in the create table statement and see if that works.
On Tue, Apr 15, 2014 at 12:39 PM, Russell Jurney <[email protected]>wrote: > 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 > -- Dan Di Spaltro
