Mastering PHP and Laravel

October 11, 2018 by Kenny

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.

September 25, 2018 by Kenny

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.