So if this is your query and based on the explain this is what I would recommend.
SELECT Distinct (a.id) >From table1 a INNER JOIN table1 b USING ( p_id ) INNER JOIN table2 c USING ( p_id ) INNER JOIN table3 d USING ( out_id ) INNER JOIN table4 e ON ( d.name_id = e.name_id ) INNER JOIN table4 f ON ( e.start_id BETWEEN f.left_id AND f.end_id ) WHERE (f.name_id =45 OR f.name_id =56) AND b.id =275 AND a.id != b.id For some reason you are getting the using temporary on table b, I assume based on the explain that you have an index called (id) that only has the id in it. Here's the first problem. You are limiting b, by id. And joining on p_id. So mysql is trying to use the index with the combination of both of them which is combine I assume. So removing distince really won't help with this one much, since your indexes will always have a little problems. INNER JOIN table4 f ON ( e.start_id BETWEEN f.left_id AND f.end_id ) This is probably the second biggest problem, you will always get a range. And ranges will always be slower. Don't really have a solution without actually touching the data. If you really have more than 50 million records this is really bad. a.id != b.id I would recommend trying to rewrite your query and just focus on table a and f. If you can get rid of them returning the extra 2 and 3 rows, I think that would solve your problem. Donny > -----Original Message----- > From: karthik viswanathan [mailto:[EMAIL PROTECTED] > Sent: Sunday, February 29, 2004 6:46 PM > To: Donny Simonton; 'karthik viswanathan'; [EMAIL PROTECTED] > Subject: RE: optimizing a select statement over a database with >50 > million recs > > Here is the command line explain, earlier i had used phpAdmin > > +---------+--------+-------------------------------------+--------------+- > --------+--- > ------------------+------+-------------------------------------------+ > | table | type | possible_keys | key | > key_len | > ref | rows | Extra | > +---------+--------+-------------------------------------+--------------+- > --------+--- > ------------------+------+-------------------------------------------+ > | b | ref | id,p_id,combine | combine | > 4 | > const | 5281 | Using where; Using index; Using temporary | > | c | eq_ref | PRIMARY,p_id | PRIMARY | > 4 | > b.p_id | 1 | | > | d | ref | name_id,out_id | out_id | > 4 | > c.out_id | 1 | | > | a | ref | p_id | p_id | > 4 | > b.p_id | 3 | Using where | > | e | eq_ref | PRIMARY,start_id,combine,taxId | PRIMARY | > 4 | > d.name_id | 1 | Distinct | > | f | range | PRIMARY,name_id | PRIMARY | > 4 | > NULL | 2 | Using where; Distinct | > +---------+--------+-------------------------------------+--------------+- > --------+--- > ------------------+------+-------------------------------------------+ > > > > But looking at your explain with is hard to understand as I mentioned > above, > > it look like your rows are 5281 * 1 * 1 * 4 * 2 or 42248 rows it has to > go > > through. > > I think u r right > > > Also see if you can change WHERE (f.name_id =45 OR f.name_id =56) to use > IN. > > It has proven to be much faster for me. But that won't speed it up .3 > > seconds. > > i tried that i dint see any difference. > > If I take out the distinct explain doesnt show the "Using temporary" but I > dint see > any difference in the time also. Please let me know if you need any > further > information > > Thanks > Karthik > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
