DEV Community: TAPAS MAHATA

10 MySQL Performance Optimization Techniques Every PHP Developer Must Know

TAPAS MAHATA — Tue, 08 Sep 2026 09:10:36 +0000

Slow MySQL queries can cripple your web application. This guide covers indexing, query optimization, table design, and caching techniques to make your database run 10x faster.
10 MySQL Performance Optimization Techniques Every PHP Developer Must Know

A slow database query can make your entire PHP application feel sluggish. Even a 500ms query running 100 times per page load adds 50 seconds of wait time. I've compiled the 10 most effective MySQL optimization techniques that have helped me speed up real projects.

Use Indexes Properly Indexes are the single most important optimization. Add indexes to columns used in WHERE, JOIN, and ORDER BY:

-- Check existing indexes
SHOW INDEX FROM users;

-- Add index to frequently searched column
ALTER TABLE users ADD INDEX idx_email (email);

-- Composite index for multiple columns
ALTER TABLE orders ADD INDEX idx_user_date (user_id, created_at);

EXPLAIN Your Queries Always use EXPLAIN to understand how MySQL executes your query:

EXPLAIN SELECT * FROM orders WHERE user_id = 5 AND status = 'pending';

Look for:
type=ALL → full table scan (BAD)
type=ref or type=range → index used (GOOD)

Select Only What You Need

// BAD - fetches all columns
$users = $db->query("SELECT * FROM users WHERE active = 1")->fetchAll();

// GOOD - fetch only needed columns
$users = $db->query("SELECT id, name, email FROM users WHERE active = 1")->fetchAll();

Solve the N+1 Query Problem

// BAD: N+1 queries
$users = $db->query("SELECT * FROM users")->fetchAll();
foreach ($users as $user) {
    $posts = $db->query("SELECT * FROM posts WHERE user_id = {$user['id']}")->fetchAll();
}

// GOOD: Single JOIN query
$results = $db->query("
    SELECT u.id, u.name, p.title
    FROM users u
    LEFT JOIN posts p ON p.user_id = u.id
")->fetchAll();

Always Paginate Large Results

-- Page 1 (20 results)
SELECT * FROM products
WHERE category_id = 5
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- For very large tables, use cursor pagination
SELECT * FROM products
WHERE id > :last_id
ORDER BY id ASC
LIMIT 20;

Avoid Functions on Indexed Columns

-- BAD: function prevents index usage
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- GOOD: range query uses index
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';

Use Proper Data Types

CREATE TABLE products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL,   -- not FLOAT for money
  is_active TINYINT(1) DEFAULT 1, -- not VARCHAR for booleans
  description TEXT                 -- only TEXT when needed
);

Optimize JOINs

-- Ensure JOIN columns are indexed
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

-- Prefer INNER JOIN over LEFT JOIN when possible
SELECT o.id, c.name
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending';

Cache Query Results in PHP

function getCachedQuery($db, $key, $sql, $params = [], $ttl = 300) {
    $cacheFile = sys_get_temp_dir() . '/db_' . md5($key) . '.cache';

    if (file_exists($cacheFile) && (time() - filemtime($cacheFile)) < $ttl) {
        return unserialize(file_get_contents($cacheFile));
    }

    $stmt = $db->prepare($sql);
    $stmt->execute($params);
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    file_put_contents($cacheFile, serialize($result));
    return $result;
}

Monitor Slow Queries

-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Quick Checklist
[ ] Add indexes to WHERE, JOIN, ORDER BY columns
[ ] Run EXPLAIN on slow queries
[ ] Replace SELECT * with specific columns
[ ] Fix N+1 queries with JOINs
[ ] Add LIMIT to all queries
[ ] Use range queries instead of functions on indexed columns
[ ] Use correct data types
[ ] Cache repeated queries

[ ] Monitor slow query log

I've also published detailed guides on Web Security, REST API with PHP, CSS Flexbox, and JavaScript Async/Await on my dev tools site.
Check out the free SQL Formatter tool at tsmtools.in/devtools.html — useful for cleaning up complex queries before running them.
What's your biggest MySQL performance challenge? Drop a comment below! 👇

I Built 93+ Free Browser-Based Tools for Developers — No Signup, No Upload

TAPAS MAHATA — Sun, 06 Sep 2026 23:59:34 +0000

I Built 93+ Free Browser-Based Tools for Developers — No Signup, No Upload

I'm a teacher and self-taught web developer from India. Over the past few months, I built TSM Tools — a completely free, browser-based toolkit with 93+ tools for developers, SEO professionals, and students.

No backend. No database. No signup. Everything runs 100% in your browser — your files and data never leave your device.

Here's what I built and why.

🤔 Why I Built This

I was tired of switching between 10 different websites just to:

Format a JSON response
Check keyword density of a blog post
Generate a QR code
Calculate an EMI
Decode a JWT token

Most of these tools either require signup, show aggressive ads, or upload your files to their server. I wanted something fast, private, and free — so I built it myself.

🛠 What's Inside

Developer Tools (24 tools)

JSON Formatter & Validator — beautify or minify JSON instantly
JS / CSS / HTML Formatter — format messy code in one click
JS / CSS Minifier — reduce file size for production
JWT Decoder — inspect tokens without any library
.htaccess Generator — build Apache redirect rules visually
Cron Expression Builder — create cron schedules with plain-English preview
Base Converter — Binary ↔ Decimal ↔ Hex ↔ Octal
SQL Formatter — format raw SQL queries
Regex Tester — test regular expressions with live matches
And more...

🔄 Converters (26 tools)

Excel Viewer — open .xlsx, .xls, .ods with multi-sheet support
Excel → JSON / CSV / HTML — export spreadsheet data instantly
CSV → Excel / JSON → Excel — download as .xlsx in one click
JSON ↔ CSV ↔ XML — convert data formats instantly
Image Compressor — reduce file size with quality slider
Image Resizer — custom dimensions with aspect ratio lock
Format Converter — PNG ↔ JPEG ↔ WebP
PDF to HTML — each page rendered as image, exact visual copy
Word to HTML — convert .docx to HTML markup
Markdown ↔ HTML — live preview conversion
Text ↔ Binary / Hex / Morse Code — encode and decode instantly

⚡ Utilities (21 tools)

EMI Calculator — home, car, personal loans with tenure quick-select
BMI Calculator — metric and imperial with health range
Age Calculator — exact age with next birthday countdown
QR Code Generator — URL, text, contact info
Invoice Generator — create and print professional invoices
Unit Converter — length, weight, temperature, speed, area, data
Password Generator — with strength indicator
IP Address Lookup — detect your current IP instantly
Color Picker — HEX, RGB, HSL values
Word Counter — characters, sentences, reading time

📈 SEO Tools (21 tools)

SERP Preview — see how your page looks in Google search
Keyword Density Checker — analyze keyword usage
Meta Tag Generator — create optimized title and description
Sitemap Generator — create XML sitemaps for Google Search Console
Schema Markup Generator — JSON-LD structured data
Twitter Card Preview — see how links look when shared
HTTP Header Checker — inspect response headers
Core Web Vitals Guide — LCP, FID, CLS explained

🔬 PHP/JS/HTML Health Checker

This is the tool I'm most proud of. Upload a ZIP file of your PHP, JavaScript, or HTML project and get:

SQL injection vulnerability detection
XSS vulnerability scanning
JavaScript syntax checking
Security best practice analysis
Auto-fix suggestions

All scanning happens in your browser using JavaScript — your code is never uploaded to any server.

🏗 How I Built It

Everything is vanilla HTML, CSS, and JavaScript — no frameworks, no build tools.

Key libraries used:

SheetJS (xlsx) — Excel file reading and writing
Mammoth.js — Word to HTML conversion
PDF.js — PDF rendering on canvas
QRCode.js — QR code generation

What I learned:

Browser APIs are incredibly powerful — File API, Canvas API, Blob API, URL.createObjectURL()
You can do a lot without a backend if you design around client-side processing
Keeping everything in one HTML file per page makes deployment trivially simple

📊 The Numbers

Category	Tools
Dev Tools	24
Converters	26
Utils	21
SEO Tools	21
Health Checker	1
Total	93+
Blog Articles	24

🚀 What's Next

More Excel tools (pivot table viewer, formula helper)
More SEO tools (backlink checker, page speed analyzer)
Dark mode
Mobile app (PWA)

🔗 Try It

👉 https://tsmtools.in

All tools are free, forever. No signup. No upload. No tracking beyond basic analytics.

I'd love to hear your feedback — what tools do you use most? What's missing? Drop a comment below! 👇

Built with ❤️ by a teacher who codes.