Re: Big SELECT: ordering results by where matches are found

At 13:34 -0400 10/9/07, Baron Schwartz wrote:
>Looks like you've found the solution you need. The only other
>suggestion I have is to use UNION ALL if you don't need to eliminate
>duplicate rows in the UNION, because there's some overhead for
>checking for them.

Hi Baron

Thanks for this, and I did try it, but the difference in time taken to execute the query was negligible (I tested it multiple times) - it was around 0.02 seconds whichever way I did it, and when I used EXPLAIN, the results were identical except for one detail:

The number of rows in the first row of the EXPLAIN result was lower with plain UNION than if I used UNION ALL. As far as I can tell from my relatively limited experience with all this, the first row refers to my outer 'wrapper' select from the derived table (the table in the first row is given as '<derived2>' and the Extra column shows 'Using temporary'). For a given query, with UNION ALL that has 45 rows, with UNION it's 31. So I guess I'll stick to plain UNION.

As far as my desire to cope with multiple search terms is concerned, I realise now that fulltext handles that anyway! So I've changed the few non-numeric fields that weren't indexed that way (fore, sur and topic) to fulltext and bingo! Not only that, but it all happens fully FOUR TIMES as quickly!

So many thanks, Baron - mainly due to you, yesterday was a very good MySQL day for me. It's not often I get two 'lightbulb moments' on the same day!

-- 
Cheers... Chris
Highway 57 Web Development -- 
http://highway57.co.uk/

Good people will do good things, and bad people will do bad things.
But for good people to do bad things - that takes religion.
    -- Steven Weinberg, physicist and Nobel Laureate

-- 
MySQL General Mailing List
For list archives: 
http://lists.mysql.com/mysql
To unsubscribe:    
http://lists.mysql.com/mysql?unsub=lists@pantek.com