i found great article here http://www.empulsegroup.com/?p=342
But when i try:
root@vps:~# for i in `mysql -e 'select concat(table_schema,".",table_name) from information_schema.tables where engine="MyISAM"'`; do mysql -e "optimize table $i"; done
ERROR 1046 (3D000) at line 1: No database selected
many thanks for your time.
Despite the statement in that article, you probably don't need to run optimize table very often. It really won't make queries run much faster.
But let's talk about the error you got and how to fix it.
The first line out output of your query is not a table name, it's the column heading, which in this case is the expression you used.
for i in `mysql -e 'select concat(table_schema,".",table_name) from information_schema.tables where engine="MyISAM"'`;
do
echo "$i";
done
Output:
concat(table_schema,".",table_name)
test.m
(I had to create a table m that was MyISAM, because I don't use MyISAM normally, and I recommend that you should not use MyISAM either.)
If you want to skip the column heading, use the -N or --skip-column-names option.
for i in `mysql -N -e 'select concat(table_schema,".",table_name) from information_schema.tables where engine="MyISAM"'`;
do
echo "$i";
done
Output:
test.m
You may also want to delimit the schema and table name, because either name might be a reserved word.
for i in `mysql -N -e 'select concat("\`",table_schema,"\`.\`",table_name,"\`") from information_schema.tables where engine="MyISAM"'`;
do
echo "$i";
done
Output:
`test`.`m`
You specify the database to use for the mysql command by using the -D or --database=... argument.
