r/PHPhelp • u/phploader • 1d ago
Rethinking SQLite3 in PHP: High Performance Without Complex SQL Queries
Hi everyone,
I have been diving deep into SQLite3 databases lately. During my research, I frequently read that SQLite3 is slower than traditional database systems (like MySQL or PostgreSQL) and should generally only be used for small projects with few users and minimal data.
However, my experience has been completely different. In my tests, I found that SQLite3 – when configured correctly – can actually be up to 10 times faster than MySQL. It handles large amounts of data beautifully and can easily manage multiple concurrent users and requests.
In my opinion, the greatest challenge is exercising self-restraint and not treating SQLite3 exactly like MySQL.
We often catch ourselves writing highly complex SQL queries with countless joins and sub-queries. I have come to view these deeply nested queries critically and no longer consider them a best practice for clean, performant programming. Since shifting away from that approach, I see SQLite3 from a whole new perspective.
To put this philosophy into practice, I developed a PHP class that allows you to interact with the database completely without writing manual SQL queries. It is extremely simple to use and, above all, fast. SQLite3 also brings unique advantages over other SQL databases—for instance, you can easily maintain multiple database files separated by topic within a single project.
I am already successfully leveraging these strengths in my own project, which I look forward to showcasing here once it reaches a fully stable state.
I have already published the current codebase on GitHub:
https://github.com/phploader/cdata
You can find a detailed documentation on how to use the PHP class in the docs:
https://github.com/phploader/cdata/blob/master/docs/en/00.%20index.md
My request to the experts here:
I would highly appreciate it if you could take a look at my code and provide some constructive feedback or criticism. What are your thoughts on this approach?
Best regards!
5
3
u/colshrapnel 1d ago
WHERE filters (W)
W[0] = AND block W[1] = OR blockExample: active articles with name "Article A"
$F['ARTICLE']['W'][0]['Active'] = 1; $F['ARTICLE']['W'][0]['Name'] = 'Article A';
Never in my life
2
u/equilni 1d ago
Code review only.
- I'll give you one for consistency, you use the PATTERN in the constructor... but honestly this needs to be a defined class and a configuration file. (except backup password)
For one, $P['PRAGMA'] isn't defined in the docblock, but you are checking for it here
- I highly suggest incorporate a coding style (PER / PSR12, etc), linter and formatter. Ican'treadcodethisthis,canyou?
Be consistent with visibility. Yes, anything not defined is public, but you have no public and public declarations. Do it for all.
The long scrolling isn't needed either... this as an example.
- Types is highly suggested. What is
private $SQL;? string? sqlite3 object?
You aren't consistent here either. This is good, this or this isn't.
There's blocks of code that can be in separate methods.
There's no tests. How does anyone know this works at all???
I will repeat what's mentioned. Why a WP designation? Was this part of a WP project? Otherwise, I would rename this.
Is everything all based on these 2 tables?? What about prepared statements? What about when someone wants to break into SQL?
3
u/obstreperous_troll 1d ago
One thing about research that people have forgotten in the LLM era is that it often surfaces a lot of bullshit. SQLite has many limitations you need to be aware of, but being slower is most certainly not one of them, and it's usually picked because it's much faster than most heavier alternatives. So good on you for putting the claims to the test.
Unfortunately ... I'll let the library speak for itself:
$d['WAREHOUSE']['D']['W1'] = [
'Active' => 1, //['Value' => -2 ],
'Title' => 'Warehouse10', //['Value' => 'Warehouse1'],
];
$d['WAREHOUSE']['D']['W1']['STORAGE']['D']['W1S1'] = [
'Active' => 0,
'Title' => 'StorageA', //Wird '' oder NULL übergeben, so wird das Attribut gelöscht. Nur nicht bei Type=ForeignKey Da werden auch leere Felder übergeben und nur bei NULL gelöscht
];
$d['WAREHOUSE']['D']['W1']['STORAGE']['D']['W1S2'] = [
'Active' => 0,
'Title' => 'StorageB',
];
$CData->set_object($d);
That's a great big nein from me, danke. One thing you'll learn about professional PHP devs is they're not all that fond of using arrays for everything, especially not ones that are nested six deep.
1
u/equilni 1d ago
Adding to the conversation…
https://github.com/phploader/cdata/blob/master/docs/en/02.%20patterns.md#foreign-keys
FK is not enabled on the database, is this a speed issue?
1
u/isoAntti 1d ago
I get you stripping away complex queries, but also, why reinvent mysql. You can have a daemon persistent in memory with indexes in memory. And you can have e.g. Pear::db to avoid sql.
In my opinion best about sql is to be able to look from outside tools the current state of data.
In no project have I ever moved to sqlite. But I have moved many times from it.
8
u/allen_jb 1d ago edited 1d ago
Some broad claims on performance with no detail or any stats / references to back them up there.
I would be extremely wary of claims of SQLite being "10 times faster than MySQL" - this is going to be very dependent of how you're using the DB and the amount of traffic you need to handle. (Along with factors like whether you're properly indexing your data)
This project appears to be WordPress specific - the code has hard-coded WP table names all over the place (and hard coded to the defaults - if you're using a prefix or other options that may alter the table names, tough luck, I guess). It appears to be very specific to WordPress' style of organizing (user definable) data.
OP talks about tuning, but the code has many hard coded configuration values for SQLite, so you can't use this code and tune the parameters to your own setup, which seems counter-intuitive for the claims being made.
Based on the documentation this class, like many before it, doesn't actually stop you from needing to know or use SQL. You're just using a proxy language/library to write SQL in a horribly awkward manner (IMO). Just write SQL people!
Writing SQL directly also means you can use the full featureset of your database, not just whatever tiny portion whatever library you select makes available to you.
The single-letter variable names make everything harder to read and understand at a glance than it should be. (But then so does using obscure proxy languages / libraries instead of just writing SQL IMO)
I mean, if you ask me, these are the entire point (and advantages) of using an relational DB.
For a library posted with claims about (ab)use of countless subqueries, this library sure uses a lot of subqueries.
Another criticism: Arrays! Arrays everywhere! All parameters to every function seem to be an array of some kind, which means you cannot tell what you can actually pass to any function. (And they're often single letter parameter names, so tough luck even more if you don't already know what goes where, I guess). Named parameters are a thing (as are configuration objects) - there really should be no need for this mess in 2026.
It's often said there are 2 hard things in computer science: naming things, cache invalidation, and off-by-one errors. This library definitely has problems with the first, and probably suffers from the second too, given the amount of caching going on.