Databases
StackOverflow2013
Postsdbo
POMySQL performance in Ruby on Rails
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL performance in Ruby on Rails
    primarykey
    data
    text
    <p>I'm currently experiencing some performance issues with MySQL and trying to come up with a solution. I've already added some indexes to various tables and it seems to have shaven off several hundreds of ms from the query length, but I was wondering if the following could be optimized:</p> <p>The code responsible for this is pretty big to post below, but in general:</p> <ul> <li>A CV has many desired_occupation (= Occupation model)</li> <li>A CV has many past_occupations (= Occupation model)</li> <li>A CV has many occupational_skills (= Skill model)</li> <li>A CV has many educational_skills (= Skill model)</li> <li>An Occupation has many skills</li> <li>An Occupation belongs to a Concept</li> <li>A Skill belongs to a Concept</li> <li>A Concept has many and belongs to a Concept</li> </ul> <p>I know it's a bit tricky to do this without the models, but the post has a limit on the amount of characters. Most of the queries in the log look like this:</p> <pre><code> Language Load (0.0ms) SELECT `languages`.* FROM `languages` WHERE `languages`.`code` = 'en' LIMIT 1 Skill Load (1.0ms) SELECT `skills`.* FROM `skills` INNER JOIN `occupation_skills` ON `skills`.id = `occupation_skills`.skill_id WHERE ((`occupation_skills`.occupation_id = 156)) Concept Load (1.0ms) SELECT `concepts`.* FROM `concepts` WHERE `concepts`.`id` = 10 LIMIT 1 ConceptLabel Load (1.0ms) SELECT `concept_labels`.* FROM `concept_labels` INNER JOIN `labels` ON `labels`.`id` = `concept_labels`.`label_id` WHERE `concept_labels`.`concept_id` = 10 AND `labels`.`language_id` = 1 LIMIT 1 Label Load (1.0ms) SELECT `labels`.* FROM `labels` WHERE `labels`.`id` = 5432 LIMIT 1 </code></pre> <p><strong>Some of these come directly from the cache like so:</strong></p> <pre><code> CACHE (0.0ms) SELECT `concept_labels`.* FROM `concept_labels` INNER JOIN `labels` ON `labels`.`id` = `concept_labels`.`label_id` WHERE `concept_labels`.`concept_id` = 10 AND `labels`.`language_id` = 1 LIMIT 1 CACHE (0.0ms) SELECT `labels`.* FROM `labels` WHERE `labels`.`id` = 5432 LIMIT 1 CACHE (0.0ms) SELECT `concept_labels`.* FROM `concept_labels` INNER JOIN `labels` ON `labels`.`id` = `concept_labels`.`label_id` WHERE `concept_labels`.`concept_id` = 10 AND `labels`.`language_id` = 1 LIMIT 1 CACHE (0.0ms) SELECT `labels`.* FROM `labels` WHERE `labels`.`id` = 5432 LIMIT 1 CACHE (0.0ms) SELECT `concept_labels`.* FROM `concept_labels` INNER JOIN `labels` ON `labels`.`id` = `concept_labels`.`label_id` WHERE `concept_labels`.`concept_id` = 10 AND `labels`.`language_id` = 1 LIMIT 1 CACHE (0.0ms) SELECT `labels`.* FROM `labels` WHERE `labels`.`id` = 5432 LIMIT 1 </code></pre> <p><strong>The heaviest query amongst them is</strong></p> <pre><code>Label Load (56.0ms) SELECT `labels`.* FROM `labels` WHERE (`labels`.`id` IN (9909,9888,9855,9822,9900,9867,9834,9912,9891,9879,9846,9813,9870,9858,9825,9903,9882,9837,9804,9894,9861,9849,9816,9873,9840,9828,9796,9906,9885,9852,9807,9897,9864,9831,9819,9876,9843,9810)) </code></pre> <p>Yet the output still takes too long to load to my liking:</p> <pre><code>Rendered static/categorize.html.haml within layouts/application (515.1ms) Completed 200 OK in 1651ms (Views: 424.0ms | ActiveRecord: 188.0ms) </code></pre> <p>Is there something else missing because, last time I checked 188 + 424ms != 1651ms... When running performance tests it takes up to 8 seconds according JMeter to receive a proper response...</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJohn Topley
    1. UScoding-bunny
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL performance in Ruby on Rails
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL performance in Ruby on Rails
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL performance in Ruby on Rails
      1. USAndy Waite
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload