0

I have a database with millions of record and I have to search and show the records based on search key. I am using PHP to communicate with MySQL and I can do it in traditional way but it's not a optimized way to do that.

I want to this with MySQL procedures, is this a optimized way? If yes then how we can create procedure and how to use in PHP code? I am using this code to create:

DELIMITER $$
DROP PROCEDURE IF EXISTS `getExerciseForSearchKey` $$
CREATE PROCEDURE `getExerciseForSearchKey`(IN searchText varchar(255),OUT result varchar)
BEGIN
SELECT * FROM exercisetemplate WHERE key like '%searchKey%';
END $$

DELIMITER ;
halfer
  • 19,824
  • 17
  • 99
  • 186
Purushottam
  • 844
  • 14
  • 25
  • Some remarks: `key` is a reserved word and you've got to escape this column name with backticks. This procedure will not be better optimized than this as a normal query. MySQL can't use an index for this sort of queries. You can omit your OUT parameter, because you don't use this parameter. – VMai Sep 20 '14 at 10:26
  • I don' think this offers any optimisation over the usual approach. Just index your search column and get a good server. If you really do want to use stored procedures, please do a search for that. – halfer Sep 20 '14 at 10:31

0 Answers0