>> Index on most integer fields only. Text fields can be indexed, but is not >> important when you design your DB well. >> >> Don't index just all integer fields. Keep track of the cardinality of a >> column. If you expect a field to have 100.000 records, but with only 500 >> distinct values it has no use to put an index on that column. A full record >> search is quicker. > > Hmmm... That's new. :) To explain that further the idea is that if you have something like a 'status' field which can only hold 5 values, there's no point indexing it if there's a reasonably even spread. If you could only ever have a handful of fields with a status code of '1', then it's worth indexing if you have to find those particular records quickly. I don't think mysql supports partial indexes, but some databases do so you only index the fields that match a certain criteria. I'd suggest a more thorough approach to working out what to index rather than just trying to guess what's going on. Work out how long queries are taking (either use the mysql slow log or if you're using a database abstraction class, it should be easy enough to hack in) and concentrate on those first. http://www.designmagick.com/article/16/PostgreSQL/How-to-index-a-database (While it's on a postgresql site, there's nothing specifically for postgresql in that article - the same rules apply to mysql, oracle, mssql). -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
