[SOLVED] Help with optimizing my sql query with multiple AND

[Linda_swe]

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

 

 

???

 

[kickstart]

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

[Linda_swe]

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

[kickstart]

Hi

 

Seems to be WAY too long. However are the tables decently keyed / indexed?

 

All the best

 

Keith

[fenway]

Let's see the EXPLAIN output.

[Linda_swe]

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.

[Linda_swe]

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.

 

 

[kickstart]

Hi

 

Can you export he table structures? Export in phpmyadmin but not the data, just the structure.

 

All the best

 

Keith

[fenway]

A.K.A. SHOW CREATE TABLE.

 

Yeah, you have no indexes.

[Linda_swe]

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!

[kickstart]

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

[fenway]

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.

[Linda_swe]

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

[fenway]

Improvement, yes... but are those compositive indexes?

[Linda_swe]

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?

[fenway]

They will still work -- and probably even better.

 

I suggested composite because you're joining on two values....

[kickstart]

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

[Linda_swe]

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 

[Linda_swe]

just a quick note to you guys.

 

I did a composite index with the suggested columns.

 

0.0002 seconds...wow!