The Dangers of SELECT * in SQL Queries

Exploring MySQL Stored Procedures through a different lens ✍️ I recently created this hand-drawn, architectural-style infographic to break down one of the most powerful features in SQL—Stored Procedures (SP). Instead of just reading documentation, I mapped everything visually: • Syntax & structure using "DELIMITER //" • Parameters: IN, OUT, INOUT • Variable declarations • Control flow (IF-ELSE, CASE, loops) • Error handling with handlers This approach helped me understand not just how stored procedures work, but why they matter—modularity, performance, and cleaner database logic. Sometimes, slowing down and sketching concepts like a developer’s notebook makes complex topics much easier to grasp. If you're learning SQL or backend development, try turning concepts into visual notes—it’s a game changer. #MySQL #SQL #WebDevelopment #BackendDevelopment #Database #Programming #LearningJourney #DeveloperNotes #100DaysOfCode

SlothDB is a super fast embedded SQL database. 😁 You point SQL at a file. Parquet, CSV, JSON, Avro, Arrow, SQLite, Excel. No server, no import step, no extension to install before you can read a Parquet file. Same embedded model as SQLite and DuckDB, different defaults. A few things we cared about while building it: It is one binary. Drop slothdb.exe somewhere, run it. It runs in the browser. The WASM build is 1.3 MB and fits Workers' 1 MB script cap in the edge variant. It is fast enough to be worth the swap for analytical work. On a 5-query warm batch over 10M rows, SlothDB finishes in 138 ms. DuckDB 1.1.5 finishes the same batch on the same hardware in 540 ms. It is also early. v0.1.8 shipped today. The Python wheel had a packaging bug last week that I only caught because a stranger filed an issue. So if you hit a rough edge, file one. We read every one. Try it in 10 seconds at https://slothdb.org or pip install slothdb. Our Github repo- https://lnkd.in/gxCSmACA #SQL #DataEngineering #DuckDB #OpenSource #OLAP

I reduced an endpoint response time by 75% without changing a single line of infrastructure. I just changed how the query reached the database. The scenario: a report with 5 JOINs, SUM and COUNT aggregations, city and date filters, running against 1 million records. Spring Data JPQL with DTO projection: 1,240ms at p95. Starting point. Native SQL with nativeQuery = true: 780ms. 37% faster just by writing raw SQL and removing the ORM translation overhead. Materialized View mapped as a read-only entity: 310ms. The endpoint became a simple SELECT with filters. The database had already done the heavy lifting before the request arrived. The lesson was straightforward: the ORM is not the villain, but it has a cost that shows up when the query gets heavy. Knowing when to move away from JPQL and when to go beyond Native SQL makes a real difference in production. Which of these approaches are you using today for reports with many JOINs? #Java #SpringBoot #Backend #SoftwareEngineering #DatabasePerformance

CTEs have quietly become my favorite SQL feature. Not because they're fancy, but because I can come back to a query three months later and actually understand what past-me was doing. 🧐 Instead of one monstrous nested subquery, you get named blocks that read top to bottom. customers_last_year AS (...), their_orders AS (...), final select. That's it. 👇 Recursive CTEs took me longer to warm up to. I avoided them for months because the syntax looked intimidating. Then I had to flatten an employee-manager hierarchy and spent an afternoon fighting it with self-joins before giving up and trying a recursive CTE. Took about 8 lines. Should have learned it sooner. 🔁🤓 Fair warning: they're not always faster. A temp table or indexed subquery sometimes wins on performance. But for making queries you won't hate opening later, CTEs are the move. 💡 #SQL #CTEs #DataAnalytics #Programming

🚀 High Performance SQL Query on LeetCode! Just solved “Customer Placing the Largest Number of Orders” and this one felt extra satisfying 😄 📊 Result: ✔️ Accepted ✅ (19/19 test cases passed) ✔️ Runtime: 427 ms ⚡ ✔️ Beats ~80% of submissions 🚀 💡 Problem Insight: The task was to find the customer who placed the maximum number of orders — a great use case of GROUP BY + aggregation + subquery. 🧠 My Approach: Counted orders per customer Compared it with the maximum order count using a subquery Returned the customer with the highest count SELECT customer_number FROM Orders GROUP BY customer_number HAVING COUNT(order_number) = ( SELECT MAX(order_count) FROM ( SELECT COUNT(order_number) AS order_count FROM Orders GROUP BY customer_number ) AS temp ); 🔥 Key Takeaway: Combining aggregation + nested queries helps solve ranking-type problems efficiently. Consistency is paying off — step by step improving both logic and performance 💪 #SQL #LeetCode #CodingJourney #Database #Learning #Tech #PlacementPreparation #Consistency

CTEs vs. Subqueries (Clean Your Code!) ✍️ Title: Stop writing "Inception" style SQL queries. We’ve all seen them—queries nested inside queries, inside more queries. They are hard to read, impossible to debug, and a nightmare for your teammates. The Solution? Common Table Expressions (CTEs). The Old Way (Subqueries): It’s like reading a book from the middle. You have to jump to the innermost brackets to understand what's happening. The Better Way (CTEs): Using the WITH clause allows you to write your logic from top to bottom. It breaks your complex logic into small, named "steps." Why I prefer CTEs: Readability: It reads like a story. Step A, then Step B, then the Final Result. Relabability: You can reference the same CTE multiple times in your main query. Debugging: You can comment out parts of the query easily to test each step. Are you Team CTE or Team Subquery? Let’s settle the debate below! 👇 #SQL #CleanCode #DataEngineering #ProgrammingTips #DataAnalytics #SoftwareDevelopment

💥 Stored Procedure vs Query — which one should you use? 🤔 I used to write direct queries everywhere… until I realized where Stored Procedures actually help 😅 🔍 The Confusion: When should you use: 👉 Direct SQL Query 👉 Stored Procedure ✅ Use Stored Procedure when: ✔️ You need better performance (execution plan reuse) ✔️ You want security (no direct table access) ✔️ Logic is complex & reusable ✔️ Multiple operations in one call ✅ Use Direct Query when: ✔️ Simple SELECT/INSERT ✔️ One-time or small logic ✔️ Quick debugging ⚡ Realization: It’s not about “which is better” ❌ It’s about “where to use what” ✅ ⚡ Pro Tip: In large applications → Stored Procedures can make your system more structured and secure 🔐 💬 What do you prefer more—Stored Procedures or direct queries? Let’s discuss 👇 🔖 Save this post for future decisions! #sql #database #developer #coding #backend #tricks

Stop writing SQL "Inception." Use CTEs instead. 🛑📖 We’ve all been there: opening a query only to find a subquery, inside a subquery, inside another subquery. It’s hard to read, impossible to debug, and a nightmare for your future self. If you want to move from a "Junior" to a "Senior" SQL mindset, you need to master CTEs (Common Table Expressions). Why CTEs are a game-changer: Readability: They allow you to read code from top to bottom, like a story, rather than from the inside out. Reusability: You can reference the same CTE multiple times in one query. No more copy-pasting the same subquery logic! Debugging: You can test each "layer" of your data transformation individually before joining them all together. The Golden Rule: If your logic has more than two levels of nesting, it’s time for a WITH clause. #SQL #DataEngineering #Database #CodingTips #CleanCode #DataAnalytics #CareerGrowth

Most developers know indexes make queries faster. But if you don't understand the tradeoffs, you'll either index too much and slow your database down or too little and kill your read performance. Here's what's actually happening 👇 When you query a database with no index, it scans every single row in the table. That's fine at 1,000 rows. But at 10 million rows? It's a disaster!!!. An index lets the database jump straight to the data it needs:- like a book index that takes you to the exact page instead of making you read the whole textbook. Under the hood, most databases use a B-tree structure. Instead of checking millions of rows, the database makes roughly 30 decisions and arrives at the answer. That's the difference between a slow app and a fast one. Indexes cost you on writes. Every INSERT, UPDATE, or DELETE forces the database to update the index too, not just the table. The more indexes you have, the more overhead every write carries. So the strategy is simple: - Index columns you filter and search on frequently - Prioritise columns with lots of unique values; IDs, emails, timestamps - Avoid indexing boolean or low-variety columns; they rarely help - Go easy on tables that get written to constantly Indexing is a deliberate decision, not a default setting. Get it right, and your queries fly. Get it wrong, and that performance debt compounds fast at scale. _________________________________________ What's the worst index-related bug you've ever seen? Drop it in the comments 👇 #Database #DatabaseIndexing #SQL #SoftwareEngineering #BackendDevelopment #TechTips #DataEngineering #Programming #SystemDesign #Engineering

SQL Progress: Logic & CASE Statements! Today I solved another Medium challenge on LeetCode. This problem was a great lesson in how to calculate percentages and rates directly in SQL. What I learned today: 1. AVG with CASE WHEN: I learned that I can use AVG(CASE WHEN condition THEN 1.0 ELSE 0.0 END) to calculate a rate. It’s a very clear way. 2. Handling NULLs in Rates: By using a LEFT JOIN between the Signups and Confirmations tables, I ensured that users with no actions are still included, and the AVG function automatically treats them as 0 if they don't meet the "confirmed" criteria. 3. Precision with ROUND: Used ROUND(..., 2) to make sure the final confirmation rate is clean and meets the required format(0.00). I would love to learn from your experience: is ther another methods cleaner? قليل مستمر خير من كثير منقطع #SQL #DataEngineering #PostgreSQL #LeetCode #100DaysOfCode #DataAnalytics #ProblemSolving