Bug #9868 mysql_performance
Submitted: 13 Apr 2005 12:46 Modified: 14 May 2005 14:53
Reporter: moemen mira Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:mysql 5 OS:Linux (linux redhat 9)
Assigned to: Assigned Account CPU Architecture:Any

[13 Apr 2005 12:46] moemen mira
Description:
I have a problem for running this query on my database 

"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
"

the output time was so long, about  15.minuts  even though i ran this query on previuos engine which was sql200 server and the output was not with so much time

i don't know where is the problem in the performance of mysql , i copied the configuration of "my-huge.cnf" to be used as defualt configuration 

my sever is of 2GB RAM,2.4 pentium 4 processor

the installation of mysql server was the defualt installation with no added configuration options 

Tables Type are of MYISAM
Index of the query tables are of text type columns
Tables records for the query case are 35,000 and 27,000 record for each table

How to repeat:
every time i ran the same query it takes much time

please send fix reply  (moemen@linux-plus.com)

Suggested fix:
I tried to change the key_buffer to big enough but with no enhancment

Tried to disable the temp tables to be stored indisk  but with no enhancment

I don't know what is the problem is it bug or what
[13 Apr 2005 21:43] Jorge del Conde
Hi!

Your problem sounds like a performance/tuning question rather than a bug.  I strongly suggest you purchase a MySQL Support license as I'm certain our support team will be able to guide you though the optimization/tuening process to speed up your query.

You can order a support license by clicking on the following link:
https://order.mysql.com/?ref=mjdc

Thanks!
[14 May 2005 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".