On Fri, 20 Dec 2002, Noah Silverman wrote:
> Issue #1: Speed of inserts is relatively slow. 100000 inserts is
> taking
> roughly 10 minutes. This isn't EVIL, but mysql appears to be about
> ten times faster here. Is there something we could do to the indexes
> differently? Disable transactions? Is there a more "raw" insert, which
> may not set off triggers?
Are you doing these in a transaction? If not, then try adding a
begin;end; pair around your inserts. i.e.
begin;
insert 100000 rows
end;
that should help.
Reading the rest of your message, it appears there are two issues here.
One is you might get some help from a multi-column index.
Further, have you run analyze on your database?
Have you read the administrative docs yet? There's lots more good stuff
in there too. These are the basics.
The other issue is the assumption that indexes are ALWAYS faster, which
they aren't. If the query planner thinks it's gonna grab some significant
portion of a table, it will just grab the whole thing instead of using an
index, which makes a certain amount of sense. To reduce the likelihood of
the planner picking a sequential scan, change random_page_cost from the
default 4 to something lower. A 1 means that the cost of grabbing a page
randomly is the same as grabbing it sequentially, which shouldn't be
possible, but is, if the data is all in memory.
Next, use EXPLAIN ANALYZE to get an output of both what the query planner
THOUGHT it was going to do, and what the query actually did, in terms of
time to execute.
Let us know how it all turns out.