Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 67 10 Older Entries

Displaying posts with tag: stored procedure (reset)

MySQL inline query versus stored procedure comparison
+0 Vote Up -0Vote Down

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 …

  [Read more...]
Employee +3 Vote Up -1Vote Down

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.
In its simplest form, this feature is a kind of "boosted derived table".

Assume that a table T1 has three columns:

YEAR INT, # 2000, 2001, 2002 ... …

  [Read more...]
Take the long view on the MySQL PERFORMANCE_SCHEMA with ps_history and sys_history
+1 Vote Up -0Vote Down

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 ago?

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 …

  [Read more...]
How to use value from row derived column from a table in another query?
+1 Vote Up -1Vote Down

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.

"accounts" table

| Field        | Type        | Null | Key | Default | Extra          |
| account_id   | …

  [Read more...]
Use of loops in MySQL stored procedures
+2 Vote Up -1Vote Down

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.

  [Read more...]
Using conditional expressions inside MySQL Stored Procedure
+1 Vote Up -1Vote Down

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$$


  [Read more...]
How to use and get output arguments in stored procedure using OUT parameter mode?
+1 Vote Up -0Vote Down

It is sometimes required to get the result of a stored procedure in a variable which can be used later or to output the result.

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 …

  [Read more...]
An approach to MySQL dynamic cross-reference query
+0 Vote Up -0Vote Down

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 =
        INNER JOIN …
  [Read more...]
Employee_Team +1 Vote Up -0Vote Down

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 …

  [Read more...]
Using Perl Stored Procedures for MariaDB, slides uploaded.
+1 Vote Up -0Vote Down

Just recently did the presentation and Q&A for the Using Perl Stored Procedures presentation at Percona Live 2013.

The presentation has been uploaded:

Using Perl Stored Procedures for MariaDB from Antony T Curtis

(repeat posting because didn't notice it the first time around)

Showing entries 1 to 10 of 67 10 Older Entries

Planet MySQL © 1995, 2015, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.