Today I played to optimize a slow one SQL request of the type

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

Where is the problematic moment here – last part ‘% word%’ and in even greater specificity the sign % before the word, for which we do. Wildcard symbol % ,before any value, directly turns our request directly into slow, because in this way our query stops using field indexes. Solutions as always, but they are not always clear 😆 In general MySQL have a solution to this problem with fulltext search field indexing. How to change the field is much written in the documentation, but I will quickly describe how the above query changes, because we will come to a little drama at the end. Follow by applying the fulltext to the box above, the request must change in type:

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

Thus, the structure is obvious and there is no need for unnecessary discussion. The above application will take effect, if the word, for which you are requesting is at least 4 characters, the default is this value, if you want to modify it you must specify the value, which you want in my.cnf in the part [mysqld] with the declaration ft_min_word_len= 3 or 2, 1 not a good choice obviously 😉 . After changing the value and restarting the mysql server you need to repair your tables, so that the new indexing can take effect. So far, everything is clear: I make the changes, restart, I rebuild the indexes and make the request and it returns to me 0 line 😀 I check with


I see that values, which I have set have come into force, I rebuild the indexes again – the same result. 🙄 Unpleasant, very unpleasant. From here, a big swearing and digging for the key to the shed began 😀 Which turned out to be quite, quite interesting. Generally, when I started reading the documentation for I don't know which way and I 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 😳 I give my plate was small – it was a test after all. Our query in a large table with over 2 000 000 order and there things fell asleep. Well, the problem is already clear. To make the decision clear, I will mention briefly, that full text search supports 3 advanced modes BOOLEAN , EXPRESSIONS and NATURAL LANGUAGE as the latter works by default. You can check the documentation for the different modes, I will explain with 2-3 words for BOOLEAN because it contains the key. It supports AND logical operators, OR , NOT and so on and you can do various spells with the searched phrases, to have one, to have no other and so on. It also supports the symbol *, which is the equivalent of the wildcard symbol % 😉 It is useful, when the search word is below the length of ft_min_word_len or for small plates ;). At least for me on a table with about 100 the order does a perfect job. It remains only to see the completed request:

SELECT * FROM `table` WHERE MATCH (field)

Here comes the moment whether indexing with the wildcard symbol works for us – the answer is I don't know. Basically I think, that yes, because nothing else is said in the documentation, but the documentation obviously doesn't say or show many things 😀

Enhanced by Zemanta

How PulseAudio works.

Image via Wikipedia

Today most- finally, after a chamber of time, I decided to buy more cables to hang up 5.1 my audio system fully. Until now I used it with a normal stereo cable and then the system divided the audio channels itself. Which is not good at all. Now there is a separate cable for each channel and the sound is much clearer (of course), the denser and in the movies the feeling is improved many times over. There were a few small dramas that were relatively intuitive. The main drama was, that the sound went only in the front speakers without buffer center or rear after I reconfigured the sound scheme of Gnome to be 5.1 . In most players I have the option to set in alsa part for how many channels the audio is, but there are programs that do not have such an option as, say, adobe flash player. My original idea was just to reconfigure .asoundrc by setting parameters to duplicate the sound on all channels – a decision I'm not proud of because I didn't consider it at all. The configuration is trivial :

pcm.!default {
type plug
slave.pcm “surround51”
slave.channels 6
route_policy duplicate

With it, things fell into place until I realized that alsa can only handle more 1 process and until he finishes using it another can not. 😳 Unpleasant but in fact I forgot the times when you listen to either player or youtube and if one of you gets bored you have to close the program, to use the other application. Which immediately told me where the real root of things was – the pulseaudio server configuration. Pulseaudio is roughly a proxy server that processes the information between the audio hardware and the audio software that wants to use it. A system that has proven itself many times over the years. При нея конфигурацията се намира в /etc/pulse/daemon.conf. По подразбиране всичко е закоментирано с ; така че ако не сте праивли промени може направо смело да добавите следните редове накрая на файлът:

enable-lfe-remixing = yes

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

The names of the options are obvious we set the value of how much channel audio we will be a small tuning of the standard bitrate and enable lfe remixing. След това остава да рестартирате alsa и pulseaudio с

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

From now on, all that's left for me is to enjoy the already good audio.

edit: .asoundrc must be with content so that you do not have problems with skype and some other applications

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

Enhanced by Zemanta