My doctrine is Really Slow. Simple query and one second to get the result

Question

Here is my setup:
Windows Server 2008 R2
MySql 5.1.562
Php 5.3.2
Doctrine 1.2

Anybody have an Idea why my query is taking about one second to perform a simple query.

echo date("Y-m-d H:i:s", time()) ."::::::" . microtime(true)."<br />";

             $q = Doctrine_Query::create()
            ->from("Ordering")
            ->where("client_id = ?",array($_SESSION["UserID"]));

            $ResProduct = $q->execute();

echo date("Y-m-d H:i:s", time()) ."::::::" . microtime(true)."<br />";

Here is the result of the 2 echo to show you how long it's take to perform the query.

2011-04-21 01:48:24::::::1303364904.8051
2011-04-21 01:48:25::::::1303364905.8418

An other thing, there is no data in the database.

Edit
I Perform the query directly in the mysql console and get the result very quickly

mysql> select * from Ordering where client_id = 2;
+----+------------+-------+------+-----------+
| id | product_id | price | qty  | client_id |
+----+------------+-------+------+-----------+
|  7 |          1 |  0.89 |   20 |         2 |
+----+------------+-------+------+-----------+
1 row in set  (0.00 sec)

I don't see why you use `microtime_float()` on php 5.3.2. Use `microtime(true)`. — Vladislav Rastrusny, Apr 21 '11 at 06:04

score 2 · Accepted Answer · answered Apr 21 '11 at 06:09

2

Use microtime(true) instead of microtime_float()
Configure MySQL not to resolve IP's of connecting clients: http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_skip-name-resolve
client_id = ?- why do you cast $_SESSION["UserID"] to array? I think this is unnecessary.

answered Apr 21 '11 at 06:09

Vladislav Rastrusny

29,378
23
95
156

The guys here is a Master :) Thanks a lot @FractalizeR. You save my Night. Many Thanks. 39 MS executing the Same query but with skip-name-resolve skip-host-cache in the my.ini file. – Jean-Francois Apr 21 '11 at 06:40

score 0 · Answer 2 · answered Apr 21 '11 at 05:59

0

First of all, your code doesn't seem wrong or anything.

Basically, though, your SQL query will typically look like this :

select *
from Ordering
where client_id = 123456

Which means that setting an index on the client_id column should probably help -- a lot, if there are many rows in that table.

A couple of relevant links, about that :

answered Apr 21 '11 at 05:59

Pascal MARTIN

395,085
80
655
663

I already have index on client_id. And as I said in my post, there are no data in the database yet. – Jean-Francois Apr 21 '11 at 06:05

score 0 · Answer 3 · answered Apr 21 '11 at 06:12

Just out of curiosity have you attempted a RawSQL Query? We had an issue similar to this and it ended up being a DNS issue. Even though everything was supposed to be localhost the apache server was going external to resolve the address to mysql for some reason.

My doctrine is Really Slow. Simple query and one second to get the result

3 Answers3

Linked