stored procedure mysql

Question

I have a database call that i am not sure if i am doing it the most efficient way. Basically the call queries a table of events with zip codes and then joins a zip code database that gives the lat/lon of that events zip. Then it joins the logged in user to the query and that user has a lat/lon of upon logging in. So the whole query pulls events from within so many miles of of the users lat/lon.

My question, is there a better way to do it then calling this query each time the page is loaded? would a stored procedure be faster? I dont have any experience with them. I am using MySQL.

    $this->db->select('*');
$this->db->from('events');
$this->db->join('zipcodes', 'zipcodes.zipcode = courses.courseZip');
$this->db->join('eventTypes', 'eventTypes.eventTypeID = events.eventType');
$this->db->where('eventApproved', 1);
$this->db->select('(DEGREES(ACOS(SIN(RADIANS('.$this->user['userLat'].'))
     * SIN(RADIANS(latitude))
     + COS(RADIANS('.$this->user['userLat'].'))
     * COS(RADIANS(latitude))
     * COS(RADIANS('.$this->user['userLon'].' - longitude))))) * 69.09 AS distance');

$this->db->having('distance <', 100);

Answer 1

Yes it will help to have a stored procedure here. Reasons are 1. it makes your Database layer more managable 2. SP are precompiled. When you run then first them the engine will create a execution plan and save the plan. Next time when it runs it will reuse the plan. So you get some performance benifit. In your case you might get lot of benifit if the underlined table is not changing (updated/deleted) too much after SP is made. If it is then you can RECOMPILE the sp(Running it WITH RECOMPILE OPTION) and it will create and save a new plan.

How you do it . Well it is quite easy. If you are using HeidiSQL for MySQL Front End or the query browser of MYSQL enterprise 5.0 you might be able to generate the SP graphically . But even if you want to code it from scratch it is easy.

http://dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html

Sp also are advisible from Security point of view because they can stop SQL Injection attacks.

Once you have the SP you can tune the table to make the SP faster. 1. Create a Index (nonclustered ) on the columns in where clause 2. Include the column that you bringing in SELECT in this Index.

In Microsoft SQL Server you can do this usign covering index. I am not sure if you can do it in MYSQL or not. But even if you can you should try to create a index covering as many columns as you can in either clustered or non clustered index.

HTH

Answer 2

Your going to want to store as much data (such as the user's lat/long) in Session as possible. This way your not querying for this data, that's isn't really changing, on every page load.