From: "Sebastian"
> i have a table with several thousand records. i want to know if adding
> an index will improve things, example:
>
> -------------------------------------
> | id | item | text | runtime | dateline
> -------------------------------------
>
> now say i create this query:
>
> $stats = mysql_query("
> SELECT COUNT(*) as total, AVG(runtime) as avgtime
> FROM table WHERE dateline > ".strtotime('6 hours ago')."
> ");
>
> should dateline be an index?
> basically, i would like to know when i should create an index, when an
> index is not needed, and what columns should be indexes..It is very hard to give general guidelines, as you might have noticed in the MySQL documentation. Many pages contain clues and pointers about using indexes in various situations. But the actual solution depends on the data itself (how many different values exist in a column), the table type (different engines handle indexes differently), the query, the serverhardware, etc. In your example the first thing you can improve is probably the selection of the records that will be used to build the result of the query. Dateline is a candidate in this example. It will improve the query from a 'full table scan' to a 'range scan'. If the selection would retrieve more than approx. 30% of the records MySQL will not use an index anyway, because a full table scan (sequential read of the records) will probably be faster than using an index (random read of the records). http://dev.mysql.com/doc/mysql/en/query-speed.html is a good chapter to find out about optimizing queries. http://dev.mysql.com/doc/mysql/en/optimizing-database-structure.html contains various chapters about using indexes. When tables get bigger, you might need other configuration settings than the standard my.conf your probably now working with; dig through http://dev.mysql.com/doc/mysql/en/optimizing-the-server.html One of the more valuable tools in MySQL (besides loads of experience and trial-and-error) is the EXPLAIN statement (http://dev.mysql.com/doc/mysql/en/explain.html). Because you need basic information about indexes, it might be useful to read a good book on MySQL. Most books cover the basic use of indexes. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
