# MySQL / MariaDB Slow Query Analysis

**Files analysed:** `server-slow.log`, `slow30kdebug.log`
**Window covered:** 2026-09-08 18:47:22 → 19:16:00 (~29 minutes, the two logs are back-to-back)

| | slow30kdebug.log | server-slow.log | Combined |
|---|---|---|---|
| Queries logged | 150,780 | 42,972 | **193,752** |
| Distinct query patterns | 172 | 66 | 173 |
| Total execution time | 144,952 s | 528 s | **145,481 s** |
| Rows examined | 273,514,501 | 41,961,568 | **315,476,069** |
| Rows actually returned | 1,205,616 | 35,168 | 1,240,784 |

> **Correction to an earlier assumption.** I initially inferred `long_query_time=0`. The actual config is:
> ```ini
> long_query_time = 10
> slow_query_log = 1
> log_queries_not_using_indexes = 1
> ```
> `log_queries_not_using_indexes=1` is what produces the volume: it logs a query **regardless of how fast it ran** if the optimiser could not use an index. This makes the analysis *stronger*, not weaker — see §0.

---

## 0. What the config means for reading these logs

With `long_query_time=10` and `log_queries_not_using_indexes=1`, every entry lands in the log for **one of two distinct reasons**. Separating them is the single most useful cut of this data:

| Reason logged | Executions | Distinct patterns | Total time | Rows examined |
|---|---|---|---|---|
| **Took ≥ 10 s** (hit `long_query_time`) | 155 | **6** | 144,433 s | 98.7 M |
| **Used no index** (`log_queries_not_using_indexes`) | 193,597 | **169** | 1,047 s | 216.8 M |

**99.92% of the log lines are there because MySQL itself judged the query to be unindexed.** That answers your second question directly and authoritatively — I no longer have to infer "missing index" from a rows-examined heuristic. The server has already labelled them. Anything in these files that ran under 10 seconds is, by definition, a query with no usable index.

Only **6 query patterns** ever crossed the 10-second threshold:

| Pattern | Execs ≥10s | Time | Rows exam/call |
|---|---|---|---|
| `cart_products` FIND_IN_SET + levenshtein_ratio | 137 | 144,162 s | 594,007 |
| `cart_products`+`cart_brands` `COUNT(*) as total` | 11 | 147 s | 731,219 |
| `cart_products` FIND_IN_SET (variant, empty result) | 1 | 48 s | 0 |
| `cart_products`+`cart_brands` `SELECT cp.*, cb.brandname` | 3 | 38 s | 633,838 |
| `cart_products`+`cart_brands` brandid=10 | 2 | 24 s | 709,497 |
| `cart_products`+`cart_product_customfieldsx` double subquery | 1 | 15 s | 5,941,631 |

Every one of them touches `cart_products`. That table is the entire problem.

### Is the logging itself hurting you?

Partly, yes — it is a real secondary cost, though not the root cause.

| | Write rate | Projected |
|---|---|---|
| `slow30kdebug.log` window | 39 KB/s | **3.5 GB/day** |
| `server-slow.log` window (peak) | 110 KB/s | **9.7 GB/day** |

At 232 queries/sec being logged, each write is a synchronous append that takes a mutex on the log file. That serialises threads and adds latency to *every* query, including healthy ones on the other databases sharing this server. It also fills the disk quickly, and a full disk will take MySQL down outright.

So: `log_queries_not_using_indexes=1` is causing the **log size** problem and contributing to general latency. It is **not** what made the site slow — the levenshtein query in §1 did that, and it would have been slow with logging off entirely. Turning logging off would hide the problem, not fix it.

### Recommended logging config

Keep the diagnostic value, drop the volume. Add:

```ini
# Stop logging scans of tiny lookup tables — this alone removes ~99% of your lines
min_examined_row_limit = 1000

# Cap repeats of the same unindexed query to 10 per minute
log_throttle_queries_not_using_indexes = 10
```

`min_examined_row_limit=1000` is the high-leverage one. It would have excluded the 288-row `cart_product_customfieldsx` scans (113,452 lines), the 487-row `cart_pricing_rules` scans (43,247 lines) and the 1,741-row `cart_customer_remember_me` scans (8,649 lines) — about 165,000 of your 193,752 lines — while still catching everything that examines a meaningful number of rows.

If this is MariaDB (the `QC_hit` field in your log says it is), you have better options still:

```ini
log_slow_filter = full_scan,full_join,tmp_table_on_disk,filesort_on_disk
log_slow_rate_limit = 100        # log 1 in 100 qualifying queries
log_slow_verbosity = query_plan,explain
```

`log_slow_verbosity = query_plan,explain` is worth enabling temporarily — it writes the actual EXPLAIN output into the log, which would let you confirm the index recommendations in §3 without guessing at table structure.

Once the `cart_products` queries are fixed, run with these settings for a day and re-read the log. What remains will be a much shorter, genuinely actionable list.

---

## 1. Headline finding

**One single query pattern is responsible for 99.0% of all database time.**

```sql
SELECT p.*, pi.*
FROM cart_products p
LEFT JOIN cart_product_images pi
       ON (p.productid = pi.imageprodid AND pi.imageisthumb = 1)
WHERE p.prodvisible = 1
  AND productid != 76554
  AND FIND_IN_SET(83, prodcatids)
  AND prodavailability = 'unreserved'
  AND prodcurrentinv != 0
  AND levenshtein_ratio('(813) 896-2999', prodname) > 60
ORDER BY levenshtein_ratio('(813) 896-2999', prodname) DESC
LIMIT 5;
```

| Metric | Value |
|---|---|
| Executions | 137 |
| Total time | **144,162 s (~40 hours of CPU inside a 29-minute window)** |
| Average | 1,052 s per execution |
| Slowest single run | **5,765 s (96 minutes)** |
| Rows examined per run | 594,007 |
| Rows returned per run | 5 |
| Efficiency | 0.001% |
| **Max copies running simultaneously** | **60** |

Across the whole log, peak in-flight queries reached **1,006 concurrent connections**. This is a classic thundering-herd meltdown: the query is slow, users/bots retry, more copies pile on, each copy gets slower, and the server saturates.

### Why it cannot use an index (three separate reasons)

1. **`levenshtein_ratio('(813) 896-2999', prodname)`** — a user-defined function is executed on *every one of ~594,000 rows*, and then a **second time** for the `ORDER BY`. That is ~1.2 million UDF calls per execution. No index can ever help this.
2. **`FIND_IN_SET(83, prodcatids)`** — categories are stored as a comma-separated string in a single column. `FIND_IN_SET` can never use an index. This is a schema design problem.
3. **`prodcurrentinv != 0` / negations** — `!=` conditions are poorly selective and generally force a scan.

### What to do

- **Immediate (today):** rate-limit or disable this feature, and set `max_statement_time` so nothing can run for 96 minutes:
  ```sql
  SET GLOBAL max_statement_time = 30;  -- MariaDB, seconds
  ```
- **Short term:** the search string `'(813) 896-2999'` is a **phone number** being fuzzy-matched against product names. Do an exact/prefix match on a normalised phone column instead of Levenshtein:
  ```sql
  ALTER TABLE cart_products ADD COLUMN prodname_digits VARCHAR(20)
    AS (REGEXP_REPLACE(prodname, '[^0-9]', '')) STORED,
    ADD INDEX idx_prodname_digits (prodname_digits);
  ```
  Then `WHERE prodname_digits = '8138962999'` — from 594,007 rows examined down to ~1.
- **Proper fix:** replace the comma-separated `prodcatids` with a junction table:
  ```sql
  CREATE TABLE cart_product_categories (
    productid  INT NOT NULL,
    categoryid INT NOT NULL,
    PRIMARY KEY (categoryid, productid),
    KEY idx_product (productid)
  ) ENGINE=InnoDB;
  ```

---

## 2. Slow queries — ranked

| # | Query (table) | Execs | Avg | Max | Rows exam/call | Rows sent/call | Total time |
|---|---|---|---|---|---|---|---|
| 1 | `cart_products` + `cart_product_images` — FIND_IN_SET + levenshtein_ratio | 137 | 1052 s | 5765 s | 594,007 | 5 | **144,162 s** |
| 2 | `cart_products` + `cart_brands` — `COUNT(*) as total` | 106 | 4.77 s | 17.5 s | 728,934 | 1 | 506 s |
| 3 | `cart_products` + `cart_brands` — `SELECT cp.*, cb.brandname` | 104 | 4.74 s | 15.4 s | 560,562 | 3,178 | 494 s |
| 4 | `wp_weebot_user` — `UPDATE ... WHERE user=0 AND type='support'` | 154 | 0.36 s | 1.1 s | 31,592 | 0 | 56 s |
| 5 | `wp_options` — WP transient cleanup self-join | 80 | 0.50 s | 1.3 s | 11,345 | 0 | 40 s |
| 6 | `cart_product_customfieldsx` — `fieldname='reserved'` | 113,452 | 0.0004 s | 0.009 s | 288 | 0.9 | 40 s |
| 7 | `cart_customer_remember_me` — `md5(concat(...))` | 8,649 | 0.0028 s | 0.008 s | 1,741 | 1 | 24 s |
| 8 | `cart_products` + `cart_product_customfieldsx` — double subquery COUNT | 1 | 14.8 s | 14.8 s | 5,941,631 | 1 | 15 s |
| 9 | `cart_products` + `cart_brands` — brandid=10, ORDER BY productid DESC | 2 | 12.1 s | 13.9 s | 709,497 | 115,367 | 24 s |
| 10 | `cart_pricing_rules` — 5 variants | 43,247 | 0.0004 s | 0.008 s | 487 | 0.2 | 16 s |
| 11 | `wp_postmeta` — `meta_key='_wp_attached_file' AND meta_value=...` | 48 | 0.157 s | 0.4 s | 57,177 | 0.4 | 7.5 s |
| 12 | `information_schema.tables` — `data_free WHERE ENGINE='InnoDB'` | 5 | 1.14 s | 1.6 s | 103 | 1 | 5.7 s |
| 13 | `cart_products` + `cart_product_images` — `COUNT(p.productid)` | 1 | 4.94 s | 4.9 s | 610,504 | 1 | 4.9 s |
| 14 | `cart_sessions` — `DELETE WHERE sesslastupdated < ...` | 8 | 0.382 s | 0.9 s | 174,970 | 0 | 3.1 s |
| 15 | `wp_weebot_user` — `SELECT * WHERE session='...'` | 166 | 0.024 s | 0.8 s | 32,523 | 0 | 3.9 s |

Items 6, 7 and 10 are individually fast but run **tens of thousands of times** in 29 minutes — an N+1 query problem in the application (see §5).

---

## 3. Missing indexes — ready-to-run DDL

Given `log_queries_not_using_indexes=1`, **all 169 patterns that ran under 10 s are unindexed by the server's own judgement** (193,597 executions). The DDL below covers the ones where an index will actually help; §4 covers the ones where it cannot.

Test each on a staging copy first; `ALTER TABLE` on `cart_products` (~600k rows) will lock or rebuild.

### `excellen_vip3` (the e-commerce cart — highest priority)

```sql
-- 288-row table scanned 122,000+ times in 29 minutes
ALTER TABLE cart_product_customfieldsx
  ADD INDEX idx_field_prod (fieldname, fieldprodid),
  ADD INDEX idx_prodid (fieldprodid);

-- 175,000 rows scanned per session-cleanup call
ALTER TABLE cart_sessions
  ADD INDEX idx_sesslastupdated (sesslastupdated);

-- 2,921 rows scanned for every login/email lookup
ALTER TABLE cart_customers
  ADD INDEX idx_custconemail (custconemail);

-- 700 rows scanned, 271 executions
ALTER TABLE cart_returns
  ADD INDEX idx_retcustomerid (retcustomerid);

-- 145 rows scanned, 271 executions
ALTER TABLE cart_wishlists
  ADD INDEX idx_customerid (customerid);

-- helps the 'full' and 'area' rule_condition variants only
ALTER TABLE cart_pricing_rules
  ADD INDEX idx_status_cond_value (status, rule_condition, value);

-- partial help for the big product listing queries
ALTER TABLE cart_products
  ADD INDEX idx_visible_avail_inv (prodvisible, prodavailability, prodcurrentinv);
ALTER TABLE cart_product_images
  ADD INDEX idx_prod_thumb (imageprodid, imageisthumb);
```

### `webmak_wbekly%$35vdk` / `webmaklay_devel090123`

```sql
ALTER TABLE wp_weebot_user
  ADD INDEX idx_session (session),
  ADD INDEX idx_user_type (user, type),
  ADD INDEX idx_type_online (type, online);
```
44,481 rows scanned per session lookup and per "online" heartbeat UPDATE. The UPDATE also held locks up to 0.395 s, which blocks other writers.

### `friendss_wp`

```sql
ALTER TABLE wp_postmeta
  ADD INDEX idx_key_value (meta_key(32), meta_value(191));

ALTER TABLE wp_wc_admin_notes
  ADD INDEX idx_name (name),
  ADD INDEX idx_type (type);

ALTER TABLE wp_rt_rtm_media
  ADD INDEX idx_type_size (media_type, file_size);

ALTER TABLE wp_yith_wcwl_lists
  ADD INDEX idx_user_default (user_id, is_default);

ALTER TABLE wp_fsq_data
  ADD INDEX idx_form_ip (form_id, ip);

ALTER TABLE wp_wfhits
  ADD INDEX idx_ctime_action (ctime, action);

ALTER TABLE wp_wfnotifications
  ADD INDEX idx_new_ctime (`new`, ctime);
```

### `nzreal_estate237653TYtre`

```sql
ALTER TABLE wp_favethemes_insights
  ADD INDEX idx_listing_time (listing_id, time);

ALTER TABLE wp_houzez_crm_activities
  ADD INDEX idx_user_activity (user_id, activity_id);
```

---

## 4. Queries an index will NOT fix — these need a rewrite

| Query | Why no index can help | Fix |
|---|---|---|
| `levenshtein_ratio(..., prodname)` | UDF evaluated per row, twice | Normalised phone column + exact match (see §1) |
| `FIND_IN_SET(83, prodcatids)` | Comma-separated list in one column | Junction table |
| `md5(concat(firstname,email,token,ip))` on `cart_customer_remember_me` | Function wraps the columns | Stored generated column, below |
| `cart_pricing_rules` `LIKE CONCAT(value,'%')` | The **column** is on the pattern side, not the search side | Cache the 487-row table in the app; it changes rarely |
| `matomo_session` `WHERE modified + lifetime < ...` | Arithmetic on columns | Store an `expires_at` column and index it |
| `prodcurrentinv != 0`, `brandapiflag != 0` | Negation, low selectivity | Rewrite as `> 0` / positive `IN` list |
| `information_schema.tables WHERE ENGINE='InnoDB'` | Metadata table, no indexes | Disable the plugin doing this, or cache the result |

For the remember-me token (MariaDB 10.2+ / MySQL 5.7+):

```sql
ALTER TABLE cart_customer_remember_me
  ADD COLUMN token_hash CHAR(32)
    AS (MD5(CONCAT(customer_firstname, customer_email, device_token, device_ip))) STORED,
  ADD INDEX idx_token_hash (token_hash);
```
Then change the application to `WHERE token_hash = '...'`. Drops 1,741 rows examined to 1.

---

## 5. Application-level problem: N+1 queries

These are individually fast, so they will not show in a normal slow log, but they dominate query *count*:

| Query | Executions in 29 min | Rate |
|---|---|---|
| `cart_product_customfieldsx` — `fieldname='reserved'` | 113,452 | ~65/sec |
| `cart_product_customfieldsx` — `fieldname='customerid'` | 8,403 | ~5/sec |
| `cart_pricing_rules` (5 near-identical variants per page load) | 43,247 | ~25/sec |
| `cart_customer_remember_me` | 8,649 | ~5/sec |
| `cart_tax_rates` 3-table join | 5,252 | ~3/sec |

`cart_product_customfieldsx` has only **288 rows total**. The application is querying it once per product in a loop. Load the whole table once per request into an array, or use a single `WHERE fieldprodid IN (...)`. Same for `cart_pricing_rules` (487 rows, queried 5× per checkout).

The 5 `cart_pricing_rules` variants all scan the same 487 rows and could be collapsed into one query with `rule_condition IN ('full','start','end','contains','area')`.

---

## 6. Low-priority unindexed queries (correct, but don't ignore forever)

**This section previously claimed ~116 patterns were "healthy / index in use." That was wrong** — it was based on a rows-examined heuristic written before I knew the config. With `log_queries_not_using_indexes=1`, nothing that appears in these files is using an index. There is no "healthy" group here.

What there *is* is a group where the missing index currently costs almost nothing, because the table is tiny:

| Query | Execs | Avg | Rows exam/call | Why it's low priority |
|---|---|---|---|---|
| `cart_tax_rates` 3-table JOIN | 5,252 | 0.0009 s | 0 | Tables are empty or const-optimised; unindexed but free |
| `SELECT 1 FROM wp_wfls_passkeys LIMIT 1` | — | 0.0003 s | 0 | Existence probe |
| `INFORMATION_SCHEMA.COLUMNS` probes (wpguppy) | ~80 | 0.001 s | 5–19 | Metadata, cannot be indexed |
| `wp_wc_admin_notes WHERE name = ?` | 180 | 0.0005 s | 279 | 279-row table |
| `cart_wishlists WHERE customerid = ?` | 271 | 0.0004 s | 145 | 145-row table |

The risk with this group is **growth**. `cart_product_customfieldsx` is 288 rows today and a scan costs 0.4 ms; at 288,000 rows the same query costs 400 ms and the application does it 65 times a second. The indexes in §3 are cheap insurance on small tables — add them now rather than after they become incidents.

If you set `min_examined_row_limit = 1000` as suggested in §0, this entire group disappears from the log, which is the right outcome: it stops hiding the queries that matter.

---

## 7. Load distribution

**By schema (slow30kdebug.log + server-slow.log):**

| Schema | Queries | Total time | Rows examined |
|---|---|---|---|
| `excellen_vip3` | ~193,000 | **145,400 s (99.9%)** | 313.9 M |
| `webmaklay_devel090123` | ~95 | 41 s | ~1.1 M |
| `webmak_wbekly%$35vdk` | ~190 | 60 s | ~10.3 M |
| `friendss_wp` | ~1,200 | 12 s | ~3.1 M |
| `nzreal_estate237653TYtre` | ~130 | 1.5 s | ~0.3 M |
| others | ~50 | <1 s | small |

This is a shared server, but **one database (`excellen_vip3`) is consuming essentially all resources**. The other sites are collateral damage — their queries are queuing behind the 60 concurrent product searches.

---

## 8. Recommended action order

1. **Kill the runaway query.** Set `max_statement_time = 30` globally right now. Nothing should ever run for 96 minutes.
2. **Disable or rate-limit the product fuzzy-search feature** in `excellen_vip3` until it is rewritten. This alone recovers 99% of database time.
3. **Rewrite the phone search** using a normalised, indexed digits column.
4. **Apply the index DDL in §3**, starting with `cart_product_customfieldsx`, `cart_sessions`, `wp_weebot_user`, and `cart_customers`.
5. **Fix the N+1 loops** (§5) — biggest win per line of code changed.
6. **Replace `prodcatids` comma-list** with a junction table (§1). This is the root schema flaw.
7. **Tame the logging** (§0): add `min_examined_row_limit = 1000` and `log_throttle_queries_not_using_indexes = 10`. Keep `log_queries_not_using_indexes=1` — it is giving you genuinely valuable data — but stop it writing 3.5–9.7 GB/day. Check free disk space now; a full disk stops MySQL dead.
8. **Temporarily enable `log_slow_verbosity = query_plan,explain`** (MariaDB) to confirm which indexes are actually missing before running the DDL in §3.

**Expected impact:** steps 1–3 should take total database time from ~145,000 s to under ~1,300 s for the same workload.

---

## Appendix: inferred table sizes

Derived from `Rows_examined` on full scans, so approximate.

| Table | Schema | ~Rows |
|---|---|---|
| `cart_products` | excellen_vip3 | ~594,000 – 731,000 |
| `cart_sessions` | excellen_vip3 | ~175,000 |
| `wp_weebot_user` | webmak_wbekly | ~44,481 |
| `wp_postmeta` | friendss_wp | ~57,177 |
| `wp_favethemes_insights` | nzreal_estate | ~11,000 |
| `wp_options` | webmaklay_devel | ~11,345 |
| `wp_rt_rtm_media` | friendss_wp | ~7,823 |
| `wp_houzez_crm_activities` | nzreal_estate | ~6,571 |
| `matomo_session` | webmak_analyst | ~5,745 |
| `cart_customers` | excellen_vip3 | ~2,921 |
| `cart_customer_remember_me` | excellen_vip3 | ~1,741 |
| `cart_categories` | excellen_vip3 | ~985 |
| `cart_returns` | excellen_vip3 | ~700 |
| `cart_pricing_rules` | excellen_vip3 | **487** |
| `cart_product_customfieldsx` | excellen_vip3 | **288** |