List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 24 2005 3:39pm
Subject:Re: mysql_performance
View as plain text  

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.


Peter Brawley


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.'
>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
>='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_performancemoemen saad eldeen24 Apr
  • Re: mysql_performancePeter Brawley24 Apr
    • RE: mysql_performancemathias fatene24 Apr