Alter a table to create a fulltext index of the columns specified..
ALTER TABLE tablename ADD FULLTEXT(column1, column2);
Search a fulltext index, using query expansion which increases relevancy but decreases the speed of the query. Further speed up the query by limiting the result set to 10 results.
SELECT * FROM tablename WHERE MATCH(column1,column2,column3) AGAINST('Search Phrase' WITH QUERY EXPANSION) LIMIT 10;
To import data from a text file into a MySQL database, assuming the text file is a list with each new record on a new line. For example:
Number OneUse the following MySQL command:
Thing number 2
and a Third
LOAD DATA INFILE '/home/user/Desktop/file.txt' INTO TABLE tablename (column1);This import will only work on a database that is already setup with the proper column settings and data type.
Execute MySQL query from a bash shell script:
#!/bin/bash
TABLE_NAME=testtable
USER_NAME=root
IP_ADDR=localhost
PASSWD=Secret
somevar=`echo "select column1 from testtable where idkey='5'" | mysql -h $IP_ADDR -u $USER_NAME -p$PASSWD $TABLE_NAME `
echo $somevar
Notes on the above bash script: You can replace the MySQL query in the script with any MySQL command.
Dont forget about the ticks ( ` ` ) that encapsulate the commands inside. This is a useful trick that took me a while to research and figure out. I wanted to set a variable to the results of some commands and found that you need to use the ` ` , not the ' (single quote) this tick shares the same key as the tilde key, to the left of the 1 key.
To view any query that may be hung up or taking a long time to execute:
show full processlist
This will display a table of the MySQL processes running, the process ID, and the Status.
If you find a rogue or otherwise not needed query you can kill that process ID using:
kill 1234Where 1234 is the process ID that you want to end.
More coming soon.
No comments:
Post a Comment