> CREATE INDEX dpi5 on device_perf_interval( > interval_end_date, > interval_type, > interval_duration > ); > > explain query plan > SELECT d.device_type, dpi.* > FROM device d, device_perf_interval dpi > WHERE d.device_id=dpi.device_id AND > dpi.interval_type=1 AND > dpi.interval_duration=300 > ORDER BY dpi.interval_end_date > LIMIT 5; > 0|1|TABLE device_perf_interval AS dpi WITH INDEX dpi5 ORDER BY > 1|0|TABLE device AS d USING PRIMARY KEY
Actually, I wouldn't recommend using the above index. Despite the use of the dpi5 index in ORDER BY, this query would likely be much slower than the previous dpi1 index query plan if just a small subset of the device_perf_interval table need be scanned. Don't obsess with the output of EXPLAIN QUERY PLAN. Use whatever set of indexes is fastest for most of your queries for your dataset within reason. Too many indexes bloat the size of your database and slow down insert speed. ____________________________________________________________________________________ The fish are biting. Get more visitors on your site using Yahoo! Search Marketing. http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
