First of all, do I need to do this or does CartoDB do this based on the query on the fly?
NYC's 311 database is pretty big and it'd be nice to be able to have an index on created_date and complaint_type.
1
-
According to docs at developers.cartodb.com/documentation/using-cartodb.html indices are automatically created on "cartodb_id" and "the_geom" columns. – Scro Jan 15 '14 at 2:22
Add a comment
|
After some research, answering my own question.
First, you do need to create your own indices. At the end of the day, its PostgreSQL underneath it all.
To do so, use the SQL API:
http://{account}.cartodb.com/api/v2/sql?q={SQL statement}&api_key={Your API key}
So for the specific example of creating an index for complaint_type and created_date:
http://{someaccount}.cartodb.com/api/v2/sql?q=create index on table_311_jan_2013_present (lower(complaint_type))&api_key={some API key}
http://{someaccount}.cartodb.com/api/v2/sql?q=create index on table_311_jan_2013_present (created_date)&api_key={some API key}
And should you not want to bother with the API, you can actually issue just about any SQL command, not just SELECT statements from the SQL Query window in the CartoDB dashboard!
It does filter the statement and prevents you from querying PostgreSQL system tables, but I was able to do CREATE INDEX, ALTER TABLE from the SQL window successfully.
