ideal indexing for inner join, rowcount of 1

[Andrew Nuss]

Hi,

I am reading an indexing book that says that the ideal index for a single table select includes all the where clause columns first, the order by columns next, and then any additional select columns. This index avoids hitting the table itself.

Is this true for MySQL?

-----------------------

In this regard, concerning the following inner join:

SELECT d.imageUrl
FROM CategoryParentChild pc
INNER JOIN Document d
ON pc.childId = d.id
WHERE pc.parentPath like :likepath AND d.imageUrl IS NOT NULL
ORDER BY pc.parentPath
LIMIT 0, 1

Where the :likepath argument is a simple string followed by the '%' char.

I think the index on CategoryParentChild should be:

parentPath, childId

and the join would only consider those rows that match parentPath in the simple like expression and then also get the childId from the index

and the index on Document would be:

id, imageUrl

so that candidates that satisfy the where clause part relating to the primary table of the join, are then checked against the join id and the imageUrl check.

So question 1 is do I have my indices right? Or do I need more?

Question 2 is with a LIMIT 0,1, will the query engine stop the query early or get all the rows anyway?

If anyone can answer these questions, I think I can do my indices correctly for all my joins.

Thanks,
Andy