为什么简单的MySQL查询需要超过5秒才能执行？

Update 2011/12/12: Now isolated as FastCGI on my (IIS-based) hosting package. I had them turn it off because a scheduled task kept timing out. I know where I am now. Thanks again, all.

Mark Iliff

Update 2011/12/11: OK, I have to put my hands up to inadvertent misdirection.

Thanks to your many suggestions I've now identified this as a problem with PHP pages on my hosting package, not specifically MySQL. An empty PHP page still takes 5-6 seconds to load, whereas the same page with an ASP or HTML extension loads too fast to measure. I'm taking this up with my hosting provider.

Sorry for not thinking to check this first and thanks for pitching in: much appreciated.

Mark Iliff

I'm relatively new to MySQL/PHP and suspect I'm doing something stupid with the following:

        <?
        // Slave page: block/unblock merchant
        $id = $_POST["id"] ;
        $val = $_POST["val"] ;

        if ( isset( $id ) && isset( $val ) ) {
            $conx = mysqli_connect ( "sql05", $dbAc, $dbPwd, "finewine" ) 
                or die ("Conx failed") ;

            // update record
            $sql = "UPDATE wsMerchants SET 
                blockem = ".$val.", updateDT = '".date( "Y-m-d H:i:s" )."' 
                WHERE id = ".$id.";" ;
            $result = mysqli_query( $conx, $sql ) ;

            //tidy
            mysqli_close( $conx ) ;
            };
        ?>

Result of SHOW CREATE:
CREATE TABLE `wsmerchants` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `merchant` text NOT NULL,
 `country` tinytext NOT NULL,
 `blockem` tinyint(1) DEFAULT '0',
 `benchmark` tinyint(1) DEFAULT '0',
 `createDT` datetime NOT NULL,
 `updateDT` datetime NOT NULL,
 UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=119 DEFAULT CHARSET=utf8

I'm calling this from JQuery (using $.post). The db table has 29 records and 7 fields.

It works, but incredibly slowly.

With sad old Access (+ Classic ASP) queries like this are, for all practical purposes, instantaneous. In MySQL, according to the timer in Firebug, the query takes 5-6 seconds.

I'm running MySQL 5.0.51a + PHP 5.2.13 in a hosted Windows environment.

I've rootled around other questions in here but they mainly seem to involve complex SQL queries.

Other issues aside, I doubt the bottleneck here is the query.

First, try the query from the MySQL console to ensure your database server is ok.

Then, make a simple test script that only connects to the database and does not perform any queries. Make sure you place something after the connect call so that you can see when it finishes connecting.

That's my guess anyway - is that the bottleneck lies in the database connection.

One more thing, is this an AMP stack (WAMP, XAMPP) in Windows, or are you running PHP through IIS?

I don't see anything within that query that would take >5 seconds. Firebug is probably giving you the time of the total request, not just the query. That said if the latency is particularly high between the client and application server or the application server and the database then 5 seconds is not unthinkable. I would start by benchmarking the script to figure out exactly what is taking so long. You can do this by taking timestamps using microtime() throughout the script and then subtracting them to figure out how long each each part of the script is taking.

Judging from the conversation in the comments, it seems like your issue is with the network rather than with your code. My gut tells me it's probably a DNS issue, in which case you may want to look at this section of the MySQL manual. Alternatively, try using an IP address rather than "sql05" in your connection string, see if that speeds things up.