Page 1 of 3 123 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    20
    Rep Power
    0
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    can you do a SHOW CREATE TABLE for each table, then do an EXPLAIN on the update query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    20
    Rep Power
    0

    Sure


    Here is the explain as requested... and create tables ( looks horrible... but I used the mysql tags )

    I can't post a link to the image.. if you go to flickr com slash crysallis you'll see the image

    Code:
    15:08:46	EXPLAIN FORMAT=JSON update final_list,     org_chart  set      final_list.position_name = (select              concat(org_chart.job_descrip,                         ' - ',                         org_chart.fname,                         ' ',                         org_chart.middle_int,                         ' ',                         org_chart.lname)         ),     final_list.job_descrip = org_chart.job_descrip,     final_list.dept_name = org_chart.dept_name,     final_list.Manager_Name = org_chart.mgr_name where     final_list.user_name = org_chart.adp_emp_id	1 row(s) returned	0.000 sec / 0.000 sec
    Code:
    'final_list', 'CREATE TABLE `final_list` (\n  `user_name` varchar(50) DEFAULT \'empty\',\n  `fullname` varchar(50) DEFAULT \'empty\',\n  `adp` varchar(45),\n  `pcname` varchar(50) DEFAULT \'empty\',\n  `job_descrip` varchar(30) DEFAULT NULL,\n  `position_name` varchar(59) DEFAULT NULL,\n  `dept_name` varchar(30) DEFAULT NULL,\n  `soft_ID` bigint(20) unsigned DEFAULT \'0\',\n  `Software_Name` varchar(255) DEFAULT \'\',\n  `publisher` varchar(255) DEFAULT NULL,\n  `Monitor_Replacement` varchar(25),\n  `refresh` int(11) DEFAULT NULL,\n  `pc_upgrade` int(11) DEFAULT NULL,\n  `user_migration_only` int(11) DEFAULT NULL,\n  `Manager_Name` varchar(27) DEFAULT NULL,\n  `mgr_name` varchar(21) DEFAULT NULL,\n  `mgr_adp` varchar(45) DEFAULT NULL,\n  `OS` varchar(100) DEFAULT \'\',\n  `cs_model` varchar(255) DEFAULT \'empty\',\n  `to_discuss` int(11) DEFAULT \'1\',\n  `PivotData` varchar(255) DEFAULT \'\',\n  `PivotSoftware` varchar(255) DEFAULT \'\'\n) ENGINE=InnoDB DEFAULT CHARSET=utf8'
    Code:
    'org_chart', 'CREATE TABLE `org_chart` (\n  `ID` int(11) NOT NULL,\n  `adp_emp_id` varchar(25) NOT NULL,\n  `FNAME` varchar(15) DEFAULT NULL,\n  `MIDDLE_INT` varchar(4) DEFAULT NULL,\n  `LNAME` varchar(19) DEFAULT NULL,\n  `REPORT_LOC_NUM` int(11) DEFAULT NULL,\n  `WORK_LOC_NUM` int(11) DEFAULT NULL,\n  `JOB_DESCRIP` varchar(30) DEFAULT NULL,\n  `DEPT_NAME` varchar(30) DEFAULT NULL,\n  `REGION_NAME` varchar(29) DEFAULT NULL,\n  `MGR_FNAME` varchar(14) DEFAULT NULL,\n  `MGR_MI` varchar(1) DEFAULT NULL,\n  `MGR_LNAME` varchar(17) DEFAULT NULL,\n  `MGR_ADP_EMP_ID` varchar(11) DEFAULT NULL,\n  `MGR_NAME` varchar(27) DEFAULT NULL,\n  PRIMARY KEY (`ID`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8'
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by crysallis
    ( looks horrible... but I used the mysql tags )
    in case you hadn't noticed, those tags create links to the mysql site for search purposes

    use code tags, especially for your EXPLAIN output, as they use fixed font so the layout spacing won't collapse
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    20
    Rep Power
    0
    I did notice.. but didn't pay attention... I figured... mysql should go in the mysql tags lol wrongly so

    thanks
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by crysallis
    I can't post a link to the image.. if you go to flickr com slash crysallis you'll see the image
    that's pretty, but 1) i can't read it, and 2) i was expecting text, which is why i mentioned the code tags
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    here, i did some more work for ya, i formatted your CREATE TABLE statements so that they don't involve those ugly @ss horizontal scrolls...
    Code:
    CREATE TABLE final_list 
    ( user_name varchar(50) DEFAULT 'empty'
    , fullname varchar(50) DEFAULT 'empty'
    , adp varchar(45)
    , pcname varchar(50) DEFAULT 'empty'
    , job_descrip varchar(30) DEFAULT NULL
    , position_name varchar(59) DEFAULT NULL
    , dept_name varchar(30) DEFAULT NULL
    , soft_ID bigint(20) unsigned DEFAULT '0'
    , Software_Name varchar(255) DEFAULT ''
    , publisher varchar(255) DEFAULT NULL
    , Monitor_Replacement varchar(25)
    , refresh int(11) DEFAULT NULL
    , pc_upgrade int(11) DEFAULT NULL
    , user_migration_only int(11) DEFAULT NULL
    , Manager_Name varchar(27) DEFAULT NULL
    , mgr_name varchar(21) DEFAULT NULL
    , mgr_adp varchar(45) DEFAULT NULL
    , OS varchar(100) DEFAULT ''
    , cs_model varchar(255) DEFAULT 'empty'
    , to_discuss int(11) DEFAULT '1'
    , PivotData varchar(255) DEFAULT ''
    , PivotSoftware varchar(255) DEFAULT ''
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    ;
    
    CREATE TABLE org_chart 
    ( ID int(11) NOT NULL
    , adp_emp_id varchar(25) NOT NULL
    , FNAME varchar(15) DEFAULT NULL
    , MIDDLE_INT varchar(4) DEFAULT NULL
    , LNAME varchar(19) DEFAULT NULL
    , REPORT_LOC_NUM int(11) DEFAULT NULL
    , WORK_LOC_NUM int(11) DEFAULT NULL
    , JOB_DESCRIP varchar(30) DEFAULT NULL
    , DEPT_NAME varchar(30) DEFAULT NULL
    , REGION_NAME varchar(29) DEFAULT NULL
    , MGR_FNAME varchar(14) DEFAULT NULL
    , MGR_MI varchar(1) DEFAULT NULL
    , MGR_LNAME varchar(17) DEFAULT NULL
    , MGR_ADP_EMP_ID varchar(11) DEFAULT NULL
    , MGR_NAME varchar(27) DEFAULT NULL
    , PRIMARY KEY (ID)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    20
    Rep Power
    0
    okay I redid the original post with code tags for both the CREATE TABLE and the explain statement...I simply copied the output from the requested commands. Original post does also include the explain statement as given to me by MySQL*** workbench. Sorry...I didn't format them the way you wanted. I should have cleaned them up..but since you asked for a specific command I gave you its direct output. Thanks again for any insight. .l
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    could you please grab the text output of the EXPLAIN?

    i'm not familiar with sql workbench, but EXPLAIN is a mysql command, and it produces text

    if sql workbench has an sql tab or sql window, where you can type in your query, do it there please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    20
    Rep Power
    0
    Originally Posted by r937
    could you please grab the text output of the EXPLAIN?

    i'm not familiar with sql workbench, but EXPLAIN is a mysql command, and it produces text

    if sql workbench has an sql tab or sql window, where you can type in your query, do it there please
    The first code block is what I get when I do explain. ..In text format. If that is not what you want I'll try again tomorrow when I'm at work
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by crysallis
    The first code block is what I get when I do explain. ..In text format. If that is not what you want I'll try again tomorrow when I'm at work
    yes, that is not what i want, you showed the EXPLAIN but not its output

    see http://glebshchepa.blogspot.ca/2012/...ormatjson.html

    if you're gonna do the FORMAT=JSON version, fine, but i'm not sure i'll be able to understand it

    please to the ordinary EXPLAIN, that produces output like this --
    Code:
    +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
    | id | select_type | table | type   | possible_keys | key     | key_len | ref       | rows | Extra       |
    +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
    |  1 | SIMPLE      | t1    | ALL    | NULL          | NULL    | NULL    | NULL      |    3 | Using where |
    |  1 | SIMPLE      | t2    | eq_ref | PRIMARY       | PRIMARY | 4       | test.t1.i |    1 | Using where |
    +----+-------------+-------+--------+---------------+---------+---------+-----------+------+-------------+
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,451
    Rep Power
    1751
    My guess is you'd be better off with something akin to:

    Code:
    UPDATE final_list f 
      INNER JOIN org_char o
        ON f.user_name = o.adp_emp_id
      SET
        f.position_name = concat(o.job_descrip, ' - ',
                                              o.fname, ' ',
                                              o.middle_int, ' ',
                                              o.lname),
        f.job_descrip = o.job_descrip,
        f.dept_name = o.dept_name,
        f.Manager_Name = o.mgr_name
    As ever, with any such thing run a 'normal' SELECT query with the JOIN/ON/WHERE clauses to check that you're going to hit the right rows.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    20
    Rep Power
    0
    I tried that as well...figuring the join would speed it up...If not make it look better. I'm new to sql, I was tossed into a project and I'm trying to learn as I go...but either was it takes like 20+ minutes to run...I realize it's touching 700+ thousand records but I thought maybe something I was doing made it so slow...I'll post that requested explain...tomorrow when I'm at work. I haven't used mysql since php3 lol
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,451
    Rep Power
    1751
    One thing I notice is that the two columns used for the link condition are defined differently (VARCHAR(25) and VARCHAR(50), not sure what effect that will have.
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    it'll be the lack of indexes, simon, not the disparity in varchar length
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
Page 1 of 3 123 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo