John Davidson

Message:

I am currently converting a PHP web app with a MYSQL database to a SQLite one. On the MYSQL side I have a function which I call in one of my queries. SQLite does not allow for stored functions so I am creating that function and binding it in PHP.

What is the difference in performance between the two methods? Is the necessity of binding a PHP function and calling it for each row in SQLite still faster than using the MYSQL function?

MYSQL Function

CREATE FUNCTION `SortLab` (location VARCHAR(16))

RETURNS int

NO SQL

BEGIN

 DECLARE working VARCHAR(16);

 DECLARE leftStr VARCHAR(16);

 DECLARE rightStr VARCHAR(16);

 DECLARE outcome INT;



 SET working = REGEXP_REPLACE(location, '[A-Za-z]+-[A-Za-z]*', '_');

 SET leftStr = LEFT(working, INSTR(working, '_')-1);

 SET rightStr = LPAD(RIGHT(working, LENGTH(working)-INSTR(working, '_')), 4, 0);



 SET outcome = CONCAT(leftStr, rightStr);

 RETURN outcome;

END$$

Psuedo PHP for SQLite

function sorter($value) {

 result = replace(value, regex, '_');

 result = left(result);

 result = right(result);

}



$db->createFunction('sortLab', 'sorter')



$results = $db->query(SELECT sortLab(data) FROM table);

I realise that in this example I can use the existing functions in SQLite but I have pulled this and written the psuedo from a much larger query

---

My Answer:
In terms of performance, it is difficult to say definitively which method would be faster without testing in your specific environment. However, there are a few factors to consider:

1. SQLite is known for being lightweight and fast, especially for smaller databases. If your database is not very large and the queries are not very complex, SQLite may perform well.

2. Calling a PHP function for each row in SQLite may introduce some overhead compared to using a stored function in MySQL. However, PHP is generally fast and efficient, so the difference may not be significant depending on the size of your dataset.

3. It is also worth considering the complexity of the function itself. If the function is very complex and involves a lot of calculations or string manipulations, it may impact performance more in SQLite compared to MySQL.

In general, it is recommended to test both methods in your specific environment to see which one performs better for your use case. You can use tools like profiling to measure the performance of your queries and functions in both SQLite and MySQL.