www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Super-Slow SQL Queries Of Slowness.

  1. #1
    Join Date
    Sep 2004
    Location
    At the corner of WALK and DONT WALK
    Posts
    1,834

    Super-Slow SQL Queries Of Slowness.

    Here are the relevant tables:

    Code:
    mysql> describe ds_categories;
    +-------------------+--------------+------+-----+-----------------+-------+
    | Field             | Type         | Null | Key | Default         | Extra |
    +-------------------+--------------+------+-----+-----------------+-------+
    | categ_id          | varchar(50)  | NO   | PRI |                 |       |
    | categ_name        | varchar(200) | NO   |     | Please Add Name |       |
    | parent_categ      | varchar(50)  | YES  |     | NULL            |       |
    | grandparent_categ | varchar(50)  | YES  |     | NULL            |       |
    +-------------------+--------------+------+-----+-----------------+-------+
    4 rows in set (0.13 sec)
    
    mysql> describe ds_line_categs;
    +----------+----------------------+------+-----+---------+-------+
    | Field    | Type                 | Null | Key | Default | Extra |
    +----------+----------------------+------+-----+---------+-------+
    | set_num  | tinyint(1) unsigned  | NO   | PRI |         |       |
    | line_num | smallint(4) unsigned | NO   | PRI |         |       |
    | categ_id | varchar(30)          | NO   | PRI |         |       |
    +----------+----------------------+------+-----+---------+-------+
    3 rows in set (0.02 sec)
    Here are the queries (almost identical, save for one field):

    Code:
    SELECT
    	`ds_line_categs`.`set_num`,
    	`ds_line_categs`.`line_num`,
    	`ds_categories`.`grandparent_categ`,
    	CONCAT(
    		`ds_categories`.`grandparent_categ`,"_",
    		`ds_line_categs`.`set_num`,"_",
    		`ds_line_categs`.`line_num`
    	) AS `rec_id`
    	FROM `ds_categories`,`ds_line_categs`
    	WHERE `ds_line_categs`.`categ_id` = `ds_categories`.`categ_id`
    		AND `ds_categories`.`grandparent_categ` IS NOT NULL
    		AND CONCAT(
    			`ds_categories`.`grandparent_categ`,"_",
    			`ds_line_categs`.`set_num`,"_",
    			`ds_line_categs`.`line_num`
    		) NOT IN (SELECT CONCAT(
    			`ds_line_categs`.`categ_id`,"_",
    			`ds_line_categs`.`set_num`,"_",
    			`ds_line_categs`.`line_num`
    		) FROM `ds_line_categs`)
    ;
    Code:
    SELECT
    	`ds_line_categs`.`set_num`,
    	`ds_line_categs`.`line_num`,
    	`ds_categories`.`parent_categ`,
    	CONCAT(
    		`ds_categories`.`parent_categ`,"_",
    		`ds_line_categs`.`set_num`,"_",
    		`ds_line_categs`.`line_num`
    	) AS `rec_id`
    	FROM `ds_categories`,`ds_line_categs`
    	WHERE `ds_line_categs`.`categ_id` = `ds_categories`.`categ_id`
    		AND `ds_categories`.`parent_categ` IS NOT NULL
    		AND CONCAT(
    			`ds_categories`.`parent_categ`,"_",
    			`ds_line_categs`.`set_num`,"_",
    			`ds_line_categs`.`line_num`
    		) NOT IN (SELECT CONCAT(
    			`ds_line_categs`.`categ_id`,"_",
    			`ds_line_categs`.`set_num`,"_",
    			`ds_line_categs`.`line_num`
    		) FROM `ds_line_categs`)
    ;
    Their purpose is simply to show me where a category/line-set combo has an entry in `ds_line_categs`, but there isn't one for that category's parent or grandparent. The information of the two statements are used in a PHP page to create an INSERT query that will add the missing entries.

    The problem is, the one with `grandparent_categ` takes 5 seconds to run, the one with `parent_categ` nearly three times that. Any way I can make these run quicker? I'm using mysql.
    Last edited by Mr Initial Man; 01-01-2018 at 11:33 PM.

  2. #2
    Join Date
    Apr 2017
    Posts
    70
    The problem is in your WHERE. You need to JOIN the tables

    Problem:
    WHERE `ds_line_categs`.`categ_id` = `ds_categories`.`categ_id`

    Problem
    WHERE `ds_line_categs`.`categ_id` = `ds_categories`.`categ_id`
    AND `ds_categories`.`parent_categ` IS NOT NULL

  3. #3
    Join Date
    Sep 2004
    Location
    At the corner of WALK and DONT WALK
    Posts
    1,834
    Okay, what should it be?

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,335
    I'm not sure how much a "normal" join syntax will help (maybe a lot, maybe a little?), but I'd certainly rewrite it. However, what immediately jumps out at me is this:
    Code:
    		AND CONCAT(
    			`ds_categories`.`parent_categ`,"_",
    			`ds_line_categs`.`set_num`,"_",
    			`ds_line_categs`.`line_num`
    		) NOT IN (SELECT CONCAT(
    			`ds_line_categs`.`categ_id`,"_",
    			`ds_line_categs`.`set_num`,"_",
    			`ds_line_categs`.`line_num`
    		) FROM `ds_line_categs`)
    The DBMS will need to do a full table scan each time, as it has to do all those CONCATs before it can then compare each one to see what matches/doesn't match. I'll have to think a bit before coming up with any suggestions... You may then also need to add some indexes to one or both tables to ensure any column being compared/sorted has an index.
    "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

  5. #5
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,335
    Okay...I think this replicates what you are trying to do? I took some formatting liberties to help me parse what was going on based on my preferred style.
    Code:
    SELECT
      dlc.set_num,
      dlc.line_num,
      dc.grandparent_categ,
      CONCAT(
        dc.grandparent_categ,"_",
        dlc.set_num,"_",
        dlc.line_num
      ) AS rec_id
    FROM ds_categories AS dc
    INNER JOIN ds_line_categs AS dlc ON dlc.categ_id = dc.categ_id
    WHERE
      dc.grandparent_categ IS NOT NULL AND
      dc.grandparent_categ <> = dlc.categ_id
    ;
    "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

  6. #6
    Join Date
    Sep 2004
    Location
    At the corner of WALK and DONT WALK
    Posts
    1,834
    Hmmm... Just tried it, but it gives me every record where the dlc.categ_id and dc.grandparent_categ are different--well over a thousand records. I just want the ones where the grandparent record is missing.

  7. #7
    Join Date
    Sep 2004
    Location
    At the corner of WALK and DONT WALK
    Posts
    1,834
    In essence, I'm looking for an id present in one table, and missing in another.

    Is it possible to do a string split in SQL? Say, split on "_"?

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,335
    Quote Originally Posted by Mr Initial Man View Post
    In essence, I'm looking for an id present in one table, and missing in another.

    Is it possible to do a string split in SQL? Say, split on "_"?
    I believe it's possible, but again, that probably results in full table scans if you use any of those splits for comparing and/or sorting. Such a desire is a "code smell" that the data structure needs some normalization.

    Not sure if/when I'll have time to think about it today, but if you could provide a smallish data example and the desired result set for one such query, that might help me wrap my brain around what exactly you want to do.
    "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

  9. #9
    Join Date
    Sep 2004
    Location
    At the corner of WALK and DONT WALK
    Posts
    1,834
    Okay. I'll post a line, the main category that applies to it, and how they relate.


    The DragonSpeak Line:
    Code:
    mysql> select * from ds_lines where set_num = 0 and line_num = 1
        -> ;
    +---------+----------+-------------------------+
    | set_num | line_num | line_desc               |
    +---------+----------+-------------------------+
    |       0 |        1 | Whenever someone moves, |
    +---------+----------+-------------------------+
    1 row in set (0.06 sec)

    The "Furre" category and all subcategories:

    Code:
    mysql> SELECT * from ds_categories WHERE categ_id LIKE "Furre%";
    +----------------------------+---------------------------------------------+-------------------+-------------------+
    | categ_id                   | categ_name                                  | parent_categ      | grandparent_categ |
    +----------------------------+---------------------------------------------+-------------------+-------------------+
    | Furres                     | Furres                                      | NULL              | NULL              |
    | Furres_Actions             | What the furre is doing                     | Furres            | NULL              |
    | Furres_Actions_Idle        | Furre Idling                                | Furres_Actions    | Furres            |
    | Furres_Actions_Message     | Speaking, Emitting, Emoting                 | Furres_Actions    | Furres            |
    | Furres_Actions_Move        | Furre Movement                              | Furres_Actions    | Furres            |
    | Furres_Actions_Orient      | Which way a Furre is facing                 | Furres_Actions    | Furres            |
    | Furres_Actions_Posture     | Sitting, Standing, Laying                   | Furres_Actions    | Furres            |
    | Furres_Actions_Turn        | Furre Turning                               | Furres_Actions    | Furres            |
    | Furres_Appearance          | What The Furre Looks Like                   | Furres            | NULL              |
    | Furres_Appearance_Colour   | Colour                                      | Furres_Appearance | Furres            |
    | Furres_Appearance_Gender   | Furre Gender                                | Furres_Appearance | Furres            |
    | Furres_Appearance_Size     | Furre Size                                  | Furres_Appearance | Furres            |
    | Furres_Appearance_Species  | Furre Species                               | Furres_Appearance | Furres            |
    | Furres_Digo                | Digo                                        | Furres            | NULL              |
    | Furres_Digo_DigoPower      | Uses a Digo Power                           | Furres_Digo       | Furres            |
    | Furres_Digo_DigoToggle     | Activate, Deactivate, or Switch Digo        | Furres_Digo       | Furres            |
    | Furres_Digo_Type           | Digo Type                                   | Furres_Digo       | Furres            |
    | Furres_Digo_Wings          | A Furre's Wings                             | Furres_Digo       | Furres            |
    | Furres_Dreams              | Furres and Dreams                           | Furres            | NULL              |
    | Furres_Interact            | Interactions With Other Furres              | Furres            | NULL              |
    | Furres_Interact_Desctags   | Desctag Shenanigans                         | Furres_Interact   | Furres            |
    | Furres_Interact_LeadFollow | Leading And Following                       | Furres_Interact   | Furres            |
    | Furres_Interact_Summon     | Summoning a Furre                           | Furres_Interact   | Furres            |
    | Furres_Select              | Furre Selection                             | Furres            | NULL              |
    | Furres_Select_All          | All Furres In Dream                         | Furres_Select     | Furres            |
    | Furres_Select_Arriving     | Furre Arriving In Dream                     | Furres_Select     | Furres            |
    | Furres_Select_InSight      | Any Furre That Can See...                   | Furres_Select     | Furres            |
    | Furres_Select_Named        | Any Furre Named...                          | Furres_Select     | Furres            |
    | Furres_Select_Present      | Any Furre Present                           | Furres_Select     | Furres            |
    | Furres_Select_Triggering   | Triggering Furre                            | Furres_Select     | Furres            |
    | Furres_Sponsorship         | Sponsorship Level                           | Furres            | NULL              |
    | Furres_SwimFly             | Swimming and Flying (as opposed to walking) | Furres            | NULL              |
    +----------------------------+---------------------------------------------+-------------------+-------------------+
    32 rows in set (0.12 sec)

    How the two are related:

    Code:
    mysql> SELECT * from ds_line_categs WHERE set_num=0 AND line_num=1;
    +---------+----------+--------------------------+
    | set_num | line_num | categ_id                 |
    +---------+----------+--------------------------+
    |       0 |        1 | Furres_Actions           |
    |       0 |        1 | Furres_Actions_Move      |
    |       0 |        1 | Furres_Select_Triggering |
    +---------+----------+--------------------------+
    5 rows in set (0.29 sec)
    I removed two records from the above listing, so that I can explain what I want.

    First: the parent of Furres_Select_Triggering--which is Furres_Select-- is missing, though it should be there. Also, the grandparent of Furres_Select_Triggering--Furres--is also missing. The two queries should pick out which records are missing from the table, and display them.
    Last edited by Mr Initial Man; 01-05-2018 at 12:58 PM.

  10. #10
    Join Date
    Sep 2004
    Location
    At the corner of WALK and DONT WALK
    Posts
    1,834
    Or would it be better for me to just grab the information from the tables, and use PHP to figure out what should be there?

  11. #11
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,335
    I still feel like I'm not totally grasping the problem space here.

    Are you saying that in your last example, since you have 0,1,"Furres_Select_Triggering" in ds_line_categs, you want to make sure there are also rows for the same set_num/line_num combination with "Furres_Select" and "Furres"? (If so, then my next question might be "Why?", since you can grab those from the ds_categories table any time you need them. [Or I'm totally not grasping it. ])
    "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

  12. #12
    Join Date
    Sep 2004
    Location
    At the corner of WALK and DONT WALK
    Posts
    1,834
    Quote Originally Posted by NogDog View Post
    I still feel like I'm not totally grasping the problem space here.

    Are you saying that in your last example, since you have 0,1,"Furres_Select_Triggering" in ds_line_categs, you want to make sure there are also rows for the same set_num/line_num combination with "Furres_Select" and "Furres"?
    Exactly.

    Quote Originally Posted by NogDog View Post
    (If so, then my next question might be "Why?", since you can grab those from the ds_categories table any time you need them. [Or I'm totally not grasping it. ])

    Because it's being used in a webpage designed to help people find specific lines based on what categories they belong to. So if someone wants to find all lines pertaining to "Furre", this line needs to be amongst them.
    Last edited by Mr Initial Man; 01-07-2018 at 08:43 PM.

  13. #13
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,335
    Okay, so I *think* maybe a query like this would get the applicable set/line for a given search category without having to put in parent/grandparent rows in the ds_line_categs table:
    Code:
    select distinct
        dlc.set_num,
        dlc.line_num
    from ds_line_categs as dlc
    left join ds_categories as dc on dc.categ_id = dlc.categ_id
    where
        dlc.categ_id = 'search_value' OR
        dc.parent_categ = 'search_value' OR
        dc.grandparent_categ = 'search_value'
    ;
    You'd want to ensure there are indexes on each column using in the table join ON clause, as well as each column in the WHERE clause.

    Totally untested, of course.
    "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

  14. #14
    Join Date
    Sep 2004
    Location
    At the corner of WALK and DONT WALK
    Posts
    1,834
    Maybe what I'll do is multiple queries and use PHP to sort out what should and shouldn't be there, since my input webpage allows for non-existent lines to show up as well. >_>;;;

  15. #15
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    22,335
    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 (assuming refactoring the whole database design isn't an option ). Your mileage may vary.
    "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 1 users browsing this thread. (0 members and 1 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.13829 seconds
  • Memory Usage 3,053KB
  • Queries Executed 13 (?)
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
  • (9)bbcode_code
  • (3)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (15)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
  • (15)postbit
  • (15)postbit_onlinestatus
  • (15)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 (72):
  • 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_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