Which Is Fastest in PHP- MySQL or MySQLi

MySQLi query vs PHP Array, which is faster?

In my case, as shown on the update part of the question, I think arrays have better performance than mysql databases.

Array usage showed 10 times faster response even when I search through the cells to find desired values in a row. Even good indexing of the table couldn't beat the array functionality and speed.

Which is faster for Large Data Sets? mysql, mysqli or PDO?

mysql shouldn't be used since it's outdated and has been superseded by mysqli. As for the differences between mysqli and PDO, consider these:

Reasons to use mysqli

• Should be slightly faster.
• Easier, especially if you're familiar with the mysql extension, since it is quite similar.

Reasons to use PDO

• Much easier to transition to other databases.
• More features (eg. named parameters in prepared statements, object mapping).

mysqli speed vs php speed?

It really depends on what your doing, something PHP is going to be light speeds faster and other MySQL is going to be. If there are going to be a lot of read/writes from the database I'd generally say MySQL is faster, but if you're doing anything with arrays of the data PHP is most likely going to be faster.

Mysqli query fast enough? Or is there another way?

If the table has really many rows you can add LIMIT 1 (or 2, or 3, etc.)

$db->prepare("SELECT id FROM table WHERE id = ? LIMIT 2");

In this case when 2 rows are found the search ends.

If a field is after WHERE it should be indexed. Just click index in phpMyAdmin for this field.

mysqli vs mysql-performance and usage of mysqli is complicated

If you are planning for high traffic website, then this small
difference in performance may cause problem.

Have you actually tested this out with your code? In many cases it is a negligible difference at best. So I would not worry. Typically these dire warnings of speed differences mean that something that took 1.5 seconds to complete would now take 1.6 seconds.

Besides, PDO adapters are simply the future. That is why they allow for prepared statements via ORM (object-relational mapping) or straight queries (see the example below).

But in mysqli we have to use this something very complicated query for
select/update/delete/insert etc. Is there any way I can use almost
similar code for mysqli as well?

Yes, you can. mysqli has the capabilities to allow you to use pure MySQL queries without using PDO structure:

$db = new mysqli('localhost', 'user', 'pass', 'demo');
$result = $db->query("SELECT * FROM `summary` WHERE id='35'  limit 1");
while($row = $result->fetch_assoc()){
  echo '<pre>';
  print_r($row);
  echo '</pre>';
}

Also, if you are truly worried about MySQL performance, then just run a script like this MySQL Tuning Primer script regularly to performance tune your MySQL install. That will do more to improve performance than obsessing over mysql versus mysqli in PHP.

MySQL performance again MySQLi

The mysql_* functions will be deprecated in PHP 5.5. It is not recommended for writing new code as it will be removed in the future.

With that said, concerns of benchmarks and performance are irrelevant. Furthermore, following the 80/20 rule, optimization should be focused on your queries and DB structure well before code.

Nonetheless, I did run my own simple benchmarks years ago when making the switch. I found mysqli to be more performant than mysql. Moreso when using prepared statements.

As noted by Your Common Sense, performance is also heavily dependent on how you write your code. Be sure you understand the difference in the libraries and which methods to use.

Fastest way to connect with MySQLi?

You don't need a HandlerSocket.

• HandlerSocket is a MySQL plugin that implements a NoSQL protocol for MySQL.
• Allows applications to communicate more directly with MySQL storage engines, without the overhead associated with using SQL.

From the docs:

Once HandlerSocket has been downloaded and installed on your system,
there are two steps required to enable it.

First, add the following lines to the [mysqld] section of your my.cnf
file:

loose_handlersocket_port = 9998
  # the port number to bind to for read requests
loose_handlersocket_port_wr = 9999
  # the port number to bind to for write requests
loose_handlersocket_threads = 16
  # the number of worker threads for read requests
loose_handlersocket_threads_wr = 1
  # the number of worker threads for write requests
open_files_limit = 65535
  # to allow handlersocket to accept many concurrent
  # connections, make open_files_limit as large as
  # possible.

Second, log in to mysql as root, and execute the following query:

mysql> install plugin handlersocket soname 'handlersocket.so';

I agree @Your Common Sense that using a HandlerSocket is NOT even needed. Unless your a major corporation and every second counts.

---

The fastest, normal way to connect

$db = mysqli_connect("localhost","my_user","my_password","my_db");
$db->query("...");

How much speed do you actually need? If you need corporate size speed, I can help you with HandlerSocket. My guess is that you'll be just fine with standard MYSQLi connection.

---

Related Topics