I am using a combination of php and MySql. My case is I am using two queries (one 'select' and 'insert') for an activity, sometimes 3 queries. This will be done very frequently by different users.
I am using mysql_query function separately to execute the queries. Is this good or is it better to use a SQL function which executes all the queries. I want to know which is better regarding performance.
In my opinion it is better practice to create a function which performs a single operation well. This makes the functions easier to test and allows them to be utilized in other operations in the future. In your case I would create a stored procedure to execute the routine.
If you're using the mysqli extension you can utilize multi_query:
http://php.net/manual/en/mysqli.multi-query.php
However please note that if one of the queries relies on the success of another of the queries, they should be separated out for error checking.
rollback on error option for multi query functions.It would help if you could show concrete examples - right now, it's all very hypothetical. However:
If you can combine the operations into a single query, it's the fastest option. For instance:
insert into tableA
select x, y, z
from tableB
where Foo = Bar
If you have to do some processing on the results of the select statement, you can create a stored procedure to do the processing. This avoids the round trip of sending the data back to your PHP server, which should yield a performance benefit - however, it may well be that PHP is a better/faster language to execute the processing in than the stored procedure. For instance, if you have to manipulate text, I'd do it in PHP.
