Re: Free text search in MySQL

On 31/03/16 19:25, Thomas 'PointedEars' Lahn wrote:
> Conciseness can one deceive over the underlying complexity.

Worse, it can make a seemingly blindingly neat piece of SQL into a total performance nightmare, as I discovered trying to build a relational database out of 50 million lines of a-relational CSV.

In the end I threw all but the simplest 'read a line of this table' out, and used C code to do the smarts, It ran about 1000 times faster tuned to the task than generic MySQL did. I had written the code and done the task before the 'clever SQL' would have finished running.

And I am not the only one to notice this. Much as I love MySQL its ability to not turn complex queries into efficient internal algorithms is rather marked. Be warned.

Sometimes its as easy to read the entire table in, and sort through the records at application level, especially when you haven't got a good index on the field you are searching in. And let your machines virtual memory take the strain, rather than MySQL's 'temporary table' architecture.

-- 
The biggest threat to humanity comes from socialism, which has utterly 
diverted our attention away from what really matters to our existential 
survival, to indulging in navel gazing and faux moral investigations 
into what the world ought to be, whilst we fail utterly to deal with 
what it actually is.