listread wrote:

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:

Postgresql & php tutorials

PHP Database Mailing List (
To unsubscribe, visit:

Reply via email to