Answers.Body: <p>I can tell you that both of those are the semi-dynamic element queries that Craft uses to grab elements from the database.</p>
  
  <p>The 2nd one is likely the result of someone calling a <code>.total()</code> or <code>.count()</code> on <code>ElementCriteriaModel</code>.</p>
  
  <p>I see you ran <code>EXPLAIN</code>, but don't see the results from it.  You can use that as a starting point to see what index MySQL chose to execute that query and if it's not ideal.  Craft can't add an index to every <code>content</code> column (for obvious reasons), so if something in your templates is generating a SQL query frequently that causes MySQL to chose an index that isn't ideal and adding an index to content column would solve that for you, then that's something you can certainly do.</p>
  
  <blockquote>
    <p>I understand that the i18n tables are where the locales data is stored. Is there a way to disable a locale without losing any data?</p>
  </blockquote>
  
  <p>No. It either exists or it doesn't.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-04-11T23:36:31.123Z
Answers.LastActivityDate: 2017-05-12T05:24:07.617Z
Answers.LastEditDate: 2017-05-12T05:24:07.617Z
Answers.UserDisplayName: Brad Bell
Body: <p>I'm running a mid sized website (couple hundred articles, between 30-50k visits per day) on a Digital Ocean 16GB / 8 Cores web server and I have two questions:</p>
  
  <p>Most of the time the website runs smoothly, however, several times per day MySQL starts using all of the CPU and I see a spike on disk IO. </p>
  
  <p>Running show process list on MySQL shows that most queries are "Copying to tmp table"  and look like this</p>
  
  <p>I'm new using Craft and I want to know is this queries are normal and if there's a way to optimize them:</p>
  
  <pre><code>    EXPLAIN SELECT `elements`.`id`, `elements`.`type`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_i18n`.`slug`, `elements_i18n`.`uri`, `elements_i18n`.`enabled` AS `localeEnabled`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_airingDate`, `content`.`field_airingFrom`, `content`.`field_airingTo`, `content`.`field_awsPath`, `content`.`field_descripcion`, `content`.`field_descripcionSocialMedia`, `content`.`field_subtitulo`, `content`.`field_texto`, `content`.`field_titulo`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level`
      FROM `elements` `elements`
      JOIN `elements_i18n` `elements_i18n` ON elements_i18n.elementId =         elements.id
      JOIN `content` `content` ON content.elementId = elements.id
      JOIN `entries` `entries` ON entries.id = elements.id
      JOIN `sections` `sections` ON sections.id = entries.sectionId
      LEFT JOIN `structures` `structures` ON structures.id = sections.structureId
      LEFT JOIN `structureelements` `structureelements` ON (structureelements.structureId = structures.id) AND (structureelements.elementId = entries.id)
      WHERE (((((elements_i18n.locale = 'es') AND (content.locale = 'es')) AND (elements.archived = 0)) AND ((elements.enabled = 1) AND (elements_i18n.enabled = 1) AND (entries.postDate &lt;= '2017-04-11 16:56:24') AND ((entries.expiryDate is null) OR (entries.expiryDate &gt; '2017-04-11 16:56:24')))) AND (elements_i18n.enabled = 1)) AND (sections.handle='articulos')
      GROUP BY `elements`.`id`
  </code></pre>
  
  <p>Another example:</p>
  
  <pre><code>    SELECT count(`derivedElementsTable`.`elementsId`)
      FROM (SELECT `elements`.`id` AS `elementsId`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`
      FROM `elements` `elements`
      JOIN `elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
      JOIN `content` `content` ON content.elementId = elements.id
      LEFT JOIN `relations` `sources1` ON (sources1.targetId = elements.id)
      LEFT JOIN `relations` `targets1` ON (targets1.sourceId = elements.id)
      JOIN `entries` `entries` ON entries.id = elements.id
      JOIN `sections` `sections` ON sections.id = entries.sectionId
      LEFT JOIN `structures` `structures` ON structures.id = sections.structureId
      LEFT JOIN `structureelements` `structureelements` ON (structureelements.structureId = structures.id) AND (structureelements.elementId = entries.id)
      WHERE (((((elements_i18n.locale = 'es') AND (content.locale = 'es')) AND (elements.archived = 0)) AND ((elements.enabled = 1) AND (elements_i18n.enabled = 1) AND (entries.postDate &lt;= '2017-04-11 16:56:33') AND ((entries.expiryDate is null) OR (entries.expiryDate &gt; '2017-04-11 16:56:33')))) AND (elements_i18n.enabled = 1)) AND ((sources1.sourceId='175') OR (targets1.targetId='175'))
  </code></pre>
  
  <p>I understand that the i18n tables are where the locales data is stored. Is there a way to disable a locale without losing any data?</p>
  
Tags: <database><performance><mysql>
Title: Poor performance on midsized site / Queries without index?
ThreadText: # Poor performance on midsized site / Queries without index?
  
  (Asked by: username_0 on 2017-04-11)
  
  I'm running a mid sized website (couple hundred articles, between 30-50k visits per day) on a Digital Ocean 16GB / 8 Cores web server and I have two questions:
  
  Most of the time the website runs smoothly, however, several times per day MySQL starts using all of the CPU and I see a spike on disk IO.
  
  Running show process list on MySQL shows that most queries are "Copying to tmp table" and look like this
  
  I'm new using Craft and I want to know is this queries are normal and if there's a way to optimize them:
  
  ```
      EXPLAIN SELECT `elements`.`id`, `elements`.`type`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_i18n`.`slug`, `elements_i18n`.`uri`, `elements_i18n`.`enabled` AS `localeEnabled`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_airingDate`, `content`.`field_airingFrom`, `content`.`field_airingTo`, `content`.`field_awsPath`, `content`.`field_descripcion`, `content`.`field_descripcionSocialMedia`, `content`.`field_subtitulo`, `content`.`field_texto`, `content`.`field_titulo`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level`
      FROM `elements` `elements`
      JOIN `elements_i18n` `elements_i18n` ON elements_i18n.elementId =         elements.id
      JOIN `content` `content` ON content.elementId = elements.id
      JOIN `entries` `entries` ON entries.id = elements.id
      JOIN `sections` `sections` ON sections.id = entries.sectionId
      LEFT JOIN `structures` `structures` ON structures.id = sections.structureId
      LEFT JOIN `structureelements` `structureelements` ON (structureelements.structureId = structures.id) AND (structureelements.elementId = entries.id)
      WHERE (((((elements_i18n.locale = 'es') AND (content.locale = 'es')) AND (elements.archived = 0)) AND ((elements.enabled = 1) AND (elements_i18n.enabled = 1) AND (entries.postDate <= '2017-04-11 16:56:24') AND ((entries.expiryDate is null) OR (entries.expiryDate > '2017-04-11 16:56:24')))) AND (elements_i18n.enabled = 1)) AND (sections.handle='articulos')
      GROUP BY `elements`.`id`
  
  ```
  
  Another example:
  
  ```
      SELECT count(`derivedElementsTable`.`elementsId`)
      FROM (SELECT `elements`.`id` AS `elementsId`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`
      FROM `elements` `elements`
      JOIN `elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
      JOIN `content` `content` ON content.elementId = elements.id
      LEFT JOIN `relations` `sources1` ON (sources1.targetId = elements.id)
      LEFT JOIN `relations` `targets1` ON (targets1.sourceId = elements.id)
      JOIN `entries` `entries` ON entries.id = elements.id
      JOIN `sections` `sections` ON sections.id = entries.sectionId
      LEFT JOIN `structures` `structures` ON structures.id = sections.structureId
      LEFT JOIN `structureelements` `structureelements` ON (structureelements.structureId = structures.id) AND (structureelements.elementId = entries.id)
      WHERE (((((elements_i18n.locale = 'es') AND (content.locale = 'es')) AND (elements.archived = 0)) AND ((elements.enabled = 1) AND (elements_i18n.enabled = 1) AND (entries.postDate <= '2017-04-11 16:56:33') AND ((entries.expiryDate is null) OR (entries.expiryDate > '2017-04-11 16:56:33')))) AND (elements_i18n.enabled = 1)) AND ((sources1.sourceId='175') OR (targets1.targetId='175'))
  
  ```
  
  I understand that the i18n tables are where the locales data is stored. Is there a way to disable a locale without losing any data?
  
  **Tags:** database, performance, mysql
  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_1 on 2017-04-11. Score: 2
  
  I can tell you that both of those are the semi-dynamic element queries that Craft uses to grab elements from the database.
  
  The 2nd one is likely the result of someone calling a `.total()` or `.count()` on `ElementCriteriaModel`.
  
  I see you ran `EXPLAIN`, but don't see the results from it. You can use that as a starting point to see what index MySQL chose to execute that query and if it's not ideal. Craft can't add an index to every `content` column (for obvious reasons), so if something in your templates is generating a SQL query frequently that causes MySQL to chose an index that isn't ideal and adding an index to content column would solve that for you, then that's something you can certainly do.
  
  > I understand that the i18n tables are where the locales data is stored. Is there a way to disable a locale without losing any data?
  
  No. It either exists or it doesn't.
  
  
  ---
  
  