2

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.

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.