Introduction This article is part of a series of posts related to calling various relational database systems stored procedures and database functions from Hibernate. The reason for writing this down is because there are many peculiarities related to the underlying JDBC driver support and not every JPA or Hibernate feature is supported on every relational … Continue reading How to call MySQL stored procedures and functions with JPA and Hibernate →
10 Older Entries »
Simple query using group clause for 1 million records resulting
in final list of 27 records.
First time takes 0.43053775 secs.
Same query through Stored procedure: First time takes 0.43341600 secs.
So in terms of time, first time they are very close.
Profiling comparison for both can be seen in below figure no_cache_comparison.png where left one is simple inline query and right one is stored procedure query.
There are some actions which are extra in the inline query:
1. freeing items
2. logging slow query
3. cleaning up
Running both second time retrieve data from cache taking
0.00048025 secs for simple query and 0.00036625 for stored procedure.
Profiling comparison for …
Explaining and providing solutions of MySQL error 1449: The user specified as a definer does not exist using SQL SECURITY INVOKER and DEFINER.
The performance_schema is a powerful tool for analyzing MySQL
performance and behavior. One aspect of the performance_schema is
that the view of the data is "right now", and very little
historical information is present. You can see that there are 10
threads connected right now, but what about five minutes
ps_history is a set of stored routines and events for periodically collecting the data in the performance_schema into another schema called ps_history. The ps_history schema contains a copy of each performance_schema view as a real table, and timestamp and server_id columns have been added to each table. Periodically (by default every 30 seconds) the performance_schema data is written into the history tables.
ps_history comes as one script (setup.sql) which will create the ps_history schema, the tables within it, and …
SELECT...INTO can be used to store the single row column
result value from a query to be used in another query depending
on that value.
| Field | Type | Null | Key | Default | Extra |
| account_id | int(11) | NO | PRI | NULL | auto_increment |
| account_name | varchar(50) | NO | | NULL | |
| balance | float | …
Loops -> Repetitive tasks
3 types of loops in MySQL stored program:
- Simple loop using LOOP and END LOOP clauses
- WHILE and END WHILE clauses that runs while a condition is true
- REPEAT and UNTIL clauses that runs until a condition is true
Termination of loop -> LEAVE statement.
I will demonstrate the use of loops the simple loop way.
Simple counter from 1 to 10:
Store below stored procedure in a file named my_loop.sql
DROP PROCEDURE IF EXISTS my_loop$$
CREATE PROCEDURE my_loop()
Sometimes there is need to use conditional expression inside
stored procedure to control the flow of execution.
We can use IF or CASE statements for this.
Below is a stored procedure to check the performance of a student based on its score.
Store the below stored procedure in a file called get_performance.sql
DROP PROCEDURE IF EXISTS get_performance$$
CREATE PROCEDURE get_performance
(score NUMERIC(8, 2),
OUT result VARCHAR(11))
IF (score >= 90) THEN
SET result = 'OUTSTANDING';
ELSEIF (score >= 70 …
It is sometimes required to get the result of a stored procedure
in a variable which can be used later or to output the
To do so, we can use the "OUT" parameter mode while defining stored procedures.
In the below section, we will be writing a stored procedure to get the square root of a number returned in an output variable provided by us.
Stored Procedure Definition:
Store the below stored procedure in a file named my_sqrt.sql and save it.
DROP PROCEDURE IF EXISTS my_sqrt$$
CREATE PROCEDURE my_sqrt(inp_number INT, OUT op_number FLOAT)
I’m using Redmine to manage projects and bug reports, I was needing a daily report with spent time by user on each project, nothing complicated, but I wanted a cross-reference result like Excel dynamic tables. As we know, MySQL doesn’t support dynamic cross-reference queries but I make an approach:
1. Write the main query with rows: project identifier, task subject and total hours in a date range.
SELECT p.identifier, e.issue_id, i.subject, round(SUM(e.hours),2) AS total_hours FROM redmine_nsp.time_entries e INNER JOIN redmine_nsp.issues i ON e.issue_id = i.id INNER JOIN redmine_nsp.projects p ON e.project_id = p.id WHERE e.spent_on BETWEEN '2014-07-01' AND '2014-07-07' GROUP BY p.identifier,e.issue_id;
+------------+----------+----------------------------+-------------+ | identifier | issue_id | subject | total_hours | …[Read more]
If you have been using certain DBMSs, or reading recent versions
of the SQL standard, you are probably aware of the so-called
“WITH clause” of SQL.
Some call it Subquery Factoring. Others call it Common Table Expression. A form of the WITH CLAUSE, “WITH RECURSIVE”, allows to design a recursive query: a query which repeats itself again and again, each time using the results of the previous iteration. This can be quite useful to produce reports based on hierarchical data. And thus is an alternative to Oracle’s CONNECT BY. MySQL does not natively support WITH RECURSIVE, but it is easy to emulate it with a generic, reusable stored procedure. Read the full article …
10 Older Entries »