Using Composite Indexing

[drmaves]

I am using a simple query against a MySQL table with a selection based upon two
columns.

SELECT *
FROM clog
WHERE cfid = '#client.cfid#' AND cftoken = '#client.cftoken#'

Used together cfid and cftoken are unique so...

Considering performance, am I better off indexing both cfid and cftoken and
leaving my WHERE clause as it is above or should I create a composite index?

If I create a composite index will MySQL automaticlly use it if I use the
WHERE clause above or do I need to change the WHERE clause, if so how?

[Neculai Macarie]

> I am using a simple query against a MySQL table with a selection based
upon two

> columns.
>
> SELECT *
> FROM clog
> WHERE cfid = '#client.cfid#' AND cftoken = '#client.cftoken#'
>
> Used together cfid and cftoken are unique so...
>
> Considering performance, am I better off indexing both cfid and cftoken

and

> leaving my WHERE clause as it is above or should I create a composite

index?

I think in this case it's better to create a composite index on cfid and
cftoken.

> If I create a composite index will MySQL automaticlly use it if I use the
> WHERE clause above or do I need to change the WHERE clause, if so how?

I'm pretty sure you don't need to change the where clause. You can use
explain to see what index will be used:

EXPLAIN
SELECT *
FROM clog
WHERE cfid = '#client.cfid#' AND cftoken = '#client.cftoken#'

--
<mack />

[drmaves]

Using EXPLAIN helped a lot. I had an index on cfid, cftoken and a composite
index on cfid and cftoken. Keeping the WHERE clause the same, EXPLAIN showed
that MySQL used the composite index. Now I can delete the first two indexes and
maintain only the composite index.