I have an entity called InstagramTag which is very basic and simple:
class InstagramTag
{
/**
* @var integer $id
*
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
protected $id;
/**
* @var string
* @ORM\Column(name="tag", type="string", nullable=true)
*/
protected $tag;
/**
*
* @ORM\OneToMany(targetEntity="App\MainBundle\Entity\InstagramPictureTag", mappedBy="tag")
*/
protected $picturetag;
/**
* Get id
*
* @return integer
*/
public function getId()
{
return $this->id;
}
/**
* Get tag
*
* @return string
*/
public function getTag()
{
return $this->tag;
}
/**
* Set tag
*
* @param string $tag
* @return InstagramTag
*/
public function setTag( $tag)
{
$this->tag = $tag;
return $this;
}
}
and I have this routine that basically check if InstagramTag with a particular tag already exists or not. If it doesn't exist then create one, so the code looks like this:
foreach ($image->tags as $tag) {
$existingTag = $this->em->getRepository('AppMainBundle:InstagramTag')->findOneByTag($tag);
$instaPictureTag = new InstagramPictureTag();
$instaPictureTag->setPicture($instaShopPicture);
if ($existingTag) {
$instaPictureTag->setTag($existingTag);
} else {
$instagramTag = new InstagramTag();
$instagramTag->setTag($tag);
$this->em->persist($instagramTag);
$instaPictureTag->setTag($instagramTag);
}
$this->em->persist($instaPictureTag);
}
The code is relatively slow since the InstagramTag table is of 1.6 million entries. So searching just for 1 tag takes about 1.3 second on MySQL table.
It seems that MySQL is a bottle neck in your case. So what I would advice is not to check if tag exists inside foreach loop because you query mySql each iteration of loop.
Instead I would create custom repository method which will find entities for all tags at once (so you call mySQL only once)
public function getRowsByTags(array $tags)
{
return $this->createQueryBuilder('i')
->andWhere('i.tag IN (:tags)')
->setParameter(':tags', $tags)
->getQuery()
->execute();
}
This way you get collection of InstagramTag entities. Then, inside your foreach loop you need to iterate over this collection (you iterate in memory, without touching your slow mysql), check if your InstagramTag is there. If not, you need to create new one.
Other question here is if you have your tag column indexed properly in mysql as this is quite slow.