# Performance tuning recommendations

This document lists host-level (php.ini / Apache / MySQL) tweaks that
the application can't set from inside PHP. They're not enabled by
default; enable them where your hosting environment allows.

## 1. OPcache (PHP bytecode cache)

OPcache eliminates the need for PHP to re-parse and re-compile every
script on every request. On most shared hosts it's already on; on a
fresh install it isn't. Check with `phpinfo()` or:

```php
<?php var_dump(function_exists('opcache_get_status') ? opcache_get_status(false) : 'OPcache not loaded');
```

Recommended `php.ini` (or `opcache.ini`):

```ini
[opcache]
opcache.enable=1
opcache.enable_cli=0
opcache.memory_consumption=128
opcache.interned_strings_buffer=16
opcache.max_accelerated_files=10000
opcache.validate_timestamps=1
opcache.revalidate_freq=2
opcache.fast_shutdown=1
opcache.save_comments=1
opcache.jit=tracing
opcache.jit_buffer_size=64M
```

`opcache.revalidate_freq=2` means PHP re-checks the file's mtime every
2 seconds — fast enough that CSS/JS edits show up almost immediately,
slow enough to skip the stat call on hot requests.

For XAMPP specifically, edit `xampp/php/php.ini` and uncomment/add
the block above, then restart Apache.

## 2. Realpath cache

```ini
realpath_cache_size=4096K
realpath_cache_ttl=600
```

PHP has to resolve `../includes/db.php` and similar paths on every
`require_once`. The realpath cache stores those resolutions.

## 3. Apache `mod_deflate` (gzip)

Already wired in `.htaccess`. Verify it's loaded:

```bash
apachectl -M | grep deflate
```

On XAMPP it's compiled in by default but not enabled in `httpd.conf`.
Uncomment `LoadModule deflate_module modules/mod_deflate.so` and
restart Apache.

## 4. Apache `mod_expires` and `mod_headers`

Already wired in `.htaccess`. Verify:

```bash
apachectl -M | grep -E 'expires|headers'
```

## 5. Brotli (optional, faster than gzip)

If your host has `mod_brotli` installed, prefer it over gzip. It ships
separately and isn't standard on XAMPP — skip on shared hosts unless
you're sure it's available.

## 6. SQLite tuning (application-level)

The codebase already uses these via `initDB()`:

- `CREATE INDEX` on `products.created_at`, `products.category_id`,
  `products.price`, `orders.created_at`, `orders.ip_address`,
  `orders.product_id`, `ip_blocks.ip_address`, `ip_blocks.expires_at`.
- `ANALYZE` to refresh query planner statistics.

If you ever hit database lock contention (multiple writers), consider
enabling WAL mode:

```sql
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
```

(WAL readers don't block writers and vice-versa; `NORMAL` is safe with
WAL and ~10x faster than the default `FULL`.)

To enable from PHP, add to `getDB()` in `includes/db.php`:

```php
$db->exec('PRAGMA journal_mode=WAL');
$db->exec('PRAGMA synchronous=NORMAL');
```

These pragmas are persistent (stored in the DB file) so they only
need to be set once per database file.

## 7. .htaccess caching summary

The shipped `.htaccess` does the following:

- Gzips text assets (`mod_deflate`)
- Sets `Cache-Control: public, max-age=31536000, immutable` for static
  assets, `max-age=3600` for HTML.
- Sets `Vary: Accept-Encoding` so caches store the gzip and non-gzip
  copies separately.
- Disables directory listings.
- Sets `X-Content-Type-Options: nosniff`.

## 8. Browser-side reminders

The codebase already:

- Lazy-loads off-screen images (`loading="lazy" decoding="async"`).
- Preloads the critical stylesheet and the logo.
- Uses `fetchpriority="high"` for the hero / first slider image.
- Defers `main.js` and the analytics pixels.

If you want to take it further:

- Convert product photos to AVIF/WebP at upload time. The
  `assets/uploads/` folder already contains several `.webp` images;
  extending the admin upload form to generate AVIF alongside would
  cut image weight ~30% more.
- Inline the critical CSS for above-the-fold elements (header, hero,
  product card). The shipped `.htaccess` keeps the cost low but
  inlining is the only way to skip the first CSS round-trip entirely.

## 9. Verifying the optimizations

```bash
# Server timing
curl -o /dev/null -s -w '%{time_starttransfer}\n' https://yoursite/

# Cache headers
curl -I https://yoursite/assets/css/style.css

# Gzip / compression
curl -H 'Accept-Encoding: gzip' -I https://yoursite/ | grep -i content-encoding

# SQLite plan with indexes
sqlite3 database/quickcart.db 'EXPLAIN QUERY PLAN SELECT id, name, price, images FROM products ORDER BY created_at DESC LIMIT 8;'
```

The last command should report `USING INDEX idx_products_created_at` —
confirms the new index is being used.