Category Archives: MySQL

Primary Key : INT UNSIGNED – About 4.3 billion available IDs. Going unsigned gives you the extra 2 billion. Phone Number : VARCHAR – Phone numbers are identifiers that happen to use digits. – See this post. Price : FLOAT … Continue reading →

Make the unique key long enough to allow for reasonable duplication of names in the real world For example, for a table of people, a unique key of country/state/city/address might be a good choice.  This allows for two people in … Continue reading →

Store phone numbers as strings, not numbers. Phone numbers are identifiers that happen to use digits. Phone numbers starting with zero are valid, but may be interpreted as octal by a programming language. Strip the phone number to only digits … Continue reading →

This stuff has helped improve many of my queries. The basics: Its bad whenever you have the second table in the explain returning more than 1 row. The driving table is first and all other tables joined to it should … Continue reading →

How to sort values from lowest to highest, but leaving the zeroes last: SELECT foo FROM bar order by foo = 0, foo ASC

Slow Query Example SELECT widget_id , widget_name , COUNT(DISTINCT type) AS num_types FROM widget GROUP BY type ORDER BY num_types DESC With lots (>100K) of rows, this query will get slow because it orders on an aggregate COUNT DISTINCT function. … Continue reading →

When optimizing a query, the order of fields matter in a multi-field index. In general, you should use the most selective fields first. For example, in a hypothetical location index, use zip, city, state, rather than the reverse. Source: MySQL … Continue reading →

Use UNSIGNED INT (or BIGINT if needed) for primary key, since you don’t need negative primary keys. Don’t use ON DELETE CASCADE, since you might delete rows you wanted to keep.  Explicitly delete rows in your code. Don’t use ON … Continue reading →

Instead of: ALTER TABLE my_table ADD KEY `key1` (`apple`); ALTER TABLE my_table ADD KEY `key2` (`banana`); do this: ALTER TABLE my_table ADD KEY `key1` (`apple`), ADD KEY `key2` (`banana`);

Move a column after some other column mysql> ALTER TABLE mytable MODIFY COLUMN mycolumn INT AFTER someothercolumn; Move a column to the first position mysql> ALTER TABLE mytable MODIFY COLUMN mycolumn INT FIRST;

Use EXPLAIN to show which indexes are being used. mysql> EXPLAIN SELECT mycolumns FROM table1 LEFT JOIN table2;\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: ag type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1010 … Continue reading →