Filtering A MySQL Result Set Based On The Return Value Of A Function

Question

I'm a SQL noob, and I need a little bit of help understanding the big picture of if it is possible, and if so how to go about filtering a result set based on the return value of a function which is passed one of the fields of the record.

Let's say I have a table called "Numbers" with just one field: "Value".

How could I correctly specify the following "pseudo-sql"?:

SELECT Value FROM numbers WHERE IsPrime(Value)=true

Can I accomplish such a thing, and if so, where/how do I put/store "IsPrime"?

I'm using MySQL.

Answer 1

I think you may find some help with the doc about the user-defined functions: http://dev.mysql.com/doc/refman/5.1/en/adding-functions.html

Answer 2

I don't know anything about user defined functions, but I could imagine that for computation-intensive functions it might be best to have that value precomputed and stored in the database somewhere.

Depending on how the data gets in the database you could require the client to compute isPrime (that can be done if the client is a web service or app on your own server), or perhaps a scheduled job which processes every record with isPrime is null or something like that. That is not instantaneous, but for some scenario's it may be good enough.

If isPrime is only used sometimes you could also post-process/filter the data on the client when retrieving data.

Answer 3

I agree with extraneon that it's usually better to store this value in the database rather than compute it in the where clause. This way you can compute it once per row and index the data for faster performance.

As long as you are on MySQL 5.x, I would recommend a stored function, as opposed to a UDF. You can add an IS_PRIME column of type TINYINT to your DB, add an index on that column, then use the stored function to calculate the value at insert time. You can even calculate the IS_PRIME value using a before insert trigger if you don't want to change the insert code.

The stored function would look something like this:

DELIMITER $$

DROP FUNCTION IF EXISTS IS_PRIME $$

CREATE FUNCTION IS_PRIME(P_INT BIGINT) RETURNS TINYINT
BEGIN
  DECLARE V_FACTOR BIGINT;
  DECLARE V_MAX_FACTOR BIGINT;

  SET V_FACTOR := 2;
  SET V_MAX_FACTOR := round(sqrt(P_INT),0);

  WHILE (V_FACTOR <= V_MAX_FACTOR)
  DO
    IF (P_INT % V_FACTOR) = 0
    THEN
      RETURN FALSE;
    END IF;
    SET V_FACTOR := V_FACTOR + 1;
  END WHILE;

  RETURN TRUE;

END $$

DELIMITER ;