SQL Indexing Best Practices

Before we show best practices, let’s look at how database indexes work. A database index is a type of data structure like an array or hash. Without any indexes our database has to scan every row in the table. What if this table had 20 million rows? Running a single query consume a lot of resources and if this table used by an application it could have thousand similar queries. The solution to this problem is to use indexes. We want to index the data is looked up most frequently.

SELECT * 
FROM users
WHERE city = 'Berlin'
ORDER BY balance DESC;
CREATE INDEX city_idx ON users(city);

SQL index is basically a shorted list with the values from one or more rows of a table and a pointer value that tells the database where to find.

Indexes are automatically maintained by database engine. This means that whenever a DML operation is performed on the table, the same operation is done on each of the indexes.

For queries that are going to be run frequently we can make this even more efficient by multi-cloumn index.

CREATE INDEX idx_city_balance ON users (city, balance DESC);

Don’t create more indexes than you need.

-- Less Frequent Query:
SELECT * 
FROM users
WHERE created_at >= '2023-01-01';

-- Avoid Unnecessary Indexes: If the created_at query is less frequent and not resource-intensive, you might choose not to create an index for it.
CREATE INDEX idx_created_at ON users (created_at);

Try to avoid large indexes.

-- Indexing the first 255 characters of a large column
CREATE INDEX idx_large_text ON users (large_text_column(255));

When in doubt, use EXPLAIN

EXPLAIN SELECT * 
FROM users 
WHERE city = 'Berlin' 
ORDER BY balance DESC;
                                    QUERY PLAN                                    
---------------------------------------------------------------------------------
 Sort  (cost=12.50..12.51 rows=100 width=200)
   Sort Key: balance DESC
   ->  Seq Scan on users  (cost=0.00..10.00 rows=100 width=200)
         Filter: (city = 'Berlin')

Index hint is a directive, you can provide to the database query optimizer to force it to use spesific index for query. In Oracle we can define this way:

SELECT /*+ INDEX(users idx_city_balance) */
       *
FROM users
WHERE city = 'Berlin'
ORDER BY balance DESC;

 

Yorumlar

Yorum Gönder

Bu blogdaki popüler yayınlar

JUnit 5 Parameterized Tests

  JUnit 5 provides parameterized tests, which let you run a test multiple times with different arguments. This is especially useful when you want to test a function with various inputs without writing repetitive test cases. Basic Structure of a Parameterized Test To create a parameterized test, use the @ParameterizedTest annotation. You also need a source of parameters, which JUnit provides through different annotations like @ValueSource , @CsvSource , @CsvFileSource. ValueSource : Supplies a single array of primitive types or strings. @Service public class AccountValidationService { // Example rules: // - Account number must be 10 digits // - Must start with "ACC" public boolean validateAccountNumber (String accountNumber) { if (accountNumber == null ) { return false ; } if (!accountNumber.startsWith( "ACC" )) { return false ; } if (accountNumber.length() != 10 ) { ...
Devamı

Kafka Message Key Hashing

Resim
 In Kafka each event message contains an optional key and a value. key == null : messages are distributed evenly across partitions in a topic (a round-robin strategy). key != null : All messages that share same key will always be sent and stored in the same partition. ** A key can be anything to identify a message (a string, numeric value, binary value etc.). Key Hashing is the process of determining the mapping of a key to a partition. A Kafka partitioner is a code logic that takes a record and determines to which partition to send it into. ** In the default Kafka partitioner, the keys are hashed using murmur2 algorithm ( murmur hash ). targetPartition = Math.abs(Utils.murmur2(keyBytes) % (numPartitions - 1)) Example Flow: Message 1 : account_id = 12345 hash(12345) % 2 might result in 0 -> Partition 0 Message 2 : account_id = 67890 hash(67890) % 2 might result in 1 -> Partition 1 Message 3 : account_id = 12345 (same as Message 1) hash(12345) % 2 still results in...
Devamı

Transactional Outbox Pattern

Resim
Dual Write Problem The dual write problem means it’s hard to keep different data sources working together when you write or save things. We need to make sure that all the writes are done correctly and the same in all places. If one write operation fails or only partially succeeds, it can lead to data integrity issues. Dual Write Problem Diagram The Transactional Outbox pattern helps solve the Dual Write Problem. It adds a middle step to keep writing actions separate. How the Pattern Works Let’s assume that we also need to send an event to the notification service when a customer is created. It doesn’t immediately tell another service. Instead, it puts the important details in a special table called an outbox table. Customer savedCustomer = customerRepository.save( new Customer (customerDto.username(), customerDto.email())); //customerProducerService.sendCustomerCreatedEvent(savedCustomer); OutboxEvent outboxEvent = new OutboxEvent (); outboxEvent.setEventPayloa...
Devamı