bookmark_borderMySQL Stored Procedures

Here is a tip you can use when writing a stored procedure, and using prepared statements.

Since you have to pass the statements in through EXECUTE stmt USING …   it can get confusing if there are a large number of parameters.

I had this happen recently when I had about 40 parameters in one query.

Although you can pass the same parameter multiple times,  like this:

EXECUTE stmt USING @p1,@p2,@p1,@p2,@p3,@p4

I did something that would allow me to easily match up the parameters with the lines they go with in the stored procedure.

Here’s a snippet as an example:

AND
  CASE
   WHEN ? = 1 AND ? = 1 AND ? = 1 THEN #l2
   last_seen BETWEEN ? AND ?  #l3
    WHEN ? = 1 AND ? = 1 THEN #l4
     (last_opened BETWEEN ? AND ? OR last_viewed BETWEEN ? AND ? ) #l5
    WHEN ? = 1 AND ? = 1 THEN  #l6
     (last_opened BETWEEN ? AND ? OR last_clicked BETWEEN ? AND ? ) #l7
    WHEN ? = 1 THEN #l8
     last_opened BETWEEN ? AND ?  #l9
    WHEN ? = 1 AND ? = 1 THEN  #l10
     (last_viewed BETWEEN ? AND ? OR last_clicked BETWEEN ? AND ? )  #l11
    WHEN ? = 1 THEN #l12
     last_viewed BETWEEN ? AND ?  #l13
    WHEN ? = 1 THEN #l14
     last_clicked BETWEEN ? AND ?  #l15
    ELSE
     last_seen BETWEEN ? AND ?   #l16
    END

I copied the code to another editor, and added line numbers. I could have left them in the stored procedure but chose not to in order to keep it clean.

Then, I was able to make sure that the variables were in the right places by naming them the same way:

USING ..., @l2use_opens,@l2use_views,@l2use_clicks, @l3start,@l3end, @l4use_opens,@l4use_views, @l5start1,@l5end1,@l5start2,@l5end2, @l6use_opens,@l6use_clicks, @l7start1,@l7end1,@l7start2,@l7end2, @l8use_opens, @l9start,@l9end, @l10use_views,@l10use_clicks, @l11start1,@l11end2,@l11start2,@l11end2, @l12use_views, @l13start,@l13end, @l14use_clicks, @l15start1,@l15end1, @l16start2,@l16end2, ...;

Also, notice how I group them together, leaving a space at each place where there is a new line.

You could do the spacing and grouping trick and not use line numbers too, since you can reuse variables in the list, but I prefer this because it is very explicit and clear.

bookmark_borderLaravel Eloquent Attributes

When dealing with an Eloquent model, you can add your own attributes. These can be useful for computed values.

For example:

class Person extends Model
{
   // By default, all the database fields will be available.
   // Let's assume for this example class that we have
   // first_name, last_name, age
   // but we also want a property called full_name, that doesn't 
   // exist in the database. 

   public function getFullNameAttribute()
   {
      return $this->first_name . ' ' . $this->last_name;
   }

}

// To access that property in a controller, you use the camelCase version of it:
// (assuming you already have an object instance called $aPerson)

$fullName = $aPerson->fullName;

// To access the property directly from the class instance in a view using blade, 
//(assuming that you passed the instance to the view), you use the snake_case version of it

{{ $aPerson->full_name }}

Just remember that when creating an attribute, you start with get and end with Attribute, and the name goes in the middle. The whole thing is camelCase. This is Laravel’s convention.

bookmark_borderSQL EXPLAIN: The “Extra” column

This column contains additional information about how MySql resolved the query. There is a line in the manual which states:

If you want to make your queries as fast as possible, look out for Extra values of Using filesort and Using temporary.

Let’s examine why those two are bad.

  • Using filesort

If you see this, it means that an extra pass has been made through the data, in order to sort it. This is not as efficient, and indicates that you may be able to improve query performance.

  • Using temporary

This means that a temporary table will have to be created to hold the result. The manual states that this typically happens if the query contains GROUP BY and ORDER BY clauses that list columns differently.

The MySQL Manual has a fascinating page about ORDER BY Optimization.

bookmark_borderSQL EXPLAIN: The “ref” column

The “ref” column shows which columns or constants are compared to the index named in the “key” column to select rows from the table.

This comes right from the manual, but I have noticed that when the value is const, it means that you are comparing to a constant value rather than say, another column, but it does not tell you what that value is. The wording in the manual indicates it will tell you the constant’s value, but that is not correct.

The important takeaway here is that you can can see what is being compared to what. For example, a constant value to an index.