[fdo-dev] Temporary tables from information_schema

[Mateusz Loskot]

Brent,

Thank you for this clarification.
I've got rid of these features already, so if it is performance-only,
I can defer implementing it for next week, if I will
find it's good to have it too.

I'm going to consult this subject regarding PostgreSQL with Paul Ramsey.

Cheers
Mateusz

Brent Robinson wrote:
> 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.
> 
> Brent.
> 
> -----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
> 
> Hi,
> 
> 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
http://mateusz.loskot.net