listread wrote:
Greetings!
We are working on a 4 million record db with about 55 fields. We need
to build indexes to speed queries, but it seems to take way too long.
I'm assuming your using mysql but that's just a guess.
What database? (mysql, oracle, postgres, firebird, sqlite, other)
If it's mysql, what db engine?
Here are some questions:
1) If a query uses two or three fields is it best to prepare an index
with those two or three fields, or will the query be just as efficient
if we build three separate index, one for each field?
Relates to above, but assuming mysql then it depends on what version.
multicolumn indexes work well in all mysql versions. separate indexes
can be used and combined in newer versions (v5 I think it came in).
2) Is the index building process any slower, per index, if you are
building more than one index at a time?
Most indexing I think takes an exclusive lock on the table, so index 1
gets created, index 2 has to wait it's turn until index 1 finishes.
I wrote an article on this a while ago, might give you some more
insights: http://www.designmagick.com/article/16/
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
