Mysql Query performance

[anandsatchin]

Hello,

wondering whether anyone could shed light on my problem..

Basically, I need to exectute a query,
when I execute the first query (with left join), it takes nearlly 4 mins 29.88 sec
and the same query (with right join), it takes nearlly 0.22 sec.

I know I might be wrong somewhere..can anyone tell me how to optimize the first query

Cheers
Anand

Details
--------

P4, 1GB, Windows xp

mysql 5.0(All tables uses InndoDB Type)

Table Name Field used for Indexes table rows

fujitsu_returns job_number 24900
internal_ref

repair job_number 16809

Queries
-------

1. Using Left Join

SELECT date_into_repair,date_repair_completed,repair_completed, product_category_id, doa_or_nff,repair.job_number,fujitsu_returns.job_number
FROM fujitsu_returns left join repair on fujitsu_returns.job_number = repair.job_number
where doa_or_nff like '%doa%' and (date_out_sorting is not null or date_out_sorting != '000-00-00')
and (date_repair_completed is null or date_repair_completed = '0000-00-00')
order by fujitsu_returns.batch_number, fujitsu_returns.job_number

Output
1424 rows in set (4 mins 29.88 sec)

2. Using Right Join

SELECT date_into_repair,date_repair_completed,repair_completed, product_category_id, doa_or_nff,repair.job_number,fujitsu_returns.job_number
FROM fujitsu_returns right join repair on fujitsu_returns.job_number = repair.job_number
where doa_or_nff like '%doa%' and (date_out_sorting is not null or date_out_sorting != '000-00-00')
and (date_repair_completed is null or date_repair_completed = '0000-00-00')
order by fujitsu_returns.batch_number, fujitsu_returns.job_number

Output
86 rows in set (0.22 sec)

 

[guelphdad]

Do you have your tables properly indexed?

are you running the second query directly after the first? My guess is that the query is cached and is returning the results from the cache and thus the improvement in time.

 

[anandsatchin]

hi,

The tables are indexed properly. The main diffence between 2 queries are that, if I use the left join it takes longer time( and almost hangs) and if u use right join it takes fraction of seconly only.

I dont know why it is happening..

Cheers Anway