MySQL SELECT Statement against an Index

Question

I have a table that is indexed on the first 2 columns. Column A is called "directory" and Column B is called "name".

Is there short syntax for my select statement that I can use to return the proper row?

Example:

Can I

SELECT * FROM table WHERE indexname = '/dir/sub-dir/page.html'

or do I have to

SELECT * FROM table WHERE directory = '/dir/sub-dir/' AND name = 'page.html'

If I can use the first example, what does the WHERE clause look like? Thanks.

Answer 1

If you have an index defined against both columns, the second usage is the proper one. In SQL, you can only define predicates against columns in tables, views, or table valued user-defined functions (which I don't think MySQL has yet). You can't select off an index, but the optimizer will know to use the appropriate index when you include both columns.