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
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
