Skip to content

sql.md

Latest commit

 

History

History
92 lines (79 loc) · 6.63 KB

sql.md

File metadata and controls

92 lines (79 loc) · 6.63 KB

SQL

What is having in sql?

  • HAVING filters records that work on summarized GROUP BY results.
  • HAVING applies to summarized group records, whereas WHERE applies to individual records.
  • Only the groups that meet the HAVING criteria will be returned.
  • HAVING requires that a GROUP BY clause is present.
  • WHERE and HAVING can be in the same query.
Relative links:

What are the possible issues with indexes?

Although indexes are intended to enhance a database's performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered.

  • Indexes should not be used on small tables.
  • Tables that have frequent, large batch updates or insert operations.
  • Indexes should not be used on columns that contain a high number of NULL values.
  • Columns that are frequently manipulated should not be indexed.
Relative links:

When using a composite index, under what conditions will the database use it for a query that filters only on a subset of the indexed columns?

The database uses the composite index only if the query filters on a leftmost prefix of the index columns. If the leftmost column is omitted, the index is typically not used for efficient seeks (though some databases offer skip‑scan optimization).

Relative links:

How to verify that an index was used in a query?

The standard way is to examine the query's execution plan, which reveals the actual operations the database performs. The primary tool is the EXPLAIN command, which you prepend to your query.

Does it make sense to add an index on a column with a highly skewed distribution to selectively query the minority value?

Partial indexes are a powerful tool for optimizing database performance when you consistently query a subset of your data.

Relative links:

Sql works very slowly, what are the possible improvements (the database is MySQL, for example)?

  1. Index All Columns Used in 'where', 'order by', and 'group by' Clauses
  2. Optimize Like Statements With Union Clause
  3. Avoid Like Expressions With Leading Wildcards
  4. Take Advantage of MySQL Full-Text Searches
  5. Optimize Your Database Schema
  6. MySQL Query Caching
Relative links:

NoSQL vs SQL Databases

Relative links:

What are the main types of NoSQL databases, and when is each type most useful?

The four primary NoSQL database types are optimized for specific use cases:

  • Key-value: Ideal for fast lookups, session management, real-time bidding, and shopping carts. Examples: Redis, DynamoDB.
  • Document: Best for product catalogs, user profiles, and content management. Examples: MongoDB, Couchbase.
  • Wide-column / Column-family: Suited for time-series data, real-time analytics (e.g., Sum, Count, Avg), IoT, and recommendation engines. Examples: Cassandra, HBase.
  • Graph: Optimal for social networks, fraud detection, logistics, and recommendation engines. Examples: Neo4j, JanusGraph.
Relative links:

OLAP vs OLTP Databases

Relative links:

By which instruments of DB is ACID supported?

  • Write-Ahead Logging – records changes before committing (Atomicity, Durability).
  • Checkpointing – periodically flushes dirty pages to disk to speed recovery (Durability).
  • Locks / MVCC – controls concurrent access to data (Isolation).
  • Constraints & Triggers – enforce data integrity rules (Consistency).
Relative links:

What is Multi-Version Concurrency Control (MVCC)?

MVCC is a database technique that creates a new version of a record instead of overwriting it when updated. This lets readers view the old version while writers update a new version simultaneously without any read/write blocking. It uses timestamps and transaction IDs to maintain consistency, ensuring no transaction ever waits to read an object. The result is increased concurrency and minimized read delays.

Relative links:

What is the difference between keyset and offset pagination?

  • Offset pagination uses OFFSET and LIMIT. Performance degrades linearly (O(n)) as pages get deeper, and results can be inconsistent if data changes between page loads.
  • Keyset pagination uses a WHERE clause on a unique, indexed column. It provides consistent O(1) performance regardless of dataset size and is immune to data changes, but it does not support jumping to random pages.
Relative links:

Home Page