Re: Index size

[Samuel Williams <space.ship.traveller@xxxxxxxxx>]

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

--