migration_problem

[kazajshaki miro]

I had a problem after migration from sql server2000 to mysql4.1

I had a query that was getting output results in max 3 seconds on sql server 2000

but took about more than 30 mins to output results using mysql4.1

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

(After Migration) I ran explain for this select query and give the output results:

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------+-------+----------------------------------------------+
| 1 | SIMPLE | T3 | ref | idx2 | idx2 | 50 | const | 21442 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | T2 | ref | idx2 | idx2 | 50 | const | 35457 | Using where |
| 1 | SIMPLE | T1 | eq_ref | PRIMARY,idx2 | PRIMARY | 50 | func | 1 | Using where


All the tables used in the query are:
1- MYISAM Type
2-VarChar Column Type
3-Contain 50365, 235673 records


Our Hardware server is with high memory 2GB and 3 GB CPU .

And the OS Environment is RedHat Linux ES

Is there any problem of migration from windows sql server 2000 to linux mysql4.1 server which may causing this slow down in query execution?

thank you