create event of mysql with some logic not working

Some suggestions:

• user-defined variables (variables named starting with @ character) are separate and distinct from local variables

• there's no need to declare local variables that aren't referenced

• use local variables in favor of user-defined variables

• a column alias (identifier) that starts with @ character need to be escaped (or MySQL will throw a syntax error)

• assigning a column alias (identifier) that looks like a user-defined variable is just a column alias; it is not a reference to a user-defined variable

• use SELECT ... INTO to assign scalar values returned from statement into local variables and/or user-defined variables

• declaring datatype DECIMAL is equivalent to specifying DECIMAL(10,0)

• in INSERT ... VALUES statement the keyword is VALUES not VALUE

• best practice is to give local variables names that are different from column names

• best practice is to qualify all column references

• its a bit odd to insert only a single column, a calculated value, into a table without some other identifying values (it's not illegal. it may be exactly what the specification calls for. it just strikes me as a bit odd. I bring it up in light of the code as written, because appears that the author of the code is not familiar with MySQL.)

• using CONVERT_TZ is a bit odd; given that any datetime value referenced in a SQL statement will be interpreted in the current session time zone; we're kind of assuming that the date column is DATETIME datatype, but that's just a guess.

• to create a MySQL stored program that contains semicolons, the DELIMITER for the session needs to be changed to character(s) that don't appear in the stored program definition

Rather than address each individual problem in the stored program, I'm going to suggest a revision that does what it looks like the original code was intended to do:

DELIMITER $$

CREATE EVENT alert_2 ON SCHEDULE EVERY 300 SECOND DO
BEGIN
   DECLARE ld_current_time DATETIME;
   DECLARE ln_calc_value   DECIMAL(20,2);
-- DECLARE li_attempted    INT;
-- DECLARE li_completed    INT;

   SET ld_current_time = CONVERT_TZ(NOW(), @@session.time_zone, '+0:00');

   SELECT ROUND( 100.0
               * SUM(CASE WHEN c.seconds > 0 THEN 1 ELSE 0 END)
               / COUNT(c.uniqueid)
          ,2) AS calc_value
 --     , COUNT(c.uniqueid) AS attempted
 --     , SUM(CASE WHEN c.seconds > 0 THEN 1 ELSE 0 END) AS completed
     FROM callinfo c
    WHERE c.date >  ld_current_time + INTERVAL -300 SECOND
      AND c.date <= ld_current_time
     INTO ln_calc_value
 --     , li_attempted
 --     , li_completed
   ;
   IF ln_calc_value <= 10.00 THEN
     INSERT INTO report ( value1 ) VALUES ( ln_calc_value );
   END IF;
END$$

DELIMITER ;

For performance, we want to have an index with date as the leading column

... ON `callinfo` (`date`, ...)

Ideally (for the query in this stored program) the index with the leading column of date would be a covering index (including all of the columns that are referenced in the query), e.g.

... ON `callinfo` (`date`,`seconds`,`uniqueid`)

Q: Is this create any overload on callinfo table ?

Since this runs a query against callinfo table, it will need to obtain shared locks. With an appropriate index available, and assuming that 5 minutes of call info is a smallish set of rows, I wouldn't expect this query to contribute significantly towards performance problems or contention issues. If it does cause a problem, I would expect that this query in this stored program isn't the root cause of the problem, it will only exacerbate a problem that already exists.

Q: If yes,Would you like to suggest any other way to achieve same thing ?

It's difficult to suggest alternatives to achieving a "thing" when we haven't defined the "thing" we are attempting to achieve.

Q: May i create similar but multiple around 50. Will it create huge load on callinfo table.

A: As long as the query is efficient, is selecting a smallish set of rows via a suitable index, and runs quickly, I wouldn't expect that query to create huge load, no.

FOLLOWUP

For optimal performance, we are definitely going to want an index with leading column of date.

I'd remove the reference to uniqueid in the query. That is, replace COUNT(c.uniqueid) with SUM(1). The results from those are equivalent (given that uniqueid is guaranteed to be non-NULL) except in the case of no rows, COUNT() will return 0 and SUM() will return NULL.

Since we're dividing by that expression, in the case of "no rows" it's a difference between "divide by zero" and "divide by null". And a "divide by zero" operation will raise an error with some settings of sql_mode. If I divide by COUNT(), I'm going to want to convert a zero to NULL before I do the division

   ... / NULLIF(COUNT(...),0)

or the more ansi standards compliant

   ... / CASE WHEN COUNT(...) = 0 THEN NULL ELSE COUNT(...) END 

but we can avoid that rigmarole by using SUM(1) instead, then we don't have any special handling for the "divide by zero" case. But what that really buys us is that we are removing the reference to the uniqueid column.

Then a "covering index" for the query will require only two columns.

... ON `callinfo` (`date`,`seconds`)

(i.e. EXPLAIN will show "Using index" in the Extra column, and show "range" for access)

Also, I'm not getting my brain wrapped around the need for CONVERT_TZ.