[fdo-dev] Temporary tables from information_schema

Brent Robinson brent.robinson at autodesk.com
Mon Dec 11 11:51:38 EST 2006

Hi Mateusz,

The temporary tables are mainly for performance. Selects that joined 2
information_schema tables were getting n-squared performance depending
on the total number of tables or column in the MySQL instance. These
selects appear to not make use of indexes. Surprisingly, it was much
faster to copy the information_schema data into temporary tables, add
indexes, and then select from the temporary tables than select directly
from the information_schema. 

This performance issue is particular to MySQL so you likely won't need
the implement temporary tables for PostGIS.


-----Original Message-----
From: Mateusz Loskot [mailto:mateusz at loskot.net] 
Sent: Monday, December 11, 2006 11:17 AM
To: fdo-dev
Subject: [fdo-dev] Temporary tables from information_schema


Could anyone explain me what is the purpose of temporary tables
created on base of information_schema table by MySQL provider?

The MySQL Owner class creates temporary tables for
information_schema.tables, information_schema.columns, etc.
and populates these temp tables with subset of original rows.

The comment inside sources says:

"Create the temporary table from the <NAME OF SOURCE TABLE> table.
Make all string fields case sensitive."

Is this case (in)sensitivity the reason?

I also see, this temporary tables are optional - controled by
boolean parameter.

I wonder if I need this feature in PostGIS. Currently, I don't see any
reason, but I also don't see the reason behind these tables for
MySQL provider, so it's hard to be sure.

Thanks for any explanation
Mateusz Loskot

To unsubscribe, e-mail: dev-unsubscribe at fdo.osgeo.org
For additional commands, e-mail: dev-help at fdo.osgeo.org

More information about the Fdo-internals mailing list