Today I played to optimize a slow SQL request type

SELECT * FROM 'table' WHERE `field` LIKE '%word%'

Where is the troublesome one moment here – the last part word% ‘%’ and in even greater concreteness characters % before the word, for that we do. Wildcard symbol % ,before any value, our application directly translates directly into a slow, because in this way the application stops us to use the indexes of the. As always there are solutions, but are not always clear 😆 generally MySQL you have a solution to this problem with fulltext search Indexing of the. How is the change of the field there is a lot written in the documentation, but in a hurry I will describe how I change the top request, because we will get to a little drama finally. Make a cute as applicable to fulltext field up, the query needs to be changed in the form:

SELECT * FROM `table` WHERE MATCH (field) AGAINST ('word')

So the structure is obvious and there is no need for unnecessary discussion. The above query will enter into force, If the word, for which you are making a request at least 4 the symbol, This is the default value, If you want to modify it, you must specify the value, you want to my.cnf in part [mysqld] with the Declaration ft_min_word_len= 3 or 2, 1 not a good choice obviously 😉 . After you change the value and restart the mysql server-need to do a repair of tables, in order for the new indexing enters into force. Here all clear: do you change, I reset, rebildvam indices and make my request and returns 0 Checking line with 😀

SHOW VARIABLES

I see that the values, I've asked have entered into force, rebildvam indexes again – same result. 🙄 Hate, very uncomfortable. From here on began a large cursing and scratching the key to the shed 😀 who was quite, pretty interesting. Generally, I started reading documentation do not know which road and came to an interesting passage

Such a technique works best with large collections (in fact, it was carefully tuned this way). For very small tables, word distribution does not adequately reflect their semantic value, and this model may sometimes produce bizarre results. For example, although the word "MySQL" is present in every row of the articles table shown earlier, a search for the word produces no results

BEAM 😳 Dame tray I was small – It was still a test. In a large application Naših table with over 2 000 000 order and there things are asleep. Well it's already clear the problem. To make it clear the solution, I will mention briefly, that full text search support 3 Advanced modes BOOLEAN , EXPRESSIONS and NATURAL LANGUAGE as the last works by default. About the different modes you can check the documentation, I'll explain with 2-3 words to BOOLEAN because it is the key. It supports logical operators of the type AND, OR , NOT and so on and can make some magic with the search phrases, to have a, is there another, etc.. Supports and symbol *, that is the equivalent of wildcard characters % 😉 It is useful, When the search term is in the length of the ft_min_word_len or small trays ;). At least to me at the table with about 100 the line is doing a perfect job. The only thing left to see and the finished application:

SELECT * FROM `table` WHERE MATCH (field)
AGAINST ('*word*' IN BOOLEAN MODE)

Now here comes a time indexing works with us wildcard characters – the answer is I don't know. Basically I, to, because otherwise indicated in the documentation, but the documentation is clearly not say or show much 😀

Enhanced by Zemanta

How PulseAudio works.

Image via Wikipedia

Today most- Finally, after a lot of time I decided to buy another cable to hang 5.1 I need a audio system. So far I used it with a normal stereo cable and then the system alone make partition of audio channels. Which is not so good. Now for each channel has a separate cable and the sound is pretty neat (Naturally), the dense and the films feel has been improved many times. There were a few little dramas that are fixed relative intuitively. The main drama was, that left only the front columns without buffer or rear Center after I have recalibrated the sound scheme of Gnome is 5.1 . Most of the players have the option of setting up in the alsa how to channel the audio but there are programs in which absent such opportunity as, say, adobe flash player. My initial idea was just to reconfigure the parameters as ask asoundrc. duplicating the sound on all channels – decision with which I'm not proud of it at all because I don't consider. Configuration trivial :

pcm.!default {
type plug
slave.pcmsurround51
slave.channels 6
route_policy duplicate
}

With her things came on the spot until I realized that alsa can process only 1 process and while he finishes to use it else can. 😳 Неприятно но факт бях забравил времената в който слушаш или player или youtube и ако едното ти омръзне трябва да затвориш програмата, You can use the other application. I immediately told me where is the true root of things – the configuration of the pulseaudio Server. Pulseaudio roughly speaking is the proxy server that handles the information between audio hardware and audio software that wants to use it. System that for years has proved to be many multiples. It is located in the configuration in/etc/pulse/daemon.conf. By default, it is zakomentirano with ; so if you are not praivli changes might boldly add the following lines at the end of the file:

enable-lfe-remixing = yes

default-sample-rate = 48000
default-sample-channels = 6

The names of the options are asking the obvious value of how we're going to be a small channel audio tuning of the standard bitrate of lfe and razrešvane remiksiraneto. След това остава да рестартирате alsa и pulseaudio с

sudo /etc/init.d/alsa-utils restart && pulseaudio -k

From now on only for me and enjoying the good audio.

edit: .asoundrc must be content to have no problems with skype and other applications

pcm.pulse {type pulse }
ctl.pulse { type pulse }

Enhanced by Zemanta