2 simple SQL questions: optimizing aggegate query
От | Alex Rice |
---|---|
Тема | 2 simple SQL questions: optimizing aggegate query |
Дата | |
Msg-id | BE0640A2-7F2B-11D7-969D-000393529642@ARCplanning.com обсуждение исходный текст |
Ответы |
Re: 2 simple SQL questions: optimizing aggegate query
|
Список | pgsql-sql |
Hello, I am using Mnogosearch with a PostgreSQL backend. I am writing my own queries because I can't use the existing Perl or PHP front ends to Mnogosearch. 1) Is there a way to optimize this query? It takes ~6 seconds on my workstation :-( I would like to get it under 2 seconds. SELECT url.rec_id, url, title, content_type, txt, sum( case when dict.word = 'wordx' then 1 when dict.word = 'wordx' then 1 when dict.word = 'wordy' then 1 else 0 end ) as rank FROM dict, url WHERE url.rec_id = dict.url_id GROUP BY rec_id, url, title, content_type, txt ORDER BY rank DESC 2) In the above query, why can't I write "HAVING rank > 0"? instead of repeating the whole entire sum() expression "HAVING sum(...)" Thanks in advance, Alex Rice, Software Developer Architectural Research Consultants, Inc. alrice@ARCplanning.com alrice@swcp.com
В списке pgsql-sql по дате отправления: