Changing a domain in WordPress is a pain. I've had to do a few lately and things are happening sportily fast 😀 . If I can summarize the steps are 2 – of course without moving the files, настройките ако се сменя изцяло хостинга.

1. Промяна на старото URL със новототука нещата са тривиални. Отваряте си wp-config.php файлът и във него поставяте следните 2 order

define('WP_HOME','http://example.com');
define('WP_SITEURL','http://example.com');

Като замествате http://example.com със вашият нов.

2. So far, the site opens well, the urls work, but the uploaded content as pictures, documents and so on are not visible. Rough intervention is already required here. The old urls must be replaced with the new ones in the database. This was a terribly unpleasant process, especially for novice users, which do not handle SQL syntax well, but there is already a pretty nice script searchreplacedb2, who does everything unpleasant for you. Its use is trivial – you upload it to the main directory where your wordpress page is located and open it through your browser. След това следвате стъпките като първо ще ви пита за потребителско име и парола който е взел от вашия wp-config.php и след това ще ви пита за новото и старото url. After the last step you will have to wait for me it took an average of 40 seconds -50 seconds.

This is basically nothing difficult or super complicated.

Image representing MySQL as depicted in CrunchBase

Some time ago I wrote about MySQL Full Text Search 🙂 Today I had a very interesting experience with one request. In general, the query looks for results that are missing from another table. One basic Select and one sub select in the WHERE part of the query. In general, the skeleton is

SELECT DISTINCT (
`field`
)
FROM `table1`
WHERE `someID` =44
AND `firsTextField` NOT
IN (

SELECT DISTINCT (
`secondTextField`
)
FROM `table2`
WHERE `otherID` =44
)

Basically a simple query. I wrote it for 30 sec I run it and the machine loops. After a long and patient wait on my part or more precisely ~ 43 sec . I spat out the result lol . Pffff madhouse. I enter the machine watching the CPU is normally loaded almost in idle condition. Shock and horror. I run the query again the same result. Fuck WTF. I run explain the query and everything shines – the second field secondTextField is only full text search without index, and there the plate is modest of about 35k lines. Who to read – full text search is not an index. The problem is quickly clear one

ALTER TABLE `links` ADD INDEX ( `linkUrlID` ) 

And things fell into place Query took 0.0005 sec 😀

Be careful how you put your indexes, the speed of the query depends on them marginally.

p.s In general, I am to blame for the above situation not only because it lacks an index but because it does not use the full text search method 😀

Enhanced by Zemanta

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, която желаете в my.cnf в частта [mysqld] with the declaration ft_min_word_len= 3 or 2, 1 не е добър избор очевидно 😉 . 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

SHOW VARIABLES

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

ГРЕДА 😳 Дам табличката ми беше малка – 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)
AGAINST ('*word*' IN BOOLEAN MODE)

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