Thread: would number of fields in a table affect search-query time?

would the number of fields in a table significantly affect the
search-query time?

(meaning: less fields = much quicker response?)

I have this database table of items with LOTS of properties per-item,
that takes a LONG time to search.

So as I was benchmarking it against SQLite, MySQL and some others, I
exported just a few fields for testing, into all three databases.

What surprised me the most is that the subset, even in the original
database, gave search results MUCH faster than the full table!

I know I'm being vague, but does anyone know if this is just common
knowledge ("duh! of course!") or if I should be looking at is as a
problem to fix?

Miles Keaton <mileskeaton@gmail.com> writes:
> What surprised me the most is that the subset, even in the original
> database, gave search results MUCH faster than the full table!

The subset table's going to be physically much smaller, so it could just
be that this reflects smaller I/O load.  Hard to tell without a lot more
detail about what case you were testing.

            regards, tom lane

On Mon, Oct 04, 2004 at 04:27:51PM -0700, Miles Keaton wrote:
> would the number of fields in a table significantly affect the
> search-query time?

More fields = larger records = fewer records per page = if you read in
everything, you'll need more I/O.

> I have this database table of items with LOTS of properties per-item,
> that takes a LONG time to search.

It's a bit hard to say anything without seeing your actual tables and
queries; I'd guess you either have a lot of matches or you're doing a
sequential scan.

You might want to normalize your tables, but again, it's hard to say anything
without seeing your actual data.

/* Steinar */
--
Homepage: http://www.sesse.net/

Miles,

> would the number of fields in a table significantly affect the
> search-query time?

Yes.

In addition to the issues mentioned previously, there is the issue of
criteria; an OR query on 8 fields is going to take longer to filter than an
OR query on 2 fields.

Anyway, I think maybe you should tell us more about your database design.
Often the fastest solution involves a more sophisticated approach toward
querying your tables.

--
Josh Berkus
Aglio Database Solutions
San Francisco