AI Chat With DBeaver Community Edition

 

AI Chat With DBeaver Community Edition

July 29, 2026

 DBeaver recently introduced interactive chat capabilities into the free, open-source Community Edition. What does that mean? 

It means some of your database tasks can be accomplished significantly faster! 

First, you can ask for information without coding SQL. I love Structured Query Language, but it is much faster to write this prompt:

What are the top ten most popular rentals and how much revenue did they generate?

And faster than I look at the schema, the AI answers:


-- This query finds the top 10 most rented films and their total revenue

with film_rentals as (

select

i.film_id,

count(r.rental_id) as rental_count,

sum(p.amount) as total_revenue

from rental r

join inventory i on r.inventory_id = i.inventory_id

join payment p on r.rental_id = p.rental_id

group by i.film_id

)

select

f.title,

fr.rental_count,

fr.total_revenue

from film_rentals fr

join film f on fr.film_id = f.film_id

order by fr.rental_count desc, fr.total_revenue desc

limit 10;


BTW - I am using the MySQL Sakila database, DBeaver Community Edition, 26.1.3, and Github Copilot gpt-4.1 for this blog.


Second, does this query perform as well as it could:


Am I missing any indexes that could speed this query up?


Great question! Indexes can significantly improve query performance, especially for joins and aggregations. Let’s analyze the query and the involved columns:

Tables and Join Columns Used

  • rental: inventory_id, rental_id
  • inventory: inventory_id, film_id
  • payment: rental_id
  • film: film_id

Query Operations

  • Joins:
    • rental.inventory_id = inventory.inventory_id
    • rental.rental_id = payment.rental_id
    • inventory.film_id = film.film_id
  • Grouping:
    • group by inventory.film_id

Recommended Indexes

1. rental Table

  • Needed for join: inventory_id, rental_id
  • Recommended index: Composite index on (inventory_id, rental_id)
  • If you often filter or join on inventory_id, an index on just inventory_id is also helpful.

2. inventory Table

  • Needed for join: inventory_id, film_id
  • Recommended index: Composite index on (film_id, inventory_id)
  • If you often join on inventory_id, an index on just inventory_id is also helpful.

3. payment Table

  • Needed for join: rental_id
  • Recommended index: Index on rental_id

4. film Table

  • Needed for join: film_id
  • Recommended index: Index on film_id

Example Index Creation Statements

-- On rental table

create index idx_rental_inventory_id on rental (inventory_id);

create index idx_rental_rental_id on rental (rental_id);

-- On inventory table

create index idx_inventory_film_id on inventory (film_id);

create index idx_inventory_inventory_id on inventory (inventory_id);

-- On payment table

create index idx_payment_rental_id on payment (rental_id);

-- On film table

create index idx_film_film_id on film (film_id);


Summary

  • The most critical indexes for this query are on the join columns: rental.inventory_id, rental.rental_id, inventory.inventory_id, inventory.film_id, and payment.rental_id.
  • If your tables are large and you don’t already have these indexes, adding them will speed up the query, especially the joins and the grouping.

Tip:
You can check your current indexes with:

show indexes from rental;

show indexes from inventory;

show indexes from payment;

show indexes from film;


Want a Demo??


🪄Join a webinar on 10 ways to use AI with your databases. They range from generating SQL in plain language to connecting AI tools to your database via MCP. If you’re using DBeaver Community or DBeaver PRO, you can learn something new. 📅 Aug 4 at 11 AM CT


Comments

Post a Comment

Popular posts from this blog

Migrating From MySQL To PostgreSQL in Five Simple Steps

Image
There are many reasons you may want to migrate from MySQL to PostgreSQL, which we will skip over for brevity.  I see many questions about moving tables and data, and sadly, the answers range from sloppy to incredibly complicated.  Each database migration is unique, as issues range from heavily relying on a vendor's feature to extremely complex schemas that require attention to minute detail. Step 1 This may seem obvious, but the first step is access to the original 'source' database. You will need FULL admin access to the database.  If you do not have full access for security, administrative, or other reasons, you will most likely not receive all the desired data.  We will use DBeaver Enterprise to connect to the source database. We need to choose a MySQL database driver Configure the connection We will be porting the World database. In this example, we will port the MySQL World database. This dataset has been used for decades in MySQL documentation, training, and ex...
Read more

Village SQL - A MySQL Fork With Easy Extensions

 I was happy to see a new MySQL fork appear. Village SQL  bills itself as 'A drop-in replacement for MySQL with extensions for the agentic AI era'. Mainstream Oracle MySQL does have Plug-ins, but I have found them less easy to develop and use than other implementations, such as PostgreSQL extensions. So how does it work? Head over to  https://github.com/villagesql/villagesql-server and follow the directions to clone and build the code. You will see detailed instructions for Linux and Mac builds. I followed the directions, let the make run overnight (slow hampsters in my testbed Ubuntu laptop).   The instructions were complete and easy to follow. First Run $ ~/build/villagesql/bin/mysql -u root -h 127.0.0.1 Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.4.6-villagesql0.0.1-dev Source distribution Copyright (c) 2000, 2025, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corp...
Read more

Do You Need An AI Assistant?

Image
 Artificial Intelligence has been over hyped the last several years. But one of the bright, shining spots for AI is acting as an assistant. a super-powerful IDE, when working with a database. Example You want to find your top three customers in the Sakila database. This database has been used for decades in the MySQL world. As your question in English (or another language) The '@ai' directs the rest of the prompt to the DBeaver Artificial Intelligence Assistant See the generated SQL Here is the generated Structured Query Language And get the results. Now we have the top three customers without having to write any SQL directly Fixing Mistakes Occasionally, you may 'fat finger' a query.  I will take the query from above and change first_name to just first .   SELECT c . customer_id , c . first , c . last_name , SUM ( p . amount ) AS total_revenue FROM customer AS c JOIN payment AS p ON c . customer_id = p . customer_id GROUP BY ...
Read more