jasonswett.net

Let’s say you have a table called customer (not customers) that has a column called ssn which is a varchar. (Side note: I don’t normally advocate abbreviations but it’s a different story when the abbreviation is a super-common one like SSN.)

Now let’s say you commonly do SELECTs and UPDATEs on customer.ssn. You can improve your performance, perhaps dramatically, by doing two things:

• Make ssn an int instead of a varchar
• Put an index on ssn

I had this exact situation earlier today. Results will vary depending on the size of your table and other factors, but my INSERTs/UPDATEs got almost 100 times faster after I made just these two changes. A significant difference!

Let’s say you’d like to create an index on customer.name. Here’s how you’d do it:

CREATE INDEX index_customer_name ON customer (name) USING BTREE;

If you’re new to indexes, here’s a tip on how indexes can improve performance. If you want to see all your indexes later, here’s how to do that.

Today I tried to drop one of the tables in my database but, when I tried to do it, I got a foreign key constraint error. I thought that was odd because as far as I could think I didn’t have any foreign keys pointing to that table. Apparently I was wrong and I didn’t want to manually dig through my 50+ tables to figure out what I was forgetting.

Luckily you can see all your foreign keys by doing this:

  SELECT TABLE_NAME,
         column_name,
         referenced_table_name,
         referenced_column_name
    FROM information_schema.key_column_usage
   WHERE table_schema = 'your_database_name'
     AND referenced_table_name IS NOT NULL
ORDER BY referenced_table_name