In Mysql, Does Putting Select Foo Increase In Performance If Foo Is Indexed?
Solution 1:
First thing to be aware of is that MySQL only uses one index per psuedo-SELECT (not statement) - when you view output of the SELECT using EXPLAIN, you'll see which index was chosen per. EXPLAIN can only be run on SELECTS, so we have to assume that a DELETE/UPDATE is using the same plan when you swap out the syntax for SELECT...
Most databases (embedded ones can be odd) to my knowledge support the use of indexes in the following clauses:
- SELECT
- JOIN (ANSI-92 syntax)
- WHERE (because there's both ANSI-89 and filteration here)
- HAVING (WHERE equivalent, but unlike WHERE - allows aggregate use without needing subquery)
- ORDER BY
I'm not 100% on GROUP BY, so I'm omitting it for the time being.
Ultimately, it's the optimizers choice for what to use based on it's algorithm and the statistics it has onhand. You can use the ANALYZE TABLE syntax to refresh the statistics (periodically, not constantly please).
Addendum
MySQL also limits the amount of space that for allocating indexes - 1,000 bytes for MyISAM tables, and 767 bytes for InnoDB tables. Because of MySQL only using one index per psuedo-SELECT, covering indexes (indexes that include more than one column) are a good idea but it really comes to testing the most common query & optimizing for it as best you can. The indexing priority should be:
- Primary key (somewhere in v5, index creating for the pk became automatic)
- Foreign keys (next most likely JOIN candidate
- Filtration criteria (assuming you have the space)
Solution 2:
If you are filtering on those columns you are planning to index you might get a performance increase. Since your database is mostly readonly (you only get 500-2000 new rows a day and you are probably not updating that much), you can give it a chance. You definitely won't hurt your database very much if you add those indices.
Post a Comment for "In Mysql, Does Putting Select Foo Increase In Performance If Foo Is Indexed?"