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

Question

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.

Answer 1

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?