Re: Which query is less expensive / faster?

Jon Asher wrote:
> Thanks for the reply... but which query will be faster and less expensive?
> I don't have a table now with 4 million rows, but I'm thinking of building
> such a table. Querying it would return 1 row. The alternative is to query
> an existing table of 200k rows, and return 800 rows.
>
> Option 1: Query a table of 4 million rows, on 4 indexed columns. It
> will return 1 row:
>
> SELECT field1, field2, field3, field4 FROM tablea WHERE field1 = $1
> AND field2 = $2 AND field3 = $3 AND field4 = $4
>
> Option 2: Query a table of 200,000 rows on 1 indexed column.
> It will return 800 rows:
>
> SELECT *
> FROM tableb
> WHERE field1 = $1
>
> Which one is going to return results the fastest, with the least
> expense to the database server?

30 seconds anyway.The answer can only be "that depends". Maybe option1
has very wide columns for indexing and a poor spread of values. Maybe
option 2 has only three small columns, the indexed column is very
discriminating and the whole table plus index fits in RAM. Maybe you
have an array of 15,000rpm SCSI disks, maybe you have a laptop IDE
drive, maybe the client is at the end of a 56k modem line and fetching
800 rows will take Of course, that's all without taking into account
whatever else the database is doing, not to mention the rest of the machine.

Now, in general fetching one row will be quicker, but without real
information no-one can say. The best and quickest solution? Mock up some
tables with test-data and try it with your actual setup. Make sure your
test-data has a realistic distribution of values and number of rows. I
got caught out a couple of weeks ago where one small and insignificant
table crippled a big stats recalculation - all because my testing had
focused on the typical case of a few dozen rows and not the occasional
case of a thousand or so.

--
Richard Huxton
Archonet Ltd