symfony2 doctrine query optimalization

Question

Hi this query takes 100ms i think its too much?

SELECT 
  COUNT(
    s0_.id 
  ) AS sclr0, 
  s0_.id AS id1, 
  s0_.status AS status2, 
  ...
  s0_.public AS public22, 
  t1_.username AS username23, 
  t1_.username_canonical AS username_canonical24, 
  ...
  t1_.old_new AS old_new57, 
  t1_.image_name AS image_name58, 
  s0_.user_id AS user_id59 
FROM 
  statuses s0_ 
INNER JOIN 
  users t1_ ON s0_.user_id = t1_.id 
WHERE 
  s0_.time >= ? AND s0_.suggested_status = 1 
GROUP BY 
  t1_.id 
ORDER BY 
  sclr0 DESC 
LIMIT 
  10
Parameters: [1376784000] 
[Display runnable query]
Time: 108.53 ms [  - Explain query ]

And the explanation:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  t1_ index   PRIMARY PRIMARY 4       12132   Using temporary; Using filesort

1   SIMPLE  s0_ ref IDX_4BF01E11A76ED395    IDX_4BF01E11A76ED395    4   db.t1_.id   3   Using where

Am i doing something wrong?

The point of query is select all users whose have posted a status marked as "suggested" and order the users by the count of suggested statuses for each user in last month.

you might consider creating a command that is caching the result for this query and executing the command on a regular basis using a cronjob. then access the cached value in your application. — Nicolai Fröhlich
– Nicolai Fröhlich, Commented Sep 17, 2013 at 21:37

Community · Accepted Answer · 2017-05-23 11:46:30Z

2

Try to add indexes to the table s0_.suggested_status and s0_.time.

ALTER TABLE `statuses` ADD INDEX `suggested_status` (`suggested_status`);
ALTER TABLE `statuses` ADD INDEX `time` (`time`);

http://dev.mysql.com/doc/refman/5.0/en/create-index.html

How do I add indices to MySQL tables?

You can add the indexes to the doctrine annotations (or yaml/xml): http://docs.doctrine-project.org/en/2.0.x/reference/annotations-reference.html#annref-index

edited May 23, 2017 at 11:46

CommunityBot

11 silver badge

answered Sep 17, 2013 at 21:22

Benno Eggnauer

8711 gold badge7 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

symfony2 doctrine query optimalization

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related