DEV Community: Free Peak

Lessons from the Trenches: Indexing a 200M Row Table in MySQL [MySQL part 2]

Free Peak — Sun, 20 Apr 2025 06:34:15 +0000

TL;DR

Slow queries? Check your indexes first.

Use EXPLAIN and the slow query log to guide indexing decisions.

Composite indexes only help if you use leading columns.

Covering indexes = 🔥 for performance.

Too many indexes hurt write performance.

Indexes can bloat your storage—monitor carefully.

Tools like pt-online-schema-change or gh-ost are lifesavers for zero-downtime changes.

Working with large datasets is when theory meets the cold hard reality of production. I recently worked on a MySQL table with over 200 million rows, and learned a ton about indexes—the hard way.

Here’s what that experience taught me about performance, tradeoffs, and keeping MySQL happy at scale.

🧱 The Setup

The table was part of a data processing pipeline, getting non-stop inserts and being queried for analytics. It started fine. Then the row count hit 50M. Then 100M. Then 200M.

Query performance? Yeah… it fell off a cliff.

🔍 The First Signs of Trouble

Queries were taking 30 seconds or more. Some just never returned. I started checking indexes and realized—some of the most commonly filtered and sorted columns had no indexes at all.

I added a single composite index and one query dropped from 30s to 300ms. That was my wake-up call.

🛠 Key Lessons on Indexing Large Tables

1. Start with the slow queries

Use:

EXPLAIN SELECT ...
SHOW INDEX FROM your_table

…and MySQL’s slow_query_log. These tools show you what the optimizer is doing (or not doing).

2. Composite indexes are powerful—but specific

Order matters! An index on (user_id, created_at) helps:

WHERE user_id = ? AND created_at > ?

But not:

WHERE created_at > ?

3. Balance reads vs. writes

Indexes speed up reads, but slow down inserts and updates. We dropped low-value indexes to reduce write latency, especially for high-throughput tables.

4. Covering indexes are gold

If all columns used in the query are part of the index, MySQL can skip reading the base table. Huge speedups.

CREATE INDEX idx_status_created_at ON orders (status, created_at);

SELECT status, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 50;

Blazing fast.

⚠️ Gotchas

Creating indexes on huge tables is slow and can block operations. Use ALGORITHM=INPLACE if supported.
MySQL’s query planner sometimes picks bad indexes. You might need to use FORCE INDEX (with care).
Index size bloat: In our case, indexes used more disk than the data itself—300GB+.
Always test schema/index changes on real data volumes.

✅ My Go-To Tools and Practices

EXPLAIN ANALYZE for query insight
pt-online-schema-change for online index operations
ANALYZE TABLE to keep optimizer stats fresh
Avoid indexing every column “just in case”
Always test performance after changes

💡 Final Thoughts

Taming a massive table isn’t about throwing indexes at it. It’s about understanding your access patterns, crafting intentional indexes, and constantly observing behavior as your data grows.

Indexing isn’t just a technical task—it’s part of designing for scale. And when you get it right, the payoff is immediate.

If you’ve gone through similar experiences or have some indexing horror stories, I’d love to hear them! Drop a comment or reach out.

The Power of MySQL Indexes: A Comprehensive Guide [MySQL part 1]

Free Peak — Sat, 12 Apr 2025 02:46:55 +0000

Indexes are essential tools in MySQL that significantly enhance query performance. By allowing the database to locate data without scanning every row, indexes can dramatically speed up data retrieval. However, they also introduce certain trade-offs. This article delves into what MySQL indexes are, their internal workings, advantages and disadvantages, and best practices for their use.

What Is an Index in MySQL?

An index in MySQL is a data structure that improves the speed of data retrieval operations on a database table. It functions similarly to an index in a book, enabling quick location of specific data without scanning the entire table. Indexes are particularly beneficial for large datasets where performance is critical.

All Types of Indexes in MySQL

Index Type	Storage Engine	Use Case / Purpose	Limitations
Primary Key	All (InnoDB, MyISAM)	Unique identifier for each row.	Only one allowed per table. Cannot contain NULLs.
Unique Index	All	Ensures all values in the indexed column(s) are unique.	Allows one NULL (per MySQL version rules).
B-Tree Index	InnoDB, MyISAM	Default index for most queries involving equality, range, sorting.	Slower on large insert/update/delete if overused.
Hash Index	MEMORY	Fast lookup for exact matches (`=`) in MEMORY engine.	Not usable for range queries or sorting. Only MEMORY supports it.
Full-Text Index	InnoDB, MyISAM	Efficient natural language text searches.	Only works on CHAR, VARCHAR, and TEXT. Requires `MATCH ... AGAINST`.
Spatial Index	MyISAM, InnoDB (8.0+)	Geospatial queries on geometry types.	Only works on geometry columns. InnoDB support requires `SRID`.
Composite Index	All	Multi-column filtering and sorting.	Column order matters (leftmost prefix rule).
Functional Index	InnoDB (8.0.13+)	Index on expressions or functions (e.g., `LOWER(column)`).	Requires MySQL 8.0.13+.
Descending Index	InnoDB (8.0+)	Optimize queries with `ORDER BY col DESC`.	Requires MySQL 8.0+. Older versions only store ascending.
Partial Index	InnoDB (on prefix)	Index only part of large text fields (e.g., `content(100)`).	Only supported on string types. Not full content searchable.
Covering Index	All	Index that includes all columns in SELECT, enabling index-only scan.	Must include all selected columns. May use more disk space.
Invisible Index	InnoDB (8.0+)	Exists but hidden from the optimizer. Useful for testing/removal.	Ignored in query plans unless explicitly hinted.
Foreign Key Index	InnoDB	Enforces referential integrity.	Requires index on the referenced column.

How MySQL Indexes Work Under the Hood (The Most Used)

B-Tree Indexes

The most common type of index in MySQL is the B-Tree index. This structure maintains a balanced tree where each node contains keys and pointers to child nodes, allowing efficient data retrieval. When a query is executed, MySQL traverses the B-Tree to quickly locate the desired data, reducing the number of disk reads required.

Composite Indexes

A composite index (also known as a multi-column index) is an index that includes two or more columns in a specific order. It’s used to speed up queries that filter or sort by multiple columns together.

Think of it like the index at the back of a textbook that sorts by topic and subtopic. You can quickly find what you need if you follow the order the index was built in.

Composite indexes are typically implemented as B-Tree indexes, where each entry in the tree stores a tuple of column values.

CREATE INDEX idx_name ON users (last_name, first_name);

Functional Indexes

Introduced in MySQL 8.0.13, functional indexes allow indexing based on expressions or functions applied to columns. Under the hood, MySQL creates a hidden generated column to store the result of the expression and then indexes this column.

CREATE INDEX idx_lower_email ON users ((LOWER(email)));

Pros and Cons of Using Indexes

Pros

Faster Query Performance
Efficient Sorting and Filtering
Support for Uniqueness Constraints

Cons

Increased Storage Requirements
Slower Write Operations
Complexity in Management

Best Practices for Using Indexes

Index columns used in WHERE clauses
Use composite indexes wisely and match query order
Avoid over-indexing
Leverage EXPLAIN for query analysis
Consider indexing expressions
Choose optimal data types
Monitor index usage with performance schema
Use covering indexes
Leverage partial indexes for long strings
Understand and apply cardinality

Advanced Index Optimization Techniques

Index Hint Optimization

SELECT * FROM customers USE INDEX (idx_lastname) WHERE lastname = 'Smith';

Invisible Indexes

ALTER TABLE users ALTER INDEX idx_email INVISIBLE;

Index Merging

EXPLAIN SELECT * FROM table WHERE col1 = 'A' OR col2 = 'B';

In summary, indexes are powerful tools in MySQL that, when used appropriately, can greatly enhance query performance. Understanding their internal workings and adhering to best practices ensures that you reap the benefits of faster data retrieval while mitigating potential downsides.

Chuẩn Bị Phỏng Vấn Dành Cho Các Bạn Sinh Viên Sắp Tốt Nghiệp Hoặc Fresher Đang Đi Xin Việc!

Free Peak — Sat, 22 Mar 2025 18:52:18 +0000

Phỏng vấn xin việc không chỉ là thử thách, mà còn là cơ hội để bạn thể hiện năng lực và chinh phục nhà tuyển dụng! Nếu bạn là sinh viên sắp tốt nghiệp hoặc fresher đang đi xin việc, hãy trang bị thật tốt để ghi điểm ngay từ vòng đầu tiên! Dưới đây là những bí kíp giúp bạn bùng nổ trong mọi buổi phỏng vấn! 🚀

1️⃣ Luyện LeetCode – Đừng Để DSA Đánh Bại Bạn! 🧠💡

🔹 Nhà tuyển dụng cực kỳ coi trọng khả năng tư duy thuật toán. Hãy dành thời gian luyện tập Data Structures & Algorithms (DSA) mỗi ngày.

🔹 Bắt đầu từ dễ đến khó: Làm quen với bài Easy, sau đó chinh phục Medium, tiến tới Hard.

🔹 Đừng học vẹt, hãy hiểu sâu: Mỗi bài toán đều có tư duy phía sau. Hiểu rõ thuật toán và cách tối ưu sẽ giúp bạn đối mặt với mọi câu hỏi hóc búa.

🔹 Practice makes perfect! Hãy làm ít nhất 50-100 bài LeetCode trước khi bước vào phỏng vấn.

💻 Gợi ý nền tảng luyện tập:

✅ LeetCode – Must-have cho phỏng vấn kỹ thuật

✅ Codeforces – Luyện tập Competitive Programming

✅ HackerRank – Hệ thống bài tập phong phú

2️⃣ Kiến Thức Cốt Lõi – Hiểu Sâu, Nhớ Lâu! 📚🔥

✅ Hệ Điều Hành (OS): Hiểu về multi-threading, memory management, process vs thread, deadlock. Những câu hỏi này xuất hiện rất nhiều trong phỏng vấn.

✅ OOP & Design Patterns: Nắm chắc SOLID principles, Factory Pattern, Singleton, Observer để code dễ mở rộng, bảo trì.

✅ Database: Học SQL (Joins, Indexing, Transactions) và NoSQL (MongoDB, Firebase) để xử lý dữ liệu hiệu quả.

✅ Networking: Hiểu về HTTP, TCP/IP, REST API, WebSocket để không bị “tắt điện” khi vào phỏng vấn backend.

💡 Lưu ý: Đừng chỉ học lý thuyết, hãy thử viết code demo và giải thích lại bằng lời của bạn!

3️⃣ Dự Án Cá Nhân – “Chiếc Vé Vàng” Khi Đi Phỏng Vấn! 🎟️✨

❗ Lưu ý: Nhà tuyển dụng không chỉ đánh giá bạn qua điểm số hay chứng chỉ, mà còn dựa trên những gì bạn đã làm thực tế!

💡 Làm gì để nổi bật?

✅ Xây dựng dự án cá nhân (từ app mobile, website đến chatbot AI)

✅ Đóng góp vào open-source trên GitHub để nâng tầm kỹ năng

✅ Viết blog kỹ thuật chia sẻ kiến thức, tạo thương hiệu cá nhân

👉 Một ứng viên có GitHub với nhiều dự án luôn ghi điểm hơn so với một CV toàn chữ!

Github của mình: https://github.com/linhdmn

4️⃣ Rèn Luyện Kỹ Năng Mềm – “Vũ Khí Bí Mật” Khi Đi Phỏng Vấn! 🎤😎

🔥 Kỹ năng kỹ thuật rất quan trọng, nhưng kỹ năng mềm sẽ giúp bạn chiến thắng!

✅ Communication: Hãy tập giải thích code, thuật toán một cách rõ ràng, dễ hiểu.

✅ Problem-solving mindset: Nhà tuyển dụng thích những ứng viên có tư duy phản biện và sáng tạo.

✅ Làm việc nhóm: Dù làm remote hay onsite, teamwork vẫn là yếu tố quyết định.

👉 Tip nhỏ: Hãy luyện tập giải thích một vấn đề kỹ thuật với bạn bè hoặc viết bài chia sẻ, điều này giúp bạn diễn đạt lưu loát hơn!

5️⃣ Lời Khuyên Cuối Cùng – Đừng Chỉ Học, Hãy Hành Động! 🚀🔥

✅ Học tập có định hướng, đừng lan man!

✅ Hãy đặt mục tiêu cụ thể: “Trong 3 tháng, mình sẽ hoàn thành 100 bài LeetCode và xây dựng một dự án cá nhân.”

✅ Phỏng vấn không phải là điểm đến cuối cùng – đó là hành trình giúp bạn giỏi hơn mỗi ngày!

💡 Gợi ý thêm:

👉 Tạo danh sách công ty muốn apply và nghiên cứu JD trước khi phỏng vấn.

👉 Luyện mock interview với bạn bè để tăng sự tự tin.

👉 Hãy giữ vững tinh thần! Rớt phỏng vấn không có nghĩa là thất bại, mà là một bước đệm để bạn tiến xa hơn!

🔥 Bạn đã sẵn sàng chinh phục phỏng vấn chưa? Hãy chia sẻ bài viết này cho bạn bè cùng chuẩn bị nhé! 💪

🚀 Building an MCP Server Go Package in 2 Hours - A Journey of Innovation & Passion

Free Peak — Fri, 21 Mar 2025 10:02:45 +0000

GitHub Repo: github.com/FreePeak/db-mcp-server

🔥 The Challenge: Dream Big, Code Fast!

Yesterday, I dared to dream big and pushed my limits by taking on a seemingly impossible challenge: build a Go package for MCP Server integration in just 2 hours! ⏱️💥
With time ticking and excitement in the air, I armed myself with my secret weapon - Cursor Pro - and embarked on this wild adventure.
My database MCP Server

🎯 Setting the Stage: Goals with Passion

I started by clearly defining my objectives:
Seamless Integration: Make @CursorEditor an impeccable client for MCP Server.
Effortless Setup: Streamline Go modules and kick boilerplate to the curb.
Smart Automation: Utilize AI to handle database queries, HTTP endpoints, and more.
Rock-Solid Testing: Ensure every feature shines through comprehensive tests.

These ambitions fueled my determination. With every tick of the clock, the excitement grew - because nothing beats the thrill of building something truly innovative! 💪🚀

🤖 Cursor Pro: The AI Superpower That Made It Happen

Imagine coding with a tool that feels like pure magic! ✨ With Cursor Pro by my side, I experienced:
Instant Code Generation: HTTP endpoints and Go structs created in a flash!
Effortless Debugging: No more head-scratching over JSON marshalling issues.
Automated Testing: Auto-suggested test cases that left no stone unturned.

It was as if I had a genius co-pilot guiding every keystroke - turning what seemed like chaos into a symphony of code! 🎼👨‍💻

✅ Testing & Debugging: Perfection in Minutes

Testing can be a monster, but not this time! Thanks to Cursor Pro's intelligent insights, I:
Covered All Bases: Auto-generated test cases for every possible scenario.
Fixed Bugs Instantly: Real-time debugging helped squash issues as soon as they appeared.
Achieved Smooth Integration: Ensured MCP Server and Cursor Editor synced flawlessly.

In mere minutes, what typically takes hours became a seamless process. The result? A robust, reliable package ready for the real world! 🚀🔧

💸 Sharing & Earning: The Sweet Taste of Success
With the package polished and perfected, I decided to share my creation on BuyMeACoffee. And guess what?
Within hours, I received my first $10 donation - a tangible nod to my hard work and innovation. 🎉💰
That moment was pure magic: a reminder that taking risks and pushing boundaries truly pays off. It wasn't just money; it was validation, inspiration, and the spark to aim even higher. ☕️❤️

🚀 Key Takeaways: Inspire, Innovate, Impact

This adventure taught me that with passion and the right tools, the sky's the limit:
Dream Big: Every challenge is an opportunity to innovate.
Work Smart: Leverage AI and smart tooling to turbocharge your workflow.
Share & Inspire: Build in public, gather feedback, and inspire the community.

Let this be a reminder: When you combine ambition with cutting-edge technology, incredible things happen! 🌟🔥

🌈 What's Next? Let's Shape the Future Together!

The journey doesn't end here. I'm already brainstorming new projects - more integrations, advanced AI automation, and maybe even a full SaaS solution. The future is wide open, and I want YOU to be a part of it.
Join the conversation:
💡 What should I build next? Share your ideas in the comments or open an issue on the GitHub repo.
Let's keep pushing boundaries, inspiring each other, and making a real impact. Together, we're shaping the future of development! 🚀💫

GitHub Repo: github.com/FreePeak/db-mcp-server
#BuildInPublic #CursorPro #Golang #AIDevelopment #Inspiration #TechRevolution
If you found this post inspiring, please share it - let's spread the passion and ignite the spark of innovation across the community! Happy coding! 🎉🚀❤️

Mastering the AI Prompt: A Software Engineer’s Guide to Thinking With AI

Free Peak — Mon, 10 Mar 2025 18:14:21 +0000

As a software engineer in 2025, AI has become an indispensable tool in your toolkit. Yet many of us still struggle with the fundamental question: how do we effectively translate our thoughts and intentions into prompts that yield genuinely useful results? In this first part of our educational AI series, we'll explore the mental framework needed to bridge the gap between your ideas and AI-powered solutions.

The Translation Challenge: From Brain to Prompt

The greatest barrier to effective AI use isn't technical knowledge—it's the ability to articulate your thinking in a way AI systems can process optimally. This translation process requires a distinct mental model.

Step 1: Clarify Your Intention

Before typing a single word, ask yourself:

What specific outcome am I seeking?
What format do I need the response in?
How detailed should the output be?

For example, instead of a vague request like "Help me with unit testing," refine it to: "Generate unit test cases using Jest for a React component that handles user authentication with appropriate mocks for API calls."

Step 2: Break Down Complex Problems

AI excels when given well-structured problems. Try these approaches:

Decompose large tasks: Split complex requirements into smaller, manageable chunks
Define scope boundaries: Explicitly state what's included and excluded
Provide context: Include relevant technical details about your environment

Step 3: Think Iteratively

The most effective AI interactions happen through conversation, not one-shot prompts. Start simple, evaluate the response, then refine your approach:

Initial prompt: "Create a function that sorts an array of objects by multiple properties"

Follow-up: "Now modify the function to handle nested properties and custom comparators"

The Engineer's Prompt Pattern

As software engineers, we can leverage our understanding of programming patterns to structure prompts effectively:

The Specification Pattern

CONTEXT: Building a React Native app with Redux and TypeScript
TASK: Create a reducer for user authentication
REQUIREMENTS:
- Handle login, logout, and registration actions
- Store JWT token securely
- Include loading and error states
- Type-safe implementation
FORMAT: TypeScript code with comments

This pattern leverages our familiarity with requirements documentation to ensure comprehensive prompts.

The Test-Driven Prompt

GIVEN a Node.js API endpoint that processes payment information
WHEN a user submits invalid credit card details
THEN the system should return appropriate validation errors

Implement this endpoint using Express.js with proper error handling.

Translating Thought Patterns into Effective Prompts

Different thinking styles require different prompting strategies:

For Exploratory Thinking

When exploring possibilities, frame prompts as divergent questions:

"What are five different architectural approaches to implementing a real-time collaboration feature in a document editor? For each approach, list the main advantages, disadvantages, and complexity factors."

For Analytical Thinking

When analyzing existing code or systems:

"Analyze this database schema for potential performance bottlenecks, normalization issues, and scalability concerns. Suggest specific improvements with their tradeoffs."

For Implementation Thinking

When you need concrete implementation details:

"Implement a caching layer for this GraphQL resolver function that handles both memory and Redis cache with appropriate invalidation strategies."

Practical Exercise: Transform Your Thinking

Let's practice transforming a general thought into an effective prompt:

Initial thought: "I need to make my API faster."

Step 1 - Clarify intention: "I need to identify and resolve performance bottlenecks in my REST API."

Step 2 - Add context and specifics: "I have a Node.js/Express REST API with MongoDB that's experiencing slow response times (>500ms) under load (>100 concurrent users)."

Step 3 - Request specific actionable output: "Provide a systematic troubleshooting approach with specific profiling techniques, common bottlenecks to check, and optimization strategies for Node.js REST APIs with MongoDB."

Final prompt:

I need help optimizing a Node.js/Express REST API with MongoDB that's experiencing slow response times (>500ms) under load (>100 concurrent users).

Please provide:
1. A systematic approach to identify where the bottlenecks might be occurring
2. Specific profiling techniques and tools I should use
3. Common performance issues in this stack and their solutions
4. Both quick wins and longer-term architectural improvements

My API primarily handles user authentication, product catalog browsing, and order processing.

Conclusion: Building Your AI Translation Skills

Effective AI prompting is a skill that improves with deliberate practice. As software engineers, we have a natural advantage in structured thinking, but we need to adapt these skills to the AI interaction model.

In the next part of this series, we'll explore how to use AI for specific software engineering tasks, from debugging to architecture design, with practical examples and techniques.

Remember that the journey from thought to prompt is not just about getting better results—it's about expanding how you conceptualize problems and solutions in collaboration with AI systems.

How do you approach prompting AI assistants for your software engineering tasks? Share your techniques in the comments below.

AI vs. Developers: How to Future-Proof Your Software Engineering Career in 2025

Free Peak — Sat, 08 Mar 2025 17:01:12 +0000

Modern software development increasingly involves collaboration with AI tools

In recent years, the rapid advancement of AI tools has sparked debates about the future of software development careers. As someone deeply immersed in this evolving landscape, I'd like to share my perspective on whether AI will replace developers, and more importantly, how you can position yourself to thrive in this new era.

The Reality Check: AI as a Partner, Not a Replacement

Let's address the elephant in the room first: No, AI will not completely replace software engineers. What we're witnessing is not a replacement but a transformation of how development work is done.

AI tools augment human capabilities rather than replacing them

AI tools like GitHub Copilot, ChatGPT, and Claude are becoming incredibly powerful coding assistants. They excel at generating boilerplate code, suggesting solutions to common problems, and helping debug issues. However, they still lack several critical capabilities:

Understanding the broader business context and translating nebulous requirements into technical specifications
Designing complex systems with appropriate architecture and scalability considerations
Making critical judgment calls about security, performance trade-offs, and technical debt
Collaborating effectively with cross-functional teams to align technology with business goals

These uniquely human skills will remain valuable for the foreseeable future. The most successful developers will be those who learn to leverage AI tools while continuing to develop these higher-order capabilities.

How to Thrive in the AI Era

1. Embrace AI as a Productivity Multiplier

Modern developers leverage AI tools to boost productivity

Rather than fearing AI, treat it as a powerful ally that can handle routine tasks while freeing you to focus on more complex, creative work. Learn to:

Use AI pair programming tools effectively
Craft precise prompts that yield better code generation
Review AI-generated code critically rather than accepting it blindly
Automate repetitive tasks with AI assistance

2. Focus on "AI-Resistant" Skills

Soft skills like collaboration remain difficult for AI to master

Some skills will remain difficult for AI to master in the near term:

System design and architecture
Cross-functional communication and collaboration
Product sense and business domain expertise
Mentoring and technical leadership
Ethical decision-making and handling edge cases

3. Continuous Learning is Non-Negotiable

Continuous learning is essential in the rapidly evolving tech landscape

The pace of change in our industry is accelerating. To stay relevant:

Dedicate time each week to learning something new
Follow technology trends but distinguish between hype and substantive changes
Build projects using emerging technologies to gain hands-on experience
Contribute to open source to broaden your network and skills

4. Develop T-Shaped Expertise

The T-shaped developer has both depth in a specialty and breadth across the tech ecosystem

The most valuable developers have both depth and breadth:

Cultivate deep expertise in at least one area (backend, frontend, ML, etc.)
Develop working knowledge across adjacent domains
Understand how your specialty connects to the broader technology ecosystem

5. Cultivate Your Professional Network

Professional networking becomes even more valuable in uncertain times

In uncertain times, your network becomes even more valuable:

Attend meetups and conferences (virtual or in-person)
Engage with developer communities on platforms like GitHub, Stack Overflow, and Discord
Share your knowledge through blogging, speaking, or creating content
Maintain relationships with former colleagues who know your work quality

The Path Forward: Adaptation, Not Fear

Technology evolution has always required adaptation rather than resistance

The history of technology is filled with innovations that changed how we work without eliminating the need for skilled professionals. Word processors didn't replace writers. Spreadsheets didn't replace accountants. CAD software didn't replace architects.

Similarly, AI will change how developers work, but won't replace the need for human creativity, judgment, and problem-solving in software development.

The developers who will thrive are those who view AI as an opportunity rather than a threat—who use these tools to amplify their capabilities while focusing on the uniquely human aspects of software development that create the most value.

The question isn't whether AI will take your job, but how you'll adapt your career to harness AI's power while developing the skills that remain distinctly human.