Indexing sql columns to speed up join query

Question

On the first answer on this post: Update one MySQL table with values from another it states that in order to speed up the query an index must be created. How do I do this?

I know it's fairly simple I just can't figure out how..

Answer 1

How to create index (official doc)

Answer 2

ALTER TABLE `your_table` ADD INDEX `your_index_name` 
    (`column_that_should_be_indexed`)

The general syntax for creating an index is as follows -

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
ON tbl_name (index_col_name,...)
    [index_type]

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

Answer 3

The typical syntax is:

 create index <index name> on <tablename>(<one or more columns>);

For instance:

create index t_col1_col2 on t(col1, col2);

Note that you also create indexes when you declare a column to be a primary key or unique (or add a unique constraint).

Also, indexes are sometimes most effective when they are composite -- that is, they contain more than one column.