DB is MySQL i need to optimize this query select distinct word,url2 from search order by datetime2 Asc limit 250 every time it runs it createa a temp table Query 4 Copying to tmp table select distinct word,url2 from search order by datetime2 Asc limit 250 I have a index on word field process list "search" table has 1000000 records Regards Alex
Have you tried using group by instead of select distinct? SELECT word, url2 from search GROUP BY word ORDER BY datetime2 ASC limit 250
hi Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/www2/public_html/footer2.php on line 59 Warning: Invalid argument supplied for foreach() in /home/www2/public_html/footer2.php on line 73 Warning: array_multisort() [function.array-multisort]: Argument #1 is expected to be an array or a sort flag in /home/www2/public_html/footer2.php on line 78
This works But in process list it takes same time Mysql says both are equivalent I want optimization 9 Copying to tmp table SELECT word, url2 from search GROUP BY word,url2 ORDER BY datetime2 ASC limit 250
You could try adding an index on url2, that should help. Also, what do you do with the data once it is in the temp table? Does it need to be ordered in the temp table or can you do the order by as you pull it from the temp table. Since the temp table will have fewer records the order by clause will have a much smaller effect on the query.
You would not use DISTINCT or GROUP BY. Copy the rows in the temp table, then use GROUP BY on the temp table, on which you will build an index on word,url2 the query will love.