PHP PDO Connection very slow when connecting to Mysql remotely

Posted on 2016-07-26
Last Modified: 2016-08-10
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?
Question by:ido90
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 35

Expert Comment

ID: 41730520
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?
LVL 110

Accepted Solution

Ray Paseur earned 500 total points
ID: 41730958
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.

Author Comment

ID: 41731153
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.
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.


Author Comment

ID: 41731382
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();
LVL 35

Expert Comment

ID: 41731388
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.
LVL 35

Expert Comment

ID: 41731407
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

LVL 110

Expert Comment

by:Ray Paseur
ID: 41731735
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.

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Part of the Global Positioning System A geocode ( is the major subset of a GPS coordinate (, the other parts being the altitude and t…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question