SQL query from php not use mysql cache

Question

The Mysql server is set to cache:

query_cache_size = 1G
query_cache_type = 1

When I execute the query from phpmyadmin the first time the query took 2 sec and the second time 0.0001 sec.

So the cache does work. But in the php script, the query keeps taking 2 sec every time.

php version 5.4.14
mysql 5.5.30

Script:

<?php
    $db = new mysqli("localhost", "user", "pass", "search");
    $t = microtime(true);
    $res = $db-> query("SELECT 
                            i.page_id, 
                            SUM(i.weight) as w 
                        FROM
                            search.search_index i 
                        INNER JOIN 
                            search.word_full w1 
                            ON w1.word=i.word 
                        INNER JOIN 
                            search.word_full w2 
                            ON w1.word_base=w2.word_base 
                        WHERE 
                            w2.word = 'api' 
                        GROUP BY 
                            i.page_id 
                        ORDER BY NULL");
    print($res->num_rows."\n");
    print(microtime(true) - $t);
?>

<?php
    $link = mysql_connect("localhost", "root", "pass");
    mysql_select_db("search");

    $t = microtime(true);
    $res = $db-> query("SELECT 
                            i.page_id, 
                            SUM(i.weight) as w 
                        FROM
                            search.search_index i 
                        INNER JOIN 
                            search.word_full w1 
                            ON w1.word=i.word 
                        INNER JOIN 
                            search.word_full w2 
                            ON w1.word_base=w2.word_base 
                        WHERE 
                            w2.word = 'api' 
                        GROUP BY 
                            i.page_id 
                        ORDER BY NULL");
    print(mysql_num_rows($res)."\n");
    print(microtime(true) - $t);
?>

If I try this:

    $t = microtime(true);
    $res = $db-> query("SELECT 
                            i.page_id, 
                            SUM(i.weight) as w 
                        FROM
                            search.search_index i 
                        INNER JOIN 
                            search.word_full w1 
                            ON w1.word=i.word 
                        INNER JOIN 
                            search.word_full w2 
                            ON w1.word_base=w2.word_base 
                        WHERE 
                            w2.word = 'api' 
                        GROUP BY 
                            i.page_id 
                        ORDER BY NULL");
    print(mysql_num_rows($res)."\n");
    print(microtime(true) - $t."\n");

    $t = microtime(true);
    $res = $db-> query("SELECT 
                            i.page_id, 
                            SUM(i.weight) as w 
                        FROM
                            search.search_index i 
                        INNER JOIN 
                            search.word_full w1 
                            ON w1.word=i.word 
                        INNER JOIN 
                            search.word_full w2 
                            ON w1.word_base=w2.word_base 
                        WHERE 
                            w2.word = 'api' 
                        GROUP BY 
                            i.page_id 
                        ORDER BY NULL");
    print(mysql_num_rows($res)."\n");
    print(microtime(true) - $t."\n");
?>

Script return

235899
1.8554458618164
235899
1.8542320728302

Mysql cache not work too.

Table structure:

CREATE TABLE IF NOT EXISTS `search_index` (
  `page_id` varchar(32) NOT NULL,
  `word` varchar(32) NOT NULL,
  `weight` int(11) NOT NULL,
  PRIMARY KEY (`word`,`page_id`),
  KEY `page_id` (`page_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE IF NOT EXISTS `word_full` (
  `word` varchar(32) NOT NULL,
  `word_base` varchar(32) NOT NULL,
  PRIMARY KEY (`word`,`word_base`),
  UNIQUE KEY `word_base` (`word_base`,`word`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Answer 1

phpmyadmin modify query

SELECT 
    i.page_id, 
    SUM(i.weight) as w 
FROM
    search.search_index i 
INNER JOIN 
    search.word_full w1 
    ON w1.word=i.word 
INNER JOIN 
    search.word_full w2 
    ON w1.word_base=w2.word_base 
WHERE 
    w2.word = 'api' 
GROUP BY 
    i.page_id 
ORDER BY NULL

To

SELECT 
    i.page_id, 
    SUM(i.weight) as w 
FROM
    search.search_index i 
INNER JOIN 
    search.word_full w1 
    ON w1.word=i.word 
INNER JOIN 
    search.word_full w2 
    ON w1.word_base=w2.word_base 
WHERE 
    w2.word = 'api' 
GROUP BY 
    i.page_id 
ORDER BY NULL
LIMIT 0, 30

add LIMIT 0,30 то the end of query.

And size of query > query_cache_limit

I increased query_cache_limit to 10M.

Answer 2

I am assuming here that word_full.word_base and word_full.word are both VARCHAR columns. If this is not the case please provide full table structure including keys for both search_index and word_full tables.

I would

1. add a wordID column to word_full (INT UNSIGNED AUTO_INCREMENT NOT NULL) and make that the Primary KEy
2. add a UNIQUE key on word_full.word
3. change word_base to reference wordID (so now wordID and word_base are both numeric)
4. add a key to word_full.word_base
5. change search_index.word to be search_index.wordID
6. add a key to search_index.wordID

Changing words to run off a numeric key will allow MySQL to use the indexes far more efficiently. At present (I assume) you are joining a varchar column twice, and then searching a varchar column. This is quite inefficient.

Lastly, moving the WHERE into the JOIN would potentially allow it to reduce the rows at a different point and so potentally be more optimal. This gives a final query of

SELECT 
    i.page_id, 
    SUM(i.weight) as w 

FROM search.search_index i 

INNER JOIN search.word_full w1 
    ON w1.wordID = i.wordID

INNER JOIN search.word_full w2 
    ON w1.wordID_base = w2.wordID_base 
    AND w2.word = 'api' 

GROUP BY i.page_id 
ORDER BY NULL

Answer 3

Every call in PHP is a new instance, which is not relevant from the previous one. Unless you explicitly declare it.

Take a look at query caching: http://php.net/manual/en/mysqlnd-qc.quickstart.caching.php