Is It max performance on one server or did I missed something ?

MySQL & MariaDB Other MySQL® Questions
21
[B]xaff wrote on Fri, 11 June 2010 11:50[/B]

I added index but,there still “f#%?! copying to temp table ^^” it seems because of “… * (vtpDomain.Rank + vtpUri.Rank)”, without those EXPLAIN no more says Using Temporary.

Yes you might still get copying to temp table, but what is actually the performance at this point after the last index and how many rows you got left in the result set?

I ask this question since the WHERE conditions and the JOIN should have reduced this result set to a manageable size, or do you still a lot of rows so that the intermediate table is large?
You should calculate this size and set the tmp_table_size values about maybe 15-20% larger than the size of this temporary table instead of 1000M as you have now.

And on a side note you can rewrite your query like this, where you place the conditions for each table in the JOIN condition instead of the WHERE. It makes it easier to read and know which indexes that should exist on each table:

SELECT SQL_NO_CACHE HIGH_PRIORITY SQL_NO_CACHE SQL_CALC_FOUND_ROWS vtpUri.Id as UriId, (ws0.Score) * (vtpDomain.Rank + vtpUri.Rank) as uriScore, vtpUri.Domain_Id FROM vtpUri INNER JOIN vtpWordScore AS ws0 ON (ws0.Uri_Id = vtpUri.Id) AND ws0.Word_Id = 4598 – ← hereINNER JOIN vtpDomain ON (vtpDomain.Id = vtpUri.Domain_Id) AND vtpDomain.vtpCategory_Id != 2 – ← here AND vtpDomain.IsOff != 1 – ← hereORDER BY uriScore desc LIMIT 0,400