0

I'm on local machine. I only get that error when loading the page the first time after an insert|create|delete. Then it loads very fast. Then again if I modify the database, the first time(sometimes the second time) when accessing the page i get that error. I don't get it.

Why do i get that error only the first time when I access the site after modifying the database?

$sth = $this->dbh->prepare("SELECT g.t_tree_c_parent AS gp ,h.t_tree_c_parent AS hp
                FROM t_tree a INNER JOIN (t_data b, t_data c, t_tree d, t_data e, t_data f, t_tree g, t_tree h, t_tree i) 
                ON a.t_tree_c_child=b.t_data_c_space
                AND b.t_data_c_object=c.t_data_c_object
                AND c.t_data_c_space=d.t_tree_c_child
                AND d.t_tree_c_parent=e.t_data_c_object
                AND e.t_data_c_space=f.t_data_c_object
                AND f.t_data_c_space=g.t_tree_c_child
                AND g.t_tree_c_parent=h.t_tree_c_child
                AND e.t_data_c_space=i.t_tree_c_child
                AND i.t_tree_c_parent=?
                WHERE a.t_tree_c_child=?");
$sth->execute(array($this->glob['children'], $child)); //  <- LINE:183

I will split that query to test out. But I'm asking maybe I'm missing something.

UPDATE asked by @jcho360:

CREATE TABLE `t_data` (
`t_data_c_space` VARCHAR(50) NOT NULL DEFAULT '0.00000000000',
`t_data_c_object` VARCHAR(50) NULL DEFAULT NULL,
`t_data_c_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`t_data_c_space`)
 )
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

CREATE TABLE `t_tree` (
`t_tree_c_parent` VARCHAR(50) NOT NULL,
`t_tree_c_child` VARCHAR(50) NOT NULL,
`t_tree_c_timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

In HeidiSQL for a child(in project this query is in a recursive function):

2.137 sec the first time after a small update in db then 0.000 sec

I start to think that is the mysql caching, sugested by Leandro Barreto.

Alqin
  • 1,305
  • 3
  • 16
  • 35

1 Answers1

1

I will give you a wild guess here: Database caching.

On the first time you execute the page, the query is not cached in the database, so, it takes longer, thus, expiring PHP's time limit.

On the other times, the query is much faster.

You can either split the query, as you mentioned, or you could try increasing the time limit on the script:

set_time_limit(0); //No time limit
set_time_limit(60); //60 seconds

To be sure, you could use this to clean database cache and use it always sou you will always run the page on clean cache.

Community
  • 1
  • 1
Leandro Barreto
  • 361
  • 2
  • 18
  • I imagined that it will be faster if I combined as much as possible in one query. So now I have to split the query to get faster results? – Alqin Nov 29 '12 at 14:11
  • Well, I don't think you are wrong. Usually, combining into one larger query gives you faster results. If your database is really large, or the server you are running MySQL bad, splitting the query won't do much good. I guess you could split the inner join statement. You are running a cartesian join first (I think...) – Leandro Barreto Nov 29 '12 at 14:16
  • Also your "cartesian join" guess made me reconsider how I will build my queries from now on. Thanks! – Alqin Nov 29 '12 at 14:46