I know it's all about indexes and structures but even when that is already done well. What schould I use by then?
Mysql, mysqli, pdo or postgresql?
I was using mysql but then people said mysqli was faster. Than people said choose mysqli for speed and pdo for functionallity now they are saying choose pdo it's also faster. So what is really the fastest? and again I know it's all about structure your database and index it but let's say that is already totally right.
MySQLi is still MySQL, it's just an improved API for accessing it. It *might* have some performance improvements in terms of the API code, but it will not affect how fast the actual queries execute. (You may get some improvements by using prepared statements in MySQLi, but that can be situation-dependent.) Likewise, PDO is a DBMS-agnostic interface that allows you to use many different DBMS's via the same interface, and as such won't affect how fast your queries execute.
So from your post, as far as DBMS's go, you're talking about MySQL or PostgreSQL. From what I've read, current versions are pretty comparable speed-wise when you use MyISAM table types in MySQL, but then you miss out on some features, notably transactions.
These days, a popular approach when data speed becomes an issue is a "No SQL" solution, such as MongoDB; though there are plenty of reasons to consider the pros and cons before jumping to such a solution.
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
That's how I see it. You can do 100 different things to improve database performance. It can't be explained in 10,000 characters or less, which makes a forum post a bad idea.
The speed can be determined by:
how well the queries take advantage of the data structures
.. things I missed ...
If the network is REALLY slow then it may not be worth fetching raw data from MySQL. Instead, it might be faster to have MySQL transform the data and return you a smaller result. The raw data could be processed in MySQL or the application layer, it just happens to be faster overall to run it through a stored procedure in MySQL than it is to fetch the raw data and transform it on the application layer.
So it's not so strait forward, there are a lot of issues like this. Logs are great for integrity, binary log, slow query log, query log, etc etc. But it takes time to write that all to disk.
For whatever it's worth, I was told by someone that a single disk strike (reading data from the disk) is comparable to ~1,000,000 operations in the CPU. So if you're reading from disk, you might consider trying to find ways to not read from disk. That's part of what databases are trying to minimise. The B-Tree (balanced tree) is a resilient data structure for this, because if you can search using a TINY little index, then you can find out where the correct place on disk to strike is very fast. You can make GIANT balanced tress with billions of entries. The index/btree can add another row of nodes with exponentially more data than the last row. So as the maximum time to compare and traverse to any given node on a btree goes up in a linear fashion, the amount of data you can store goes up in an exponential fashion. I say "fashion", because there are certain limitations again, such as hardware. The index may not fit in memory, etc... it's not some perfect function.
If you want to store a crap ton of data and it's obvious you will need to strike the disk to access it (5TB of data), use a database. They are designed for this.
If you only have a tiny amount of data that can fit into memory, then the fastest option is to not use a database. You would only be slowing yourself down. There exists a myriad of options for storing data. MySQL is just one of them.
So what is really the fastest?
Back to your question about mysqli mysql_* and PDO... I use PDO, and in certain situations it is incredibly faster than mysql_* functions. But in general, they are all perfectly fine. I would hate for you to miss out on some of the really cool functionality in PDO.
I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;
It depends on the complexity of your SQL queries.
If you use SQL queries with complicated joins than PostgreSQL will give you faster performance than MySQL on average.
If you have just simple SELECT and UPDATE queries on one or two tables than MySQL is your best choice.
To go to the extreme use MySQL 4 - it is faster.
If I may, I'd like to point out that the question was about the speed of the database. So I guess we should isolate the answer to the database architecture. I guess there is a misconception that PDO and MySQLi is a database. It is technically not. To answer the question it is based on the configuration of the database.
E.g. In MSSQL the leaf size is 8MB. Meaning each node of the B-Tree will have 8MB of data in it before it creates another child. In MySQL you can increase this by changing the block size. In MyISAM engines modify the .myi. In postgresql this is also configurable. In fact in postgresql you can even query the nodes directly. However, given same configuration there is no benchmark as to which is faster between them as that would require a low level analysis.