0

I'm finally switching to mysqli.

However I have spotted a significant performance difference.

I have a script which makes about 25.000 queries. The Script takes 15 seconds with mysqli and mysqlnd as driver And 10 seconds with MySQL and the old MySQL client library.

The raw SQL time is 7.7 seconds on both scenarios.

It has to be the connection time between PHP and MySQL that names the difference.

Is mysqli slower than the old mysql?

I have located the performance difference in the connection process.

I log the time in these to scripts. And the time difference is about 4 seconds

function connect() {
    $time_start = $this->microtime_float();
        $this->link = mysql_connect($GLOBALS['DB_HOST'], $GLOBALS['DB_USER'], $GLOBALS['DB_PASS'])
        or die ("Could not connect to DB\n");

        mysql_select_db($GLOBALS['DB_NAME'])
        or die("Could not select ".$GLOBALS['DB_NAME']."\n");
    $time_end = $this->microtime_float();   
    $_SESSION["MySQLTimeConnection"] += ($time_end - $time_start);
}







function connect() {
    $time_start = $this->microtime_float();
    $this->link = mysqli_connect($GLOBALS['DB_HOST'], $GLOBALS['DB_USER'], $GLOBALS['DB_PASS'],$GLOBALS['DB_NAME'])
        or die ("Kunne ikke få forbindelse til databasen ".$GLOBALS['DB_NAME']."\n");
    $time_end = $this->microtime_float();   
    $_SESSION["MySQLTimeConnection"] += ($time_end - $time_start);
}
ClausRasmussen
  • 1
  • 3
  • 6
    You have a script which makes 25000 queries?!?! I think you need to seriously reconsider your application design. – eggyal Sep 03 '15 at 12:23
  • 3
    Too much information is missing - you didn't include what types of queries you're running (`insert`, `select`, `update`, `delete`, a mix of them etc) and what the connection parameters are, the SAPI you use (`mod_php`, `php-fpm`, `CLI`). You can't just replace mysql_ with mysqli_ and expect something better out of the blue, it's not a drop-in replacement. If you expand the question with more info and *why* you're doing 25k queries - we might be able to help you boost it up or configure better. – Mjh Sep 03 '15 at 12:25
  • @eggyal I'll just assume this is not a *website* he's talking about, but some command line utility script for database maintenance/importing/exporting etc... Not entirely unreasonable, depending on what exactly needs to be done. – deceze Sep 03 '15 at 13:05
  • Sorry about my poor description. It is a website. But the 25.000 queries is just for pinpointing the problem and to blow it up in a larger scale. – ClausRasmussen Sep 03 '15 at 13:11
  • 2
    Unless I've misinterpreted your question, what you are showing is that the initial connection (which you would surely only need to do once on each request, regardless of number of queries) is taking about a fifth of millisecond longer with `mysqli`. – Eborbob Sep 03 '15 at 13:40

1 Answers1

0

Yes, for standard queries mysqli is often slower than the old mysql functions in PHP.

See http://af-design.com/blog/2009/01/30/php-mysql-vs-mysqli-database-access-metrics/ for one such benchmark.

There are two salient points though:

1 - mysql set of functions is deprecated and likely to be unavailable at some point so performance is not really an issue.

2 - In your example of 25,000 queries you could probably change your code to use prepared statements (which you can't do in mysql) and make significant savings. On the smaller scale of 1 or 2 queries per page prepared statements probably wouldn't help performance, but at the same time the performance loss you are seeing from mysqli probably isn't significant anyway.

Eborbob
  • 1,905
  • 1
  • 15
  • 30
  • I’m definitely gonna look into to prepared statements, but for now I’m just wondering why the connection is so much slower. As @Mjh pointed out I can’t expect it to work as a drop in replacement. Is there some config issues that I haven't been aware of? – ClausRasmussen Sep 03 '15 at 13:34
  • 2
    Your question suggest that it is taking `mysqli` 0.00016 seconds longer to make the connection than `mysql` - is that really so much slower? – Eborbob Sep 03 '15 at 13:42