When specifying a table in input file, only select relevant data from information_schema

[sosso]

LOAD DATABASE FROM mysql://root:root@localhost/mydb?mytable
 INTO postgresql://postgres:postgres@localhost/pgdb?public.mytable
INCLUDING ONLY TABLE NAMES MATCHING 'mytable';

Even though I'm only targeting one table, it generates the following query:

select c.table_name, c.column_name,
         c.data_type, c.column_type, c.column_default,
         c.is_nullable, c.extra
    from information_schema.columns c
         join information_schema.tables t using(table_schema, table_name)
   where c.table_schema = 'mydb' and t.table_type = 'BASE TABLE'
order by table_name, ordinal_position;

On my production system, that query takes 15 minutes, as we have a very large number of very wide tables; It would be great to add an additional where to only hit my specific table.

[dimitri]

Currently the MySQL source is only meant to support full MySQL migration rather than piecemeal migration like what you're talking about. Producing the right SQL query using the REGEX MySQL operator rather than filtering the query result in the client might solve your problem, but might not solve it either.

Could you try the following query and tell us how long it takes?

select c.table_name, c.column_name,
         c.data_type, c.column_type, c.column_default,
         c.is_nullable, c.extra
    from information_schema.columns c
         join information_schema.tables t using(table_schema, table_name)
   where c.table_schema = 'mydb' and t.table_type = 'BASE TABLE'
            and c.table_name REGEXP 'mytable'
order by table_name, ordinal_position;

[sosso]

Hi,

That query takes 1 second compared to the 14 minutes of the other one. I'm not familiar with Lisp, but I think you could just also add and c.table_name = 'mytable' and achieve the same result? Regex works as well, clearly.

[dimitri]

Please test this commit which should fix it for you.

We support both regex and equality in the INCLUDING clause, so I wanted to make sure we had a way to implement both in the generated SQL query.

[sosso]

Does the --self-upgrade feature still work? It does not seem to work on 3.0.99, and is not in the usage statement.

It looks like the linked debian package does not reflect the current HEAD. I can try building from source.

[dimitri]

I've been told the --self-upgrade only works when you have a local copy of all the dependencies (as obtained with make libs) but I didn't have time to care about those parts yet. That said, 3.0.99 didn't have the option yet.

Please consider building from source, yes.