So, uh, my main question was, does MySQL add null values to an index, and is this different from Postgres. The schema is irrelevant, except that the column allows null values. I noticed when you create an index you can add a where clause. Could it be I should add WHERE the fields are not null?
Here is the schema. Any advice or suggestions most welcome. -- -- PostgreSQL database dump -- -- Dumped from database version 9.5.4 -- Dumped by pg_dump version 9.5.4 SET statement_timeout = 0; SET lock_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SET check_function_bodies = false; SET client_min_messages = warning; SET row_security = off; SET search_path = public, pg_catalog; SET default_tablespace = ''; SET default_with_oids = false; -- -- Name: user_event; Type: TABLE; Schema: public; Owner: http -- CREATE TABLE user_event ( id integer NOT NULL, user_id integer NOT NULL, latitude numeric(9,6), longitude numeric(9,6), active boolean DEFAULT true NOT NULL, poi_id integer, deal_id integer, category_id integer, what character varying(32) NOT NULL, locale character varying(8), created_at timestamp without time zone NOT NULL, parameters jsonb ); ALTER TABLE user_event OWNER TO http; -- -- Name: user_event_id_seq; Type: SEQUENCE; Schema: public; Owner: http -- CREATE SEQUENCE user_event_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE user_event_id_seq OWNER TO http; -- -- Name: user_event_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: http -- ALTER SEQUENCE user_event_id_seq OWNED BY user_event.id; -- -- Name: id; Type: DEFAULT; Schema: public; Owner: http -- ALTER TABLE ONLY user_event ALTER COLUMN id SET DEFAULT nextval('user_event_id_seq'::regclass); -- -- Name: user_event_pkey; Type: CONSTRAINT; Schema: public; Owner: http -- ALTER TABLE ONLY user_event ADD CONSTRAINT user_event_pkey PRIMARY KEY (id); -- -- Name: index_user_event_for_reporting; Type: INDEX; Schema: public; Owner: http -- CREATE INDEX index_user_event_for_reporting ON user_event USING btree (latitude, longitude, created_at, user_id); -- -- Name: index_user_event_on_created_at; Type: INDEX; Schema: public; Owner: http -- CREATE INDEX index_user_event_on_created_at ON user_event USING btree (created_at); -- -- Name: index_user_event_on_deal_id; Type: INDEX; Schema: public; Owner: http -- CREATE INDEX index_user_event_on_deal_id ON user_event USING btree (deal_id); -- -- Name: index_user_event_on_poi_id; Type: INDEX; Schema: public; Owner: http -- CREATE INDEX index_user_event_on_poi_id ON user_event USING btree (poi_id); -- -- Name: index_user_event_on_user_id; Type: INDEX; Schema: public; Owner: http -- CREATE INDEX index_user_event_on_user_id ON user_event USING btree (user_id); -- -- Name: index_user_event_on_what_category_id_created_at_latlng; Type: INDEX; Schema: public; Owner: http -- CREATE INDEX index_user_event_on_what_category_id_created_at_latlng ON user_event USING btree (what, category_id, created_at, latitude, longitude); -- -- PostgreSQL database dump complete --