I can see a cartesian product on the EMP table. Is this really what
you're looking for. There is no column joining T2 and T3 !
Also as Peter said, you should have a lack of indexes on your tables.
If you can send me your .frm,.myd and .myi files of the two tables as a
zip file, I may help you to execute your query in less than 20mn.
Question : count(*) from EMP = ?
From: Peter Brawley [mailto:peter.brawley@stripped]
Sent: dimanche 24 avril 2005 17:39
Subject: Re: mysql_performance
You assign a string _position_ result from LOCATE to a SUBSTRNG _length_
argument. Is that what you mean?
Do you have indexes on persons.item_id, emp (item_id, item_type, f2, f6,
f7)? Once there are such indexes, try ordering the WHERE ... ANDs to
correspond to those keys so the optimiser can use the index
If it takes 20 mins, you likely have lots of rows, and the per-row
SUBSTRING(...LOCATE...) calls will likely slow it down. If the above
changes don;t help, you could try breaking out the substrings to
separate columns and index on them too.
Try running EXPLAIN on your query before & after such changes to see if
you're changing the query engine's plan.
moemen saad eldeen wrote:
>I have a problem running this query
>"select distinct T1.item_id, T1.f2 from Persons as T1 , Emp as
>T2 , Emp as T3 where T1.item_type='6.' and T2.item_type='6.1.9.'
>T3.item_type='6.1.' and T2.f2 like '1.1.16.%' and
>='4.1.1.') and (T3.f7 is null ) and (T2.f6 is null ) order by
>on my server the output come after about 20 mins i have tried all
>possible solutions for tunning my server using :
>1-server parameters like: increasing key_buffer,read buffer,setting
>result buffer, disable swapping
>2-high memory: 2GB RAM
>All my tables are MYISAM and with varchar type
>I don't know how to speeding output
>can anyone help
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=1