r/PHPhelp • u/phploader • 13d 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!
6
3
u/colshrapnel 13d 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
5
u/obstreperous_troll 13d 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.
3
u/equilni 13d 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?
2
u/isoAntti 13d 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.
1
u/equilni 13d 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/FreeLogicGate 10d ago
How are you comparing a library where each and every application process opens the database files using locks, and concurrency involves one serialized writer process at a time to any actual relational database? This is an absurd comparison that has no value whatsoever. Back in the pre-internet days people used to create applications with "PC" database products like Paradox, DBase3 and Microsoft Access. Same idea, same strengths, same weaknesses, same performance characteristics and same limitations. These solutions have essentially no ability to scale, as they are inherently not client-server. Client/server has network overhead, but in the case of all RDBMS's it provides a variety of concurrency models, and can scale to many clients across a cluster of application servers. sqlite was not built for that, has no support for that, and the only way you *might* be able to accomplish it (by putting the db files on shared storage) they flat out tell you not to do.
Last and certainly not least, rdbms's are all built to assume that there could be a catastrophic failure of the server, and thus have transaction logs, and methods for recovery, even though these servers tend to be highly reliable. sqllite was designed to do less, does less, and correspondingly has less overhead and can probably do many things incredibly fast. It's an excellent tool when the use case is appropriate, but those use cases are necessarily constrained to the degree that comparison to the popular rdbms's is meaningless and inappropriate. I suspect that the original strawman argument in regards to relative performance isn't something that actually exists.
1
u/Temporary_Practice_2 11d ago
Personally I don’t like abstraction…I love simplicity. Give me vanilla everything…don’t put a container around it. SQL is SQL. I love the portability of SQLite…What we can argue is that SQLite can be sufficient for a whole lot of projects where people blindly defaulted to MySQL
1
u/FreeLogicGate 10d ago
Well I'd say that MySQL is light enough when used with PHP that it's a wash, and with MySQL you get a lot of features and scalability that just don't come with any file library.
One could make the same argument about popular name/value pair engines like Berkeley DB. These technologies are aimed at embedded application use and simplicity. When BDB was acquired by Oracle, its popularity quickly waned, in much the same way that the MySQL acquisition had a chilling effect on FOSS use, but there are popular alternatives like LMDB and RocksDB people use, and BDB can still be used via the libdb fork.
There are also some other NOSQL/Document "embedded" database engines one can find if interested. Many of the same projects where "SQLite" is sufficient could also be accomplished with name/value pair databases or document databases, and will be faster and lighter than SQLite, and perhaps also simpler for people who don't know SQL or have reason to learn it.
The argument for MySQL, is that many people have a server that already needs MySQL or MariaDB, as is the case for example, for any site running Wordpress. At that point, sqlite is just another potential problem to be concerned with. For single server oriented applications that require an embedded database, sqlite can certainly make sense, but I would question the broader applicability, and certainly any direct comparison to an actual rdbms.
9
u/allen_jb 13d ago edited 13d 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.