Re: Index size

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

--