标签：#MySQL

One of the most common questions I hear is: "My API is slow. Where do I start?" The first instinct is usually: Upgrade the server Increase CPU Add more RAM But in many cases, the database query is the real bottleneck . Whenever I investigate a slow Laravel application, I follow the same checklist. It helps me identify performance issues before making unnecessary infrastructure changes. Let's go through it. 1️⃣ Find the Slow Queries First Don't start optimizing random queries. Start with the queries that are executed the most or take the most time. Useful tools: Laravel Telescope Laravel Debugbar (development) MySQL Slow Query Log Application Performance Monitoring (APM) You can't optimize what you haven't measured. 2️⃣ Stop Using SELECT * One of the easiest improvements. ❌ Instead of: SELECT * FROM users WHERE id = 10 ; Use: SELECT id , name , email FROM users WHERE id = 10 ; Why? Less data transferred Lower memory usage Faster response Easier for MySQL to use covering indexes Only fetch the columns your application actually needs. 3️⃣ Always Check the Execution Plan Before changing anything, run: EXPLAIN SELECT id , name FROM users WHERE email = 'john@example.com' ; Things I usually look for: Is MySQL scanning the whole table? Is an index being used? How many rows are examined? Is there a temporary table? Is filesort being used? EXPLAIN often tells you exactly why a query is slow. 4️⃣ Verify Your Indexes Indexes are one of the biggest performance improvements you can make—but only when they match your queries. Example: SELECT * FROM orders WHERE customer_id = 100 ; Create an index: CREATE INDEX idx_customer_id ON orders ( customer_id ); Now MySQL can jump directly to the matching rows instead of scanning the entire table. 5️⃣ Look for Composite Index Opportunities Suppose your query is: SELECT id , total FROM orders WHERE customer_id = 10 AND status = 'paid' ; Instead of two separate indexes: customer_id status A composite index is often better: CREATE INDEX idx_cu

Pagination is a key component on web-applications that let users navigate through pages making easy to read/find records. Also, pagination is a great strategy to improve performance by avoiding to load entire dataset at once. However, while working with Kaminari, a popular pagination gem in Rails, I encountered an unexpected issue that revealed an interesting edge case. Identify the issue Basically, pagination in frontend was not working properly. Datatable should load 316 total rows, although when the user started to load 15 records per page, frontend is showing inaccurate total rows. Multiple of 15 should ends at 0 or 5. There were pages with 64 records, crazy world. Some pages were loading 12 or 11 rows. There is no issues or error messages in the frontend or backend. Lost in debugging-land After discarding Angular frontend errors, I started to dig into backend controller and Kaminari configuration. Nothing seems wrong. Everything looked good: test suite, smoke tests, desktop debugging. Despite of test results, I started to wonder: what if returned-data is wrong after all? and... Bingo! Finally, after checking every response I noticed that there were duped records in two different pages(pagination requests). Those duped records were skipped from Angular data-table and that's why loaded/total rows did not match. Bingo: a sort of mistake This tricky bug has a simple explanation: bad sorting. Kaminari uses a SQL query using LIMIT/OFFSET strategy: SELECT * FROM posts ORDER BY id LIMIT 25 OFFSET 0 ORDER BY : sort the collection. LIMIT : number of records per page. OFFSET : is used to skip a specified number of rows before starting to return rows from a query. This works perfectly using ORDER BY id because primary key is unique. Check table A. id title body created_at lock 101 Welcome to the Platform First post introducing the new platform features. 2026-06-16 08:15:22 false 102 Summer Update Announcing the latest improvements and updates. 2026-06-16 08:15:22 true 103

For two years, our Sponsored Placements service booked limited ad inventory through Redis: a counter in Redis, a Redlock around the decrement, and a TTL key per hold. It oversold. Not catastrophically — consistently. 40–60 double-booked placements a month , each one a manual refund and an apology email to an advertiser. The root cause was never one bug. It was the architecture: two sources of truth that could not be made atomic with each other. The count lived in Redis; the ownership lived in SQL. No transaction spans both. The Redlock only ever protected the Redis half. The one mental shift SKIP LOCKED turns a contended table into a concurrent work queue. Instead of every request fighting over one counter, each request grabs different rows and ignores the ones someone else is holding. FOR UPDATE alone serializes — that's the experience that scares people off SQL locking. FOR UPDATE SKIP LOCKED is the opposite: a transaction that would have blocked instead skips the locked row and takes the next free one. One row per reservable unit, then: START TRANSACTION ; SELECT id FROM inventory_unit WHERE placement_id = 42 AND ( status = 'available' OR ( status = 'held' AND hold_expires_at < NOW ( 3 ))) -- self-healing expiry ORDER BY id LIMIT 2 FOR UPDATE SKIP LOCKED ; -- the whole trick UPDATE inventory_unit SET status = 'held' , reservation_id = 'uuid' , hold_expires_at = NOW ( 3 ) + INTERVAL 10 MINUTE WHERE id IN ( 1107 , 1108 ); INSERT INTO reservation (...) VALUES (...); COMMIT ; Two concurrent requests for the same pool lock different rows. Neither waits. The claim, the hold, and the reservation are one transaction — there is nothing to reconcile because there is nothing else. The numbers (8 weeks before vs 8 weeks after) Metric Redis + Redlock MySQL SKIP LOCKED Oversells / month 40–60 0 Reservation p95 210 ms 34 ms Reservation p99 540 ms 61 ms Throughput / instance ~600 RPS 1,400 RPS Lock-wait timeouts / day ~900 <5 Nightly reconciliation 9–14 min deleted Redis cluster

Every headless WordPress conversation starts the same way — someone draws an architecture diagram with arrows pointing from a REST API to a shiny Next.js frontend, and it looks clean. Too clean. This is a post about what happens when you close the whiteboard and open the actual codebase. The Stack Decision: GraphQL vs. REST vs. Direct MySQL This is usually the first fork in the road. For this build, the client already had a well-indexed WooCommerce site. The product catalog, slugs, and taxonomy structure were already doing heavy SEO work. So the constraint was simple: nothing about the data layer changes, only how we consume it. WPGraphQL was a real option — but it meant adding a plugin dependency to a WordPress install we were actively trying to slim down. The WP REST API was already there, no installation required, and exposed exactly what we needed: products, categories, pages, and media — all queryable by slug. The decision: WP REST API, consumed server-side via Next.js fetch in Server Components. // Fetching a product by slug — preserving the existing URL structure const res = await fetch ( ` ${ process . env . WP_API_BASE } /wp/v2/product?slug= ${ params . slug } &_embed` , { next : { revalidate : 3600 } } ); const [ product ] = await res . json (); No new dependencies on the WordPress side. The legacy install runs as a lean shell — no active theme, minimal plugins, just the REST API and the data. The Site Kit Problem: Bridging Familiar Workflows This is where most migrations quietly fail the client. The previous team lived inside WordPress admin. Google Site Kit gave them traffic stats, Search Console data, and Analytics — all surfaced in a UI they knew. Ripping that away and telling them "just use Google Analytics directly" is a workflow regression, not an upgrade. The pivot here was building a lightweight admin dashboard as part of the Next.js project — not a full replacement for Site Kit, but a mirror of the metrics they actually checked daily: Page views

🚀 Overview — Why Semantic Search Matters Semantic search surpasses simple keyword matching because embeddings place texts in a high‑dimensional vector space where cosine similarity directly reflects intent. A dedicated vector store is therefore required to persist those embeddings and serve nearest‑neighbor queries efficiently. This post demonstrates a pinecone fastapi semantic search tutorial that wires a FastAPI service to Pinecone, showing the full data flow from embedding generation to similarity lookup. 📑 Table of Contents 🚀 Overview — Why Semantic Search Matters 🛠 Environment Setup — How to Install Dependencies 🐍 Python Virtual Environment 📦 Required Packages 📦 Building the FastAPI Service — How to Create the API 🧩 Data Model with Pydantic 🔗 Core FastAPI Application 🔎 Integrating Pinecone — How to Store and Query Vectors 🗂 Index Creation and Configuration 📤 Upserting Documents 🔎 Performing a Semantic Search 📊 Performance & Scaling — How Indexes Influence Latency 🟩 Final Thoughts ❓ Frequently Asked Questions How do I secure the Pinecone API key in production? Can I use a different embedding model? What happens if I need to change the index dimension? 📚 References & Further Reading 🛠 Environment Setup — How to Install Dependencies Creating a reproducible environment guarantees that the tutorial runs identically on any machine. 🐍 Python Virtual Environment $ python3 -m venv venv $ source venv/bin/activate (venv) $ python -V Python 3.11.5 Activating the virtual environment isolates package installations from the global interpreter. 📦 Required Packages $ pip install fastapi[all] uvicorn pinecone-client sentence-transformers Collecting fastapi[all] Downloading fastapi-0.109.0-py3-none-any.whl (48 kB) Collecting uvicorn Downloading uvicorn-0.24.0-py3-none-any.whl (66 kB) Collecting pinecone-client Downloading pinecone_client-2.2.2-py3-none-any.whl (81 kB) Collecting sentence-transformers Downloading sentence_transformers-2.2.2-py3-none-any.whl (1.1 MB) ... Successful

The engineering team at Meta recently outlined how the company migrated a data ingestion platform that transfers several petabytes of MySQL social graph data daily to improve reliability and operational efficiency. The team used techniques like reverse shadowing and continuous checksum monitoring to ensure zero downtime during the transition. By Renato Losio