On 28 Jul 2015, at 8:25pm, rotaiv <rotaiv at gmail.com> wrote: > From what I read, it was recommended to create indexes for any field used > in the SELECT statement. Is that correct? Give my original query: > > SELECT home.fpath > FROM home > LEFT JOIN work ON work.fpath = home.fpath > WHERE work.fpath IS NULL > AND home.ftype = 'f';
I really must get around to writing a web page on this subject. It's almost a decade since I decided to. Ignore the idea of creating indexes for certain fields. It will speed things up slightly but nowhere near as much as is possible. A better approach is to look at each SELECT command (or WHERE clause) and to create an index ideally suited to speeding up that one thing. Something to bear in mind is that each search or sort operation can only usefully use one index. So for this SELECT: SELECT * FROM myTable WHERE a = '123' ORDER BY b there's no point in creating both these indexes CREATE INDEX m_a ON myTable (a); CREATE INDEX m_b ON myTable (b); Because once SQL has used m_a to pick the right rows it is no longer looking at the whole table so it can't use index m_b to do the sorting. However, this index CREATE INDEX m_a_b ON myTable (a,b); can be used to do both operations: it allows SQL to find the right rows in the right order, just by selecting a section of the index. It is the ideal index for that SELECT. Simon.
