PHP PDO Connection very slow when connecting to Mysql remotely

Local Computer running apache, PHP 7.1. Connecting using PDO
Remote computer running MySQL 5.7

Connecting to MySQL server remotely using PHP PDO connection returns data very slowly. I have tried adding skip_name_resolve in the MySQL my.ini file but does not make a significant difference. The remote computer is connected via OpenVPN but I have also tried on the same network and I get the same speed results.
How can I resolve this issue?
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Ray PaseurConnect With a Mentor Commented:
The remote computer is connected via OpenVPN but I have also tried on the same network and I get the same speed results.
This suggests to me that the observed slow speed results are not caused by the VPN.  You can further eliminate the VPN and other communications pipes as a potential cause by testing FTP or some similar protocol.  If the bandwidth and throughput look good, then the problem is most likely something on the MySQL computer.  Or it may be that too much data is being returned by the queries.

Would we be on firm ground in understanding that the MySQL computer is dedicated to your work and is not also running work from other requests?

Have you investigated the speed of the MySQL queries?  This is where the slow query log can be helpful.  Some ideas...
  • Never use SELECT *; choose columns by name instead
  • Use LIMIT clauses to avoid a table scan
  • Add an index on any column used in WHERE, ORDER, HAVING, GROUP, JOIN, etc
  • Use EXPLAIN SELECT on any query that touches more than one table

You might also consider caching the SELECT query responses.
You said it "returns data" slowly. The skip_name_resolve will only provide a speed benefit during the initial connection and only if there's a problem doing a reverse lookup on your IP address.

1. Provide some specifics (e.g. how many seconds is "very slowly")

2. Describe if it's slow to connect, or slow to query. A slow initial connection will have a very different cause than a slow query.

3. Make sure the slow query log is enabled on the server (you need to restart the MySQL service if you made the changes to the config file) and set to a threshold like 2 seconds or so. Then see if your queries are showing up in that log.

4. If you try connecting using the MySQLi extension, is it any faster?
ido90Author Commented:
I guess this is a limitation of the connection, For one query through the VPN I get 2.9s and on the local PC get 0.052s. I don't see anything special in the logs. It would have been nice to work this way but I guess try to serve through HTTP instead.
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

ido90Author Commented:
Is PDO slower then mysqli?

Do you think there is a faster way then using this:
function __construct()
         try {
               $this->dbh = new PDO('mysql:host=' . MYSQLHOST . ';dbname=' . MYSQLDB, MYSQLUSER, MYSQLPASS, array( PDO::ATTR_PERSISTENT => true ));
         catch (PDOException $e) {
                     echo $e->getMessage();
I wouldn't jump to that conclusion just yet. There are usually 3 entities involved in a query. It USUALLY goes like this for a PHP script that accesses a database:

1. Visitor asks the web server for a PHP page.
RESULT: Network connection from visitor's PC to web server.

2. Web server reads the request, finds the right file for that request, and sends the file to the PHP engine.
RESULT: PHP engine starts and begins processing the file.

3. PHP reaches the line of code that connects to the database and performs the connection request (using the MySQL libraries it has, like MySQLi).
PHP: mysqli_connect("host","user","password","database");
RESULT: Network connection from web/PHP server to the MySQL server.

4. MySQL receives the connection and authenticates the credentials, optionally invoking SSL and/or reverse DNS lookups based on the MySQL config.
RESULT: Connection is established and kept open if everything checks out.

5. PHP continues until it finds the line of code that runs a query and then sends the query to MySQL.
PHP: mysqli_query("SELECT fields FROM table WHERE criteria");
RESULT: Network message sent to the MySQL server.

6. MySQL receives the query, optimizes it, and runs it.
RESULT: Result set built in memory.

7. MySQL sends the finished result set back to PHP.
PHP: The final stage of the mysqli_query() call from step 5.
RESULT: Results are downloaded through the established network connection.

8. PHP receives the results, stores them in memory, and allows code to loop through the results.

Now, a query will take about the same time to run (step 6) regardless of what connection you are on. However, MySQL caches the results so if a query takes 3 seconds to run on the server, then it might take a few milliseconds the next time because it will pull the results from the cache instead of executing the query again. So when you're testing connections, make sure you add "SQL_NO_CACHE" after the "SELECT" portion to bypass the query cache for more accurate testing.

SELECT SQL_NO_CACHE fields FROM table WHERE criteria;

Usually, performance issues are either on:
Steps 3,4 - Connection problems.
Step 6 - Query problems.

Unless the VPN is just EXTREMELY slow for every protocol and every download, step 7 should not be the issue, since it's downloading of data over an established connection.
Regarding PDO vs mysqli, I'm not necessarily concerned about the code itself causing issues, but rather any potential issues with the extension itself. The extension is what actually does all the heavy lifting when connecting to the MySQL server and sending the queries and receiving/managing the results. So if there are any issues with the extensions, it can lead to performance issues that you cannot solve.

So I was just asking you to execute a basic test using both PDO and then MySQLi, to see if there were any differences. Use microtime(true) to trace how long each line takes:

$start = microtime(true);
$db = mysqli_connect("server","user","pw","database");
$end = microtime(true);
$connection_time = ($end - $start);

$start = microtime(true);
$rs = mysqli_query($db, "SELECT SQL_NO_CACHE fields FROM table");
$end = microtime(true);
$query_time = ($end - $start);

echo "Connection Time: {$connection_time}s<br />\n";
echo "Query Time: {$query_time}s<br />\n";

Open in new window

Ray PaseurCommented:
Is PDO slower than mysqli?
Not in my experience.  The differences are sub-second on a query-by-query basis, in other words, so small as to not be noticeable until there is a huge load on the web site.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.