The new InnoDB full-text search feature in MySQL 5.6 helps in this case. I use the following query: SELECT MATCH(column) AGAINST(‘(word1* word2*) (“word1 word1”)’ IN BOOLEAN MODE) score, id, column FROM table having score>0 ORDER BY score DESC limit 10; where ( ) groups words into a subexpression. The first group has like word% meaning; … Read more
I once worked with a very large (Terabyte+) MySQL database. The largest table we had was literally over a billion rows. It worked. MySQL processed the data correctly most of the time. It was extremely unwieldy though. Just backing up and storing the data was a challenge. It would take days to restore the table … Read more
For those who are running DB on different machine, you can do the following: First, from where the container is, run docker ps to get the containers details including the Container ID [root@test-001 ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 1b02333fb3b9 tutum/nginx “/usr/sbin/nginx” 6 weeks ago Up 7 days 0.0.0.0:80->80/tcp docker_nginx_1 … Read more
The accepted solution is, unfortunately, wrong. It is right as far as it says, Kill the connection with lock This is indeed (almost surely; see below) what to do. But then it suggests, Kill 1398 …and 1398 is not the connection with the lock. How could it be? 1398 is the connection waiting for the … Read more
Late answer… You can use CAST or CONVERT thus CAST(foo AS CHAR(100)) CONVERT(foo, CHAR(100)) Supported types (5.5) are: BINARY[(N)] CHAR[(N)] DATE DATETIME DECIMAL[(M[,D])] SIGNED [INTEGER] TIME UNSIGNED [INTEGER] You can not cast to varchar directly. There is an open MySQL bug from 2008 which no-one seems to care about and is damn annoying
The reason why I usually suggest PostgreSQL before MySQL is because MySQL is far from the standards (SQL-wise). It does not support the use of window functions (8.4 version), common table expressions (8.4), CHECK constraints, EXCEPT/MINUS operator, even FULL OUTER JOINs… Even though you may have never heard of these words, you’ll have to use … Read more
There’s a clear best option out of the three you suggested (plus one from the comments). BIGINT — uses just 8 bytes, but the largest BIGINT only has 19 decimal digits; if you divide by 1018, the largest value you can represent is 9.22, which isn’t enough range. DOUBLE — only has 15–17 decimal digits … Read more
It is possible that it could be faster because the LIKE condition can be evaluated more quickly then the regular expression so if most rows fail the test it could be faster. However it will be slower if most rows succeed as two tests must be run for successful rows instead of just one. It … Read more
Delete or comment the bind_address parameter from the my.ini file. (The file name is different depend on the OS. On Linux my.ini is actually my.cnf located in directory /etc/mysql/) Restart the service. Create the root user (yes, a new user because what exists is ‘root@localhost’ which is local access only): CREATE USER ‘root’@’%’ IDENTIFIED BY … Read more
You can also have the IN clause take the results of a query, such as: SELECT * FROM table1 WHERE table1.id IN ( SELECT id from table2 ) That way, you don’t need to generate a text string with all the possible values. In mysql, you should be able to put as many values in … Read more