Rebuilding Tag-files

[ecco]

Hi,

I have a suggestion for rebuilding the Tag files.

It is now possible to turn on rebuilding Tag files at startup or do it manualy.

It would be great to have the possibility to automaticly rebuild only the tag files of the active/selected database.

second maybe you can add an option in de dropdown menu of the database (right click on a DB in the Object browser) to rebuild tags of that DB.

[Martin Larsen]

ecco wrote on Aug 24 2006, 10:30 AM:

It would be great to have the possibility to automaticly rebuild only the tag files of the active/selected database.

I would like that very much too.

But I was thinking if it was possible to use the information in information_schema.tables to check which tables have been changed since the last rebuild of the tag files.

It would be nice if SQLyog could keep track of this and only rebuild the tags for those tables which have changed.

[peterlaursen]

Just go on .. ideas are welcome. Actually I was not aware the Information_Schema keeps track of changed tables and the time of it. But that is right, it does.

We will not change anything here in the 5.2 tree, I think. But never say 'never' …

Quote:

It would be great to have the possibility to automaticly rebuild only the tag files of the active/selected database.

It is a possibility to define a single database with the connection.

Except that on some systems the tag-building gives some audible harddisk noise, are there then any other issues? Isn't the system responsive?

[Martin Larsen]

peterlaursen wrote on Sep 5 2006, 10:40 AM:

Just go on .. ideas are welcome. Actually I was not aware the Information_Schema keeps track of changed tables and the time of it. But that is right, it does.

Yes, I just found out too. The information in information_schema is actually quite nice.

Would it be possible to implement the autocompletion without the tags file, only using the information in information_schema?

It seems like it would be possible to use:

Code:

select * from `columns` where table_schema='' and table_name='

' order by column_name;

But I guess that depends on performance.

peterlaursen wrote on Sep 5 2006, 10:40 AM:

It is a possibility to define a single database with the connection.

No, it would not fit in with my working-style.

peterlaursen wrote on Sep 5 2006, 10:40 AM:

Except that on some systems the tag-building gives some audible harddisk noise, are there then any other issues? Isn't the system responsive?

Yes, it is. My “problem” comes from the fact that I

• Works with servers with many databases, each having quite a lot of tables (50-70 databases with 150 tables each are common)
• Have other programs making changes to the database schemas
• Works on a laptop

It's not a major problem, but it is one of the only annoying things for me when using SQLyog.

[peterlaursen]

hmmmm …..

I do not think that 10% of MySQL servers in use yet are MySQL 5.x.

But obviously we should keep an eye with this posibility and the developments.

A small SQLite table holding the time of last rebuild that could be compared with the value in I_S and skipping tables that have not changed is absolutely a possibility. And for those people having thousands of tables with hundreds of columns it does make sense!

I do not believe in using Information_Schema 'on the fly'. SQLite is undoubtly faster with remote servers at least. I cannot tell what query SQLyog sends to get the columns-list with MySQL 5, but somebody else must be able to.

But one thing is certain and that is that querying MySQL Information_Schema is not fast as of now. 'SHOW' statements are much faster! This bug: http://bugs.mysql.com/bug.php?id=19588 is very much a 'show-stopper' for MySQL 5.

[Martin Larsen]

peterlaursen wrote on Sep 5 2006, 11:19 AM:

hmmmm …..

I do not think that 10% of MySQL servers in use yet are MySQL 5.x.

But obviously we should keep an eye with this posibility and the developments.

A small SQLite table holding the time of last rebuild that could be compared with the value in I_S and skipping tables that have not changed is absolutely a possibility. And for those people having thousands of tables with hundreds of columns it does make sense!

I do not believe in using Information_Schema 'on the fly'. SQLite is undoubtly faster with remote servers at least. I cannot tell what query SQLyog sends to get the columns-list with MySQL 5, but somebody else must be able to.

But one thing is certain and that is that querying MySQL Information_Schema is not fast as of now. 'SHOW' statements are much faster! This bug: http://bugs.mysql.com/bug.php?id=19588 is very much a 'show-stopper' for MySQL 5.

Whoa.. I was not aware that I_S is that slow.

Code:

mysql> select count(*) from tables;
+———-+
| count(*) |
+———-+
| 88061 |
+———-+
1 row in set (2 hours 2 min 45.43 sec)

That is totally unacceptable. 😮

My thougt was that if I_S was “fast enough” it was worth a tiny amount of smaller performance to skip the tags files totally. My guess is that it would make the logic behind autocomplete much simpler.

But to compare the changed times from the tags files with the changed time in I_S would be great. Lets just not hope the I_S slownes outweighs the improvement. 🙂

[TomBez]

Martin Larsen wrote on Sep 5 2006, 11:30 AM:

Whoa.. I was not aware that I_S is that slow.

Code:

mysql> select count(*) from tables;
+———-+
| count(*) |
+———-+
| 88061 |
+———-+
1 row in set (2 hours 2 min 45.43 sec)

That is totally unacceptable. 😮

My thougt was that if I_S was “fast enough” it was worth a tiny amount of smaller performance to skip the tags files totally. My guess is that it would make the logic behind autocomplete much simpler.

But to compare the changed times from the tags files with the changed time in I_S would be great. Lets just not hope the I_S slownes outweighs the improvement. 🙂

select count(*) from tables;

198

547 ms

that would be quite fair. problem might be, that there isn't any index on that table, dont know if this would be possible for the memory-engine

[Author]

Posts