Is there a more efficient way to do this query?

NogDog

Assume a table something like this:

identifier
fld1
fld2
fld3
fld4
fld5

The "identifier" column is indexed, and there is a unique index across the five "fldN" columns. (They're not really called that, I'm just changing the names to protect the guilty.)

I want to get one instance of the 5 "fldN" fields per "identifier" (any one instance will do, as long as I get one for each unique "identifier"). The immediately obvious way I see is requires some sort of iterative process:

SELECT DISTINCT identifier FROM the_table

Then while looping on that result in the PHP code, do:

SELECT fld1, fld2, fld3, fld4, fld5 FROM table_name WHERE identifier=:identifier LIMIT 1

Order of results does not matter, I just need one and only one set of fields per "identifier".

So far I'm not thinking of a single-query solution that would work, but then it is Friday morning, so who knows? If it has any impact on what you might suggest, it's a large(ish) table with over 70,000,000 rows. I guesstimate that there are somewhere between 5 and 10 million unique "identifier" values.

Derokorian

if you don't care which row, why not just

SELECT fld1, ..., fldN FROM table GROUP BY identifier;

For clarity my test:

[07:31 pm] [-- on test] > select * from test_NogDog;
+----+------+------+------+------+------+
| id | fld1 | fld2 | fld3 | fld4 | fld5 |
+----+------+------+------+------+------+
|  1 |   11 |   12 |   13 |   14 |   15 |
|  1 |   21 |   22 |   23 |   24 |   25 |
|  1 |   31 |   32 |   33 |   34 |   35 |
|  2 |   41 |   42 |   43 |   44 |   45 |
|  2 |   51 |   52 |   53 |   54 |   55 |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)

[07:32 pm] [-- on test] > select * from test_NogDog group by id;
+----+------+------+------+------+------+
| id | fld1 | fld2 | fld3 | fld4 | fld5 |
+----+------+------+------+------+------+
|  1 |   11 |   12 |   13 |   14 |   15 |
|  2 |   41 |   42 |   43 |   44 |   45 |
+----+------+------+------+------+------+
2 rows in set (0.00 sec)

NogDog

That looks promising!

Thanks.

(Not sure when I'll get to try it, as I'm in Friday afternoon fire-fighting mode. 🙁 )

Derokorian

What?!? Fires on a Friday?? That never happens!! </sarcasm>

NogDog

Unfortunately, that does not work in PostgreSQL. It insists that all the fields in the query either be in the group by clause, or else within an aggregate function. 🙁

NogDog

Best(?) I've come up with so far:

select
  identifier, 
  string_agg(
    fld1 || '|' || fld2 || '|' || fld3 || '|' || fld4 || '|' || fld5,
    ','
  ) as the_data
from the_table
group by identifier

Then I'd just explode() "the_data" in the PHP code and pull off the first (or last) element (and then explode() that element). :queasy:

Derokorian

Sorry, so used to using MySQL that I forget it has a little neat (however unstandard) tricks.

I will take a look this evening after work at doing it in PostgreSQL because its a challenge for me now 🙂

Weedpacket

I think [font=monospace]SELECT DISTINCT ON (identifier) identifier, fld1, fld2, ... FROM the_table[/font] would be PostgreSQL's approach. The expression(s) in font=monospace[/font] is/are the criteria used to determine if a row is "distinct", while the following expressions are the columns actually returned.

NogDog

Weedpacket;11035657 wrote:
I think [font=monospace]SELECT DISTINCT ON (identifier) identifier, fld1, fld2, ... FROM the_table[/font] would be PostgreSQL's approach. The expression(s) in font=monospace[/font] is/are the criteria used to determine if a row is "distinct", while the following expressions are the columns actually returned.

We may have a winner!

select distinct on (identifier) identifier, fld1, fld2, fld3, fld4, fld5
from fubar.the_table
order by identifier
limit 30

"identifier","fld1","fld2","fld3","fld4_almost_always_empty","fld5"
"10000112","834","ANTH","000000780352","","01"
"10000141","834","ANTH","000000780421","","01"
"10000236","834","ANTH","000000780612","","01"
"10000238","834","ANTH","000000780614","","01"
"10000303","834","ANTH","000000780793","","01"
"10000801","803","NY1B","135JP1","","01"
"10000805","834","ANTH","000000781636","","01"
"10000806","834","ANTH","000000781637","","01"
"10000807","834","ANTH","000000781638","","01"
"10001003","834","ANTH","000000782035","","01"
"10001089","530","AZL2","79892","","05"
"10001091","834","OHF2","000000782287","","01"
"10001095","834","OHF1","000000782296","","01"
"10001310","834","OHF2","000000782988","","01"
"10001312","834","ANTH","000000782998","","01"
"10001317","834","ANTH","000000783007","","01"
"10001321","834","ANTH","000000507501","","01"
"10001924","834","ANTH","000000784439","","01"
"10001979","834","ANTH","000000784449","","01"
"10002784","834","OHF1","310950892990","","01"
"10003643","834","OHL5","000000706247","","01"
"10003786","834","ANTH","000000788508","","01"
"10007388","834","ANTH","000000795577","","01"
"10012466","134","OHF1","3110113791","","01"
"10012468","134","OHF1","0105546521","","01"
"10012472","834","OHF1","0105925821","","01"
"10012479","834","OHF1","010801599001","","01"
"10012481","134","OHF1","0108015991","","01"
"10012482","134","OHF1","3417242241","","01"
"10012485","134","OHF1","0166003024","","01"