tag:blogger.com,1999:blog-9144505959002328789.post808857659120312369..comments2024-03-29T08:14:29.447+01:00Comments on Karlsson on databases and stuff: Using SQL to generate SQLKarlssonhttp://www.blogger.com/profile/04874338187076980133noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-9144505959002328789.post-20602410680430382962008-11-11T12:42:00.000+01:002008-11-11T12:42:00.000+01:00mysql -e"SELECT CONCAT('OPTIMIZE TABLES ', GROUP_C...mysql -e"SELECT CONCAT('OPTIMIZE TABLES ', GROUP_CONCAT(TABLE_SCHEMA, '.', TABLE_NAME ORDER BY TABLE_SCHEMA, TABLE_NAME SEPARATOR ',')) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname' GROUP BY TABLE_SCHEMA\G<BR/>" | awk -F: '{print $2}' | mysqlshantanuhttps://www.blogger.com/profile/04386423685935921709noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-67990229202667485752008-10-20T15:51:00.000+02:002008-10-20T15:51:00.000+02:00Or in a clickable form:http://blog.shlomoid.com/20...Or in a clickable form:<BR/><BR/><A HREF="http://blog.shlomoid.com/2008/06/how-to-perform-connection-massacre.html" REL="nofollow">http://blog.shlomoid.com/2008/06/how-to-perform-connection-massacre.html</A>shlomoidhttps://www.blogger.com/profile/08509735030020026930noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-39925424322046751462008-10-20T15:49:00.000+02:002008-10-20T15:49:00.000+02:00I am always surprised by the number of ways to do ...I am always surprised by the number of ways to do the same thing :) Btw, you can use the mysql -s parameter, which does what you want as well.<BR/><BR/>I've wrote something similar before, but dealing with connections and not table optimization statements, here:<BR/>http://blog.shlomoid.com/2008/06/how-to-perform-connection-massacre.htmlshlomoidhttps://www.blogger.com/profile/08509735030020026930noreply@blogger.comtag:blogger.com,1999:blog-9144505959002328789.post-87522053253324952032008-10-15T18:39:00.000+02:002008-10-15T18:39:00.000+02:00A minor correction, you can specify multiple table...A minor correction, you can specify multiple tables with optimize tables http://dev.mysql.com/doc/refman/5.0/en/optimize-table.html. <BR/><BR/>So you *could* do something like:<BR/>SELECT CONCAT('OPTIMIZE TABLES ', GROUP_CONCAT(TABLE_SCHEMA, '.', TABLE_NAME ORDER BY TABLE_SCHEMA, TABLE_NAME SEPARATOR ',')) <BR/>FROM INFORMATION_SCHEMA.TABLES<BR/>WHERE TABLE_SCHEMA <> 'information_schema'<BR/>GROUP BY TABLE_SCHEMA;<BR/><BR/>(should also probably quote the schema components, just in case)<BR/><BR/>However, with a lot of tables you'll still hit group_concat_max_len (default 1024) or if you increase that, then max_allowed_packet.Andrewhttps://www.blogger.com/profile/01066694104880792602noreply@blogger.com