www.webdeveloper.com
Page 2 of 2 FirstFirst 12
Results 16 to 17 of 17

Thread: Super-Slow SQL Queries Of Slowness.

  1. #16
    Join Date
    Sep 2004
    Location
    At the corner of WALK and DONT WALK
    Posts
    1,829
    Quote Originally Posted by NogDog View Post
    I have heard it said that 3 simple queries often can run as fast as one complex query that joins across them -- and sometimes even faster -- so it might be a good approach
    Yeah, the three simple queries would be:

    Code:
    SELECT CONCAT(`set_num`,':',`line_num`) AS `line_id` FROM `ds_lines`;
    SELECT
      CONCAT(`set_num`,':',`line_num`) AS `line_id`, 
      CONCAT(`set_num`,':',`line_num`,'_',`categ_id`) AS `line_cat`, 
      `categ_id`
      FROM `ds_line_categs`
    ;
    SELECT
      `categ_id`,
      `parent_categ`,
      `grandparent_categ`
      FROM `ds_categories`
    ;
    And, like I said, I'd just use PHP to sort everything out.

    *Checks speeds.* First one ran in 0.02 secondss, second in 0.09 seconds, and the third in 0.02 seconds. Yeah, this is a tad faster.

    Quote Originally Posted by NogDog View Post
    (assuming refactoring the whole database design isn't an option ). Your mileage may vary.
    What do you mean by refactoring?

  2. #17
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,284
    "Refactoring" is just developer speak for "re-doing in a different way". I'm not saying I know what the different way would necessarily be, but the fact that (a) certain text values are showing up in multiple tables, and (b) we're having trouble coming up with a clean way of doing things (which may in part be ignorance on my part) would tend to suggest that some database reorganization and design might solve those problems. The downside is that any code that uses it would then have to adjust to the new design; so it might not be worth the effort.

    The fact that you have a hierarchy structure in your data (with parents and grandparents) already makes things a bit more complicated than many web site databases, and such hierarchy concerns have frustrated many of us in the past. (I came up with a solution for a client many years ago that had an open number of "generations", and which I eventually got to work -- but boy was it ugly. That was before I ever heard of "nested set" database designs, which if I'd used would have made it much cleaner. Of course, now I'd have to read up on it again, as I've pretty much forgotten all the details. )
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center

"

"

X vBulletin 4.2.2 Debug Information

  • Page Generation 0.20051 seconds
  • Memory Usage 2,857KB
  • Queries Executed 15 (?)
More Information
Template Usage (37):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)bbcode_code
  • (2)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (2)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (1)navbar_moderation
  • (1)navbar_noticebit
  • (1)navbar_tabs
  • (2)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (2)postbit
  • (2)postbit_onlinestatus
  • (2)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (26):
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/functions_navigation.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_facebook.php
  • ./includes/functions_calendar.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (74):
  • init_startup
  • friendlyurl_resolve_class
  • init_startup_session_setup_start
  • database_pre_fetch_array
  • database_post_fetch_array
  • init_startup_session_setup_complete
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • load_show_variables
  • load_forum_show_variables
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • strip_bbcode
  • friendlyurl_clean_fragment
  • friendlyurl_geturl
  • forumjump
  • cache_templates
  • cache_templates_process
  • template_register_var
  • template_render_output
  • fetch_template_start
  • fetch_template_complete
  • parse_templates
  • fetch_musername
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • bbcode_parse_start
  • postbit_imicons
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • pagenav_page
  • pagenav_complete
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • build_navigation_data
  • build_navigation_array
  • check_navigation_permission
  • process_navigation_links_start
  • process_navigation_links_complete
  • set_navigation_menu_element
  • build_navigation_menudata
  • build_navigation_listdata
  • build_navigation_list
  • set_navigation_tab_main
  • set_navigation_tab_fallback
  • navigation_tab_complete
  • fb_like_button
  • showthread_complete
  • page_templates