#16
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,400
    Rep Power
    1688
    Originally Posted by r937
    it'll be the lack of indexes, simon, not the disparity in varchar length
    I know mismatches in basic column type (INT vs VARCHAR, etc.) will screw up use of indices, but was not sure how different lengths of the same basic type would affect things. I was guessing it might not (else using different length literals such as name = 'Smith' or name = 'Wildebeest-Jones' would be scuppered) but was on the fence as it involved column definition correlation.
    Mainly I was concerned I'd refactored the query correctly!
    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
  2. #17
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    20
    Rep Power
    0
    I'm thinking you might be right... there is no index on the final list table.
    no matter if I use the where I've posted or the join... same explain..

    Let me know what other information you want....

    Code:
    	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
    	1	SIMPLE	final_list	ALL					819227	Using where
    	1	SIMPLE	org_chart	ref	adp_emp_id_idx	adp_emp_id_idx	35	org1.final_list.user_name	1	Using where
  4. #18
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    i'm callin shenanigans

    the CREATE TABLE statement you posted earlier did ~not~ include an index called adp_emp_id_idx, and yet your EXPLAIN output shows mysql using that index
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  6. #19
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    20
    Rep Power
    0
    Ok,

    I'll repost all the requests ... I have a production server and a test server... most of the stuff should be the same..but I have made changes to allow different things... so hear goes.. no... I have never used show create table before... If I have altered a table will that show in this command?? Also from what I can see.. it still doesn't show any indexes in the create table so I'm not sure what you are wanting? If I bring up Alter table for that table.. I see an index primary only on the ID column. this is the only output from the command you requested.

    Code:
    final_list, 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
    Code:
    # Table, Create Table
    org_chart, 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
  8. #20
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    20
    Rep Power
    0
    show index from org_chart gives this

    Code:
    # Table, Non_unique, Key_name, Seq_in_index, Column_name, Collation, Cardinality, Sub_part, Packed, Null, Index_type, Comment, Index_comment
    org_chart, 0, PRIMARY, 1, ID, A, 3450, , , , BTREE, ,
  10. #21
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by crysallis
    show index from org_chart gives this
    and yet the EXPLAIN shows an index called adp_emp_id_idx

    what's up with that?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #22
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    20
    Rep Power
    0
    Ran the explain on a test version ... the rest on the production... .likely I was playing with stuff.... so this explain goes with that newest show tables. sorry... told ya.. I'm new to this....no need to think I'm faking some how!

    Code:
    	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
    	1	SIMPLE	org_chart	ALL					3450	
    	1	SIMPLE	final_list	ALL					808665	Using where; Using join buffer (Block Nested Loop)
  14. #23
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    20
    Rep Power
    0
    Looking back at all the original stuff.. it looks like I didn't take into account the indexes from them..when creating the new tables... ( some of which get recreated weekly )

    If that will help.. i'll define some indexes.. things I could pivot the data on...

    final_list.adp
    final_list.mgr_adp
    final_list.user_name
    org_chart.adp_emp_id
    org_chart.mgr_adp_emp_id
    -are all things that get searched on, so should those all be indexes?
  16. #24
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by crysallis
    it looks like I didn't take into account the indexes from them..when creating the new tables...
    more shenanigans

    you never mentioned creating new tables

    you're copying something from a production environment, right?

    yeah, if it isn't obvious yet, you'll need those indexes

    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  18. #25
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    20
    Rep Power
    0
    well how much history do you want/need?? I'll be glad to lay it all out...

    Someone else created these starter tables and routines... some of which even by my standards.. suck.. so I've rewitten many of the routines...

    I have a server that many people in my team can see and use..and I have my machine that is running a server also...

    both have basically the same stuff... as I find things that improve the performance of the searches I change the tables. All the procedures I am running happen quickly... like 10 seconds or less.... it's just a weekly update on that table ( final_list ) which is recreated each week... based on some crazy statement that I didn't write... because some information is left out and I don't know how to add it to the routine that creates it in the first place I created sub routines that run after it's created. One of them I did this post about. It takes about 10 minutes to create the final_list table from scratch. then each of my routines run...and they take forever... never knew why... so in my learning and playing around I've created new tables trying to see if it was the routines... never thinking the table itself might be making it slow. so if setting those fields as indexable will speed up my routines.. then I'll do that.. again.. I never looked at the tables.. always thought it was my routines...

    anything else you'd like or need to know?

    Code:
    Drop table if exists final_list;
    
    #Creates table final_list with all columns
    CREATE	TABLE org1.final_list (
    SELECT
    machine.user as user_name, Machine.user_fullname as fullname, user_info.adp, machine.name as pcname, org_chart.job_descrip, concat(org_chart.job_descrip,' - ',user_info.fullname) as position_name, org_chart.dept_name, software.ID as soft_ID, software.display_name as Software_Name, software.publisher, monitor_replacements.system_name as Monitor_Replacement, user_info.refresh, user_info.pc_upgrade, user_info.user_migration_only, org_chart.mgr_name as Manager_Name, user_info.mgr_name,  user_info.mgr_adp_id as mgr_adp, machine.OS_name as OS, machine.cs_model
    
    FROM
    machine LEFT JOIN user_info ON machine.user_name=user_info.user_name
    LEFT JOIN Monitor_replacements ON machine.name=monitor_replacements.SYSTEM_NAME 
    LEFT JOIN machine_software_jt ON machine.ID=machine_software_jt.MACHINE_ID 
    LEFT JOIN org_chart ON user_info.adp=org_chart.adp_emp_id 
    LEFT JOIN software ON machine_software_jt.SOFTWARE_ID=software.ID 
    
    WHERE Machine.ID IN 
    (SELECT machine.ID FROM Label JOIN machine_label_jt ON label.id=machine_label_jt.label_id 
    JOIN machine ON machine.id=machine_label_jt.MACHINE_ID 
    WHERE label.NAME like '%NETLOC-WC%' or label.name like '%NETLOC-CAF%' or label.name like '%NETLOC-VPN-ALL%')
  20. #26
  21. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,400
    Rep Power
    1688
    Forgetting any history, etc., for the server where you are running that UPDATE and seeing that slow response make sure that the two columns (one in each of the tables) has an index on it:

    Code:
    ALTER TABLE table_name
      ADD INDEX (column_name);
    and see if that makes a difference.
    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
  22. #27
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    20
    Rep Power
    0

    Update


    OK, so...thanks for the help... sorry I lead anyone down a road incorrectly... but it looks like adding the indexes fixed the issue..

    It takes about 15 minutes to build the initial final_list table...
    and about 3 minutes for both other updates I do... one of which I asked about.. the other is exactly the same format..just putting 4 columns from a different table... so I made sure all tables had indexes..and the process that was taking almost 2 hours.. now on 1 test took about 20 minutes....much improved... thanks for pointing me in the right direction... I'll post if other runs don't work.. I'll give you my current create tables...and explains...and see if they look good to you...

    Final_list
    Code:
    # Table, Create Table
    final_list, CREATE TABLE `final_list` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `user_name` varchar(50) DEFAULT 'empty',
      `fullname` varchar(50) DEFAULT 'empty',
      `adp` varchar(45) DEFAULT NULL,
      `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) DEFAULT NULL,
      `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 '',
      PRIMARY KEY (`ID`),
      KEY `user_name_idx` (`user_name`),
      KEY `adp_idx` (`adp`),
      KEY `pcname_idx` (`pcname`),
      KEY `mgr_adp_idx` (`mgr_adp`)
    ) ENGINE=InnoDB AUTO_INCREMENT=851956 DEFAULT CHARSET=utf8
    Org_chart
    Code:
    # Table, Create Table
    org_chart, 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`),
      KEY `adp_emp_id_idx` (`adp_emp_id`),
      KEY `mgr_adp_emp_id` (`MGR_ADP_EMP_ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    show index for final_list
    Code:
    	Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
    	final_list	0	PRIMARY	1	ID	A	821343				BTREE		
    	final_list	1	user_name_idx	1	user_name	A	3393			YES	BTREE		
    	final_list	1	adp_idx	1	adp	A	2746			YES	BTREE		
    	final_list	1	pcname_idx	1	pcname	A	5549			YES	BTREE		
    	final_list	1	mgr_adp_idx	1	mgr_adp	A	678			YES	BTREE
    Show Index for org_chart
    Code:
    	Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
    	org_chart	0	PRIMARY	1	ID	A	3450				BTREE		
    	org_chart	1	adp_emp_id_idx	1	adp_emp_id	A	3450				BTREE		
    	org_chart	1	mgr_adp_emp_id	1	MGR_ADP_EMP_ID	A	1725			YES	BTREE
    Explain sql
    Code:
    	id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
    	1	SIMPLE	org_chart	ALL	adp_emp_id_idx				3450	
    	1	SIMPLE	final_list	ref	user_name_idx	user_name_idx	153	org1.org_chart.adp_emp_id	242	Using where
  24. #28
  25. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,400
    Rep Power
    1688
    Good that the update query is executing faster. You really should take care that your test system matches the live environment in terms of database schema and so on; saves all sorts of hassles (and not just confusion in forums!)

    As for your table definitions - I guess they are alright: they do what is needed of them? Only slightly curious thing that I might poke gently is the fact that ID filed (primary key) of the org_chart table is not auto increment. Not knowing the whys and wherefores of the application I cannot say if that is right or wrong.
    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
  26. #29
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2014
    Posts
    20
    Rep Power
    0
    the id in org_chart isn't auto because that file is not dynamically updated... it's static table that is created from a csv that is exported from another system. I only put the id in there to have a pk.... figured that would make it work faster. I don't really think it's needed and since no additional records are ever added to the table.. only from the csv...( which i figure is faster to reload than insert if, update if's ) no need to be auto inc.

    Would you agree?
  28. #30
  29. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,400
    Rep Power
    1688
    Either that value (for id) is coming in from the csv in which case just use the value in the csv. If it's NOT coming from the csv that means you are 'manually' entering the value. As it's there just for the purpose of providing a primary (and thus unique) key I'd a) question the need for it at all and b) if it is needed/wanted have it be an auto increment to prevent attempts at inserting duplicates.

    Comments on this post

    • crysallis agrees : Simon took the time to help me understand what I was missing and narrow down my issue.
    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

IMN logo majestic logo threadwatch logo seochat tools logo