A Z <[EMAIL PROTECTED]> wrote on 02/04/2005 07:21:35 AM: > > MySql 4.0.14 > > I understand that the most effective way of speeding > up SELECT statements is to have column level indexes. > Are there any other level indexes exist? I have been > told that there are but I have no idea, looked through > the manual, could not find anything that speeds up > column level indexing. > > regards > > > > > > > ___________________________________________________________ > ALL-NEW Yahoo! Messenger - all new features - even more fun! http: > //uk.messenger.yahoo.com >
Creating a good index schema is usually the most effective way to speed up most queries. However all indexes are not used for every query. The query engine chooses up to one index (per table) if it feels that using the index will help query performance. It determines this by checking the cardinality of the index as it relates to your query. The cardinality is related to how many rows the index holds for any particular value (or value range or value set) compared to the size of the index. The higher the cardinality, the more selective an index will be and that generally means that you will get fewer records back in the results of the query. When the engine determines, by looking at the tables statistics, that using an index in a particular query will result in returning approximately 30% or less of all the rows in a table, it will probably use the index. Many new users create very non-selective indexes which will be considered but not used as they end up returning too many rows to be useful (like indexing a true/false column). The reason that the usability threshold of an index is somewhere near 30% is that, for values larger than that number, it would take longer to use an index to get the position of each target row then go get that row from the data than it would to just scan the data directly in the first place. There are at least 2 fewer disk seeks to perform a direct read of the table than for an indexed locate for EACH value retrieved. The key to creating a good index schema is to look at your query patterns. Look specifically at your WHERE clauses, first. If you notice that 80% of your queries use your_column_a and your_column_b as a pair then it would more than likely help you to create an index that covered both of those columns ALTER TABLE your_table_1 ADD INDEX (your_column_a, your_column_b) Because MySQL is "smarter" than some database engines, any query that only references your_column_a may also use that index (depending on the cardinality). My advice is: A) Avoid single column indexes whenever practical. Most useful indexes contain from 2 to 5 fields. B) Don't forget that PRIMARY keys and UNIQUE constraints are also indexes. C) Design your indexes after your most common or frequently used query patterns. Analyze your WHERE clauses first, then look at speeding up certain queries by considering values in your ORDER BY clauses. D) Learn how to use EXPLAIN. It will give you excellent advice on how to help your queries. E) Sometimes functions in your WHERE clauses eliminate the possibility of using an index. Learn how to say "your_column_name <comparison> <function or constant expression>" rather than "<function or expression using your_column_name> <comparison> <function or constant expression>". For example, if you want to find date values in the column "logdate" that are at least 60 days old, DO NOT use this: WHERE logdate + 60 days < CURDATE() use this instead: WHERE logdate < curdate() - 60 days F) Read the fine manual. http://dev.mysql.com/doc/mysql/en/explain.html http://dev.mysql.com/doc/mysql/en/estimating-performance.html http://dev.mysql.com/doc/mysql/en/select-speed.html http://dev.mysql.com/doc/mysql/en/where-optimizations.html http://dev.mysql.com/doc/mysql/en/optimizing-database-structure.html (whole chapter) (especially this part) http://dev.mysql.com/doc/mysql/en/mysql-indexes.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
