To do a mysql data import and update of the work. Data source is excel in the past.
Select the top left corner of the element inside excel, ctrl + shift + end, select all of a content cell. Save the file as a abc.txt.
Data only 2 columns, built a 2 column table, ready to load into the data, with root user logged in the shell run the following command mysql
load data infile '/ root / test.dat' into table mytable FIELDS TERMINATED BY '\ t';
Results of error.
ERROR 13 (HY000): Can't get stat of '/ root / test.dat' (Errcode: 13)
Then google, that is the question of powers, it seems likely that mysql can not read the document root directory, which was replaced with the one directory, problem-solving.
load data infile '/ tmp / test.dat' into table ppname FIELDS TERMINATED BY '\ t';
But then the problems, the basic let me crash.
Example

The naked eye looks, a and b are the same, then I like to match in accordance with strange is how to do it will not work at first thought it was the case in question, made a lower operation does not work.
Was beginning not to trust your eyes, look in the end what is

This time only to find the back of each character more than a
0D
Was the question came up for an hour, frustrated to death. Do the following treatment, the clouds finally cleared to see the Sun.
update mytable set b = UNHEX (TRIM (TRAILING '0 D 'FROM hex (b)));

The strange thing is I do not come out of this simulated data, originally thought it was not added to "LINES TERMINATED BY '\ n'" problem, but no line was added,: S
It seems eBay item ah.

Popularity: 17%

mysql has a function that can log down and run slower sql statement, the default is not the log, in order to enable this feature, to modify the my.cnf or mysql startup add some parameters. If you modify the my.cnf which is required to add the following lines

long_query_time = 1
log-slow-queries = / var / youpath / slow.log
log-queries-not-using-indexes

long_query_time is over how long the sql execution log will be down, here is 1 second.
log-slow-queries set to log written there can be empty, the system will give a default file host_name-slow.log, I generated the data log directory in mysql
log-queries-not-using-indexes is the literal meaning, log off do not use the index query.

Open the above parameters, run time, you can turn off the backs with affected production environment.

The next step is analysis, I have here a document called host-slow.log.
Following the first mysqldumpslow-help, I mainly used the
-S ORDER what to sort by (t, at, l, al, r, ar etc), 'at' is default
-T NUM just show the top n queries
-G PATTERN grep: only consider stmts that include this string

-S, is the order of the sequence, indicating not written in detail, I use down, including reading the code, mainly
c, t, l, r and ac, at, al, ar, respectively, in accordance with the query frequency, time, lock time and return the number of records to sort, preceded by the time a flashback
-T, is the top n means, that is, how many return to the previous data
-G, the back can write a regular pattern matching, case insensitive

mysqldumpslow-sc-t 20 host-slow.log
mysqldumpslow-sr-t 20 host-slow.log

The command can be seen up to 20 visits sql statement and return records set up to 20 sql.
mysqldumpslow-t 10-st-g "left join" host-slow.log
This is in accordance with the time to return to the previous 10 which connect the sql statement with the left.

Using this tool you can check out which sql statement is performance bottleneck, optimize, such as the index increases, the realization of the application methods.

Popularity: 30%