Re: temp table and view/function/procedure dilemma
Hi, You can always create any table from procedures. However, it seems to me that flexviews can solve all of your problems, take a look at it. It will provide you incrementally refreshable materialized views. Peter Boros On 04/05/2011 08:15 PM, Bgs wrote: Hi all, I have a problem here and looking for a solution. I have a temporary table which is a smaller table generated from a rather big one. The full table is too big to make real gimmicks on it, so I do need the temp table. Later I need to do several queries on the temp table. So my initial setup and needs are: - temporary table to work from - result sets derived from parametric queries - mysql views cannot work from temporary tables so I have to drop a view+select/where approach. - functions cannot return result sets - procedures can do everything but I found no way to handle the result set within mysql (officially not supported) Any ideas how to solve this? Thanks in advance Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: temp table and view/function/procedure dilemma
On 04/06/2011 09:13 PM, Sándor Halász wrote: I have a temporary table which is a smaller table generated from a rather big one. The full table is too big to make real gimmicks on it, so I do need the temp table. Later I do several queries on the temp table. So my initial setup and needs are: - temporary table to work from - result sets derived from parametric queries - mysql views cannot work from temporary tables so I have to drop a view+select/where approach. - functions cannot return result sets - procedures can do everything but I found no way to handle the result set within mysql (officially not supported) Any ideas how to solve this? Why not fake the temporariness, by DROPping the table as needful? I 'DROP'ed that approach for a couple of reasons: - While trying to minimize the load on the big table there is a real chance of concurrent jobs. Overall I find two temp table generation (loads) better than dropping each other's tables or locking other jobs. You also have to keep track of the fake-temp table's age. - A regular update of the fake-temp table would solve the above, but would produce too much load at the expected freshness. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: temp table and view/function/procedure dilemma
Hi, On 04/07/2011 08:06 AM, petya wrote: Hi, You can always create any table from procedures. However, it seems to me that flexviews can solve all of your problems, take a look at it. It will provide you incrementally refreshable materialized views. How do you create a table from a procedure output? I found it stated everywhere that it's not supported. About flexviews: Looks like a solution indeed. I will look into it! Thanks Bgs Peter Boros On 04/05/2011 08:15 PM, Bgs wrote: Hi all, I have a problem here and looking for a solution. I have a temporary table which is a smaller table generated from a rather big one. The full table is too big to make real gimmicks on it, so I do need the temp table. Later I need to do several queries on the temp table. So my initial setup and needs are: - temporary table to work from - result sets derived from parametric queries - mysql views cannot work from temporary tables so I have to drop a view+select/where approach. - functions cannot return result sets - procedures can do everything but I found no way to handle the result set within mysql (officially not supported) Any ideas how to solve this? Thanks in advance Bgs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MERGE Engine vs. UNION ALL
We've been experimenting with the merge engine. But suppose that instead of using the MERGE engine I instead modified my code to UNION ALL the shards. Would I get worse performance? In other words, besides the convenience, does the MERGE engine have specific performance optimizations that make it perform better? This is of interest for several reasons. First, my code could determine in some cases that only a few (or a single) shards were needed. Second, I could apply the shard technique to InnoDB tables. Third, I could apply the shard technique to databases that were not identical. I didn't get any response in the MERGE forum. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Index Caching with Merge Engine
We are currently evaluating the merge engine. Right now we create and preload several index caches. But what is the best way to approach this with a merged table? Do I create a single index cache and assign all of the shards to it? Or do I create a separate index cache for each shard? I didn't get a response on the Merge forum. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: efficient use of varchar?
-Original Message- From: petya [mailto:pe...@petya.org.hu] Sent: Wednesday, April 06, 2011 3:55 PM To: John G. Heim Cc: mysql@lists.mysql.com Subject: Re: efficient use of varchar? Hi, There is difference between varchar(63) and varchar(38). Instead of selecting MAX(LENGTH()) you can do PROCEDURE ANALYZE() on the table, which will tell you about the optimal record type for the data you currently have in the table. [JS] Okay, it's my turn to be puzzled. I never used PROCEDURE ANALYSE (and you **have** to use the UK English spelling, apparently), so I decided to try it. I have a VARCHAR(255) field named `prod_title`. This is what came out: == us-gii SELECT prod_title FROM prod PROCEDURE ANALYSE()\G *** 1. row *** Field_name: giiexpr_db.prod.prod_title Min_value: High Throughput Screening 2003: Improving Strategies, Technologies, and Productivity Max_value: ?½ÿ?¡ë?¡ªµáí?fÑ?»å??ºµ¥â?«í?Éå??Ä?ê???ª«??Åè?Çâµá??»ä«??èíµëï?åî Min_length: 2 Max_length: 255 Empties_or_zeros: 0 Nulls: 0 Avg_value_or_avg_length: 54.0701 Std: NULL Optimal_fieldtype: TINYTEXT NOT NULL 1 row in set (0.23 sec) == Aside from Max_value, which I'll wonder about later, why is the Optimal_fieldtype TINYTEXT? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com When you are using inreasonably large varchar columns, and mysql decides to create an in-memory temporary table with the MEMORY storage engine, practically all varchar(n) fields will be converted to char(n) fields, because memory storage engine doesn't support variable length data. Peter Boros On 04/06/2011 08:28 PM, John G. Heim wrote: Does it make any difference if I allocate a particular number of bytes for a varchar? I mean, just as an example, maybe its more efficient to use a power of 2 as the field length. Or maybe power of 2 minus 1. I'm guessing not and that anything less than 255 is the same. I'm converting some data in a spreadsheet and I have to create a table with about 150 columns. So I created all the columns varchar(255) and now I'm asking mysql to show me the longest value in each column. SELECT MAX(LENGTH(column99)) FROM TABLE99. Then I've been modifying the CREATE TABLE code to accomodate the longest value plus a little more. So if the longest value in the column is 38 characters, I'd probably make that a VARCHAR(50). But maybe I might as well make that 63 or 64. Or maybe I'm wasting my time and should leave them all 255. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: efficient use of varchar?
By the way, the weird-looking title is in Japanese (the database/table/field are UTF-8). Some of you might be able to read that. Is it possible that this is upsetting the ANALYSE procedure? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com -Original Message- From: Jerry Schwartz [mailto:je...@gii.co.jp] Sent: Thursday, April 07, 2011 12:09 PM To: 'petya'; 'John G. Heim' Cc: mysql@lists.mysql.com Subject: RE: efficient use of varchar? -Original Message- From: petya [mailto:pe...@petya.org.hu] Sent: Wednesday, April 06, 2011 3:55 PM To: John G. Heim Cc: mysql@lists.mysql.com Subject: Re: efficient use of varchar? Hi, There is difference between varchar(63) and varchar(38). Instead of selecting MAX(LENGTH()) you can do PROCEDURE ANALYZE() on the table, which will tell you about the optimal record type for the data you currently have in the table. [JS] Okay, it's my turn to be puzzled. I never used PROCEDURE ANALYSE (and you **have** to use the UK English spelling, apparently), so I decided to try it. I have a VARCHAR(255) field named `prod_title`. This is what came out: == us-gii SELECT prod_title FROM prod PROCEDURE ANALYSE()\G *** 1. row *** Field_name: giiexpr_db.prod.prod_title Min_value: High Throughput Screening 2003: Improving Strategies, Technologies, and Productivity Max_value: ?½ÿ?¡ë?¡ªµáí?fÑ?»å??ºµ¥â?«í?Éå??Ä?ê???ª«??Åè?Çâµá??»ä«??èíµëï?åî Min_length: 2 Max_length: 255 Empties_or_zeros: 0 Nulls: 0 Avg_value_or_avg_length: 54.0701 Std: NULL Optimal_fieldtype: TINYTEXT NOT NULL 1 row in set (0.23 sec) == Aside from Max_value, which I'll wonder about later, why is the Optimal_fieldtype TINYTEXT? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com When you are using inreasonably large varchar columns, and mysql decides to create an in-memory temporary table with the MEMORY storage engine, practically all varchar(n) fields will be converted to char(n) fields, because memory storage engine doesn't support variable length data. Peter Boros On 04/06/2011 08:28 PM, John G. Heim wrote: Does it make any difference if I allocate a particular number of bytes for a varchar? I mean, just as an example, maybe its more efficient to use a power of 2 as the field length. Or maybe power of 2 minus 1. I'm guessing not and that anything less than 255 is the same. I'm converting some data in a spreadsheet and I have to create a table with about 150 columns. So I created all the columns varchar(255) and now I'm asking mysql to show me the longest value in each column. SELECT MAX(LENGTH(column99)) FROM TABLE99. Then I've been modifying the CREATE TABLE code to accomodate the longest value plus a little more. So if the longest value in the column is 38 characters, I'd probably make that a VARCHAR(50). But maybe I might as well make that 63 or 64. Or maybe I'm wasting my time and should leave them all 255. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Any table visualization tools with wires connecting the actual columns?
Does anyone have any suggestions on this? I've written to SQL Maestro twice and they've not replied either. From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, April 01, 2011 4:27 PM To: mysql@lists.mysql.com Subject: Any table visualization tools with wires connecting the actual columns? I am evaluating various tools for diagram generating of existing databases on some smaller databases (9 tables or so) first. The two I've tried so far are these: http://dev.mysql.com/downloads/workbench/ http://www.sqlmaestro.com/download/#mysql Both _seem_ robust and cosmetically polished, but feel to me lacking the most obvious and key component of the whole purpose to make an EER diagram. I don't understand in workbench, why it creates new keys for me on existing tables. Maestro doesn't do this nonsense. It isn't the tools business where I have keys, it only needs to be concerned with what links to what -- that I tell it to. It's further exacerbated by the fact that the documentation indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add confusion guys? 1. Neither one seem to be smart enough to automatically know that columns of the same name should be linked, and furthermore they should be linked from all tables to the one where that column name is the PK. my tables don't have true InnoDB FKs setup. And some tables are MYISAM (as they're significantly faster). But I do use keys and I do have sane naming conventions, so I don't understand why they can't use the names, and if there are multiple tables (for some unlikely reason) then just prompt me which table to use. Which leads me to the second and third problems... So I manually have started to draw the connections, but: 2. How can I make the wires stick to a column on the left or right edge, so that I can have a direct visual link between the columns. Right now, it seems they float around the edge of the table box. That's sort of useless isn't it? it's like saying, well, something in this table points to something in that table.?! I would think that two programs with such high version numbers would have this feature. Maybe I'm missing a configuration or some way I'm supposed to do it? 3. Some of my databases point to tables in other databases on the same server. It would be useful if I could make a wire that indicates this. Are there other (better) options out there for this? I really don't want to do this in Visio or make a printout of the table boxes and tape string to my walls to visualize all the databases, tables and columns. -Daevid. _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dae...@daevid.com _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3557 - Release Date: 04/07/11
Re: Any table visualization tools with wires connecting the actual columns?
Toad for MySQL can do the diagramming piece...but, it looks and feels like you might have some of the same frustrations with it as well. But, another tool worth exploring nonetheless. On Thu, Apr 7, 2011 at 2:17 PM, Daevid Vincent dae...@daevid.com wrote: Does anyone have any suggestions on this? I've written to SQL Maestro twice and they've not replied either. From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, April 01, 2011 4:27 PM To: mysql@lists.mysql.com Subject: Any table visualization tools with wires connecting the actual columns? I am evaluating various tools for diagram generating of existing databases on some smaller databases (9 tables or so) first. The two I've tried so far are these: http://dev.mysql.com/downloads/workbench/ http://www.sqlmaestro.com/download/#mysql Both _seem_ robust and cosmetically polished, but feel to me lacking the most obvious and key component of the whole purpose to make an EER diagram. I don't understand in workbench, why it creates new keys for me on existing tables. Maestro doesn't do this nonsense. It isn't the tools business where I have keys, it only needs to be concerned with what links to what -- that I tell it to. It's further exacerbated by the fact that the documentation indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add confusion guys? 1. Neither one seem to be smart enough to automatically know that columns of the same name should be linked, and furthermore they should be linked from all tables to the one where that column name is the PK. my tables don't have true InnoDB FKs setup. And some tables are MYISAM (as they're significantly faster). But I do use keys and I do have sane naming conventions, so I don't understand why they can't use the names, and if there are multiple tables (for some unlikely reason) then just prompt me which table to use. Which leads me to the second and third problems... So I manually have started to draw the connections, but: 2. How can I make the wires stick to a column on the left or right edge, so that I can have a direct visual link between the columns. Right now, it seems they float around the edge of the table box. That's sort of useless isn't it? it's like saying, well, something in this table points to something in that table.?! I would think that two programs with such high version numbers would have this feature. Maybe I'm missing a configuration or some way I'm supposed to do it? 3. Some of my databases point to tables in other databases on the same server. It would be useful if I could make a wire that indicates this. Are there other (better) options out there for this? I really don't want to do this in Visio or make a printout of the table boxes and tape string to my walls to visualize all the databases, tables and columns. -Daevid. _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dae...@daevid.com _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3557 - Release Date: 04/07/11
Re: Any table visualization tools with wires connecting the actual columns?
At 02:17 PM 4/7/2011, Daevid Vincent wrote: Does anyone have any suggestions on this? I've written to SQL Maestro twice and they've not replied either. Take a look at SqlYog from www.webyog.com. I use their community version but their paid version has a schema designer. They are responsive to emails and forum posts. Mike From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, April 01, 2011 4:27 PM To: mysql@lists.mysql.com Subject: Any table visualization tools with wires connecting the actual columns? I am evaluating various tools for diagram generating of existing databases on some smaller databases (9 tables or so) first. The two I've tried so far are these: http://dev.mysql.com/downloads/workbench/ http://www.sqlmaestro.com/download/#mysql Both _seem_ robust and cosmetically polished, but feel to me lacking the most obvious and key component of the whole purpose to make an EER diagram. I don't understand in workbench, why it creates new keys for me on existing tables. Maestro doesn't do this nonsense. It isn't the tools business where I have keys, it only needs to be concerned with what links to what -- that I tell it to. It's further exacerbated by the fact that the documentation indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add confusion guys? 1. Neither one seem to be smart enough to automatically know that columns of the same name should be linked, and furthermore they should be linked from all tables to the one where that column name is the PK. my tables don't have true InnoDB FKs setup. And some tables are MYISAM (as they're significantly faster). But I do use keys and I do have sane naming conventions, so I don't understand why they can't use the names, and if there are multiple tables (for some unlikely reason) then just prompt me which table to use. Which leads me to the second and third problems... So I manually have started to draw the connections, but: 2. How can I make the wires stick to a column on the left or right edge, so that I can have a direct visual link between the columns. Right now, it seems they float around the edge of the table box. That's sort of useless isn't it? it's like saying, well, something in this table points to something in that table.?! I would think that two programs with such high version numbers would have this feature. Maybe I'm missing a configuration or some way I'm supposed to do it? 3. Some of my databases point to tables in other databases on the same server. It would be useful if I could make a wire that indicates this. Are there other (better) options out there for this? I really don't want to do this in Visio or make a printout of the table boxes and tape string to my walls to visualize all the databases, tables and columns. -Daevid. _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dae...@daevid.com _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3557 - Release Date: 04/07/11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Any table visualization tools with wires connecting the actual columns?
I am a paid subscriber to SQLYog -- I love that tool, but AFAIK it doesn't do diagrams (with wires between tables and all that glory). Am I wrong? Is that feature there and I just never noticed it? -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: Thursday, April 07, 2011 1:36 PM To: mysql@lists.mysql.com Subject: Re: Any table visualization tools with wires connecting the actual columns? At 02:17 PM 4/7/2011, Daevid Vincent wrote: Does anyone have any suggestions on this? I've written to SQL Maestro twice and they've not replied either. Take a look at SqlYog from www.webyog.com. I use their community version but their paid version has a schema designer. They are responsive to emails and forum posts. Mike From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, April 01, 2011 4:27 PM To: mysql@lists.mysql.com Subject: Any table visualization tools with wires connecting the actual columns? I am evaluating various tools for diagram generating of existing databases on some smaller databases (9 tables or so) first. The two I've tried so far are these: http://dev.mysql.com/downloads/workbench/ http://www.sqlmaestro.com/download/#mysql Both _seem_ robust and cosmetically polished, but feel to me lacking the most obvious and key component of the whole purpose to make an EER diagram. I don't understand in workbench, why it creates new keys for me on existing tables. Maestro doesn't do this nonsense. It isn't the tools business where I have keys, it only needs to be concerned with what links to what -- that I tell it to. It's further exacerbated by the fact that the documentation indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add confusion guys? 1. Neither one seem to be smart enough to automatically know that columns of the same name should be linked, and furthermore they should be linked from all tables to the one where that column name is the PK. my tables don't have true InnoDB FKs setup. And some tables are MYISAM (as they're significantly faster). But I do use keys and I do have sane naming conventions, so I don't understand why they can't use the names, and if there are multiple tables (for some unlikely reason) then just prompt me which table to use. Which leads me to the second and third problems... So I manually have started to draw the connections, but: 2. How can I make the wires stick to a column on the left or right edge, so that I can have a direct visual link between the columns. Right now, it seems they float around the edge of the table box. That's sort of useless isn't it? it's like saying, well, something in this table points to something in that table.?! I would think that two programs with such high version numbers would have this feature. Maybe I'm missing a configuration or some way I'm supposed to do it? 3. Some of my databases point to tables in other databases on the same server. It would be useful if I could make a wire that indicates this. Are there other (better) options out there for this? I really don't want to do this in Visio or make a printout of the table boxes and tape string to my walls to visualize all the databases, tables and columns. -Daevid. _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3557 - Release Date: 04/07/11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Any table visualization tools with wires connecting the actual columns?
Whoa! I never realized Toad did that. Man that is one robust program. I'm half minded to switch away from 'the Yog'... especially for FREE! Yeah, and it does do the sticky wires!! It only guessed some of them, but at least it's something. It seems to be missing an auto arrange kind of feature so once I make the wires, it can optimize the layout, but compared to other other crappy options, this is way better... and did I mention it's FREE! zOMGz. d -Original Message- From: Jason Trebilcock [mailto:jason.trebilc...@gmail.com] Sent: Thursday, April 07, 2011 12:38 PM To: mysql@lists.mysql.com Subject: Re: Any table visualization tools with wires connecting the actual columns? Toad for MySQL can do the diagramming piece...but, it looks and feels like you might have some of the same frustrations with it as well. But, another tool worth exploring nonetheless. On Thu, Apr 7, 2011 at 2:17 PM, Daevid Vincent dae...@daevid.com wrote: Does anyone have any suggestions on this? I've written to SQL Maestro twice and they've not replied either. From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, April 01, 2011 4:27 PM To: mysql@lists.mysql.com Subject: Any table visualization tools with wires connecting the actual columns? I am evaluating various tools for diagram generating of existing databases on some smaller databases (9 tables or so) first. The two I've tried so far are these: http://dev.mysql.com/downloads/workbench/ http://www.sqlmaestro.com/download/#mysql Both _seem_ robust and cosmetically polished, but feel to me lacking the most obvious and key component of the whole purpose to make an EER diagram. I don't understand in workbench, why it creates new keys for me on existing tables. Maestro doesn't do this nonsense. It isn't the tools business where I have keys, it only needs to be concerned with what links to what -- that I tell it to. It's further exacerbated by the fact that the documentation indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add confusion guys? 1. Neither one seem to be smart enough to automatically know that columns of the same name should be linked, and furthermore they should be linked from all tables to the one where that column name is the PK. my tables don't have true InnoDB FKs setup. And some tables are MYISAM (as they're significantly faster). But I do use keys and I do have sane naming conventions, so I don't understand why they can't use the names, and if there are multiple tables (for some unlikely reason) then just prompt me which table to use. Which leads me to the second and third problems... So I manually have started to draw the connections, but: 2. How can I make the wires stick to a column on the left or right edge, so that I can have a direct visual link between the columns. Right now, it seems they float around the edge of the table box. That's sort of useless isn't it? it's like saying, well, something in this table points to something in that table.?! I would think that two programs with such high version numbers would have this feature. Maybe I'm missing a configuration or some way I'm supposed to do it? 3. Some of my databases point to tables in other databases on the same server. It would be useful if I could make a wire that indicates this. Are there other (better) options out there for this? I really don't want to do this in Visio or make a printout of the table boxes and tape string to my walls to visualize all the databases, tables and columns. -Daevid. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Any table visualization tools with wires connecting the actual columns?
At 05:36 PM 4/7/2011, Daevid Vincent wrote: I am a paid subscriber to SQLYog -- I love that tool, but AFAIK it doesn't do diagrams (with wires between tables and all that glory). Am I wrong? Is that feature there and I just never noticed it? David, SqlYog Ultimate has a schema designer. Take a look at the screen shots. http://www.webyog.com/en/screenshots.php Mike -Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: Thursday, April 07, 2011 1:36 PM To: mysql@lists.mysql.com Subject: Re: Any table visualization tools with wires connecting the actual columns? At 02:17 PM 4/7/2011, Daevid Vincent wrote: Does anyone have any suggestions on this? I've written to SQL Maestro twice and they've not replied either. Take a look at SqlYog from www.webyog.com. I use their community version but their paid version has a schema designer. They are responsive to emails and forum posts. Mike From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, April 01, 2011 4:27 PM To: mysql@lists.mysql.com Subject: Any table visualization tools with wires connecting the actual columns? I am evaluating various tools for diagram generating of existing databases on some smaller databases (9 tables or so) first. The two I've tried so far are these: http://dev.mysql.com/downloads/workbench/ http://www.sqlmaestro.com/download/#mysql Both _seem_ robust and cosmetically polished, but feel to me lacking the most obvious and key component of the whole purpose to make an EER diagram. I don't understand in workbench, why it creates new keys for me on existing tables. Maestro doesn't do this nonsense. It isn't the tools business where I have keys, it only needs to be concerned with what links to what -- that I tell it to. It's further exacerbated by the fact that the documentation indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add confusion guys? 1. Neither one seem to be smart enough to automatically know that columns of the same name should be linked, and furthermore they should be linked from all tables to the one where that column name is the PK. my tables don't have true InnoDB FKs setup. And some tables are MYISAM (as they're significantly faster). But I do use keys and I do have sane naming conventions, so I don't understand why they can't use the names, and if there are multiple tables (for some unlikely reason) then just prompt me which table to use. Which leads me to the second and third problems... So I manually have started to draw the connections, but: 2. How can I make the wires stick to a column on the left or right edge, so that I can have a direct visual link between the columns. Right now, it seems they float around the edge of the table box. That's sort of useless isn't it? it's like saying, well, something in this table points to something in that table.?! I would think that two programs with such high version numbers would have this feature. Maybe I'm missing a configuration or some way I'm supposed to do it? 3. Some of my databases point to tables in other databases on the same server. It would be useful if I could make a wire that indicates this. Are there other (better) options out there for this? I really don't want to do this in Visio or make a printout of the table boxes and tape string to my walls to visualize all the databases, tables and columns. -Daevid. _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.com _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3557 - Release Date: 04/07/11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dae...@daevid.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
