是否可以在PHP PDO准备语句中设置innodb_stats_on_metadata？

Question

I transfer part of MySQL database from one server to another using XML. In this XML I have all DB related information I need (data & metadata). Everything works fine except one thing – obtaining foreign key constraints from information_schema.

The problem lies in performance. Getting approximately 100 constraints takes up about 7 minutes of PHP script loading! However, if one runs command

SET GLOBAL innodb_stats_on_metadata = 0

with constraint query (i.e. in PHPMyAdmin) like this

SET GLOBAL innodb_stats_on_metadata = 0;
SELECT * FROM information_schema.KEY_COLUMN_USAGE

everything takes up about 5 seconds and it's done. Now, this is a huge timesaver and it would be really great if I could use this in following fashion:

         try{
                $conn = $this->db->_pdo;
                $conn->setAttribute(PDO::ATTR_AUTOCOMMIT, 0); //disable autocommit for transaction consistency
                $conn->beginTransaction();

                $this->db->_pdo->exec('SET GLOBAL innodb_stats_on_metadata = 0');

                $query = $this->db->_pdo->prepare('
                    SELECT * 
                    FROM information_schema.KEY_COLUMN_USAGE 
                    WHERE REFERENCED_TABLE_NAME = ?');
                $query->bindParam(1, $table, PDO::PARAM_STR);
                $query->execute();
                $keys = $query->fetchAll(PDO::FETCH_ASSOC);

                $conn->commit();
                $conn->setAttribute(PDO::ATTR_AUTOCOMMIT, 1);

                if(!empty($keys)) return $keys; else return false;
        }catch (Exception $e){
                $conn->rollback();
                echo 'Caught exception: ',  $e->getMessage(), "
";
                return false;
        }

The problem is that exec command is just ignored here and process time is still very long. If I put that command into prepare (before SELECT and separated by semicolon) everything just ends with an error.

So my question is: How can I set mysqli DB parameter innodb_stats_on_metadata inside PHP PDO structure which uses transactions and prepare statements?

Answer 1

Remove word GLOBAL from the statement. It surely won't let to set GLOBAL state variable for user without root privileges.