Re: A MySQL Question

tedd wrote:

At 3:24 PM +0000 12/7/08, Nathan Rixham wrote:

On the same not does anybody else frequently use (or even know about) the awesome spatial extension for mysql? or use the information_schema tables?

I've certainly never used them, but I can imagine information_schema tables that are similar to DOCTYPE as found in xml for defining fields -- is that it? Cheers, tedd

not many have :p but there's so much more power and speed can be opened up by using them.. to get you started ( I highly recommend this ) try: SELECT * FROM information_schema.TABLES; then: SELECT * FROM information_schema.COLUMNS then cunning way's to combine the two.. for instance you could query these two tables to return back a list of all columns in database X which are of type text and use them to search you're entire database in one go like: select table_name, column_name from information_schema.columns WHERE table_schema='DB_NAME' AND data_type IN ('char','varchar','text') AND CHARACTER_MAXIMUM_LENGTH >3;select table_name, column_name from information_schema.columns WHERE table_schema='DB_NAME' AND data_type IN ('char','varchar','text') AND CHARACTER_MAXIMUM_LENGTH >3; replace DB_NAME for the database you want to search, you may want to include type's like mediumtext etc.. point is, the information_schema tables reside in memory so are ultra quick to query; and they are always up to date; and they give you far more information than normally available. so many options.. rows in a database (not table) SELECT SUM(TABLE_ROWS) FROM information_schema.TABLES WHERE TABLE_SCHEMA='DB_NAME'; etc etc.. regards!