RUEN
Package · mui-data-grid

Dialects & indexes

Of twenty-three operators, twenty are written the same way on any database. The remaining four — the case-insensitive string ones — are written the same way nowhere. This page explains why that choice was pulled out into its own decision, how it is made and what each variant costs.

Why this is a problem at all

contains in MUI DataGrid means “contains, ignoring case”. SQL has no such notion — it has three different ways to express it, and they are not interchangeable:

Database How it is written What ILIKE does
PostgreSQL col ILIKE :v works
MySQL / MariaDB col LIKE :v with a *_ci collation ERROR 1064 — syntax error
SQLite col LIKE :v (ASCII only) syntax error near "ILIKE"

Note the nature of each failure: ILIKE on MySQL is not “less efficient”, it is a query that does not run. Symmetrically, dropping case-insensitivity and always writing LIKE breaks PostgreSQL — but silently. Searching for “winter” stops finding “Winter Internals”, no error is raised, and the user simply concludes no such rows exist.

Hence the design: the spelling cannot be chosen once for everyone. It is a property of the connection, and the grid knows nothing about connections.

Why not “just LIKE”

It is tempting to always write LIKE and declare case a matter for the collation. It doesn’t work, for two reasons.

First, PostgreSQL has no case-insensitive collations in the familiar sense — a non-deterministic ICU collation exists, but it is declared per column or per database and is almost absent from existing schemas. For the vast majority of PostgreSQL projects LIKE stays case-sensitive.

Second, silent degradation is worse than a loud failure. A syntax error on MySQL surfaces on the very first request, in development and in tests. Quietly missing matches on PostgreSQL surface a month later, in a user complaint.

How the decision is made

By default the schema sits at TextMatch::Auto, and MuiGrid::wrap() resolves it once per call, before any condition is built:

text
MuiGrid::wrap($repo, $request, $schema)
│
├─ schema mode = Auto?
│    └─ yes → TextMatch::forRepository($repo)
│               │
│               ├─ $repo->getDbConfigClassName()      the config class name
│               ├─ PpaConnectionPool::getConfigDb()   the config object (from the pool cache)
│               ├─ $config->getDriver()               'pgsql' | 'mysql' | 'sqlite' | …
│               └─ forDriver()                        ILike | Like | Lower
│
└─ build the WHERE with a concrete mode

Three properties of that chain are worth noting.

It opens no connections. The driver is known from the config, not from a live handle. By the time a grid is being built the config is already registered with the pool and sits in its static cache, so resolution costs one array lookup. Even in the worst case — the config has never been touched — the pool constructs the config object and calls its setUp(), but does not connect.

It throws nothing. Any miss — no pool, an unregistered class, a repository double in a test — falls back to TextMatch::Lower, which is valid everywhere. A table page should not fail because a dialect could not be determined.

An unknown driver is not an error. Oracle, MSSQL, a custom DbConfig with an exotic driver all get Lower: lower(col) LIKE lower(:v) is standard SQL and runs anywhere.

Mode precedence

text
the column's mode (if not Auto)
      ↓ otherwise
the schema's mode (if not Auto)
      ↓ otherwise
resolution from the repository's driver
      ↓ failed
TextMatch::Lower

Explicit always beats automatic — detection never overrules the developer.

What each mode costs

Two cases must be separated here: a pattern with a leading %, and a prefix pattern.

contains and endsWith are unindexable in principle. The pattern '%winter%' starts with a wildcard, and a btree index is ordered left to right — with the start of the string unknown, there is nothing to descend the tree by. This holds for all three databases and all three modes. ILIKE, LIKE and lower() make no difference here: any of them is a full scan.

startsWith is the one place where the choice matters:

Mode startsWith Can use an index
Like col LIKE 'x%' yes — a btree on col (in PostgreSQL it needs text_pattern_ops or the C collation)
ILike col ILIKE 'x%' no — never
Lower lower(col) LIKE 'x%' yes — a functional index on lower(col)
sql
-- for the Lower mode
CREATE INDEX idx_articles_title_lower ON articles (lower(title) text_pattern_ops);

-- for the Like mode in PostgreSQL (when case doesn't matter)
CREATE INDEX idx_articles_title_pattern ON articles (title text_pattern_ops);

Counter-intuitively, the mode that looks slowest — Lower — is the only one that makes a case-insensitive prefix search indexable in PostgreSQL at all. If prefix search is a hot path in your table, ->textMatch(TextMatch::Lower) plus a functional index will do more than any other tuning.

When you actually need full-text search

If contains over a large table has become the bottleneck, the right answer is not a different mode but a different tool: pg_trgm with a GIN index in PostgreSQL, FULLTEXT in MySQL, FTS5 in SQLite. You wire it in through filterUsing() for that one column — the library does not stand in the way of substituting any condition you like.

Case folding in two places

The Lower mode folds both sides of the comparison, but with different implementations: the column through SQL lower(), the pattern through PHP’s mb_strtolower().

php
TextMatch::Lower->match('a.title', '%WiNTeR Ünïcode%');
// SQL:   lower(a.title) LIKE :iqb0
// bind:  '%winter ünïcode%'

In the overwhelming majority of cases the two agree. Divergences live at the edges: the Turkish dotless “i”, the German “ß”, the Greek final sigma — their folding rules are locale-dependent, and PHP and the database take them from different sources. If your data is affected, pin the mode explicitly and fold both sides the same way — for example, keep a pre-folded column alongside and declare it as the schema column’s expression.

A separate note on SQLite: there LIKE ignores case for ASCII only, unless built with ICU. So for non-Latin data on SQLite, Lower is not an optimization but a necessity: the Like mode simply won’t find Ünïcode when asked for ünïcode.

The COUNT query

Pagination issues two queries: the page and the total. The second is built by wrapping your SELECT while discarding ORDER BY, LIMIT, OFFSET and FOR. For dialects the practical consequence is this: the filter condition is evaluated twice.

That does not literally double the cost — the planner works with the same predicate both times — but it does mean an expensive column expression (a correlated subquery, a per-row function call) is paid for in both queries. If you don’t need to filter by such an expression, declare the column sortable() only: sorting is discarded from the COUNT query.

Next steps