Linda_swe Posted April 30, 2009 Share Posted April 30, 2009 Hi all, I am new here. I just wonder if someone could give me a hint on how to use JOIN/IN so my sql query can be optimized. It has been working great when the tables had 3000 records or less, but now many of the tables are 50000+ which means that this sql is super slow. I guess many of the statements can be used in a JOIN statement. The problem is i dont know how to combine JOIN and WHERE/AND becauase i also need to sort on given parameters Here is the query SELECT `process`.PROD_NAME FROM `process` , linktab WHERE linktab.PROCESS_KEY = `process`.PROCESS_KEY AND linktab.ELT_NO = `process`.ELT_NO AND `process`.WORK_TYPE = '0D' AND linktab.STATION = 'H654' GROUP BY (process.PROD_NAME) HAVING count(*)>1 OR count(*)=1 ORDER BY `process`.JES_SEQUENCE ASC"); I would be very grateful if someone takes a minute to help me with this one. Thanks! update. i tried using inner join but i get an error message MySQL sa: Dokumentation #1066 - Not unique table/alias: 'linktab' in this: SELECT `process`. * , linktab. * FROM `process` INNER JOIN linktab ON `process`.PROCESS_KEY = linktab.PROCESS_KEY INNER JOIN linktab ON `process`.ELT_NO = linktab.ELT_NO ??? Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/ Share on other sites More sharing options...
kickstart Posted April 30, 2009 Share Posted April 30, 2009 Hi Try this:- SELECT `process`.PROD_NAME FROM `process` INNER JOIN linktab ON linktab.PROCESS_KEY = `process`.PROCESS_KEY AND linktab.ELT_NO = `process`.ELT_NO AND WHERE `process`.WORK_TYPE = '0D' AND linktab.STATION = 'H654' GROUP BY (process.PROD_NAME) HAVING count(*)>=1 ORDER BY `process`.JES_SEQUENCE ASC" Not sure what you are trying to do with the HAVING clause there though (looks redundant to me). All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-822880 Share on other sites More sharing options...
Linda_swe Posted April 30, 2009 Author Share Posted April 30, 2009 Hi Keith! Thanks for your replay, i tried it and also removed the AND statement before WHERE in you code and it works. However.. now i am a bit unsure if i ever will get a quick search result becuase it still takes 30 seconds to perform the query which is similar to the original one. Do you know if this is resonable time in myphpadmin when the linktab table includes 20 000 rows and the process one includes 50 000 ?? Will look over the HAVING statement and see if it has anything to do with it! Again thank you and have a nice day!! Hi Try this:- SELECT `process`.PROD_NAME FROM `process` INNER JOIN linktab ON linktab.PROCESS_KEY = `process`.PROCESS_KEY AND linktab.ELT_NO = `process`.ELT_NO AND WHERE `process`.WORK_TYPE = '0D' AND linktab.STATION = 'H654' GROUP BY (process.PROD_NAME) HAVING count(*)>=1 ORDER BY `process`.JES_SEQUENCE ASC" Not sure what you are trying to do with the HAVING clause there though (looks redundant to me). All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-822897 Share on other sites More sharing options...
kickstart Posted April 30, 2009 Share Posted April 30, 2009 Hi Seems to be WAY too long. However are the tables decently keyed / indexed? All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-822911 Share on other sites More sharing options...
fenway Posted April 30, 2009 Share Posted April 30, 2009 Let's see the EXPLAIN output. Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-822920 Share on other sites More sharing options...
Linda_swe Posted April 30, 2009 Author Share Posted April 30, 2009 Keith, Wow seems i am learning a lot today from you, INNER JOIN and now the need fir indexing. To be honest it is a totally chaos in the linktab, both the STATION names and the process keys. Now i will have to find more about the sql syntax to make them decently indexed. Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-822922 Share on other sites More sharing options...
Linda_swe Posted April 30, 2009 Author Share Posted April 30, 2009 Hi fenway Not sure what you mean by EXPLAIN output. my phpadmin is in Swedish so now such option button but is it this info you mean: 1 SIMPLE process ALL NULL NULL NULL NULL 19337 Using where; Using temporary; Using filesort 1 SIMPLE linktab ALL NULL NULL NULL NULL 46401 Using where Let's see the EXPLAIN output. Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-822928 Share on other sites More sharing options...
kickstart Posted April 30, 2009 Share Posted April 30, 2009 Hi Can you export he table structures? Export in phpmyadmin but not the data, just the structure. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-822933 Share on other sites More sharing options...
fenway Posted April 30, 2009 Share Posted April 30, 2009 A.K.A. SHOW CREATE TABLE. Yeah, you have no indexes. Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-822955 Share on other sites More sharing options...
Linda_swe Posted April 30, 2009 Author Share Posted April 30, 2009 Keith, I could do a dump on the structure but i feel a bit unsure if i am allow to post it becuase it belongs to a company who starts with G and ends with M...and..well the situation for us/them are quite extreme to say atleast. :-X I hope you dont feel "offended" by that But, am i correct when saying that i can pick some important columns to post-index them in order to optimize my query? Again,thank you! Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-822966 Share on other sites More sharing options...
kickstart Posted April 30, 2009 Share Posted April 30, 2009 Hi Yes. Basics are that an index will make searches of the table more efficient. Down side is that they slightly increase write times. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-822969 Share on other sites More sharing options...
fenway Posted April 30, 2009 Share Posted April 30, 2009 Very little downside to an index -- performance penalty to leave off a useful index is HUGE! Try composite index on ( linktab.PROCESS_KEY, linktab.ELT_NO ) and composite index on ( process.PROD_NAME, process.JES_SEQUENCE ) Then post EXPLAIN again. Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-822981 Share on other sites More sharing options...
Linda_swe Posted April 30, 2009 Author Share Posted April 30, 2009 Fenway and Keith!! You are my heros. they query takes 0.0065 seconds I am so happy now, finally. Thank you, thank you...and thanks! sorry for bad table formating but: 1 SIMPLE linktab ref PROCESS_KEY,ELT_NO,OPLOC OPLOC 258 const 33 Using where; Using temporary; Using filesort 1 SIMPLE process ref PROCESS_KEY,WORK_TYPE,ELT_NO ELT_NO 5 gm.linktab.ELT_NO 6 Using where Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-823002 Share on other sites More sharing options...
fenway Posted April 30, 2009 Share Posted April 30, 2009 Improvement, yes... but are those compositive indexes? Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-823014 Share on other sites More sharing options...
Linda_swe Posted April 30, 2009 Author Share Posted April 30, 2009 To be honest i dont know, but i guess not since i only tried index them by clicking the index button in phpmyadmin. compostite index, i am trying to read your faq and documents but it seems to be to a much better indexing with n-number of columns. Still i am not even sure,how and why i need it. I am just wondering what effects all the indexes i have done in my structure will have on my other queries that involves other columns. will they be sub-optimal or they might not even work anymore ? Improvement, yes... but are those compositive indexes? Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-823033 Share on other sites More sharing options...
fenway Posted April 30, 2009 Share Posted April 30, 2009 They will still work -- and probably even better. I suggested composite because you're joining on two values.... Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-823040 Share on other sites More sharing options...
kickstart Posted April 30, 2009 Share Posted April 30, 2009 Hi Indexes are the fast way or finding data. Simple explanation. Imagine a column with the values of 1,2,3,4,5,6,7,8 or 9 on say 10k row. If you only wanted those with a value of 5 then it would have to check every one of those million rows to find those with a value or 5. The index allows it to find those rows without having to scan them all. Likely that these indexes might well help some of your existing queries. Of course writing those indexes does take some time. But probably irrelevant on most apps but if you were logging some info that is rarely searched then it is an unncessary overhead. The more closely the index applies to the specific columns being checked then the more it will help performance (hence over multiple columns). However if it doesn't tie it down much at all then it is of little help. Ie, if you had a million row table with peoples details, putting an index on the field recording their sex wouldn't narrow things down usefully. All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-823044 Share on other sites More sharing options...
Linda_swe Posted April 30, 2009 Author Share Posted April 30, 2009 Yeah, make sense to me now. Thanks for the detailed explanations.People like me (not so very familiar with mysql) will probably find this thread very useful when it comes up in the search result in the future. I will stop doing experiment with index composed of multiple columns for today but will work on it further. All i can say is thanks! Now..on to autoIT to make some nice GUI's Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-823055 Share on other sites More sharing options...
Linda_swe Posted April 30, 2009 Author Share Posted April 30, 2009 just a quick note to you guys. I did a composite index with the suggested columns. 0.0002 seconds...wow! Quote Link to comment https://forums.phpfreaks.com/topic/156300-solved-help-with-optimizing-my-sql-query-with-multiple-and/#findComment-823064 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.