SQL optimisation

[Nicholas the Italian]

MySQL is not a standard database and has many inconsistencies.

That might be true, but as HoL says, the vast majority of users do use MySQL, like it or not, so if we were going to optimize basing on some specific dbms, we'd need to concentrate on it.
Optimizing phpBB for PostgreSQL (for general audience) would be like optimizing a generic application (for general audience) for Linux, when 98% of users use Windows: that makes no sense.

Anyway, are you considering creating some kind of MOD, maybe specific to large boards running on PostgreSQL? You might skip the DBAL altogether and rewrite the db interface, and/or single queries.
(Then, the same could be made for other dbms's, MySQL first in line.)
I suppose there are people out there who may be interested.

Another thing I was wondering: why isn't the session table stored in memory? It looks like (although I'm not certain) it's the only table to be continuosly rewritten, it's little, and if data gets lost is not a big problem.

[jmpoure]

Another thing I was wondering: why isn't the session table stored in memory? It looks like (although I'm not certain) it's the only table to be continuosly rewritten, it's little, and if data gets lost is not a big problem.

Because it would require to share data between PHP processes, which is an expansive task for PHP at a large level.

Besides, most databases will cache very accessed data, like the session table.
In PostgreSQL, if you have enough space in shared memory, the whole table will probably be cached.

This is what makes looking at execution times hard.
You never really know to what extent a table is cached in PostgreSQL or not ...
Looking at detailed statistics (like seq_scans) in PostgreSQL statistics views may help.

[Cap'n Refsmmat]

Because it would require to share data between PHP processes, which is an expansive task for PHP at a large level.

Besides, most databases will cache very accessed data, like the session table.
In PostgreSQL, if you have enough space in shared memory, the whole table will probably be cached.

This is what makes looking at execution times hard.
You never really know to what extent a table is cached in PostgreSQL or not ...
Looking at detailed statistics (like seq_scans) in PostgreSQL statistics views may help.

What about a MEMORY table as MySQL provides?

[Highway of Life]

What about a MEMORY table as MySQL provides?

I don’t think it would be good to force a specific type of DB engine, we had some difficulty with that where I worked, where we could not force InnoDB engines due to the MySQL InnoDB license. I don’t know if there is a restriction on Memory engine tables, but there is a possibility of compatibility issues with those.
I would also prefer InnoDB for the benefit of transactional queries, so if we were to create a table type, that would be my vote.

Also wanted to point out that the idea is to make SQL as cross-DBAL compatible as possible. Once you start introducing things like row counts, stored procs, etc, that starts getting rather DB specific. Similarly, a query must be designed with all 10 DBMS' for phpBB3, while I imagine it will be optimised primarily for best performance in MySQL, those rules will also have a positive impact on the other 8 DBMS types (2 are MySQL and MySQLi).
An existing query that pulls 5000+ rows and shows no discernible performance advantage either way may not be adjusted, but future queries will likely be built with a specific format in mind.

Personally, I’m looking forward to queries being built in a build array format as that makes it better to MOD (adding lines instead of editing lines), which will be best for multi-MOD and future upgrade compatibility, aside from the fact that you could potentially hook into said build queries to expand functionality that more. But memory has to be taken into account when placing hooks in those places, as that could potentially kill performance very quickly with such a system.

[jmpoure]

I agree that SQL queries should run on all databases.
But to make it simple, database theory is common to ALL databases, including MySQL.

Database theory teaches us that a LEFT JOIN is always faster than an INNER JOIN.
Why?

Because building an INNER JOIN requires a 2D array and several steps to ensure uniqueness.
On the converse, building a LEFT JOIN requires only a 1D array without uniqueness questions.

Also, a LEFT JOIN is also more explicit.
The parser will try to analyse and optimise the query on more restricted choices.
It has betters chances to succeed.

PostrgeSQL Parser is aware of these issues and will try to rewrite INNER JOINs into LEFT-JOINish queries whenever possible!

Therefore, LEFT JOINs will always be faster.

When using a database like MySQL, you will not have access to this kind of knowledge.
MySQL Query analysis and optimiser are hidden and you don't have access to them.
Therefore, there are more chances that people using MySQL miss important things.

To MySQL users, asking themselves "how to optimise MySQL":
I can only recommand to stay away to MySQL.
MySQL features are "would be features". MySQL team say that these features exist ... but in fact they will be supported "in the future".
A pure example is the InnoDB table, supporting transactions.
Most databases in the world all support transactions.
But no-one is sure that MySQL really support transactions, even in InnoDB tables.

Transactionnal support of MySQL was read in code by PostgreSQL hackers and I can garantee
that people were stuck by the lack of protection of transactions.

I can only summary : stay away from MySQL for any serious board.
Using MySQL is like installing Excel and saying "Look, this is secure data".

[poyntesm]

jmpoure,

When your current user base is most likely >90% (rough guess) using MySQL you have deal with that reality . The fact is that the DBAL supports a large number of DBMS and as such you are unable to fully optimize to one DBMS to the detriment of others.

If you want to make a statement that phpBB group should replace INNER JOINS with LEFT JOINS I would expect you to have tested it on all DBMS that phpBB support. Otherwise you are making the statement while only looking at a part of the picture. While what you say might be true it would need to be verified for all supported environments. I feel from you posts that maybe you do not full get this. I am not doubting you are contributing useful input but please remember phpBB supports more than just PostgreSQL.

If you feel that the performance has areas that could be improved via using DB specific changes then I would recommend you make them available as a MOD. This way the people using that DBMS can benefit.

[Prince of area51]

I agree to poyntesm, its much like developing a website, we all know IE 7 isn't the best browser but we have to deal with it because we all know most of our users will be using that. What I can suggest is MODs for DBMS you are using like if using LEFT JOINs make queries faster in PostgreSQL, than someone can do a simple MOD which changes all the queries for you or better yet, something done at the DBAL-level which changes INNER to LEFT JOINs.

[Acyd Burn]

Database theory teaches us that a LEFT JOIN is always faster than an INNER JOIN.

Even if this holds true it can't be just "replaced". A LEFT JOIN may have an unintended result set, a result set we enforce (on purpose) with the inner join. It comes down to make this decision on a case-by-case (or query-by-query) basis.

[phpBuddy]

Types of abstraction layers

People sometimes say “SQL abstraction layer” or “database interface” fairly loosely, assuming everyone knows what they mean. Not so — I’ve seen at least four distinct meanings in common usage:

1. A software library to connect to a database server and issue queries, fetch results etc.
2. A software library to present a common API to different database servers.
3. A software library to automatically generate portable SQL queries.
4. A software library to map Object-Oriented Programming to a relational database (Object-Relational Mapping, or ORM)

Most libraries will also provide related functionality such as escaping quotes for preventing SQL injection attacks, getting server status, controlling transactions, and so forth.

Each type of interface usually builds upon the types that precede it in my numbering scheme. Each type has different goals, which you have to understand before you can decide what criteria to use when measuring goodness or badness.

The above article gives an interesting overview of ways to adopt databases.
Finally he ends with some personal opinions on this.
As usual there is no 100% best way.
You set to your goals and you adjust to what you think will get you as close as possible.

Two links in this subject:
- to the quoted blog article: Four types of database abstraction layers
- one reference to the above article in another blog:

Abstracting databases
30 August 2007 - 1:03am — Larry Garfield
http://www.garfieldtech.com
http://www.garfieldtech.com/blog/database-abstraction
------
Before we begin, in the interests of disclosure,
my own database experience is about 95% MySQL and 5% MS SQL.
I last touched Oracle back in grad school, very very briefly, and have never worked with DB2.
So, there's my bias.

I am also quite in favor of getting Drupal working on more database backends.
That is not the main focus of my work with PDO (richer API potential is),
but it is a feature that I wan to enable if at all possible.

Regards, phpBuddy

[Oshadha]

My server always worn to optimize my SQL quires how can i do that ,