Re: Best practices for using MySQL index

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



>> 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 General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux