EverSQL Database Performance Knowledge Base

Question 1

EverSQL Database Performance Knowledge Base

doctrine2 - use custom query getter

I'm using Symfony2 and Doctrine2. How would I go about using a different query for a get method? (poorly worded, let me explain)

i.e. I have an application entity, this has a one to many relation with Version

so in my application entity I have:

/**
 * Get versions
 *
 * @return Doctrine\Common\Collections\Collection 
 */
public function getVersions()
{
    return $this->versions;
}

This is all good, however, how can I implement a method, such as

getCurrentVersion, where I perform a simple query, such as:

SELECT v.* FROM version WHERE application_id = 1 AND current = 1

and so in a twig template, I can do:

{% for application in applications %}
   Name : {{ application.name }}
   Current Version: {{ application.getCurrentVersion }}
{% endfor %}

Any help is much appreciated.

p.s. if i'm going about this wrong, please enlighten me.

Thanks.

EDIT: http://www.doctrine-project.org/docs/orm/2.1/en/reference/limitations-and-known-issues.html#join-columns-with-non-primary-keys Seriously?!

EDIT, I really don't want to do this, it is unnecessary and resourcefully wasteful:

public function getCurrentVersion
{
   foreach($this->versions as $version)
   {
      if($version->current)
      {
        return $version;
      }

   }
}

Answer

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

The optimization process and recommendations:

Avoid Selecting Unnecessary Columns (query line: 2): Avoid selecting all columns with the '*' wildcard, unless you intend to use them all. Selecting redundant columns may result in unnecessary performance degradation.
Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.

Optimal indexes for this query:

ALTER TABLE `version` ADD INDEX `version_idx_application_id_current` (`application_id`,`current`);

The optimized query:

SELECT
        v.* 
    FROM
        version 
    WHERE
        version.application_id = 1 
        AND version.`current` = 1

Question 2

EverSQL Database Performance Knowledge Base

doctrine2 - use custom query getter

How to optimize this SQL query?

The optimization process and recommendations:

Optimal indexes for this query:

The optimized query:

Related Articles