Database Optimization on Hosting: Indexes, Queries and Cache

Most slow databases on shared hosting come down to three causes: queries without a suitable index, too many small queries per page (the N+1 problem) and results that are recalculated on every request instead of being cached. Database optimization starts with checking slow queries with EXPLAIN, adding composite indexes for WHERE and ORDER BY, replacing loops with a JOIN and caching finished pages. In our test with 100,000 rows, an index cut the time of a catalog query from 20.41 ms to 0.27 ms.

This applies to MySQL and MariaDB on shared hosting: you control your database and your code, but not the server settings. All measurements below come from our test bench and are not RichHost production data.

How a database shares the server on hosting

On shared hosting your database runs on a MySQL or MariaDB server shared by many accounts. To stop one website from taking over the whole server, providers set limits per account. MySQL and MariaDB have their own account resource limits for this: MAX_USER_CONNECTIONS (simultaneous connections), MAX_QUERIES_PER_HOUR, MAX_UPDATES_PER_HOUR and MAX_CONNECTIONS_PER_HOUR. The query limits are counted per hour, not per minute. The actual values are set by the provider; if they are not in the plan description, ask support.

Once a limit is reached, the server rejects the connection or the query. For the test we allowed one account 3 connections and 500 queries per hour and got these errors:

ERROR 1226: User 'site' has exceeded the 'max_user_connections' resource (current value: 3)
ERROR 1226: User 'site' has exceeded the 'max_queries_per_hour' resource (current value: 500)

Depending on the application, visitors see this as an error establishing a database connection in WordPress or as a 503 Service Unavailable error. In practice: the fewer and lighter queries a page needs, the further it stays from any limit.

Indexes: check with EXPLAIN first

We created a test table with 100,000 products and ran a typical catalog query: products of one category, newest first. EXPLAIN shows how the server plans to execute the query:

EXPLAIN SELECT id, name, price FROM products
WHERE category_id = 42 ORDER BY created_at DESC LIMIT 20;

ALTER TABLE products ADD INDEX idx_cat_created (category_id, created_at);

The first line is the result before the index, the second one after it:

type: ALL  key: NULL             rows: 99824  Extra: Using where; Using filesort
type: ref  key: idx_cat_created  rows: 500    Extra: Using where

Without an index the server read all 100,001 rows (the Handler_read_rnd_next counter) and then sorted them (Using filesort): median 20.41 ms. With the composite index (category_id, created_at) it read 20 index entries and did not sort at all: 0.27 ms. Column order matters: first the column compared with =, then the sort column. This is exactly the pattern described in the MySQL documentation on ORDER BY.

Indexes are not free. According to the MySQL documentation, every index has to be updated on INSERT, UPDATE and DELETE, and unnecessary indexes waste space. In our test, inserting 50,000 rows took 197 ms with only the primary key and 546 ms with four extra indexes (median of 5 runs). The two indexes from this article take 2.5 MB and 3.5 MB next to 7.5 MB of table data, and on hosting that counts toward your disk space.

• Index the columns used in WHERE, JOIN and ORDER BY of frequent queries
• Use composite indexes only when queries use the first column of the index
• Check every slow query with EXPLAIN: type: ALL and Using filesort are warning signs
• Drop indexes that no frequent query uses
• Do not index every column “just in case”

LIKE ‘%…%’ does not use the index

A regular B-tree index helps with LIKE only when the pattern does not start with a wildcard (MySQL documentation). The test on the indexed column name:

SELECT COUNT(*) FROM products WHERE name LIKE 'Product 4242%';  -- type: range, rows: 11
SELECT COUNT(*) FROM products WHERE name LIKE '%4242 red%';     -- type: index, rows: 99824

The prefix search read 11 index entries in 0.11 ms. The “anywhere in the text” search went through all 100,000 index entries and took 22.39 ms, longer than the catalog query with no index at all. For searching inside text, use a FULLTEXT index (MATCH ... AGAINST) or a search plugin.

N+1: 51 queries instead of one

The N+1 problem appears when code loads a list and then runs a separate query for each item. Example: a page with 50 products and the manufacturer name for each. Above is the N+1 version, below the same content with a single JOIN:

SELECT id, manufacturer_id, name FROM products ORDER BY id LIMIT 50;
SELECT name FROM manufacturers WHERE id = ?;   -- x 50

SELECT p.id, p.name, m.name AS manufacturer
FROM products p JOIN manufacturers m ON m.id = p.manufacturer_id
ORDER BY p.id LIMIT 50;

Over a local socket the N+1 version took a median of 5.43 ms and the JOIN 0.29 ms (200 runs). On real hosting each of the 51 queries also pays the round trip to the database server. The limits matter even more: with 500 queries per hour, the N+1 version served 9 page views and the JOIN version 499.

Caching: what works today

You often read that every query goes to disk. That is not quite true: InnoDB keeps frequently used table and index data in memory, in the buffer pool. A repeated query still has to be parsed and executed, though, and every query counts against the limits. A cache in the application saves exactly this work. The options are covered in more detail in our guide to Redis, Memcached and OPcache on hosting.

In WordPress the object cache is not persistent by default: cached data lives only for the duration of one request unless a persistent caching plugin is installed. Without Redis or Memcached, a page cache for anonymous visitors gives the biggest effect; see how to speed up WordPress. Server-side settings such as OPcache, PHP version and compression are covered in server configuration for website speed.

Other common mistakes

1. SELECT * without LIMIT: the query fetches all rows and columns while the page shows 10. Select only the columns you need and limit the number of rows.
2. Files in the database: images, PDFs and large JSON blobs bloat tables and backups. Keep files in the file system and store only the path in the database.
3. Too small a type for AUTO_INCREMENT: TINYINT ends at 127 (255 unsigned), after that inserts fail. In our test the 128th row failed with ERROR 167: Out of range value. WordPress uses BIGINT UNSIGNED for its ID columns.

Monitoring on shared hosting: what you can see

The most important tool is the slow query log. It is off by default, the long_query_time threshold is 10 seconds, and enabling it is a global setting: MariaDB requires SUPER for it, MySQL SYSTEM_VARIABLES_ADMIN. A regular hosting account gets this:

SET GLOBAL slow_query_log = 1;
ERROR 1227: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

So on shared hosting you ask support for an excerpt or measure on the application side. For WordPress, the Query Monitor plugin flags slow, duplicate and failed queries and shows which plugin or theme they come from. Without the PROCESS privilege, SHOW PROCESSLIST shows only your own connections: useful for finding a stuck query, but it gives no picture of the server load. That is part of general hosting and server monitoring.

• Number of queries per page and duplicate queries
• The slowest queries and their EXPLAIN
• Error 1226 and database connection errors
• Database size and the largest tables
• Page response time over time

Symptom and action

At RichHost, websites run on hosting plans with NVMe storage and our own control panel with an AI assistant, with servers in Europe and the USA. The rules above apply there just the same: fewer and lighter queries per page mean faster pages and more headroom below any limit. For WordPress projects, see also the overview of WordPress hosting.

FAQ

How many indexes are too many?

There is no fixed number. Every index speeds up certain reads and slows down every write: in our test, four extra indexes made inserts about 2.8 times slower. Keep the indexes that EXPLAIN actually shows for your frequent queries.

Does OPTIMIZE TABLE speed up the database?

Rarely. For InnoDB, OPTIMIZE TABLE rebuilds the table (ALTER TABLE ... FORCE), updates index statistics and frees unused space. That helps after mass deletions, but it does not replace a missing index.

Can I enable the slow query log on shared hosting?

Usually not by yourself, because it requires SUPER (MariaDB) or SYSTEM_VARIABLES_ADMIN (MySQL). Ask support, or use Query Monitor for WordPress.

Is the query cache worth it?

No. It was removed in MySQL 8.0, it is disabled by default in MariaDB, and according to the documentation it scales poorly on multi-core servers under load. Rely on page and object caching instead.

Which database version does WordPress need?

WordPress recommends MariaDB 10.11 or later, or MySQL 8.0 or later. You can see which version your hosting uses in phpMyAdmin or with the query SELECT VERSION();.