Visual Studio Code Hot Keys
Search
Search for file in your space
Windows
CTRL + P
Mac
command + P
HTML SEO
Title
Description
Social Media Tags
Viewport metadata
<meta name="viewport" content="width=device-width, initial-scale=1"/>
MySQL Event
MySQL Events or scheduled events are tasks that execute according to a specified schedule. They are stored in the database and executed at one or more intervals.
For example, you can create an event that optimizes a table by backfilling data from another table the database that runs at 1:00 AM every day.
MySQL Events are also known as “temporal triggers” because they are triggered by time, not by DML events like normal triggers. MySQL events are similar to a cronjob on Linux or a task scheduler on Windows. MySQL Event Scheduler manages the schedule and execution of Events. MySQL Events can be very useful in many cases such as optimizing database tables, cleaning up logs, archiving data, or generating complex reports during off-peak time.
Run SHOW PROCESSLIST to make sure that event_scheduler User shows up as an indication that you can create an event.
SHOW PROCESSLIST;
Show event details
You can use show events query to show details of events. You can also use the INFORMATION_SCHEMA.EVENTS table to do the same.
SELECT *
FROM INFORMATION_SCHEMA.EVENTS
WHERE EVENT_SCHEMA = 'database_name';
SELECT
EVENT_SCHEMA, EVENT_NAME, CREATED, LAST_EXECUTED, DEFINER, TIME_ZONE, EVENT_DEFINITION,
EVENT_TYPE, INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS, STATUS, ON_COMPLETION, EVENT_COMMENT
FROM INFORMATION_SCHEMA.EVENTS
WHERE EVENT_SCHEMA = 'database_name';
SHOW EVENTS FROM database_name;
Turn on and off the event scheduler
-- turn on event_scheduler SET GLOBAL event_scheduler = ON; -- turn off event_scheduler SET GLOBAL event_scheduler = OFF;
CREATE EVENT requires the EVENT privilege for the schema in which the event is to be created. If the DEFINER clause is present, the privileges required depend on the user value. An event is associated with a schema. If no schema is indicated as part of event_name, the default (current) schema is assumed.
// This event executes once—one hour following its creation
CREATE EVENT myevent
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE myschema.sometable SET somecol = somecol + 1;
Recurring event with end time
With ENDS the recurring event will stop its execution at that time.
CREATE EVENT IF NOT EXISTS E_MAX_FIRE
ON
SCHEDULE EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
ENDS CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
INSERT INTO activity(event_name, event_fired_at)
VALUES('max-event',NOW());
Recurring event with stored procedure
Note that with no ENDS clause the recurring event will continue to execute at EVERY 1 MINUTE forever.
delimiter |
CREATE EVENT E_MIN_FIRE
ON SCHEDULE
EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP
COMMENT 'Saves total number of sessions then clears the table each day'
DO
BEGIN
INSERT INTO activity(event_name, event_fired_at)
VALUES('min-event',NOW());
END |
delimiter ;
DROP EVENT [IF EXISTS] event_name; // delete E_MIN_FIRE event DROP EVENT IF EXISTS E_MIN_FIRE;
MySQL Index
An index is a data structure such as B-Tree that improves the speed of data retrieval on a table at the cost of additional writes and storage to maintain it.
Indexes are a type of lookup table that is used to improve the performance of SELECT queries. Without an index, MySQL performs lookups by sequential searching the table. It has to start with the first record and then read the whole table to find the relevant rows. Conversely, if the table has an index for the columns in question, MySQL can quickly find the data without having to look at all the rows in a table.
The query optimizer may use indexes to quickly locate data without having to scan every row in a table for a given query.
Indexes can consist of one or more fields up to a total of 16 columns. For certain data types, you can even index a prefix of the column. One way to think about multiple-column indexes is like a sorted array containing values that are created by concatenating the values of the indexed columns. For this reason, indexes on multiple fields are also known as “concatenated indexes”.
CREATE INDEX cannot be used to create a PRIMARY KEY.
Syntax to create an index.
CREATE INDEX [index name] ON [table name]([column name]);
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(255) DEFAULT NULL, `email` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
CREATE INDEX emailIndex ON user(email);
EXPLAIN SELECT * FROM play_ground.user WHERE email = 'lau@gmail.com';
Multiple column index
A multiple-column index is also know as composite index.
Syntax to create a multiple-column index
CREATE INDEX [index name] ON [table name]([column1,column2,column3,...]);
The query optimizer cannot use the index to perform lookups if the columns do not form a leftmost prefix of the index. In the where clause, all columns in the multiple index must be used otherwise the index will not be used.
CREATE INDEX complexIndex ON user(name,email);
SELECT * FROM user WHERE name = 'Lau' AND email = 'lau@gmail.com';
The above query works because in the WHERE clause, name and email are both used.
SELECT * FROM user WHERE email = 'lau@gmail.com';
The above query does not use our complexIndex as it does not have both of the composite index columns(name and email).
May 7, 2020Linux prepend content to a file
Prepend content to a file
Use sed with -i option. You can also specify the line to which the content will prepend to.
sed -i '' '1s/^/package home;/" Home.java
Prepend content to multiple files
Here I need to add a package to 200 java files. To do this manual would be tedios.
for f in *.java do sed -i "" "1s/^/package home;/" $f done