doctrine-orm - Symfony & Doctrine 2/加速查询

Question

加入3张表后查询速度有问题。之前速度是120-500ms。现在是1500-5000ms。可以加快我的查询速度吗？

这是我的查询构建器:

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();
}

这里是格式化查询:

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

谢谢

编辑:产品型号:

/**
     * @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 模型:

/**
     * @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;

模型模型:

/**
     * @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;

类别模型:

/**
     * @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;

Answer 1

不需要一个 Join。您加入类别表只是为了过滤 id，但您可以使用 IDENTITY 执行此操作，这对于引用 FK 列非常有帮助。您的查询应该像这样更快:

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();
}