MySQL Forums
Forum List  »  Performance

ideal indexing for inner join, rowcount of 1
Posted by: Andrew Nuss
Date: March 10, 2011 09:17PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
ideal indexing for inner join, rowcount of 1
2125
March 10, 2011 09:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.