Some Great Resources
Archives
Categories
- iPhone (2)
- Learning (40)
- Linux (4)
- Debian (1)
- Mac Terminal / Linux (5)
- Other Stuff (1)
- Programming (63)
- Slim Framework (1)
- Uncategorized (1)
- WordPress (5)
and other things too...
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:
|
1 2 3 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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:
|
1 2 3 |
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.
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
Extravalues ofUsing filesortandUsing temporary.
Let’s examine why those two are bad.
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.
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.