Improving the Performance of SQL Queries
Use this topic to find advice on improving the performance of slow queries.
Add the keyword
EXPLAIN in front of your query. For example:
EXPLAIN SELECT *
WHERE age > ?;
General advices for optimizing SQL queries apply also to Hazelcast SQL queries. However, because Hazelcast is not an RDBMS, there are some differences and special cases.
Scanning an entire table (full table scans) is the slowest way to access data, if you are filtering just a small subset of rows. If your queries are performing full table scans on a map, you can create indexes that contain at least one of the columns that the query is filtering in its
WHERE clause. See CREATE INDEX.
Each index has to be updated after each change to the IMap, so there is a tradeoff between read performance and write performance when adding a new index. Both should be evaluated for realistic workload with and without additional index.
SQL queries cannot use more than one SORTED or HASH index for given scan. If you have a query that filters rows using multiple attributes, consider creating a composite index with all these fields instead of or in addition to single-column indexes.
For example, consider an
employees mapping with the following indexes.
CREATE INDEX IF NOT EXISTS emp_age
ON employees (age)
CREATE INDEX IF NOT EXISTS emp_name
ON employees (name)
A query like the following is able to utilize only one of the indexes (exactly which one to be used depends on the filter selectivity estimation and cost calculation).
SELECT * from employees where name = 'Smith' and age > 30
You could create the following composite index which covers the query filters.
CREATE INDEX IF NOT EXISTS emp_name_age
ON employees (name, age)