#180 ✓resolved
Kieran P

Investigate speed benefits of adding index to taggings.context

Reported by Kieran P | March 30th, 2009 @ 03:49 PM | in 1.2

Investigate speed benefits of adding index to content_item_relations, contributions, taggings, and tags. Add index, benchmark, post results, and if significant, add index generation to migration.

Comments and changes to this ticket

  • Walter McGinnis

    Walter McGinnis March 30th, 2009 @ 04:36 PM

    Also experiment to see if adding an index to taggable_id and taggable_type make any difference individually or both.

  • Kieran P

    Kieran P March 30th, 2009 @ 06:46 PM

    Testing on a known page that takes a few seconds to load, I came up with the following results of 4 indexes. I won't post all 15 files. However, each index showed a speed boost to each query set.

    Results

    taggings.taggable_id

    Before Index

    SQL (0.243118)   SELECT count(*) AS count_all FROM `tags` INNER JOIN taggings ON tags.id = taggings.tag_id WHERE ((`taggings`.taggable_type = 'StillImage::Version') AND (`taggings`.taggable_id = 14950) AND ((context = 'flags')))
    Tag Load (0.200423)   SELECT `tags`.* FROM `tags` INNER JOIN taggings ON tags.id = taggings.tag_id WHERE ((`taggings`.taggable_type = 'StillImage') AND (`taggings`.taggable_id = 12774) AND ((context = 'public_tags'))) 
    
    

    After Index

    SQL (0.000847)   SELECT count(*) AS count_all FROM `tags` INNER JOIN taggings ON tags.id = taggings.tag_id WHERE ((`taggings`.taggable_type = 'StillImage::Version') AND (`taggings`.taggable_id = 14950) AND ((context = 'flags')))
    Tag Load (0.000466)   SELECT `tags`.* FROM `tags` INNER JOIN taggings ON tags.id = taggings.tag_id WHERE ((`taggings`.taggable_type = 'StillImage') AND (`taggings`.taggable_id = 12774) AND ((context = 'public_tags'))) 
    
    

    content_item_relations.related_item_id

    Before Index

    Topic Load (0.130817)   SELECT `topics`.* FROM `topics` INNER JOIN content_item_relations ON topics.id = content_item_relations.topic_id WHERE ((`content_item_relations`.related_item_id = 12774) AND (`content_item_relations`.related_item_type = 'StillImage'))
    SQL (0.012354)   SELECT count(*) AS count_all FROM `content_item_relations` WHERE ((content_item_relations.related_item_id = 12774 AND content_item_relations.related_item_type = 'StillImage')) 
    Topic Load (0.133351)   SELECT `topics`.* FROM `topics` INNER JOIN content_item_relations ON topics.id = content_item_relations.topic_id WHERE (title != 'Pending Moderation' AND title != 'No Public Version Available') AND ((`content_item_relations`.related_item_id = 12774) AND (`content_item_relations`.related_item_type = 'StillImage'))
    
    

    After Index

    Topic Load (0.000657)   SELECT `topics`.* FROM `topics` INNER JOIN content_item_relations ON topics.id = content_item_relations.topic_id WHERE ((`content_item_relations`.related_item_id = 12774) AND (`content_item_relations`.related_item_type = 'StillImage'))
    SQL (0.000346)   SELECT count(*) AS count_all FROM `content_item_relations` WHERE ((content_item_relations.related_item_id = 12774 AND content_item_relations.related_item_type = 'StillImage')) 
    Topic Load (0.000470)   SELECT `topics`.* FROM `topics` INNER JOIN content_item_relations ON topics.id = content_item_relations.topic_id WHERE (title != 'Pending Moderation' AND title != 'No Public Version Available') AND ((`content_item_relations`.related_item_id = 12774) AND (`content_item_relations`.related_item_type = 'StillImage')) 
    
    

    contributors.contributed_item_id

    Before Index

    User Load (0.027201)   SELECT `users`.* FROM `users` INNER JOIN contributions ON users.id = contributions.user_id WHERE ((`contributions`.contributed_item_type = 'StillImage') AND (`contributions`.contributed_item_id = 12774) AND ((contributions.contributor_role = 'creator'))) ORDER BY contributions.created_at LIMIT 1
    User Load (0.025176)   SELECT contributions.version, contributions.created_at as version_created_at, users.* FROM `users` INNER JOIN contributions ON users.id = contributions.user_id WHERE ((`contributions`.contributed_item_type = 'StillImage') AND (`contributions`.contributed_item_id = 12774) AND ((contributions.contributor_role = 'contributor'))) ORDER BY contributions.created_at DESC LIMIT 1
    
    

    After Index

    User Load (0.001074)   SELECT `users`.* FROM `users` INNER JOIN contributions ON users.id = contributions.user_id WHERE ((`contributions`.contributed_item_type = 'StillImage') AND (`contributions`.contributed_item_id = 12774) AND ((contributions.contributor_role = 'creator'))) ORDER BY contributions.created_at LIMIT 1
    User Load (0.001472)   SELECT contributions.version, contributions.created_at as version_created_at, users.* FROM `users` INNER JOIN contributions ON users.id = contributions.user_id WHERE ((`contributions`.contributed_item_type = 'StillImage') AND (`contributions`.contributed_item_id = 12774) AND ((contributions.contributor_role = 'contributor'))) ORDER BY contributions.created_at DESC LIMIT 1
    
    

    tag.name

    Before Index

    Tag Load (0.011986)   SELECT * FROM `tags` WHERE (`tags`.`name` IN ('ab','c','w','d','h','j','u','t','s','sd','hg','hre','ew','few','gr','ger','egw','gre','gew','egr','grw','ehr','g','regerw','ef'))
    Tag Load (0.012540)   SELECT * FROM `tags` WHERE (`tags`.`name` IN ('','a','b','c','d','e','f','g','h','i','j','k','l','m','o','n','p','q','r','s','t','u','v','w','x','y','z')) 
    
    

    After Index

    Tag Load (0.000874)   SELECT * FROM `tags` WHERE (`tags`.`name` IN ('ab','c','w','d','h','j','u','t','s','sd','hg','hre','ew','few','gr','ger','egw','gre','gew','egr','grw','ehr','g','regerw','ef'))
    Tag Load (0.000914)   SELECT * FROM `tags` WHERE (`tags`.`name` IN ('','a','b','c','d','e','f','g','h','i','j','k','l','m','o','n','p','q','r','s','t','u','v','w','x','y','z')) 
    
    

    So, as you can see, some nice speed boosts. But what does it mean page load wise....

    Before Indexes

    Processing ImagesController#show (for 127.0.0.1 at 2009-03-30 17:55:59) [GET]
      Session ID: 7d1906b15536badfbd42a08bd04a5553
      Parameters: {"action"=>"show", "id"=>"12774-clue-16-foxton-fizz-2", "controller"=>"images", "urlified_name"=>"site"}
    ..........
    Completed in 3.03702 (0 reqs/sec) | Rendering: 0.94895 (31%) | DB: 1.44484 (47%) | 200 OK [http://kete_trunk/site/images/sh...]
    $ ab -n 20 http://kete_trunk/site/images/sh...
    Time taken for tests:   90.927 seconds
    Requests per second:    0.22 [#/sec] (mean)
    Time per request:       4546.338 [ms] (mean)
    
    

    After Indexes

    Processing ImagesController#show (for 127.0.0.1 at 2009-03-30 18:00:56) [GET]
      Session ID: 1e7b83f00ee9aa7dfc1f17fb556374af
      Parameters: {"action"=>"show", "id"=>"12774-clue-16-foxton-fizz-2", "controller"=>"images", "urlified_name"=>"site"}
    ..........
    Completed in 1.59806 (0 reqs/sec) | Rendering: 0.97755 (61%) | DB: 0.20350 (12%) | 200 OK [http://kete_trunk/site/images/sh...]
    $ ab -n 20 http://kete_trunk/site/images/sh...
    Time taken for tests:   51.336 seconds
    Requests per second:    0.39 [#/sec] (mean)
    Time per request:       2566.807 [ms] (mean)
    
    

    Results with indexes

    • Completion of page loads are 1.9x faster

    • Database calls are 3.9x less (from 47% to 12%)

    • Over a period of 20 requests to the same page, 39.591s were cut off

    • Can now serve 1.7x more requests to this page

    • On average, 1979.5ms are cut off from each page request

  • Kieran P

    Kieran P March 30th, 2009 @ 06:52 PM

    • State changed from “new” to “to-review”

    This work has been completed and the migration for the indexes is available in the refinement_180_database_indexes branch.

    Changeset: http://github.com/kete/kete/comm...

  • Walter McGinnis

    Walter McGinnis March 30th, 2009 @ 07:16 PM

    • State changed from “to-review” to “open”

    All looks good. Please merge to master.

  • Kieran P

    Kieran P March 31st, 2009 @ 09:34 AM

    • State changed from “open” to “resolved”

    The migration has been merged into master. Resolving ticket.

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile ยป

Kete was developed by Horowhenua Library Trust and Katipo Communications Ltd. to build a digital library of Horowhenua material.

People watching this ticket

Pages