CODEWITHSUNDEEP
doctrine-ormsymfony
user3703456
user3703456

Reputation:

Symfony & Doctrine 2 / Speed up query

I have a problem with query speed after I joined 3 tables. Before speed was 120-500ms. Now it's 1500-5000ms. It's possible to speed up my query?

Here is my query builder:

public function findByCategory($category)
{
    $qb = $this->createQueryBuilder('p');
    $qb->select('p');
    $qb->leftJoin('p.details', 'd');
    $qb->leftJoin('p.model', 'm');
    $qb->leftJoin('m.category', 'c');
    $qb->where('c.id = :category');
    $qb->andWhere('d.quantity > 0');
    $qb->setParameter('category', $category);
    $qb->addOrderBy('p.id', 'DESC');
    $qb->setMaxResults(10);

    return $qb->getQuery()->useQueryCache(true)->useResultCache(true, 31536000, uniqid())->getResult();
}

and here is formatted query:

SELECT 
  p0_.id AS id_0, 
  p0_.title AS title_1, 
  p0_.url AS url_2, 
  p0_.description AS description_3, 
  p0_.views AS views_4, 
  p0_.price AS price_5,  
  p0_.is_active AS is_active_6, 
  p0_.created_at AS created_at_7, 
  p0_.updated_at AS updated_at_8, 
  p0_.model_id AS model_id_9, 
  p0_.details_id AS details_id_10 
FROM 
  products p0_ 
  LEFT JOIN product_details p1_ ON p0_.details_id = p1_.id 
  LEFT JOIN models m2_ ON p0_.model_id = m2_.id 
  LEFT JOIN categories c3_ ON m2_.category_id = c3_.id 
WHERE 
  c3_.id = ? 
  AND p1_.`quantity` > 0 
ORDER BY 
  p0_.id DESC 
LIMIT 
  10

Thanks

EDIT: Products model:

/**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="title", type="string", length=255)
     */
    private $title;

    /**
     * @var string
     *
     * @ORM\Column(name="url", type="string", length=255)
     */
    private $url;

    /**
     * @var string
     *
     * @ORM\Column(name="description", type="text", nullable=true)
     */
    private $description;

    /**
     * @var int
     *
     * @ORM\Column(name="views", type="bigint", nullable=true)
     */
    private $views;

    /**
     * @ORM\ManyToOne(targetEntity="Models")
     * @ORM\JoinColumn(name="model_id", referencedColumnName="id")
     */
    private $model;

    /**
     * @ORM\OneToOne(targetEntity="ProductDetails", cascade={"persist"})
     * @ORM\JoinColumn(name="details_id", referencedColumnName="id")
     */
    private $details;

    /**
     * @var File
     *
     * @ORM\OneToMany(targetEntity="ProductImages", mappedBy="product", cascade={"persist"})
     *
     */
    private $images;

    /**
     * @var File
     *
     * @ORM\OneToMany(targetEntity="Cart", mappedBy="productId", cascade={"persist"})
     *
     */
    private $cart;

    /**
     * @var string
     *
     * @ORM\Column(name="price", type="integer", length=255)
     */
    private $price;

    /**
     * @var bool
     *
     * @ORM\Column(name="is_active", type="boolean")
     */
    private $isActive;

    /**
     * created Time/Date
     *
     * @var \DateTime
     *
     * @ORM\Column(name="created_at", type="datetime", nullable=false)
     */
    protected $createdAt;

    /**
     * updated Time/Date
     *
     * @var \DateTime
     *
     * @ORM\Column(name="updated_at", type="datetime", nullable=false)
     */
    protected $updatedAt;

ProductDetail model:

/**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="`processor`", type="string", length=255, nullable=true)
     */
    private $processor;

    /**
     * @var string
     *
     * @ORM\Column(name="`graphic_card`", type="string", length=255, nullable=true)
     */
    private $graphicCard;

    /**
     * @var string
     *
     * @ORM\Column(name="`release_year`", type="string", length=4, nullable=true)
     */
    private $releaseYear;

    /**
     * @var string
     *
     * @ORM\Column(name="`ram`", type="string", length=255, nullable=true)
     */
    private $ram;

    /**
     * @var string
     *
     * @ORM\Column(name="`ssd`", type="string", length=255, nullable=true)
     */
    private $ssd;

    /**
     * @var string
     *
     * @ORM\Column(name="`hdd`", type="string", length=255, nullable=true)
     */
    private $hdd;

    /**
     * @var string
     *
     * @ORM\Column(name="`battery`", type="string", length=255, nullable=true)
     */
    private $battery;

    /**
     * @var string
     *
     * @ORM\Column(name="`color`", type="string", length=255, nullable=true)
     */
    private $color;

    /**
     * @var string
     *
     * @ORM\Column(name="`accessories`", type="string", length=255, nullable=true)
     */
    private $accessories;

    /**
     * @var string
     *
     * @ORM\Column(name="`guarantee`", type="string", length=255, nullable=true)
     */
    private $guarantee;

    /**
     * @var string
     *
     * @ORM\Column(name="`condition`", type="string", length=255)
     */
    private $condition;

    /**
     * @var string
     *
     * @ORM\Column(name="`quantity`", type="string", length=255)
     */
    private $quantity;

Models model:

/**
     * @var integer
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\ManyToOne(targetEntity="Categories")
     * @ORM\JoinColumn(name="category_id", referencedColumnName="id")
     */
    private $category;

    /**
     * @var string
     *
     * @ORM\Column(name="name", type="string", length=255)
     */
    private $name;

    /**
     * @var string
     *
     * @ORM\Column(name="url", type="string", length=255)
     */
    private $url;

    /**
     * @var string
     *
     * @ORM\Column(name="image", type="string", length=255)
     */
    private $image;

Categories Model:

/**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @var string
     *
     * @ORM\Column(name="title", type="string", length=50)
     */
    private $title;

    /**
     * @var string
     *
     * @ORM\Column(name="url", type="string", length=255)
     */
    private $url;

    /**
     * @var string
     *
     * @ORM\Column(name="description", type="string", length=140)
     */
    private $description;

    /**
     * @var string
     *
     * @ORM\Column(name="image", type="string", length=100)
     */
    private $image;

    /**
     * @var bool
     *
     * @ORM\Column(name="is_active", type="boolean")
     */
    private $isActive;

    /**
     * created Time/Date
     *
     * @var \DateTime
     *
     * @ORM\Column(name="created_at", type="datetime", nullable=false)
     */
    protected $createdAt;

    /**
     * updated Time/Date
     *
     * @var \DateTime
     *
     * @ORM\Column(name="updated_at", type="datetime", nullable=false)
     */
    protected $updatedAt;

Upvotes: 3

Views: 477

Answers (1)

glerendegui
glerendegui

Reputation: 1507

One Join is not necessary. You are joining the category table just for filter the id, but you can do that with IDENTITY, which is really helpful to reference the FK column. Your query should be faster like this:

public function findByCategory($category)
{
    $qb = $this->createQueryBuilder('p');
    $qb->select('p');
    $qb->leftJoin('p.details', 'd');
    $qb->leftJoin('p.model', 'm');
   // $qb->leftJoin('m.category', 'c'); // this don't go anymore
    $qb->where('IDENTITY(m.category) = :category');
    $qb->andWhere('d.quantity > 0');
    $qb->setParameter('category', $category);
    $qb->addOrderBy('p.id', 'DESC');
    $qb->setMaxResults(10);

    return $qb->getQuery()->useQueryCache(true)->useResultCache(true, 31536000, uniqid())->getResult();
}

Upvotes: 1

Related Questions