Cara menggunakan mongodb aggregation optimization

A database event is a task that runs according to a schedule. Also known as "scheduled events", an event is similar to a cron job in UNIX or a task scheduler task in Windows, except that scheduled events are configured using a database's syntax and/or command-line-interface (CLI). Database events have many uses, such as optimizing database tables, cleaning up logs, archiving data, or generating complex reports during off-peak time.

In previous blogs on this topic, we learned how to configure events using MySQL as our database. Today, we're going to schedule a database task using the GUI Database Management Tool.

In Navicat database management offerings, including Navicat Premium, the Event Designer is the tool for working with events. It's accessible by clicking on the Event button on the main toolbar:

Clicking the Event button opens the Event object list in the Object pane. The Object pane toolbar contains three buttons: Design Event, New Event, and Delete Event. If you have no events defined, only the New Event button will be enabled.

Click the New Event button to open a new untitled Definition tab:

You can enter any valid SQL procedure statement in the Definition tab. This can be a simple statement such as "INSERT INTO tbl_users (first_name,last_name) VALUES('Bob','Jones');", or it can be a compound statement written within BEGIN and END statement delimiters. Compound statements can contain declarations, loops, and other control structure statements.

Note that we don't have to write the CREATE EVENT code, as this is handled by Navicat (as we'll see in the following sections).

Here is an event definition that inserts a new row in the sakila.actor table:

Navicat alleviates much of the burden of scheduling events by providing a form for entering scheduling details. The scheduling form is located on the Schedule tab. It supports the adding of Intervals that may comprise either simple or complex time units. Here's a simple example that sets the event to execute 5 minutes after creation:

Here's a more complex event schedule that starts in 5 minutes, and runs every five-and-a-half hours for 3 days:

To save an Event, click the Save button on the Even tab. If you like, you can preview the generated SQL on the SQL Preview tab before saving it:

Note that the statement is read-only and cannot be edited in the preview.

You can include comments with your Event on the Comment tab.

It adds them to the CREATE EVENT statement via the COMMENT clause:

To delete an Event, select it in the Object tab and click the Delete Event button. A warning dialog will ask you to confirm that you wish to delete the Event:

To modify an Event, select it in the Object tab and click the Design Event button. That will open it in the Event tab.

Available in Non-Essentials editions of Navicat for MySQL, PostgreSQL, SQLite, MariaDB, and Navicat Premium, the Query Builder allows anyone to create and edit queries with only a cursory knowledge of SQL. In today's blog, we'll use it to write a query to fetch a list of actors that appeared in movies released during a given year.

The query that we'll be building will run against the . A former member of the MySQL AB documentation team named Mike Hillyer created the Sakila database specifically for the purpose of providing a standard schema for use in books, tutorials, and articles just like the one you're reading.

The database contains a number of tables themed around the film industry that cover everything from actors and film studios to video rental stores. Please refer to the tutorial for instructions on downloading and installing the Sakila database.

You can think of the Query Builder as a tool for building queries visually. It's accessible from the Query Designer screen. Let's bring it up by opening a new query:

• Click the Query icon on the main toolbar, followed by the New Query button from the Object toolbar:

• In the Query Designer, click the Query Builder button to open the visual SQL Builder.

  The database objects are displayed in left pane, whereas the right pane is divided into two portions: the upper Diagram Design pane, and the lower Syntax pane:

  

It's a good idea to select the tables first, so that the Query Builder knows which fields to present for the field list:

• Drag a table/view from the left pane to the Diagram Design pane or double-click it to add it to query. We'll be needing the actor, film_actor, and film tables.
• You can assign table aliases by clicking on "" beside each table. To add the table alias, simply double-click the table name and enter the alias in the Diagram Design pane.

Note how the Query Builder already knows the table relationships. That's because foreign key constraints have already been declared on Table objects:


• To include a field in the query, check the left of the field name in the Diagram Design pane. To include all the fields, click at the left of the object caption. Select the actor first and last names as well as the film title.

Adding WHERE Criteria

Clicking on "" beside the WHERE keyword adds a default WHERE condition of "<--> = <-->".

• Click on the left-hand "<--> = <-->" to select a field. That opens a popup dialog that contains a List of fields as well as an Edit tab.
• Click the List tab and choose the f.release_year field.
• Click OK to close the dialog.
• Next, click on the right-hand "<--> = <-->" to set the release year. This time enter a value of "2006" in the Edit tab. Click OK to close the dialog.
• Click OK to close the Query Builder. You should now see the generated SELECT statement in the Query Editor:

SELECT
a.first_name,
a.last_name,
f.title
FROM
actor AS a
INNER JOIN film_actor AS fa ON fa.actor_id = a.actor_id
INNER JOIN film AS f ON fa.film_id = f.film_id
WHERE
f.release_year = 2006
	

• Click the Run button to execute the query. The results will be sorted by Film title:

Whether you're a novice or experience DBA, Navicat's Query Builder makes writing SELECT queries easier than ever before. In an upcoming blog, we'll get into some of its more advanced features.