Moemen,
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.
HTH
Peter Brawley http://www.artfulsoftware.com
-----
moemen saad eldeen wrote:
Dear all,
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.'
and
T3.item_type='6.1.' and T2.f2 like '1.1.16.%' and substring(T2.item_id,1,LOCATE('.',T2.item_id))=T1.item_id and
substring(T3.item_id,1,LOCATE('.',T3.item_id))=T1.item_id and
(T3.f2
='4.1.1.') and (T3.f7 is null ) and (T2.f6 is null ) order by
T1.f2
"
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/[EMAIL PROTECTED]
