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;

As I mentioned in my answer, if you are able to give us the table structures including keys then it will help. It is most likely that your column types or keys are not suitable. — Simon at The Access Group
– Simon at The Access Group, Commented Apr 19, 2013 at 13:47

Ivan Yonkov · Accepted Answer · 2013-04-19 11:50:21Z

2

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

answered Apr 19, 2013 at 11:50

Ivan Yonkov

7,0291 gold badge20 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Simon at The Access Group Over a year ago

I assume he is potentially referring to the mysql internal query caching potentially

evzzz Over a year ago

I test mysqlnd-qc yesterday. Not working too. And phpmyadmin not work thich mysqlnd

Ivan Yonkov Over a year ago

Used the constant in the query aswell?

Simon at The Access Group · Accepted Answer · 2013-04-19 11:58:49Z

0

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

add a wordID column to word_full (INT UNSIGNED AUTO_INCREMENT NOT NULL) and make that the Primary KEy
add a UNIQUE key on word_full.word
change word_base to reference wordID (so now wordID and word_base are both numeric)
add a key to word_full.word_base
change search_index.word to be search_index.wordID
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

answered Apr 19, 2013 at 11:58

Simon at The Access Group

7,0251 gold badge25 silver badges47 bronze badges

1 Comment

evzzz Over a year ago

Query work good. Mysql used if I use phpmyadmin and mysql from console.

evzzz · Accepted Answer · 2013-04-22 07:07:50Z

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.

Collectives™ on Stack Overflow

SQL query from php not use mysql cache

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related