1

I have the following query: 

SELECT url, url_hash from pages WHERE url_hash IN (SHA1('URL1'), SHA1('URL2'), SHA1('URL3'))
ORDER BY FIND_IN_SET(url_hash, "SHA1('URL1'), SHA1('URL2'), SHA1('URL3')")

I would to keep the order of the result set the same as the order of the parameters in the IN clause.

I've found this SO question.

The problem is that mysql doesn't know how to interpret a set of SHA1 functions, if I pass them as strings, they are not evaluated correctly, and if I pass them an an expression (without quotes) I get the following error:

OperationalError: (1582, "Incorrect parameter count in the call to native function 'FIND_IN_SET'")
Community
  • 1
  • 1
Oleg Belousov
  • 9,981
  • 14
  • 72
  • 127

1 Answers1

5

I think you want to use field() rather than find_in_set():

SELECT url, url_hash
from pages 
WHERE url_hash IN (SHA1('URL1'), SHA1('URL2'), SHA1('URL3'))
ORDER BY field(url_hash, SHA1('URL1'), SHA1('URL2'), SHA1('URL3'));
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • +1 for correctness, but this is going to be horribly, *horribly* inefficient - you are calculating three hashes for every row (in the ORDER clause). – Piskvor left the building Apr 17 '14 at 14:22
  • Actually this is just an example. I have 30 - 40 urls in each query, the hash is a UNIQUE INDEX. Better off just selecting 40 rows individually?. The order of the result set is very crucial to me. – Oleg Belousov Apr 17 '14 at 14:29
  • Non-found records are also very important to me, but those ones I handle programmatically via Python. If this is truly such an overhead, perhaps i should make the sort programatically us well – Oleg Belousov Apr 17 '14 at 14:32
  • Also, I am using 512mb query cache size, which I plan to increase, and an EC node with SSD drive... – Oleg Belousov Apr 17 '14 at 14:40