Re: Merge Tables and Replication
[EMAIL PROTECTED] wrote: Does this sound about right? Anybody see any road hazards? If not, and this line of thinking is reasonable, should the DB with the older records also be replicated so that when a new old records table needs to be created, I don't have to repeat everything on the slave? Most of the problems documented here http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html and some of it here http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html The problems that stand out - A MERGE table cannot maintain uniqueness constraints over the entire table. - Key reads are slower. When you read a key, the MERGE storage engine needs to issue a read on all underlying tables to check which one most closely matches the given key. To read the next key, the MERGE storage engine needs to search the read buffers to find the next key. -- raj shekhar Thanks, raj, for underscoring the key reads issue. That might be a deal breaker... David
Re: Merge Tables and Replication
[EMAIL PROTECTED] wrote: Does this sound about right? Anybody see any road hazards? If not, and this line of thinking is reasonable, should the DB with the older records also be replicated so that when a new old records table needs to be created, I don't have to repeat everything on the slave? Most of the problems documented here http://dev.mysql.com/doc/refman/5.0/en/merge-table-problems.html and some of it here http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html The problems that stand out - A MERGE table cannot maintain uniqueness constraints over the entire table. - Key reads are slower. When you read a key, the MERGE storage engine needs to issue a read on all underlying tables to check which one most closely matches the given key. To read the next key, the MERGE storage engine needs to search the read buffers to find the next key. -- raj shekhar facts: http://rajshekhar.net | opinions: http://rajshekhar.net/blog Yoda of Borg are we: Futile is resistance. Assimilate you, we will 'Borg? Sounds Swedish.' - Lily, Star Trek First Contact -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merge Tables and Replication
The recent thread about merge tables led me to explore using them with replication. I see bug reports for the 4.0.** series regarding replication and merge tables, and I've read the manual about merge table problems in the 5.0.** series ( we are using 5.0.22 ), but I'm curious if anyone has any experiences to share? We've had replication working well now for almost 4 years, and as many tables are approaching 2 million records, it would appear that using a merge table design might be one way to accomplish a type of incremental backup. By this I mean, since all our data are static ( only inserts and selects ), the older data could be moved to a separate DB and only the active DB with the newest tables/records and the merge tables could be mysqldumped each night. Does this sound about right? Anybody see any road hazards? If not, and this line of thinking is reasonable, should the DB with the older records also be replicated so that when a new old records table needs to be created, I don't have to repeat everything on the slave? It always makes me uneasy, fiddling with something that is working... Thanks, --David.
Re: Best way to combine MYISAM to MERGE tables ...
- Original Message - From: mos [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 18, 2008 6:48 PM Subject: Re: Best way to combine MYISAM to MERGE tables ... At 11:33 AM 2/18/2008, you wrote: Hi All, I am working with MYISAM tables split by year, like: data2003, data2004, data2005, data2006, data2007, data2008, all having the same definitions. To speed up the query process, I also defined MERGE tables, like: CREATE TABLE data20032004 ... ENGINE=MERGE UNION (data2003, data2004); CREATE TABLE data20032005 ... ENGINE=MERGE UNION (data2003, data2004, data2005); CREATE TABLE data20032006 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006); CREATE TABLE data20032007 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007); CREATE TABLE data20032008 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007, data2008); CREATE TABLE data20042005 ... ENGINE=MERGE UNION (data2004, data2005); etc. etc. CREATE TABLE data20072008 ... ENGINE=MERGE UNION (data2007, data2008); For example, if a user query applies to 2003..2006, my app selects the belonging MERGE table data20032006, excluding tables data2007 and data2008 for faster results. Is this assumption correct ? Or are there better ways of doing this ? I'm using: mySQL version 5.0.15-NT TIA, Cor Cor, If the date is indexed, I don't think you're going to have a problem referencing a merge table Data_All based on all the tables. That's what I do with 25 tables (15 million rows) and it is quite fast. It also of course uses the query cache so subsequent queries are instant. Mike Thanks Mike, In my app I can't to use Year as Indexed field, because my app has multi-column keys with (5) higher selectivity fields. Key on Year would be an option, if MySQL search engine could use N separate keys. By the way, I'm using about 120 million rows in 10 tables. I will keep your experience in mind, thanks. Regards, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best way to combine MYISAM to MERGE tables ...
Hi All, I am working with MYISAM tables split by year, like: data2003, data2004, data2005, data2006, data2007, data2008, all having the same definitions. To speed up the query process, I also defined MERGE tables, like: CREATE TABLE data20032004 ... ENGINE=MERGE UNION (data2003, data2004); CREATE TABLE data20032005 ... ENGINE=MERGE UNION (data2003, data2004, data2005); CREATE TABLE data20032006 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006); CREATE TABLE data20032007 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007); CREATE TABLE data20032008 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007, data2008); CREATE TABLE data20042005 ... ENGINE=MERGE UNION (data2004, data2005); etc. etc. CREATE TABLE data20072008 ... ENGINE=MERGE UNION (data2007, data2008); For example, if a user query applies to 2003..2006, my app selects the belonging MERGE table data20032006, excluding tables data2007 and data2008 for faster results. Is this assumption correct ? Or are there better ways of doing this ? I'm using: mySQL version 5.0.15-NT TIA, Cor
Re: Best way to combine MYISAM to MERGE tables ...
At 11:33 AM 2/18/2008, you wrote: Hi All, I am working with MYISAM tables split by year, like: data2003, data2004, data2005, data2006, data2007, data2008, all having the same definitions. To speed up the query process, I also defined MERGE tables, like: CREATE TABLE data20032004 ... ENGINE=MERGE UNION (data2003, data2004); CREATE TABLE data20032005 ... ENGINE=MERGE UNION (data2003, data2004, data2005); CREATE TABLE data20032006 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006); CREATE TABLE data20032007 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007); CREATE TABLE data20032008 ... ENGINE=MERGE UNION (data2003, data2004, data2005, data2006, data2007, data2008); CREATE TABLE data20042005 ... ENGINE=MERGE UNION (data2004, data2005); etc. etc. CREATE TABLE data20072008 ... ENGINE=MERGE UNION (data2007, data2008); For example, if a user query applies to 2003..2006, my app selects the belonging MERGE table data20032006, excluding tables data2007 and data2008 for faster results. Is this assumption correct ? Or are there better ways of doing this ? I'm using: mySQL version 5.0.15-NT TIA, Cor Cor, If the date is indexed, I don't think you're going to have a problem referencing a merge table Data_All based on all the tables. That's what I do with 25 tables (15 million rows) and it is quite fast. It also of course uses the query cache so subsequent queries are instant. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merge tables and ON DUPLICATE KEY UPDATE
I want to use a merge table so that I can direct all new INSERTs to a new merge table and migrate old data off the system by having a continually sliding window of underlying MyISAM tables. The problem is that of I do INSERT ... ON DUPLCATE KEY UPDATE and that value isn't in the leading table where all INSERTs go a *new* row will be created. Is there any way around this problem? Kevin -- Founder/CEO Tailrank.com Location: San Francisco, CA AIM/YIM: sfburtonator Skype: burtonator Blog: feedblog.org Cell: 415-637-8078 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables and ON DUPLICATE KEY UPDATE
Kevin Burton wrote: I want to use a merge table so that I can direct all new INSERTs to a new merge table and migrate old data off the system by having a continually sliding window of underlying MyISAM tables. The problem is that of I do INSERT ... ON DUPLCATE KEY UPDATE and that value isn't in the leading table where all INSERTs go a *new* row will be created. Is there any way around this problem? What about using partitioning in MySQl 5.1+? Would this work? http://dev.mysql.com/doc/refman/5.1/en/partitioning.html Cheers, Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables and ON DUPLICATE KEY UPDATE
I thought about it I was actually going to use merge tables AND partitions to split the underlying MyISAM tables across two disks and then partition on top. It's POSSIBLE to use partitioning the way I want it but I'm going to have to grok it for a bit more. Thanks though. Kevin On 2/12/07, Jay Pipes [EMAIL PROTECTED] wrote: Kevin Burton wrote: I want to use a merge table so that I can direct all new INSERTs to a new merge table and migrate old data off the system by having a continually sliding window of underlying MyISAM tables. The problem is that of I do INSERT ... ON DUPLCATE KEY UPDATE and that value isn't in the leading table where all INSERTs go a *new* row will be created. Is there any way around this problem? What about using partitioning in MySQl 5.1+? Would this work? http://dev.mysql.com/doc/refman/5.1/en/partitioning.html Cheers, Jay -- Founder/CEO Tailrank.com Location: San Francisco, CA AIM/YIM: sfburtonator Skype: burtonator Blog: feedblog.org Cell: 415-637-8078 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why incomplete Cardinalities with MERGE tables ?
I have some base tables, called data2004, data2005 etc. They all have the following structure: CREATE TABLE IF NOT EXISTS `data200X` ( F1 int unsigned NOT NULL default '0', F2 smallint unsigned NOT NULL default '0', F3 smallint unsigned NOT NULL default '0', F4 tinyint unsigned NOT NULL default '0', F5 tinyint unsigned NOT NULL default '0', ... PRIMARY KEY (F1,F2,F3,F4,F5), KEY F2 (F2), KEY F3 (F3), KEY F4 (F4) ) ENGINE=MyISAM; For all these MyISAM tables SHOW INDEX gives ALL cardinalities, like: KeyName Column Cardinality Primary F19837 Primary F2220333 Primary F3 3892565 Primary F411677695 Primary F511677695 F2 F2 24 F3 F3241 F4 F4 31 I defined a MERGE table with 3 base tables, like: CREATE TABLE IF NOT EXISTS `data0406` ( F1 int unsigned NOT NULL default '0', F2 smallint unsigned NOT NULL default '0', F3 smallint unsigned NOT NULL default '0', F4 tinyint unsigned NOT NULL default '0', F5 tinyint unsigned NOT NULL default '0', ... KEY Combi (F1,F2,F3,F4,F5), KEY F2 (F2), KEY F3 (F3), KEY F4 (F4) ) ENGINE=MERGE UNION=(data2004, data2005, data2006); For this MERGE table SHOW INDEX gives NOT ALL cardinalities: KeyName Column Cardinality Primary F1 30143 Primary F2 686726 Primary F3 12589987 Primary F4 NULLabsent Primary F5 NULLabsent F2 F2 75 F3 F3 725 F4 F4 96 When defining a MERGE table with 4 base tables, like: ENGINE=MERGE UNION=(data2003, data2004, data2005, data2006); the SHOW INDEX gives even less cardinalities: KeyName Column Cardinality Primary F1 x Primary F2 xx Primary F3 NULLabsent Primary F4 NULLabsent Primary F5 NULLabsent F2 F2 xx F3 F3 xxx F4 F4 xx Any ideas ? I am using MySQL 5.0.15 NT TIA, Cor
RE: Adding and Removing tables from MERGE tables dynamically
Thanks for the information.
I want to make sure that I understand: Do you run ALTER TABLE command
on a live database(table) that is doing inserts;
Or, do you stop accepting Remote connections, flush the tables, run the
ALTER TABLE command,
start accepting connections?
Thank you,
Raymond
-Original Message-
From: Brent Baisley [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 06, 2006 10:24
To: Jacob, Raymond A Jr; mysql@lists.mysql.com
Subject: Re: Adding and Removing tables from MERGE tables dynamically
I've got a similar setup, total records across about 8 tables hoovers
around 200 million.
To change a merge table just issue an alter table with a new union.
ALTER TABLE mergetable UNION=(table1, table2, table3,...);
- Original Message -
From: Jacob, Raymond A Jr [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, September 05, 2006 5:29 PM
Subject: Adding and Removing tables from MERGE tables dynamically
Problem: I use two applications one called snort, the client that
inserts data into eleven(11) tables.
The other application BASE joins the tables into one table. When the
tables become 4GB in size,
deleting records for one month becomes unbearably slow(20-30days). The
search(Select ) is slow too but that
is a problem with the BASE application.
I thought that using MERGE tables would allow an administrator to create
a monthly table, using the original table names, composed of daily or
weekly
tables, by appending the date of creation to the table i.e.
data_table1_-MM-DD and join_table_-MM-DD. From the
documentation:
creating the table with INSERT_METHOD = FIRST results in INSERTs being
done to first table in the MERGE UNION statement.
I will assume that the first table is the latest table.
So one of the first tables should look like:
CREATE TABLE original_table
{
...
} TYPE = MERGE UNION = (data_table1_2006-09-12
,data_table1_2006-09-05)
Using cron and depending on the interval chosen daily, weekly,
bi-weekly, monthly, quarterly, or yearly at the start
of a new interval, a new table would be created with current date in
the -MM-DD format.
For example: if the start of new interval begins a week from today on
2006-09-12. At 12:00am on
2006-09-12, a script would create new tables that would look like:
CREATE TABLE data_table1_2006-09-12
{
...
}
One the MERGE TABLES should look like:
CREATE TABLE original_table
{
...
} TYPE = MERGE UNION = (data_table1_2006-09-12
,data_table1_2006-09-05)
On every Tuesday(in this case) from now on, new tables are created
ending with date in the format -MM-DD
and merged into the original table.
So that by 2006-09-30, one of the MERGE tables should look something
like
...
} TYPE = MERGE UNION = (data_table1_2006-09-26, data_table1_2006-09-19,
data_table1_2006-09-12, data_table1_2006-09-05)
On 2006-10-05 at 00:00hrs the newest table data_table1_2006-10-05
should be created and merged into the original_table. The oldest table
in this case data_table1_2006-09-05 should be removed from one of the
MERGE tables in this case original_table. The resulting merge table
should look something like
...
} TYPE = MERGE UNION = (data_table1_2006-10-05,data_table1_2006-09-26,
data_table1_2006-09-19, data_table1_2006-09-12)
Question: How does one add data_table1_2006-09-12 to original_table
dynamically?
Question: How does one remove data_table1_2006-09-05 from the
original_table dynamically?
Question: In other words, can tables be added and removed dynamically
to/from a MERGE TABLE?
Benefit: I hope is to archive individual tables. When I need to review
old data I will use a copy of the BASE application, then
Merge the tables that I am interested in, in order to search smaller
tables without changing the BASE application.
Question: Is this possible. Do these question make sense?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding and Removing tables from MERGE tables dynamically
I've got a similar setup, total records across about 8 tables hoovers around
200 million.
To change a merge table just issue an alter table with a new union.
ALTER TABLE mergetable UNION=(table1, table2, table3,...);
- Original Message -
From: Jacob, Raymond A Jr [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, September 05, 2006 5:29 PM
Subject: Adding and Removing tables from MERGE tables dynamically
Problem: I use two applications one called snort, the client that
inserts data into eleven(11) tables.
The other application BASE joins the tables into one table. When the
tables become 4GB in size,
deleting records for one month becomes unbearably slow(20-30days). The
search(Select ) is slow too but that
is a problem with the BASE application.
I thought that using MERGE tables would allow an administrator to create
a monthly table, using the original table names, composed of daily or
weekly
tables, by appending the date of creation to the table i.e.
data_table1_-MM-DD and join_table_-MM-DD. From the
documentation:
creating the table with INSERT_METHOD = FIRST results in INSERTs being
done to first table in the MERGE UNION statement.
I will assume that the first table is the latest table.
So one of the first tables should look like:
CREATE TABLE original_table
{
...
} TYPE = MERGE UNION = (data_table1_2006-09-12
,data_table1_2006-09-05)
Using cron and depending on the interval chosen daily, weekly,
bi-weekly, monthly, quarterly, or yearly at the start
of a new interval, a new table would be created with current date in
the -MM-DD format.
For example: if the start of new interval begins a week from today on
2006-09-12. At 12:00am on
2006-09-12, a script would create new tables that would look like:
CREATE TABLE data_table1_2006-09-12
{
...
}
One the MERGE TABLES should look like:
CREATE TABLE original_table
{
...
} TYPE = MERGE UNION = (data_table1_2006-09-12
,data_table1_2006-09-05)
On every Tuesday(in this case) from now on, new tables are created
ending with date in the format -MM-DD
and merged into the original table.
So that by 2006-09-30, one of the MERGE tables should look something
like
...
} TYPE = MERGE UNION = (data_table1_2006-09-26, data_table1_2006-09-19,
data_table1_2006-09-12, data_table1_2006-09-05)
On 2006-10-05 at 00:00hrs the newest table data_table1_2006-10-05
should be created and merged into the original_table. The oldest table
in this case data_table1_2006-09-05 should be removed from one of the
MERGE tables in this case original_table. The resulting merge table
should look something like
...
} TYPE = MERGE UNION = (data_table1_2006-10-05,data_table1_2006-09-26,
data_table1_2006-09-19, data_table1_2006-09-12)
Question: How does one add data_table1_2006-09-12 to original_table
dynamically?
Question: How does one remove data_table1_2006-09-05 from the
original_table dynamically?
Question: In other words, can tables be added and removed dynamically
to/from a MERGE TABLE?
Benefit: I hope is to archive individual tables. When I need to review
old data I will use a copy of the BASE application, then
Merge the tables that I am interested in, in order to search smaller
tables without changing the BASE application.
Question: Is this possible. Do these question make sense?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adding and Removing tables from MERGE tables dynamically
Problem: I use two applications one called snort, the client that
inserts data into eleven(11) tables.
The other application BASE joins the tables into one table. When the
tables become 4GB in size,
deleting records for one month becomes unbearably slow(20-30days). The
search(Select ) is slow too but that
is a problem with the BASE application.
I thought that using MERGE tables would allow an administrator to create
a monthly table, using the original table names, composed of daily or
weekly
tables, by appending the date of creation to the table i.e.
data_table1_-MM-DD and join_table_-MM-DD. From the
documentation:
creating the table with INSERT_METHOD = FIRST results in INSERTs being
done to first table in the MERGE UNION statement.
I will assume that the first table is the latest table.
So one of the first tables should look like:
CREATE TABLE original_table
{
...
} TYPE = MERGE UNION = (data_table1_2006-09-12
,data_table1_2006-09-05)
Using cron and depending on the interval chosen daily, weekly,
bi-weekly, monthly, quarterly, or yearly at the start
of a new interval, a new table would be created with current date in
the -MM-DD format.
For example: if the start of new interval begins a week from today on
2006-09-12. At 12:00am on
2006-09-12, a script would create new tables that would look like:
CREATE TABLE data_table1_2006-09-12
{
...
}
One the MERGE TABLES should look like:
CREATE TABLE original_table
{
...
} TYPE = MERGE UNION = (data_table1_2006-09-12
,data_table1_2006-09-05)
On every Tuesday(in this case) from now on, new tables are created
ending with date in the format -MM-DD
and merged into the original table.
So that by 2006-09-30, one of the MERGE tables should look something
like
...
} TYPE = MERGE UNION = (data_table1_2006-09-26, data_table1_2006-09-19,
data_table1_2006-09-12, data_table1_2006-09-05)
On 2006-10-05 at 00:00hrs the newest table data_table1_2006-10-05
should be created and merged into the original_table. The oldest table
in this case data_table1_2006-09-05 should be removed from one of the
MERGE tables in this case original_table. The resulting merge table
should look something like
...
} TYPE = MERGE UNION = (data_table1_2006-10-05,data_table1_2006-09-26,
data_table1_2006-09-19, data_table1_2006-09-12)
Question: How does one add data_table1_2006-09-12 to original_table
dynamically?
Question: How does one remove data_table1_2006-09-05 from the
original_table dynamically?
Question: In other words, can tables be added and removed dynamically
to/from a MERGE TABLE?
Benefit: I hope is to archive individual tables. When I need to review
old data I will use a copy of the BASE application, then
Merge the tables that I am interested in, in order to search smaller
tables without changing the BASE application.
Question: Is this possible. Do these question make sense?
Merge tables.
Hi, One of the databases I use just switched to using merge tables and now my queries are painfully slow. One table, initially had about 2.5 million records and now with the change this information is spread across about 1600 tables. A simple query, say select count(*) has gone from .04 to about 30 seconds, sometimes even longer. The OS is FreeBSD on a 3.1 GHz machine with a 1GB ram. Mysql is 4.1.15. My my.cnf currently has only one option: open_files_limit=24576 If anyone could provide me with some other options I might try I would appreciate it. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
Paul, One of the databases I use just switched to using merge tables and now my queries are painfully slow. One table, initially had about 2.5 million records and now with the change this information is spread across about 1600 tables. A simple query, say select count(*) has gone from .04 to about 30 seconds, sometimes even longer. Why on earth would you spread this information across 1600 (!!!) tables? That's 1600 files to maintain instead of 1. Why did you try Merge in the first place? Was there a problem? The OS is FreeBSD on a 3.1 GHz machine with a 1GB ram. Mysql is 4.1.15. My my.cnf currently has only one option: open_files_limit=24576 If anyone could provide me with some other options I might try I would appreciate it. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
I guess u had 2.5 million records splited into 1600 tables if iam not wrong :) Why you split the tables to 1600 is there any specific count ? Why you moved to merge table reason behind ? Since 2.5 million records to 1600 is a huge count on nos of tables :( instead of one ;) --Praj On Tue, 14 Mar 2006 07:14:52 -0400 Paul Halliday [EMAIL PROTECTED] wrote: Hi, One of the databases I use just switched to using merge tables and now my queries are painfully slow. One table, initially had about 2.5 million records and now with the change this information is spread across about 1600 tables. A simple query, say select count(*) has gone from .04 to about 30 seconds, sometimes even longer. The OS is FreeBSD on a 3.1 GHz machine with a 1GB ram. Mysql is 4.1.15. My my.cnf currently has only one option: open_files_limit=24576 If anyone could provide me with some other options I might try I would appreciate it. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
Hello Paul, I suggest you reply to the mailinglist :-) ... The developer insists that for scalability issues, this was the answer. It is likely, for example in my deployment, that these tables would see upwards of 10 million records or more. Well, if there are problems with scalability, I guess you could split it up in a few (not 1600) tables and have them avaialble on different physical hard drives... But - why try to fix something that ain't broken (yet)? Were you experiencing problems already? If the application is fast WITHOUT merge tables, why bother? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com One of the databases I use just switched to using merge tables and now my queries are painfully slow. One table, initially had about 2.5 million records and now with the change this information is spread across about 1600 tables. A simple query, say select count(*) has gone from .04 to about 30 seconds, sometimes even longer. Why on earth would you spread this information across 1600 (!!!) tables? That's 1600 files to maintain instead of 1. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
Martijn Tonies [EMAIL PROTECTED] wrote on 14/03/2006 11:32:10: Hello Paul, I suggest you reply to the mailinglist :-) ... The developer insists that for scalability issues, this was the answer. It is likely, for example in my deployment, that these tables would see upwards of 10 million records or more. Well, if there are problems with scalability, I guess you could split it up in a few (not 1600) tables and have them avaialble on different physical hard drives... In my opinion, splitting things into merge tables has a *strong* anti-scalability component. Searching a single table with indexes is O(log n), whereas searching MERGE tables is O(n). Therefore, by splitting your table into very many pieces, you sharply reduce your scalability in time while increasing it in space. Presumably, you want to scatter your table across several drives, so that you will not have problems when you fill one drive. But you are never likely to have 1600 drives, so 1600 is a ridiculously large number of tables to split it into. You should probably split it into no more than two or three times the largest number of disks you ever expect to have. And even so, I would rather combine disks in RAID arrays rather than uses separate tables. This can give you RAID protection as well as more disk space. Which to you expect to run out of first, space or time? You seem to have some heavyweight i.e. time intensive queries, which suggests that you will run out of time first. If that is so, the requirement for scalablity says that you should combine, not split, tables. To quote Donald Knuth (derived from Hoare) Premature optimisation is the root of all evil. You should be sure that you are optimising in the right place before you dive in: your problem suggests that you are trying to fix that which is not broken, and breaking other things in the process. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
On 3/14/06, Martijn Tonies [EMAIL PROTECTED] wrote: Hello Paul, I suggest you reply to the mailinglist :-) ... The developer insists that for scalability issues, this was the answer. It is likely, for example in my deployment, that these tables would see upwards of 10 million records or more. Well, if there are problems with scalability, I guess you could split it up in a few (not 1600) tables and have them avaialble on different physical hard drives... As an example: There was a table called event. This table is now broken up like this: event _sensor_date. So for every sensor, and every day, there is now a new table. So if I have 20 sensors, every day I will have 20 new tables. With this in mind, does this design make sense? how will this scale? Is there anything I can do through configuration (I doubt the developer will change the design) to speed things up? or a workaround that I could do on my end to compensate? Thanks. But - why try to fix something that ain't broken (yet)? Were you experiencing problems already? If the application is fast WITHOUT merge tables, why bother? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com One of the databases I use just switched to using merge tables and now my queries are painfully slow. One table, initially had about 2.5 million records and now with the change this information is spread across about 1600 tables. A simple query, say select count(*) has gone from .04 to about 30 seconds, sometimes even longer. Why on earth would you spread this information across 1600 (!!!) tables? That's 1600 files to maintain instead of 1. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
The developer insists that for scalability issues, this was the answer. It is likely, for example in my deployment, that these tables would see upwards of 10 million records or more. Well, if there are problems with scalability, I guess you could split it up in a few (not 1600) tables and have them avaialble on different physical hard drives... As an example: There was a table called event. This table is now broken up like this: event _sensor_date. So for every sensor, and every day, there is now a new table. So if I have 20 sensors, every day I will have 20 new tables. With this in mind, does this design make sense? how will this scale? According to you, it doesn't :-) Is there anything I can do through configuration (I doubt the developer will change the design) to speed things up? or a workaround that I could do on my end to compensate? What you're doing here is fixing something that isn't broken. Give your database a test with 20 million rows to see how your queries are performing, make sure your queries make sense and that you use the proper indices. Remember, database systems are designed to handle lots of rows. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
Paul Halliday [EMAIL PROTECTED] wrote on 14/03/2006 12:09:10: As an example: There was a table called event. This table is now broken up like this: event _sensor_date. So for every sensor, and every day, there is now a new table. So if I have 20 sensors, every day I will have 20 new tables. With this in mind, does this design make sense? how will this scale? Is there anything I can do through configuration (I doubt the developer will change the design) to speed things up? or a workaround that I could do on my end to compensate? Could you explain how this is meant to improve scalability? Because to my mind it is probably the best way I can imagine to make the system unscaleable. To me, this design very much does *not* make sense. You have bought, in MySQL, a highly tuned specialist engine for seqrching and sorting stuff in the most efficent manner. And then you have said that you will disable all its optimisation and force it into a linear search. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
[EMAIL PROTECTED] wrote: Paul Halliday [EMAIL PROTECTED] wrote on 14/03/2006 12:09:10: As an example: There was a table called event. This table is now broken up like this: event _sensor_date. So for every sensor, and every day, there is now a new table. So if I have 20 sensors, every day I will have 20 new tables. With this in mind, does this design make sense? how will this scale? Is there anything I can do through configuration (I doubt the developer will change the design) to speed things up? or a workaround that I could do on my end to compensate? Could you explain how this is meant to improve scalability? Because to my mind it is probably the best way I can imagine to make the system unscaleable. To me, this design very much does *not* make sense. You have bought, in MySQL, a highly tuned specialist engine for seqrching and sorting stuff in the most efficent manner. And then you have said that you will disable all its optimisation and force it into a linear search. I can think of a reason for doing this but not to extent described. Is your developer trying to create a situation where it's easy to archive of results earlier than a given day? So you store say 1000 days of data and can quickly archive the oldest day at midnight each day. Assuming this is the case: There's no point splitting further than by day so tables per day/sensor don't make any sense unless your worried about sub second locking (i.e. doing it wrong). You should make the unmerged tables as large as possible without the time to delete having an impact on your application. Having an impact depends on your applications tolerence to locking and the amount of data your adding and removing, you'll need to find it by testing. The table type you use will have a big impact on concurrent access locks. MyiSAM and Innodb are the two main candidates MyISAM is quick but is doesn't allow concurrent access to the table. Innodb will allow concurrent access but still locks rows and can lock the 'head point' during certain inserts. The fact your storing sensor data worries me. How tolerent of lag/locking on insert or retreval is your application? If it's sensitive to more than a seconds lag you need a careful review of your design. If it's hard real-time sack the developer then review the design. Hope this helps Nigel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables.
nigel wood [EMAIL PROTECTED] wrote on 14/03/2006 13:09:08: [EMAIL PROTECTED] wrote: Paul Halliday [EMAIL PROTECTED] wrote on 14/03/2006 12:09:10: As an example: There was a table called event. This table is now broken up like this: event _sensor_date. So for every sensor, and every day, there is now a new table. So if I have 20 sensors, every day I will have 20 new tables. With this in mind, does this design make sense? how will this scale? Is there anything I can do through configuration (I doubt the developer will change the design) to speed things up? or a workaround that I could do on my end to compensate? Could you explain how this is meant to improve scalability? Because to my mind it is probably the best way I can imagine to make the system unscaleable. To me, this design very much does *not* make sense. You have bought, in MySQL, a highly tuned specialist engine for seqrching and sorting stuff in the most efficent manner. And then you have said that you will disable all its optimisation and force it into a linear search. I can think of a reason for doing this but not to extent described. Is your developer trying to create a situation where it's easy to archive of results earlier than a given day? So you store say 1000 days of data and can quickly archive the oldest day at midnight each day. Assuming this is the case: There's no point splitting further than by day so tables per day/sensor don't make any sense unless your worried about sub second locking (i.e. doing it wrong). You should make the unmerged tables as large as possible without the time to delete having an impact on your application. Having an impact depends on your applications tolerence to locking and the amount of data your adding and removing, you'll need to find it by testing. The table type you use will have a big impact on concurrent access locks. MyiSAM and Innodb are the two main candidates MyISAM is quick but is doesn't allow concurrent access to the table. Innodb will allow concurrent access but still locks rows and can lock the 'head point' during certain inserts. The fact your storing sensor data worries me. How tolerent of lag/locking on insert or retreval is your application? If it's sensitive to more than a seconds lag you need a careful review of your design. If it's hard real-time sack the developer then review the design. I take your point to a certain extent. Of course, in the end it comes down to the searches being used. I would make it a rule of thumb that any search which requires more than a 10 tables is a Bad Thing. So if the very large majority of searches are for 1-4 sensors over 1-4 days, this architecture might make sense. But if searches are over 10 sensors or 10 days, this architecture will b become astoundingly inefficient. Generally, I would expect MERGE tables to be used on much larger lumps of time. If you have tables per month, any random period of a month can be checked very efficiently by merging two tables - the start month and the end month. This is the sort of thing that people tend to want to do. The OP of course knows his application, but I think it unusual for people to slice queries that small. And if the queries are of the form upon which days/sensors did event X happen, then splitting the table up is a one way path to doom. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merge tables: how to know the base tables?
Hi there, How do you know what the sources are for a MERGE table? SHOW TABLE STATUS doesn't show it, as far as I can see? Is it really only available in SHOW CREATE TABLE? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables: how to know the base tables?
Hi Simple way is show table status; there is a column Type : value MRG_MyISAM is the base table. --Praj On Tue, 7 Mar 2006 14:02:44 +0100 Martijn Tonies [EMAIL PROTECTED] wrote: Hi there, How do you know what the sources are for a MERGE table? SHOW TABLE STATUS doesn't show it, as far as I can see? Is it really only available in SHOW CREATE TABLE? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables: how to know the base tables?
Hi, Simple way is show table status; there is a column Type : value MRG_MyISAM is the base table. No, that's not what I meant -- I meant, the merge table uses a UNION clause to get signal where it get it's data from. Where can I get the sources for the merge table? That is, what is used in the UNION clause when creating it. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com --Praj On Tue, 7 Mar 2006 14:02:44 +0100 Martijn Tonies [EMAIL PROTECTED] wrote: Hi there, How do you know what the sources are for a MERGE table? SHOW TABLE STATUS doesn't show it, as far as I can see? Is it really only available in SHOW CREATE TABLE? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables: how to know the base tables?
Oh yeah got it u meant UNION clause . Still i have no idea about that i usally see it in show create tables statements . Sorry :( --Praj On Tue, 7 Mar 2006 15:10:51 +0100 Martijn Tonies [EMAIL PROTECTED] wrote: Hi, Simple way is show table status; there is a column Type : value MRG_MyISAM is the base table. No, that's not what I meant -- I meant, the merge table uses a UNION clause to get signal where it get it's data from. Where can I get the sources for the merge table? That is, what is used in the UNION clause when creating it. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com --Praj On Tue, 7 Mar 2006 14:02:44 +0100 Martijn Tonies [EMAIL PROTECTED] wrote: Hi there, How do you know what the sources are for a MERGE table? SHOW TABLE STATUS doesn't show it, as far as I can see? Is it really only available in SHOW CREATE TABLE? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merge tables: how to get the insert_method?
Hi, Anyone got a clue where to get a hold of insert_method after creating the table? Is the SHOW CREATE TABLE output the only way to get it? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables: how to get the insert_method?
Martijn Tonies [EMAIL PROTECTED] wrote on 03/07/2006 09:57:17 AM: Hi, Anyone got a clue where to get a hold of insert_method after creating the table? Is the SHOW CREATE TABLE output the only way to get it? Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com insert_method ?? I thought I knew a little about databases and table design but that's a new term for me. What does it mean and how would I apply it in order to modify a table's contents? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Merge tables: how to get the insert_method?
Hello Shawn, Thanks for replying. INSERT_METHOD is an option valid for merge tables. See http://dev.mysql.com/doc/refman/5.0/en/create-table.html From what I can see, the only way to get it, is parse the SHOW CREATE TABLE output. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Martijn Tonies [EMAIL PROTECTED] wrote on 03/07/2006 09:57:17 AM: Hi, Anyone got a clue where to get a hold of insert_method after creating the table? Is the SHOW CREATE TABLE output the only way to get it? insert_method ?? I thought I knew a little about databases and table design but that's a new term for me. What does it mean and how would I apply it in order to modify a table's contents? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Merge tables: how to get the insert_method?
On 07/03/2006, [EMAIL PROTECTED] wrote: insert_method ?? I thought I knew a little about databases and table design but that's a new term for me. What does it mean and how would I apply it in order to modify a table's contents? From http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html: To create a MERGE table, you must specify a UNION=(list-of-tables) clause that indicates which MyISAM tables you want to use as one. You can optionally specify an INSERT_METHOD option if you want inserts for the MERGE table to take place in the first or last table of the UNION list. Use a value of FIRST or LAST to cause inserts to be made in the first or last table, respectively. If you do not specify an INSERT_METHOD option or if you specify it with a value of NO, attempts to insert rows into the MERGE table result in an error. -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables: how to get the insert_method?
Martijn Tonies [EMAIL PROTECTED] wrote on 03/07/2006 10:46:58 AM: Hello Shawn, Thanks for replying. INSERT_METHOD is an option valid for merge tables. See http://dev.mysql.com/doc/refman/5.0/en/create-table.html From what I can see, the only way to get it, is parse the SHOW CREATE TABLE output. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Martijn Tonies [EMAIL PROTECTED] wrote on 03/07/2006 09:57:17 AM: Hi, Anyone got a clue where to get a hold of insert_method after creating the table? Is the SHOW CREATE TABLE output the only way to get it? insert_method ?? I thought I knew a little about databases and table design but that's a new term for me. What does it mean and how would I apply it in order to modify a table's contents? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Thank you very kindly. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: MERGE tables considered harmful for data warehouse fact tables
(still catching up!) Tom, We have similar queries where I work, and a union is the best way to go. Leaving the tables large eats up valuable buffer space for us, putting the logic in the client app is a bad idea (since you'd have to do it for all apps). In particular, our sessions table is very large (our record so far is 18k people logged in at once) and we're splitting it up currently because we've gotten to that point. -Sheeri On 12/22/05, Tom Cunningham [EMAIL PROTECTED] wrote: The script to prove it follows. Splitting a million-row fact table into a 5-part merge table makes 3-second queries take 9 seconds. The basic problem is this: fact tables are generally referenced by unique combinations of dimensions, and though often only one underlying table needs to be referenced, mysql doesn't know this, so every single underlying table is polled for each dimension-combination. Practical alternatives: (1) leave your fact table as a big one; (2) split the table up, but put logic in the client-app so it knows which table to address; (3) use a *union* of queries instead of a merge table, then mysql could look at each underlying table one at a time. If anyone has ideas for other alternatives, or improvements on the script, please tell me. Thanks. Tom. # # MERGE FACT TABLE TEST - [EMAIL PROTECTED] USE sandbox; CREATE TABLE big_table LIKE mysql.help_keyword; ALTER TABLE big_table ENGINE=MERGE UNION=(mysql.help_keyword); DROP TABLE IF EXISTS dimension_1; CREATE TABLE dimension_1 ( key_1 INT PRIMARY KEY NOT NULL, attribute_1 VARCHAR(255) NOT NULL, INDEX attribute_1 (attribute_1(10)) ); SET @A:=1; INSERT INTO dimension_1 SELECT @A:[EMAIL PROTECTED], SHA(RAND()) FROM big_table b1, big_table b2, big_table b3 LIMIT 10; DROP TABLE IF EXISTS dimension_2; CREATE TABLE dimension_2 ( key_2 INT PRIMARY KEY NOT NULL, attribute_2 VARCHAR(255) NOT NULL, INDEX attribute_1 (attribute_2(10)) ); SET @A:=1; INSERT INTO dimension_2 SELECT @A:[EMAIL PROTECTED], SHA(RAND()) FROM big_table b1, big_table b2, big_table b3 LIMIT 10; DROP TABLE IF EXISTS facts; CREATE TABLE facts ( key_1 INT UNSIGNED, key_2 INT UNSIGNED, fact_1 INT UNSIGNED, fact_2 INT UNSIGNED, PRIMARY KEY key_1_key_2 (key_1,key_2) ); INSERT IGNORE INTO facts SELECT CEIL(RAND()*10), CEIL(RAND()*10), CEIL(RAND()*100), CEIL(RAND()*100) FROM big_table b1, big_table b2, big_table b3 LIMIT 100; ## Typical query: (takes about 1.5seconds) SELECT SQL_NO_CACHE attribute_1, sum(fact_1), avg(fact_2) FROM facts JOIN dimension_1 ON dimension_1.key_1=facts.key_1 JOIN dimension_2 ON dimension_2.key_2=facts.key_2 WHERE attribute_1 LIKE 'ff%' AND attribute_2 LIKE 'a3%' GROUP BY attribute_1 LIMIT 1; ## Variation 1: Unionised fact table; DROP TABLES f1, f2, f3, f4, f5; CREATE TABLE f1 LIKE facts; INSERT INTO f1 SELECT * FROM facts WHERE key_1 BETWEEN 0 AND 2; CREATE TABLE f2 LIKE facts; INSERT INTO f2 SELECT * FROM facts WHERE key_1 BETWEEN 20001 AND 4; CREATE TABLE f3 LIKE facts; INSERT INTO f3 SELECT * FROM facts WHERE key_1 BETWEEN 40001 AND 6; CREATE TABLE f4 LIKE facts; INSERT INTO f4 SELECT * FROM facts WHERE key_1 BETWEEN 60001 AND 8; CREATE TABLE f5 LIKE facts; INSERT INTO f5 SELECT * FROM facts WHERE key_1 BETWEEN 80001 AND 10; RENAME TABLE facts TO facts_old; CREATE TABLE facts LIKE facts_old; ALTER TABLE facts ENGINE=MERGE UNION=(f1,f2,f3,f4,f5); # (now try the above same query again) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MERGE tables considered harmful for data warehouse fact tables
The script to prove it follows. Splitting a million-row fact table into a 5-part merge table makes 3-second queries take 9 seconds. The basic problem is this: fact tables are generally referenced by unique combinations of dimensions, and though often only one underlying table needs to be referenced, mysql doesn't know this, so every single underlying table is polled for each dimension-combination. Practical alternatives: (1) leave your fact table as a big one; (2) split the table up, but put logic in the client-app so it knows which table to address; (3) use a *union* of queries instead of a merge table, then mysql could look at each underlying table one at a time. If anyone has ideas for other alternatives, or improvements on the script, please tell me. Thanks. Tom. # # MERGE FACT TABLE TEST - [EMAIL PROTECTED] USE sandbox; CREATE TABLE big_table LIKE mysql.help_keyword; ALTER TABLE big_table ENGINE=MERGE UNION=(mysql.help_keyword); DROP TABLE IF EXISTS dimension_1; CREATE TABLE dimension_1 ( key_1 INT PRIMARY KEY NOT NULL, attribute_1 VARCHAR(255) NOT NULL, INDEX attribute_1 (attribute_1(10)) ); SET @A:=1; INSERT INTO dimension_1 SELECT @A:[EMAIL PROTECTED], SHA(RAND()) FROM big_table b1, big_table b2, big_table b3 LIMIT 10; DROP TABLE IF EXISTS dimension_2; CREATE TABLE dimension_2 ( key_2 INT PRIMARY KEY NOT NULL, attribute_2 VARCHAR(255) NOT NULL, INDEX attribute_1 (attribute_2(10)) ); SET @A:=1; INSERT INTO dimension_2 SELECT @A:[EMAIL PROTECTED], SHA(RAND()) FROM big_table b1, big_table b2, big_table b3 LIMIT 10; DROP TABLE IF EXISTS facts; CREATE TABLE facts ( key_1 INT UNSIGNED, key_2 INT UNSIGNED, fact_1 INT UNSIGNED, fact_2 INT UNSIGNED, PRIMARY KEY key_1_key_2 (key_1,key_2) ); INSERT IGNORE INTO facts SELECT CEIL(RAND()*10), CEIL(RAND()*10), CEIL(RAND()*100), CEIL(RAND()*100) FROM big_table b1, big_table b2, big_table b3 LIMIT 100; ## Typical query: (takes about 1.5seconds) SELECT SQL_NO_CACHE attribute_1, sum(fact_1), avg(fact_2) FROM facts JOIN dimension_1 ON dimension_1.key_1=facts.key_1 JOIN dimension_2 ON dimension_2.key_2=facts.key_2 WHERE attribute_1 LIKE 'ff%' AND attribute_2 LIKE 'a3%' GROUP BY attribute_1 LIMIT 1; ## Variation 1: Unionised fact table; DROP TABLES f1, f2, f3, f4, f5; CREATE TABLE f1 LIKE facts; INSERT INTO f1 SELECT * FROM facts WHERE key_1 BETWEEN 0 AND 2; CREATE TABLE f2 LIKE facts; INSERT INTO f2 SELECT * FROM facts WHERE key_1 BETWEEN 20001 AND 4; CREATE TABLE f3 LIKE facts; INSERT INTO f3 SELECT * FROM facts WHERE key_1 BETWEEN 40001 AND 6; CREATE TABLE f4 LIKE facts; INSERT INTO f4 SELECT * FROM facts WHERE key_1 BETWEEN 60001 AND 8; CREATE TABLE f5 LIKE facts; INSERT INTO f5 SELECT * FROM facts WHERE key_1 BETWEEN 80001 AND 10; RENAME TABLE facts TO facts_old; CREATE TABLE facts LIKE facts_old; ALTER TABLE facts ENGINE=MERGE UNION=(f1,f2,f3,f4,f5); # (now try the above same query again) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug Report (LOAD DATA FROM MASTER on MERGE Tables)
It would appear that LOAD DATA FROM MASTER processes databases and tables alphabetically. When a merge table is being copied, and it's name is alphabetically before some/any/all of it's components, the process fails with a 1017 couldn't find file error. Has this been fixed? If so, as of which version? Is this a bug on the master or the slave side? I ask that because I am replicating from 4.1.x to 5.0.x. Many thanks. Gordan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
merge tables
I read the manual and i am still confused as to how the merge tables use indexes. Can someone help me with this ? - Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more.
Locking and MERGE tables
I'm sure the answer is a grim one, but suppose I have tables foo_1 and foo_2, each with unique primary keys. I then create a MERGE table named foo. I have a routine which moves rows from one table to another thusly: LOCK TABLES foo_1 WRITE, foo_2 WRITE; INSERT INTO foo_2 SELECT * FROM foo_1 WHERE id = 100; DELETE FROM foo_1 WHERE id = 100; UNLOCK TABLES; Unfortunately, this seems to completely hose any selects on the MERGE table: all selects die with read_const: Got error 127 when reading table until I issue FLUSH TABLES. Is there a Right Way to move a row from foo_1 to foo_2 short of explicitly locking foo? It seems unwieldy to have to supply the names of any and all MERGE tables to my routine. Eamon Daly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merge tables,trigger support, and table rotation
How does one insert records based on some kind of meta data or key in
particular table belonging to a merge table?
I have a network logging program and would like to partition the table so that
analysts can query certain tables belonging
to the merge table instead of the whold table to corelate events.
/*merge table section of email */
Based on my limited research I thought that I would create a merged table such
as: /*from the web site */
mysql CREATE TABLE t1 (
-a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-message CHAR(20));
mysql CREATE TABLE t2 (
-a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-message CHAR(20));
mysql INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
mysql INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
mysql CREATE TABLE total (
-a INT NOT NULL AUTO_INCREMENT,
-message CHAR(20), INDEX(a))
-TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
/* trigger section of email */
Question(s): I will assume of that when 5.02 becomes stable and triggers are
available
then
mysql CREATE TRIGGER ins_sum INSERT ON total
IF message='happy ending' Then
INSERT INTO t1 (message) VALUES ('goldie locks'),('mama
bear'),('eat her up')
ELSE INSERT INTO t2 (message) VALUES ('KILLED Mama bear'),('papa
bear and baby bear'),
('in cirus');
Will triggers support insert, update, and delete verbs(actions) on a merged
table?
/* table rotation */
Second question: As tables grow can can a new table be created and
automatically added to the
merged table, at or on a particular date or time or when the size of the table
reaches a
certain limit?
Which leads to the third question: if myisam tables can be created dynamically
based
on size of table or time of day, can the oldest table be compressed with
myisampack
automatically?
Thank you,
Raymond
Help needed with merge tables
Hi, I'm a DBA and we r working on a migration project (from Oracle to Mysql) and we have decided to try using MERGE MyISam tables on MySQL, to be sure we are not going in crash with this decission we need some consulting. we need answer for the following questions: 1. How safe/stable are they? 2. what is the max size for MERGE tables? 3. what is the disk size of MERGE tables (in addidtion to the traditional tables)? 4. any known problems/issues/bugs? we need it from someone with field experience on MERGE tables (and not from the web, we already did that). THX Ronen - Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard.
Merge tables and Fulltext?
Hello, Is there any plan to support fulltext indexes in Merge Tables? Thank You. Santino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
merge tables for big log files ?
Hello *, goal: using merge tables for annual / monthly storage of syslog information using php-syslog-ng and syslog-ng problem: -one actual table must be available for inserts -the merge table needs to be the target for reports and queries -static parts of the merge table should be compressed question: what is the best strategy for grouping / using a merge table ? a) have one separate table for continous syslog data and synchronise this table regularily to e.g. a monthly table log_2004_06 (log_2004_06 is Part of a merge table log_global to enable global access to all monthly logs with php-syslog-ng) syslog-ng - log log --(INSERT INTO) - log_2004_06 log_global ( log_2004_12,log2004_11,log_2004_10,...) b) let the insert table for syslog-ng already be part of the merge table and reordering the merge table on a regularly schema, to be able to compress all old,static tables. log ( log_2004_12,log2004_11,log_2004_10,...) Problem for this variant: merge table has to be dropped and recreated periodically. during the short lag interval the log merge table does not exist anyone has done something like this already ? TIA Micha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: merge tables for big log files ?
On Mon, May 31, 2004 at 02:03:09PM +0200, Michael Arndt wrote: Problem for this variant: merge table has to be dropped and recreated periodically. during the short lag interval the log merge table does not exist You don't have to drop and recreate it in MySQL. You can do it under MySQL's feet without it noticing. The .MRG definition file is simply a list of tables that make up the merge, with a couple of optional configuration likes; the only one I use is #INSERT_METHOD. You can create a new .MRG file very easily in Perl, consisting of the new range of tables, then move it over the old .MRG, so in one atomic operation the merge table now has a new subtable; in order to get MySQL to notice however you have to do a flush tables. You can use mysqladmin flush-tables to do that. For insertion, this is where #INSERT_METHOD comes in handy. MySQL can either insert to the first or last table: $ tail -3 MessageIDs.MRG MessageIDs_20040529 MessageIDs_20040530 #INSERT_METHOD=LAST Now INSERT INTO merge_table will be inserting into MessageIDs_20040530, which is today. Just after midnight, my Perl runs and makes a new .MRG, and after the flush MySQL is inserting into the next table without even realising. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: merge tables for big log files ?
Hi Michael, Problem for this variant: merge table has to be dropped and recreated periodically. during the short lag interval the log merge table does not exist You don't have to drop and re-create the table. Please don't edit the MRG file directly either. You can accomplish this easily by using the ALTER TABLE statement: CREATE TABLE t1 (id INT NOT NULL, c CHAR(10) NOT NULL, PRIMARY KEY(id)); CREATE TABLE t2 (id INT NOT NULL, c CHAR(10) NOT NULL, PRIMARY KEY(id)); CREATE TABLE t_merge (id INT NOT NULL, c CHAR(10) NOT NULL, KEY(id)) TYPE=MERGE UNION=(t1, t2); So now you have a MERGE table containing both tables. If you want to add a third one later: CREATE TABLE t3 (id INT NOT NULL, c CHAR(10) NOT NULL, PRIMARY KEY(id)); ALTER TABLE t_merge UNION=(t1, t2, t3); Using ALTER TABLE with a MERGE table is an atomic operation and can be done while the server is up and running full-speed without any problems. Regards, Jeremy -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Jeremy Cole [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Training and Consulting Manager /_/ /_/\_, /___/\___\_\___/ Streetsboro, Ohio USA ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Huge query on MERGE tables
Hi everyone, I have monthly data from 4 years, this means 48 MyISAM tables with identical structure, all defined and indexed properly (I guess) with 650,000 rows (approx.) and 99 columns. The tables are stored in a dedicated partition with 80 Gb of free space. The server is running Windows 2000 with 696 megs of RAM, pentium 4 processor and a 7200 rpm hard disk. Mysql version used is 4.0.17 Now from my database, I have the following: mysql show fields from fun199801; One of the 48 tables. +---+---+--+ -+-+---+ | Field | Type | Null | Key | Default | Extra | +---+---+--+ -+-+---+ | id| int(10) unsigned | | MUL | 0 | | | comn | int(11) | | MUL | 0 | | | actc | int(11) | | MUL | 0 | | | ind | int(10) unsigned | | MUL | 0 | | | origin| char(1) | YES | | NULL| | | period| varchar(6)| YES | | NULL| | | pres | varchar(8)| YES | | NULL| | | c20 | bigint(20) unsigned | YES | | NULL| | | c27 | bigint(20) unsigned | YES | | NULL| | | c28 | bigint(20) unsigned | YES | | NULL| | | c30 | bigint(20) unsigned | YES | | NULL| | | c31 | bigint(20) unsigned | YES | | NULL| | | c32 | bigint(20) unsigned | YES | | NULL| | | c33 | bigint(20) unsigned | YES | | NULL| | | c39 | bigint(20) unsigned | YES | | NULL| | | c41 | bigint(20) unsigned | YES | | NULL| | | c42 | bigint(20) unsigned | YES | | NULL| | ... 99 rows in set (0.02 sec) I made a MERGE table for each year, so I have 4 MERGE tables named fx1998,fx1999,fx2000 and fx2001 Then I wrote the following query: SELECT IFNULL(fx1998.comn,0) as idcomn, IFNULL(fx1998pt.actc,0) as idactc, IFNULL( (CASE WHEN fx1998.id=stat.id THEN 5 WHEN fx1998.id=soc1998.id THEN 10 ELSE ( CASE WHEN fx1998.id BETWEEN 1 AND 4999 THEN 1 WHEN fx1998.id BETWEEN 5000 AND 5299 THEN 2 WHEN fx1998.id BETWEEN 5300 AND 5899 THEN 4 WHEN fx1998.id BETWEEN 5900 AND 5999 THEN 3 WHEN (fx1998.id BETWEEN 7000 AND 7699) OR (fx1998.id BETWEEN 7900 AND 7949) THEN 7 WHEN (fx1998.id BETWEEN 7700 AND 7899) OR (fx1998.id BETWEEN 7950 AND 8699) OR (fx1998.id BETWEEN 8750 AND 8999) THEN 8 WHEN fx1998.id BETWEEN 8700 AND 8749 THEN 9 WHEN fx1998.id=9000 THEN 11 ELSE 6 END) END),0) as idsoc, COUNT(DISTINCT fx1998.id) as num, SUM(c108+c111+c112+c154) as sales, SUM(c109) as cost, SUM(c39+c42) as retenc, 1998 as year FROM ipt.fx1998 LEFT JOIN utils.soc1998 ON fx1998.id=soc1998.id LEFT JOIN utils.stat ON fx1998.id=stat.id GROUP BY idcomn,idactc,idsoc UNION ALL (the same syntax as the above, but for the remaining 3 years) Here is the output of the EXPLAIN command for the query: ++--+---+--+-+-- --+-+-+ | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+-- --+-+-+ | fx1998 | ALL | NULL | NULL |NULL | NULL | 8079209 | Using temporary; Using filesort | | soc1998| ref | ind | ind | 4 | f29a1998pt.rut | 1 | Using index | | stat | ref | ind | ind | 4 | f29a1998pt.rut | 1 | Using index | | fx1999 | ALL | NULL | NULL |NULL | NULL | 8222017
MERGE tables and concurrent inserts
Hello, Further to my MERGE table query the other day, I've now put it into action and it seems to be working fine. However I'm noticing that INSERT LOW_PRIORITY statements are blocking when there are SELECTs running. Does anyone know if concurrent inserts work with MERGE tables? Are there any criteria I need to satisfy? I know for certain that the table being used to INSERT to has *no* deleted rows in it. Do I need to ensure that all children of the MERGE table have no deleted rows? I'm wondering if specifying LOW_PRIORITY disables concurrent inserts explicitly, so I'll try removing that and see if anything improves, but in the meantime, if anyone has any advice, please share :) Thanks, -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MERGE tables and concurrent inserts
On Wed, Mar 24, 2004 at 01:23:53PM +, Chris Elsworth wrote: I'm wondering if specifying LOW_PRIORITY disables concurrent inserts explicitly, so I'll try removing that and see if anything improves, but in the meantime, if anyone has any advice, please share :) Ignore that bit. Found the answer 10 seconds after sending in the INSERT page. Note that LOW_PRIORITY should normally not be used with MyISAM tables as this disables concurrent inserts. Doh :) -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full-Text Search on MERGE Tables
Lorderon [EMAIL PROTECTED] wrote: Is it possible to define MERGE table on several tables with full-text indexes? Yes, but without specification of FULLTEXT index in the MERGE table. And to make a select on the MERGE table with MATCH AGAINST? You can permorm boolean full-text search that can work without FULLTEXT index. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full-Text Search on MERGE Tables
Hello All, Is it possible to define MERGE table on several tables with full-text indexes? And to make a select on the MERGE table with MATCH AGAINST? thanks, -Lorderon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merge Tables not working...
Hello, I've posted this before, but did not get a fix or find out what I am not doing coreectly. I've established several merge tables. 3 out of the 5 of my merge tables work fine...but when I try to access (e.g. describe or query) the other two, I get the following error: mysql describe hospchar; ERROR 1016: Can't open file: 'employee.MRG'. (errno: 143) mysql I am running MySQL 4.0.15-max-nt on Windows 2000 Professional. Any ideas? Thanks in advance. Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge Tables not working...
Hi Tony, shell perror 143 Error code 143: Unknown error 143 = Conflicting table definition between MERGE and mapped table Matt - Original Message - From: [EMAIL PROTECTED] Sent: Thursday, November 13, 2003 4:00 PM Subject: Merge Tables not working... Hello, I've posted this before, but did not get a fix or find out what I am not doing coreectly. I've established several merge tables. 3 out of the 5 of my merge tables work fine...but when I try to access (e.g. describe or query) the other two, I get the following error: mysql describe hospchar; ERROR 1016: Can't open file: 'employee.MRG'. (errno: 143) mysql I am running MySQL 4.0.15-max-nt on Windows 2000 Professional. Any ideas? Thanks in advance. Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MERGE tables error
I've created several merge tables from the command prompt. All seems well, the .frm and .MRG files are created. The contents of the .MRG files are accurate. I flushed the tables (even restarted MySQL). I can see the newly created merge tables, but when I try to run a DESCRIBE or a SELECT COUNT(*) to verify the records counts, I get the following error message: ERROR 1016: Can't open file: 'new_master.MRG'. (errno: 143) All table structures are identical. Am I missing a basic error numbers page in the online docs? I've found a few, but nothing comprehensive, like a page where I can enter an error number and find the detailed message, probable cause, recommdned action, etc. The only thing that I have been able to find is: http://www.mysql.com/doc/en/Error-returns.html which essentially confirms that an error 1016 is a problem opening a file. I am using MySQL version 3.23.56-nt on Windows2000. Any help, links or guidance would be greatly appreciated. /Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MERGE tables error
Error 143 suggests that you have conflicting table definitions. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, October 24, 2003 7:08 AM To: [EMAIL PROTECTED] Subject: MERGE tables error I've created several merge tables from the command prompt. All seems well, the .frm and .MRG files are created. The contents of the .MRG files are accurate. I flushed the tables (even restarted MySQL). I can see the newly created merge tables, but when I try to run a DESCRIBE or a SELECT COUNT(*) to verify the records counts, I get the following error message: ERROR 1016: Can't open file: 'new_master.MRG'. (errno: 143) All table structures are identical. Am I missing a basic error numbers page in the online docs? I've found a few, but nothing comprehensive, like a page where I can enter an error number and find the detailed message, probable cause, recommdned action, etc. The only thing that I have been able to find is: http://www.mysql.com/doc/en/Error-returns.html which essentially confirms that an error 1016 is a problem opening a file. I am using MySQL version 3.23.56-nt on Windows2000. Any help, links or guidance would be greatly appreciated. /Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MERGE tables error
* [EMAIL PROTECTED] [...] ERROR 1016: Can't open file: 'new_master.MRG'. (errno: 143) All table structures are identical. Maybe not..? :) Am I missing a basic error numbers page in the online docs? I've found a few, but nothing comprehensive, like a page where I can enter an error number and find the detailed message, probable cause, recommdned action, etc. The only thing that I have been able to find is: http://www.mysql.com/doc/en/Error-returns.html which essentially confirms that an error 1016 is a problem opening a file. I am using MySQL version 3.23.56-nt on Windows2000. Run the perror utility, located in the mysql\bin directory: C:\mysql\binperror 143 Error code 143: Unknown error 143 = Conflicting table definition between MERGE and mapped table -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with merge tables in 4.1
Hi Dan, Ooops, this makes a lot of sense - it works now. Thanks for letting me know about the perror utility, it should help greatly to identify problems in the future. Regards, Pierre-Luc Dan Nelson wrote: In the last episode (Aug 06), Pierre-Luc Soucy said: I was working on some merge tables this morning and it worked fine, but after a few unsuccessful table creation requests (I was making some tests), I could not alter or select from a table for the following reason: mysql create table test_table (country CHAR(2) NOT NULL) TYPE=MERGE UNION=(countries); Query OK, 0 rows affected (0.00 sec) mysql select * from test_table; ERROR 1016: Can't open file: 'test_table.MRG'. (errno: 143) $ perror 143 Error code 143: Unknown error: 143 143 = Conflicting table definitions in sub-tables of MERGE table Does your countries table layout match that of test_table? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with merge tables in 4.1
In the last episode (Aug 06), Pierre-Luc Soucy said: I was working on some merge tables this morning and it worked fine, but after a few unsuccessful table creation requests (I was making some tests), I could not alter or select from a table for the following reason: mysql create table test_table (country CHAR(2) NOT NULL) TYPE=MERGE UNION=(countries); Query OK, 0 rows affected (0.00 sec) mysql select * from test_table; ERROR 1016: Can't open file: 'test_table.MRG'. (errno: 143) $ perror 143 Error code 143: Unknown error: 143 143 = Conflicting table definitions in sub-tables of MERGE table Does your countries table layout match that of test_table? -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with merge tables in 4.1
Hi, I was working on some merge tables this morning and it worked fine, but after a few unsuccessful table creation requests (I was making some tests), I could not alter or select from a table for the following reason: mysql create table test_table (country CHAR(2) NOT NULL) TYPE=MERGE UNION=(countries); Query OK, 0 rows affected (0.00 sec) mysql select * from test_table; ERROR 1016: Can't open file: 'test_table.MRG'. (errno: 143) mysql check table test_table; +-+---+--+-+ | Table | Op| Msg_type | Msg_text| +-+---+--+-+ | statscounter.test_table | check | error| Can't open file: 'test_table.MRG'. (errno: 143) | +-+---+--+-+ 1 row in set (0.00 sec) mysql repair table test_table; +-++--+-+ | Table | Op | Msg_type | Msg_text| +-++--+-+ | statscounter.test_table | repair | error| Can't open file: 'test_table.MRG'. (errno: 143) | +-++--+-+ 1 row in set (0.00 sec) I then dropped the table and attempted to create a new one, with no success. Also tried another table name, or putting the table in another database, and that did not work either. Since it worked earlier, I tried restarting the MySQL server, but that didn't correct the problem either. Do you have any idea of what I'm doing wrong, or what needs to be done to get rid of that error? I thought about submitting a bug report but do not know how this error could be reproduced on another machine. I am running 4.1.0 on dual-CPU debian box with SCSI RAID hard drives, if that can help. Thanks! Pierre-Luc Soucy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MERGE tables still gamma?
[EMAIL PROTECTED] wrote: The manual page for MERGE tables states the code is in gamma since 3.23.25. But it also says you can only SELECT, DELETE, and UPDATE, which isn't true since version 4.0.something. Can anyone verify if the MERGE tables is still in gamma? We occassionally have diskspace issues on our system, and I'd love to split our invoice table into years and use pack on the old years and replace the original table with a MERGE. MERGE tables are stable. Thanks for point, it should be documented better. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MERGE tables still gamma?
The manual page for MERGE tables states the code is in gamma since 3.23.25. But it also says you can only SELECT, DELETE, and UPDATE, which isn't true since version 4.0.something. Can anyone verify if the MERGE tables is still in gamma? We occassionally have diskspace issues on our system, and I'd love to split our invoice table into years and use pack on the old years and replace the original table with a MERGE. -- Mike Miller Business Analyst Applications Developer BMG Canada Inc. Tel: 416-586-1646 Fax: 416-586-0454 EMail: [EMAIL PROTECTED]
bug with MERGE tables and MAX
Description: MAX fails to return the correct value in some MERGE table situations. Based on experimentation I am guessing that the query fails to read all of the member tables when the query can be performed entirely by reading the index. I seem to be able to reproduce this every time on MySQL 3.23.51, .55, and one of the versions in between (but I forget which). I don't believe this is the same as any of the known bugs listed in MERGE table problems in the docs. But I could be wrong. How-To-Repeat: Here is a sequence of commands that can be source-d: -- optional drop table if exists whole; drop table if exists part1; drop table if exists part2; -- create tables create table part1 (id int(10) not null, value int(10), key (id, value)); create table part2 (id int(10) not null, value int(10), key (id, value)); create table whole (id int(10) not null, value int(10), key (id, value)) type=merge union=(part1,part2); -- insert some values insert into part1 values (1, 100), (2, 200), (3, 300); insert into part2 values (1, 200), (5, 500); -- this correctly shows all 5 rows select * from whole; -- this correctly shows the 2 values where id = 1 select value from whole where id = 1; -- this ought to say 200 but says 100 instead select max(value) from whole where id = 1; -- this also ought to say 200 but says 100 instead select floor(max(value)) from whole where id = 1; -- this correctly says 200 select max(floor(value)) from whole where id = 1; -- this also correctly says 200 select id, max(value) from whole where id = 1 group by id; Fix: Workaround: in the query, replace MAX(expr) with MAX(FUNC(expr)) where FUNC is a no-op. The original problem arose with a datetime column (I changed it to an int for the test case above), so e.g. FROM_UNIXTIME(MAX(UNIX_TIMESTAMP(tstamp))) works around the bug. Submitter-Id: Originator:S. Spencer Sun Organization: MySQL support: none Synopsis: MAX fails to return the correct value in some MERGE table situations Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.55 (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.55, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.55-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 10 min 22 sec Threads: 1 Questions: 121 Slow queries: 0 Opens: 35 Flush tables: 1 Open tables: 6 Queries per second avg: 0.195 Environment: System: Linux pratt 2.2.22-6.2.2smp #2 SMP Tue Oct 22 20:32:19 PDT 2002 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/egcs-2.91.66/specs gcc version egcs-2.91.66 19990314/Linux (egcs-1.1.2 release) Compilation info: CC='gcc' CFLAGS='-O3 -mpentiumpro' CXX='gcc' CXXFLAGS='-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Mar 13 2001 /lib/libc.so.6 - libc-2.1.3.so -rwxr-xr-x1 root root 4106572 Sep 9 09:58 /lib/libc-2.1.3.so -rw-r--r--1 root root 20336836 Sep 9 09:58 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 9 09:58 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local' '--localstatedir=/var/mysql' '--enable-assembler' '--with-mysqld-ldflags=-all-static' '--with-raid' '--enable-local-infile' 'CFLAGS=-O3 -mpentiumpro' 'CXXFLAGS=-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti' 'CXX=gcc' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
problem about merge tables in mysql
Hi, all I have two tables with full of data. Their structure is same. Tables1 id type name 1 2 3 ... Tables2 id type name 1 2 3 4 I want to merge them into one table. id typename 1 2 3 4 5 6 7 ... How can I do it? thanks in advance. luoya sql,query,queries,smallint _ MSN 8: advanced junk mail protection and 2 months FREE*. http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
temporary and merge tables
Hi, when trying to create a temporary table of type merge, mysql 2.23.53 fails with an error like this: Can't find file: '#sql24b_776_0.MRG' (errno: 2) when not using the temporary keyword, everything works fine. so i assume this is a missing feature (not documented that this is forbidden/not supported yet) or a bug. Corin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: temporary and merge tables
At 9:01 +0100 12/30/02, Webmaster (Corin Langosch) wrote: Hi, when trying to create a temporary table of type merge, mysql 2.23.53 fails with an error like this: Can't find file: '#sql24b_776_0.MRG' (errno: 2) when not using the temporary keyword, everything works fine. so i assume this is a missing feature (not documented that this is forbidden/not supported yet) or a bug. Looks like it's documented to me: http://www.mysql.com/doc/en/News-3.23.54.html Corin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: temporary and merge tables
Hi, sorry, didn't look at the changelog... ;) thanks! corin Monday, December 30, 2002, 9:29:12 AM, you wrote: PD At 9:01 +0100 12/30/02, Webmaster (Corin Langosch) wrote: Hi, when trying to create a temporary table of type merge, mysql 2.23.53 fails with an error like this: Can't find file: '#sql24b_776_0.MRG' (errno: 2) when not using the temporary keyword, everything works fine. so i assume this is a missing feature (not documented that this is forbidden/not supported yet) or a bug. PD Looks like it's documented to me: PD http://www.mysql.com/doc/en/News-3.23.54.html Corin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FW: Merge Tables Across Databases
Can you create a merge table whose component tables exist in other databases? I am able to create merge tables for tables that are in one database but I want to be able to aggregate data from multiple databases. I am using 3.23 but I am migrating to 4.0.5a next week, if that makes a difference. The following is an example of what I am trying to do: Database Name: cust1 Table Name: logs Database Name: cust2 Table Name: logs Database Name: all CREATE TABLE all.combined_logs (column defs here) TYPE=MERGE UNION=(cust1.logs, cust2.logs) INSERT_METHOD=NO; Thanks Lou Query, sql -- to defeat the spam filter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: MySQL 4.03 and merge tables: bug with BETWEEN
Tac, Friday, September 27, 2002, 10:34:46 PM, you wrote: T We're having a problem with a huge merge table and BETWEEN. The same query T in MySQL 3, using an identical (binary) copy of the database, takes a T fraction of a second, in MySQL 4.03 it runs without stopping. T The query is a simple Select * from my_merge_table where my_code BETWEEN T '2026676653' AND '2026676655' T If I run a simple script that loops through the invidiual tables of the T merge table, using the same query (with each table), the response is very T fast. And running the query when the operator is '=' instead of BETWEEN, T everything is fine, e.g. T Select * from my_merge_table where my_code = '2026676653' T Expanding out the BETWEEN still causes the query to run forever: T Select * from my_merge_table where my_code = '2026676653' AND my_code T = '2026676655' I tested it on the not so big merge table (~ 150 000 rows) and it worked fine with BETWEEN. T Another bug that's in MySQL 3 (that I haven't checked in MySQL 4) is trying T to use a merge table where one of the tables has no rows. When we create a T merge table, we go through each table first and check to make sure it has T something in it, otherwise, the merge table is created ok but queries T against it return no rows. Of course MySQL will check every table to check if it contains matched rows. T Finally, does anyone know if you can now use fulltext indexes with a merge T table? We're not yet (since it's not supported), but I did see that on the T to-do list. Full-text search still works only on MyISAM tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 4.03 and merge tables: bug with BETWEEN
We're having a problem with a huge merge table and BETWEEN. The same query in MySQL 3, using an identical (binary) copy of the database, takes a fraction of a second, in MySQL 4.03 it runs without stopping. The query is a simple Select * from my_merge_table where my_code BETWEEN '2026676653' AND '2026676655' If I run a simple script that loops through the invidiual tables of the merge table, using the same query (with each table), the response is very fast. And running the query when the operator is '=' instead of BETWEEN, everything is fine, e.g. Select * from my_merge_table where my_code = '2026676653' Expanding out the BETWEEN still causes the query to run forever: Select * from my_merge_table where my_code = '2026676653' AND my_code = '2026676655' Another bug that's in MySQL 3 (that I haven't checked in MySQL 4) is trying to use a merge table where one of the tables has no rows. When we create a merge table, we go through each table first and check to make sure it has something in it, otherwise, the merge table is created ok but queries against it return no rows. Finally, does anyone know if you can now use fulltext indexes with a merge table? We're not yet (since it's not supported), but I did see that on the to-do list. Thanks! Tac - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
merge tables - errorno: 143 - Is there a limit on columns with mergedtables ?
Hello, I get the following error when trying to access a table created of TYPE=MERGE mysql describe yy; ERROR 1016: Can't open file: 'yy.MRG'. (errno: 143) Reducing the number of colums in the tables to be merged removed the problem. Any ideas gratefully received, Dave ( MySQL version: 3.23.52 ) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: merge tables - errorno: 143 - Is there a limit on columns with merged tables ?
from perror 143 = Conflicting table definition between MERGE and mapped table On Sunday, Sep 22, 2002, at 18:36 America/Anchorage, David Herring wrote: Hello, I get the following error when trying to access a table created of TYPE=MERGE mysql describe yy; ERROR 1016: Can't open file: 'yy.MRG'. (errno: 143) Reducing the number of colums in the tables to be merged removed the problem. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
slow performance with MERGE tables
Hello,
I am running a rather simple query on a merge table that seems to be taking
much longer than it should.
First let me show the table status of the tables I have merged into table
'blah':
NameTypeRow_format RowsAvg_row_length Data_length
Max_data_length Index_lengthData_free Auto_increment Create_time
Update_time Check_time Create_options Comment
accounting_2002W29 MyISAM Fixed 4 435 17401868310773759 3072
0 NULL2002-09-19 21:11:30 2002-09-20 19:27:01 NULL
accounting_2002W30 MyISAM Fixed 10 435 43501868310773759 3072
0 NULL2002-09-19 21:24:35 2002-09-20 18:37:51 NULL
accounting_2002W31 MyISAM Fixed 1612741 435 701542335 1868310773759
975564800 NULL2002-09-19 21:08:09 2002-09-20 19:45:32 NULL
accounting_2002W32 MyISAM Fixed 2341152 435 1018401120 1868310773759
142214144 0 NULL2002-09-19 21:08:15 2002-09-20 19:45:32 NULL
accounting_2002W33 MyISAM Fixed 3204059 435 1393765665 1868310773759
195347456 0 NULL2002-09-19 21:08:25 2002-09-20 19:45:32 NULL
accounting_2002W34 MyISAM Fixed 3388325 435 1473921375 1868310773759
207994880 0 NULL2002-09-19 21:08:43 2002-09-20 19:45:32 NULL
accounting_2002W35 MyISAM Fixed 3322128 435 1445125680 1868310773759
204946432 0 NULL2002-09-20 11:26:21 2002-09-20 19:45:32 NULL
And the query I run takes very long:
mysql select count(distinct nas_ip) from blah;
++
| count(distinct nas_ip) |
++
|116 |
++
1 row in set (15 min 43.27 sec)
I consider this very long because if I do the same query on one of the merged
tables it is much faster:
mysql select count(distinct nas_ip) from accounting_2002W35;
++
| count(distinct nas_ip) |
++
| 96 |
++
1 row in set (42.03 sec)
Note I have restarted the server before each query to not let caching affect
this. I don't understand why the merge table takes so long. It would be faster
for me to issue a query for the nas_ip in each table, output this to a
temporary table, and then do a select count(distinct) on this table.
Why is the merge table showing bad performance on this query?
Here are some more details.
System: Linux 2.2.16 (Redhat 6.2)
show create table accounting_2002W35;
CREATE TABLE `accounting_2002W35` (
`acct_status_type` enum('Start','Stop') NOT NULL default 'Start',
`user_name` char(80) NOT NULL default '',
`nas_ip` char(15) NOT NULL default '',
`acct_session_time` mediumint(8) unsigned default NULL,
`acct_session_id` char(30) NOT NULL default '',
`timestamp` datetime NOT NULL default '-00-00 00:00:00',
`framed_ip` char(15) default NULL,
`input_bytes` int(10) unsigned default NULL,
`output_bytes` int(10) unsigned default NULL,
`calling_station_id` char(80) default NULL,
`called_station_id` char(80) default NULL,
`baud_rate` mediumint(8) unsigned default NULL,
`framed_protocol` char(20) default NULL,
`class` char(80) default NULL,
`visp_id` mediumint(8) unsigned default NULL,
`country_id` tinyint(3) unsigned default NULL,
`city_id` mediumint(8) unsigned default NULL,
`provider_id` mediumint(8) unsigned default NULL,
UNIQUE KEY `ac_ind` (`user_name`,`acct_session_id`,`nas_ip`,`acct_status_type`),
KEY `nas_ind` (`nas_ip`)
) TYPE=MyISAM
(all accounting tables are the same as above)
show create table blah;
CREATE TABLE `blah` (
`acct_status_type` enum('Start','Stop') NOT NULL default 'Start',
`user_name` char(80) NOT NULL default '',
`nas_ip` char(15) NOT NULL default '',
`acct_session_time` mediumint(8) unsigned default NULL,
`acct_session_id` char(30) NOT NULL default '',
`timestamp` datetime NOT NULL default '-00-00 00:00:00',
`framed_ip` char(15) default NULL,
`input_bytes` int(10) unsigned default NULL,
`output_bytes` int(10) unsigned default NULL,
`calling_station_id` char(80) default NULL,
`called_station_id` char(80) default NULL,
`baud_rate` mediumint(8) unsigned default NULL,
`framed_protocol` char(20) default NULL,
`class` char(80) default NULL,
`visp_id` mediumint(8) unsigned default NULL,
`country_id` tinyint(3) unsigned default NULL,
`city_id` mediumint(8) unsigned default NULL,
`provider_id` mediumint(8) unsigned default NULL,
UNIQUE KEY `ac_ind` (`user_name`,`acct_session_id`,`nas_ip`,`acct_status_type`),
KEY `nas_ind` (`nas_ip`)
) TYPE=MRG_MyISAM
UNION=(accounting_2002W29,accounting_2002W30,accounting_2002W31,accounting_2002W32,accounting_2002W33,accounting_2002W34,accounting_2002W35)
Thanks
Merge tables: unexpected behaviour and mysqld crash
Hi, I'm trying to make use of the new(ish) merge table type, and am running into two problems. The first is unexpected behaviour, the second is a straight MySQL server crash. I have tested 3.23.47, 3.23.49 and 3.23.51 with similar results. Details below refer to 3.23.51. I have also tested on Darwin (MacOS X 10.1.3) and Linux (RedHat 7.3, kernel 2.4.18), with the same results. Both deal with the same query type and datasets. Example tables and data: CREATE TABLE t1 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b) ) TYPE=MyISAM; INSERT INTO t1 VALUES (1,1); INSERT INTO t1 VALUES (2,1); CREATE TABLE t2 ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', PRIMARY KEY (a,b) ) TYPE=MyISAM; INSERT INTO t2 VALUES (1,2); INSERT INTO t2 VALUES (2,2); CREATE TABLE t ( a int(11) NOT NULL default '0', b int(11) NOT NULL default '0', KEY a (a,b) ) TYPE=MRG_MyISAM UNION=(t1,t2); Unexpected behaviour: mysql select max(b) from t1 where a = 1; ++ | max(b) | ++ | 1 | ++ 1 row in set (0.00 sec) mysql select max(b) from t2 where a = 1; ++ | max(b) | ++ | 2 | ++ 1 row in set (0.00 sec) mysql select max(b) from t where a = 1; ++ | max(b) | ++ | 1 | huh? ++ 1 row in set (0.00 sec) Crasher: mysql select max(b) from t1 where a = 2; ++ | max(b) | ++ | 1 | ++ 1 row in set (0.00 sec) mysql select max(b) from t2 where a = 2; ++ | max(b) | ++ | 2 | ++ 1 row in set (0.01 sec) mysql select max(b) from t where a = 2; ERROR 2013: Lost connection to MySQL server during query mysql Traceback looks like the following: pbg3$ gdb ./mysqld GNU gdb 5.0-20001113 (Apple version gdb-200) (Mon Sep 3 02:43:52 GMT 2001) (UI_OUT) Copyright 2000 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type show copying to see the conditions. There is absolutely no warranty for GDB. Type show warranty for details. This GDB was configured as powerpc-apple-macos10. Reading symbols for shared libraries ... done (gdb) run Starting program: /Users/stix/tmp/mysql-3.23.51/sql/./mysqld [Switching to thread 1 (process 15018 thread 0x1903)] /Users/stix/tmp/mysql-3.23.51/sql/./mysqld: ready for connections [Switching to thread 3 (process 15018 thread 0x2403)] Program received signal EXC_BAD_ACCESS, Could not access memory. [Switching to process 15018 thread 0x2403] 0x000f6384 in myrg_rprev (info=0x777050, buf=0x778de0 ?, inx=0) at myrg_rprev.c:29 29if ((err=mi_rprev(info-current_table-table,NULL,inx))) (gdb) bt #0 0x000f6384 in myrg_rprev (info=0x777050, buf=0x778de0 ?, inx=0) at myrg_rprev.c:29 #1 0x0009fa00 in ha_myisammrg::index_prev (this=0x778d58, buf=0x778de0 ?) at ha_myisammrg.cc:115 #2 0x00096b04 in opt_sum_query (tables=0x774178, all_fields=@0x778de0, conds=0x774240) at opt_sum.cc:159 #3 0x00068e00 in mysql_select (thd=0x7738b0, tables=0x774178, fields=@0x773ac8, conds=0x774240, order=0x0, group=0x0, having=0x0, proc_param=0xfe9a00, select_options=17339392, result=0x7742b0) at sql_select.cc:330 #4 0x0004ff28 in mysql_execute_command () at sql_parse.cc:1169 #5 0x00052804 in mysql_parse (thd=0x7738b0, inBuf=0x7739cc , length=32) at sql_parse.cc:2350 #6 0x0004ef18 in do_command (thd=0x7738b0) at sql_parse.cc:834 #7 0x0004e2f8 in handle_one_connection (arg=0x777050) at sql_parse.cc:554 #8 0x7002054c in _pthread_body () Current language: auto; currently c (gdb) If I feel game, I might start digging in the source, but I figure someone should be able to see this pretty easily. Thanks, -- Paul Ripke stixpjr @ yahoo . com . au http://www.sold.com.au - SOLD.com.au - Find yourself a bargain! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: merge tables using sql to find table names
Does your master table have a list of table names and the table's
characteristics? How is the search called?
currently i have a master table called files, the primary key is id and
the data tables are called filedata1 filedata2, etc.
where 1 and 2 correspond to the file id. Each of the filedata tables
contains all the content to dynamically create a new file based on a linked
template.
file
id = 1, name=greenville, contact=bob, template=3, defdirectory =
soccer/ etc...
filedata1
id = 29, type=1, data1=April 3, we won 6-4 against the bedford bears,
data2=null, data3=null
id=34, type=1, data1=Welcome to the greenville soccer team web page,
data2=null, data3=null
when the type=4 it looks for the text in the TextData file. However, when
there is much less text, it is stored in the filedata file itself in the
data1 column. data1 also contains the text that will be used in the alt tag
for images(type=2), and the link's tags for links (type=3), etc.
currently i am searching the TextData table using:
$sql = SELECT files.location, files.filename, TextData.*,
MATCH(TextData.text) AGAINST ('$searchFor') AS score ;
$sql .=FROM TextData, files WHERE MATCH(TextData.text) AGAINST
('$searchFor') AND files.id = TextData.fid;
1) i want to be able to merge the FileDataX tables like this- only this
doesn't work: error near (SELECT CONCAT...
$sql = CREATE TEMPORARY TABLE searchtable (id int, type tinyint, data1
varchar(255), data2 int, data3 int) TYPE=MERGE UNION=(SELECT
CONCAT('filedata',id) FROM files WHERE defdirectory LIKE '$defdirectory%')
INSERT_METHOD=LAST;
and then perform my full text search on data1 where type=1?
snip: jay
Seems like you would have to call multiple queries no matter what, unless
you creat one large merged table for your several thousand small tables. Is
this normalization gone overboard? Do all of the tables match in
configuration (same # of columns, same datatype per column, etc.)?
/snip
actaully - i thought about one BIG table, or even medium sized tables
however the primary use of the tables is to build one file from one
filedata table, with the future ability for files to reference each other,
to share data. to simplify the process, the editor reads in the entire
table, presents the old data, and then on update, it deletes the entire
table, and inserts the new data. otherwise, i would have to look for data
that was removed, create an array of deleted rows, and then run the delete
query, which actually takes lot more time than delete * from filedataX.
There are several other factors that caused me to go the route of several
filedata tables for each file.
i will also say - my normalization isn't as smart as i'd have liked, and
i'm thinking that i may move ALL the text into the textdata table, which is
3 columns
fid = files.id INT
line = filedatax.id INT
text = whatever text TEXT
but i have an index on my text column (required for the fulltext seach) and
am concerned that if it's tooo big, it'll be a very slow search! as well as
slowing down my editor too much, if got it down to about 20:1 small text :
large text.
ken
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Merge tables, was [two table's columns into one columns w/o INSERT ... SELECT?]
I have these two tables; mysql describe tblClass11; +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | ID | int(11) | | PRI | NULL| auto_increment | | RecordID | varchar(6)| YES | | NULL|| | RecordDate | varchar(10) | YES | | NULL|| | CIC | varchar(4)| YES | | NULL|| | Minutes | decimal(12,2) | YES | | NULL|| | FileName | varchar(32) | YES | | NULL|| | RecordNumber | int(11) | YES | | NULL|| | WholeRecord | text | YES | | NULL|| | ReceivedDate | varchar(10) | YES | | NULL|| +--+---+--+-+-++ 9 rows in set (0.00 sec) mysql describe tblClassOthers; +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | ID | int(11) | | PRI | NULL| auto_increment | | RecordID | varchar(6)| YES | | NULL|| | RecordDate | varchar(10) | YES | | NULL|| | CIC | varchar(4)| YES | | NULL|| | Minutes | decimal(12,2) | YES | | NULL|| | FileName | varchar(32) | YES | | NULL|| | RecordNumber | int(11) | YES | | NULL|| | WholeRecord | text | YES | | NULL|| | ReceivedDate | varchar(10) | YES | | NULL|| +--+---+--+-+-++ 9 rows in set (0.00 sec) I attempt to MERGE; mysql CREATE TABLE tblCross1 (ID INT NOT NULL, - RecordID VARCHAR(6), - RecordDate VARCHAR(10), - Minutes DECIMAL(12,2), - KEY(ID)) - TYPE=MERGE - UNION=(tblClass11, tblClassOthers); Query OK, 0 rows affected (0.01 sec) Then I attempt to query;mysql select count(*) from tblCross1; ERROR 1016: Can't open file: 'tblCross1.MRG'. (errno: 143) perror 143 143 = Conflicting table definition between MERGE and mapped table Can anyone help with this? Thanks! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Merge tables, was [two table's columns into one columns w/oINSERT ... SELECT?]
At 15:29 -0500 5/6/02, Jay Blanchard wrote: I have these two tables; mysql describe tblClass11; +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | ID | int(11) | | PRI | NULL| auto_increment | | RecordID | varchar(6)| YES | | NULL|| | RecordDate | varchar(10) | YES | | NULL|| | CIC | varchar(4)| YES | | NULL|| | Minutes | decimal(12,2) | YES | | NULL|| | FileName | varchar(32) | YES | | NULL|| | RecordNumber | int(11) | YES | | NULL|| | WholeRecord | text | YES | | NULL|| | ReceivedDate | varchar(10) | YES | | NULL|| +--+---+--+-+-++ 9 rows in set (0.00 sec) mysql describe tblClassOthers; +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | ID | int(11) | | PRI | NULL| auto_increment | | RecordID | varchar(6)| YES | | NULL|| | RecordDate | varchar(10) | YES | | NULL|| | CIC | varchar(4)| YES | | NULL|| | Minutes | decimal(12,2) | YES | | NULL|| | FileName | varchar(32) | YES | | NULL|| | RecordNumber | int(11) | YES | | NULL|| | WholeRecord | text | YES | | NULL|| | ReceivedDate | varchar(10) | YES | | NULL|| +--+---+--+-+-++ 9 rows in set (0.00 sec) I attempt to MERGE; mysql CREATE TABLE tblCross1 (ID INT NOT NULL, - RecordID VARCHAR(6), - RecordDate VARCHAR(10), - Minutes DECIMAL(12,2), - KEY(ID)) - TYPE=MERGE - UNION=(tblClass11, tblClassOthers); Query OK, 0 rows affected (0.01 sec) Then I attempt to query;mysql select count(*) from tblCross1; ERROR 1016: Can't open file: 'tblCross1.MRG'. (errno: 143) perror 143 143 = Conflicting table definition between MERGE and mapped table Can anyone help with this? Thanks! Jay Drop the MERGE table, then try creating it with all the columns in the original tables. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Merge tables, was [two table's columns into one columns w/o INSERT ... SELECT?]
[snip] Then I attempt to query;mysql select count(*) from tblCross1; ERROR 1016: Can't open file: 'tblCross1.MRG'. (errno: 143) perror 143 143 = Conflicting table definition between MERGE and mapped table Can anyone help with this? Drop the MERGE table, then try creating it with all the columns in the original tables. [/snip] OK, that work with two of the tables, I have to merge 3. Is that possible? Do the INDEXES have to exist on the same columns in each table? How identical do the tables have to be? Thanks! Jay P.S. Paul, couldn't help but notice your last name, where are you from? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Merge tables, was [two table's columns into one columns w/oINSERT ... SELECT?]
At 15:44 -0500 5/6/02, Jay Blanchard wrote: [snip] Then I attempt to query;mysql select count(*) from tblCross1; ERROR 1016: Can't open file: 'tblCross1.MRG'. (errno: 143) perror 143 143 = Conflicting table definition between MERGE and mapped table Can anyone help with this? Drop the MERGE table, then try creating it with all the columns in the original tables. [/snip] OK, that work with two of the tables, I have to merge 3. Is that possible? Yes. Do the INDEXES have to exist on the same columns in each table? How identical do the tables have to be? Identical. Column names, types, order within tables, and indexes. Thanks! Jay P.S. Paul, couldn't help but notice your last name, where are you from? Huh? Wisconsin. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Syntax error with merge tables
Hi! Eric == Eric Thelin [EMAIL PROTECTED] writes: Eric On Sat, 20 Apr 2002 [EMAIL PROTECTED] wrote: I always get a syntax error when using INSERT_METHOD in creating merge tables. If I leave off the INSERT_METHOD part it works fine. I have tried this on many versions in the past and now on 3.23.49a. I am running on Mandrake Linux 8.1 but have seen this same error on other linux distros. It is my understanding from reading the manual that the following should work but it doesn't. cut CREATE TABLE mytable ( aINTEGER NOT NULL PRIMARY KEY, bCHAR(18) NOT NULL ) TYPE=MERGE UNION=(mytable0, mytable1, mytable2) INSERT_METHOD=LAST; Since the .MRG files are plain text could someone send me what the INSERT_METHOD part is supposed to look like so I can do it manually if needed. Thanks. The problem is that INSERT_METHOD only works in MySQL 4.0, not in 3.23 If you need this feature, then you should download the MySQL 4.0.1 distribution and try this out. The MySQL 4.0 series has been out a while now and has in practice proven to be be quite stable.. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Syntax error with merge tables
Perhaps the INSERT_METHOD functionality was introduced in 4.0 but the merge tables themselves work in later 3.23 versions. If this is the case the documentation needs to be updated to show which version supports what. Eric On Sat, 20 Apr 2002, Benjamin Pflugmann wrote: Hi. AFAIK, insert for MERGE tables was introduced in version 4.0.0. Let's see... yep, http://www.mysql.com/doc/N/e/News-4.0.0.html says so. Regards, Benjamin. PS: The syntax you used should be fine with a version supporting it On Fri, Apr 19, 2002 at 04:00:02PM -0700, [EMAIL PROTECTED] wrote: [...] I always get a syntax error when using INSERT_METHOD in creating merge tables. If I leave off the INSERT_METHOD part it works fine. I have tried this on many versions in the past and now on 3.23.49a. I am [...] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Syntax error with merge tables
Hi. On Sat, Apr 20, 2002 at 01:12:14AM -0700, [EMAIL PROTECTED] wrote: Perhaps the INSERT_METHOD functionality was introduced in 4.0 but the merge tables themselves work in later 3.23 versions. As I said, INSERT for MERGE was introduced in 4.0 (including INSERT_METHOD). Of course, MERGE tables were supported before. If this is the case the documentation needs to be updated to show which version supports what. From http://www.mysql.com/doc/C/R/CREATE_TABLE.html: -- If you want to insert data in a MERGE table, you have to specify with INSERT_METHOD into with table the row should be inserted. See section 7.2 MERGE Tables. This option was introduced in MySQL 4.0.0. -- IMHO, specific enough. Regards, Benjamin. On Sat, 20 Apr 2002, Benjamin Pflugmann wrote: AFAIK, insert for MERGE tables was introduced in version 4.0.0. Let's see... yep, http://www.mysql.com/doc/N/e/News-4.0.0.html says so. [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Syntax error with merge tables
On Sat, 20 Apr 2002 [EMAIL PROTECTED] wrote: Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: I always get a syntax error when using INSERT_METHOD in creating merge tables. If I leave off the INSERT_METHOD part it works fine. I have tried this on many versions in the past and now on 3.23.49a. I am running on Mandrake Linux 8.1 but have seen this same error on other linux distros. It is my understanding from reading the manual that the following should work but it doesn't. CREATE TABLE mytable0 ( aINTEGER NOT NULL PRIMARY KEY, bCHAR(18) NOT NULL ); CREATE TABLE mytable1 ( aINTEGER NOT NULL PRIMARY KEY, bCHAR(18) NOT NULL ); CREATE TABLE mytable2 ( aINTEGER NOT NULL PRIMARY KEY, bCHAR(18) NOT NULL ); CREATE TABLE mytable ( aINTEGER NOT NULL PRIMARY KEY, bCHAR(18) NOT NULL ) TYPE=MERGE UNION=(mytable0, mytable1, mytable2) INSERT_METHOD=LAST; Since the .MRG files are plain text could someone send me what the INSERT_METHOD part is supposed to look like so I can do it manually if needed. Thanks. Eric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Syntax error with merge tables
Hi. AFAIK, insert for MERGE tables was introduced in version 4.0.0. Let's see... yep, http://www.mysql.com/doc/N/e/News-4.0.0.html says so. Regards, Benjamin. PS: The syntax you used should be fine with a version supporting it On Fri, Apr 19, 2002 at 04:00:02PM -0700, [EMAIL PROTECTED] wrote: [...] I always get a syntax error when using INSERT_METHOD in creating merge tables. If I leave off the INSERT_METHOD part it works fine. I have tried this on many versions in the past and now on 3.23.49a. I am [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SLOW MULTI COLUMN INDEXES, ESPECIALLY FOR MERGE TABLES
Hi all, I seem to have difficulties efficiently using multiple column unique indexes. I notice major performance differences in the following example. Is this a known problem, or am I not doing the right thing? The following scenario takes for me 0.76 seconds: CREATE TABLE test(d_id INT, t_id INT, value VARCHAR(255)); CREATE UNIQUE INDEX test_idx on test(d_id, t_id); SELECT * FROM test WHERE d_id 500 AND t_id 500; This takes nearly 1 minute if I try to use MERGE tables (the index would still remain UNIQUE) The data in the test table has the following features: Both d_id and t_id values occure 2-3000 times (it't probably not good for the b-tree) It seems like first the query evaluates (d_id 500) and slowly comes up with 500,000 results. Finally (t_id 500) dramatically reduces the result set to 200. If I set PACK_KEYS = 1, and even rebuild the indexes, nothing really changes. I don't know how multiple column keys are handled, are they treated as one primary key or are they looked up separately, butif I combine the d_id and t_id keys in a new table to produce a primary key e.g. as (d_id*10,000,000+t_id) everything is fast (0.01 sec), (but wierd). Could anyone recommend a better solution in this case? Thanks for any comments! Charlie _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MERGE tables
I'm trying to do the same SELECT query in 3 tables. Those tables are
equal in structure and data but different in table type:
Table_orig - a big MyISAM table
Table_raid - RAID table (mysql compiled with --with-raid)
Table_merge - MERGE table
It all worked well, except for Table_merge: when i do the query, the
mysql daemon dies and restarts itself:
All tables have the following structure (i've removed some int fields
that were not used in the query):
CREATE TABLE `Table_00` (
`idx` varchar(25) NOT NULL default '',
`theme_id` int(10) unsigned NOT NULL default '0',
`data` date NOT NULL default '-00-00',
`tempo` time NOT NULL default '00:00:00',
`ip_orig` varchar(30) NOT NULL default '',
`real_ip` varchar(30) NOT NULL default '',
`hostname` varchar(80) NOT NULL default '',
`referer` varchar(255) default NULL,
`filtrado` enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (`idx`)
) TYPE=MyISAM PACK_KEYS=1
Data was inserted and packed:
INSERT INTO Table_00
SELECT * FROM Table_orig ORDER BY data,tempo,idx LIMIT 0,697927;
INSERT INTO Table_01
SELECT * FROM Table_orig ORDER BY data,tempo,idx LIMIT 697927,697927;
INSERT INTO Table_02
SELECT * FROM Table_orig ORDER BY data,tempo,idx LIMIT 1395854,697927;
myisampack Table_00; myisamchk -rq -a -S Table_00;
myisampack Table_01; myisamchk -rq -a -S Table_01;
myisampack Table_02; myisamchk -rq -a -S Table_02;
And the merge table is:
CREATE TABLE `Table_merge` (
`idx` varchar(25) NOT NULL default '',
`theme_id` int(10) unsigned NOT NULL default '0',
`data` date NOT NULL default '-00-00',
`tempo` time NOT NULL default '00:00:00',
`ip_orig` varchar(30) NOT NULL default '',
`real_ip` varchar(30) NOT NULL default '',
`hostname` varchar(80) NOT NULL default '',
`referer` varchar(255) default NULL,
`filtrado` enum('Yes','No') NOT NULL default 'Yes',
PRIMARY KEY (`idx`)
) TYPE=MERGE UNION=(Table_00,Table_01,Table_02)
Finally, the fatal query:
mysql SELECT tempo, ip_orig, real_ip, hostname, referer, filtrado,
- data, Theme.nome INTO OUTFILE '/tmp/teste_merge'
- FROM Table_merge,Theme
- WHERE data=20010521 AND data20011107 AND
- Theme.theme_id=Table_merge.theme_id
- ORDER BY data,tempo limit 5000;
ERROR 2013: Lost connection to MySQL server during query
mysql
Number of processes running now: 0
020121 18:06:47 mysqld restarted
This is the error from the log file:
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this
binary or one of the libraries it was linked agaist is corrupt,
improperly built,or misconfigured. This error can also be caused by
malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, something is
definitely wrong and this may fail
key_buffer_size=16773120
record_buffer=131072
sort_buffer=524280
max_used_connections=2
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 80379
K bytes of memory
Hope that's ok, if not, decrease some variables in the equation
then the stack pointer
i've already used the stack trace info but i've not concluded anything.
Am i missing something about merge tables ?!?!
Does anyone know anything that could help ?
Thanx in advance,
--
Diana Soares
[EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MERGE tables and INSERT_METHOD
Hi! Chris == Chris Cooper [EMAIL PROTECTED] writes: Chris Has anyone else tried to set the INSERT_METHOD for MERGE tables? It Chris keeps throwing a syntax error for me. Chris Following the docs verbatim (http://www.mysql.com/doc/M/E/MERGE.html), Chris here's what I get: cut Chris I checked the release notes/changelog, but there's no mention of the Chris INSERT_METHOD being broken or fixed. Chris I'm running MySQL version 3.23.42-log on RH 7.1. The INSERT_METHOD is only available in MySQL 4.0.0; I have now updated the documentation about this. Regards, Monty - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MERGE tables and INSERT_METHOD
Has anyone else tried to set the INSERT_METHOD for MERGE tables? It keeps throwing a syntax error for me. Following the docs verbatim (http://www.mysql.com/doc/M/E/MERGE.html), here's what I get: output mysql CREATE TABLE t1 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20)); Query OK, 0 rows affected (0.02 sec) mysql CREATE TABLE t2 (a INT AUTO_INCREMENT PRIMARY KEY, message CHAR(20)); Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t1 (message) VALUES (Testing),(table),(t1); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql INSERT INTO t2 (message) VALUES (Testing),(table),(t2); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql CREATE TABLE total (a INT NOT NULL, message CHAR(20), KEY(a)) TYPE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST; ERROR 1064: You have an error in your SQL syntax near 'INSERT_METHOD=LAST' at line 1 end_output I checked the release notes/changelog, but there's no mention of the INSERT_METHOD being broken or fixed. I'm running MySQL version 3.23.42-log on RH 7.1. -- coop - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MERGE TABLES
I have been playing around with merge tables. You MUST have the columns and indexes in the same order. Eg. Do SHOW INDEX FROM real_table; SHOW INDEX FROM merge_table; If the Column_name order is different you will get either no records or a bunch of null records when you select on a column that is indexed. b. Sergei Golubchik wrote: Hi! On Aug 29, Johnny Withers wrote: I'm not sure if this is a bug or if this is the way MERGE TABLES works in MySQL. It seems that if I have an INDEX in a field (id for instance), and I try to SELECT using a WHERE id=NUMBER, it will fail, even though that NUMBER is a valid id. ID is an int unsigned field with a key on it. However, when I drop the key, everything works as expected.. Is this a bug? (also if I tried to ORDER BY id, while indexed, that didn't work either) Both look like a bug. Could you create a repeatable test case ? Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Bill Adams TriQuint Semiconductor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MERGE TABLES
Well, I don't know if you can specify what order to put these in, but mine just happen to not be in the same order: mysql show index from all_records; ++-+--+-+---+--- --+--++ | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | ++-+--+-+---+--- --+--++ | 1 | catalog_key |1 | catalog | A | NULL | NULL | NULL | | 1 | id_key |1 | id | A | NULL | NULL | NULL | ++-+--+-+---+--- --+--++ 2 rows in set (0.00 sec) mysql show index from data1[,2,3,4,5]; ++-+--+-+---+--- --+--++ | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | ++-+--+-+---+--- --+--++ | 0 | PRIMARY |1 | id | A | 93 | NULL | NULL | | 1 | catalog_key |1 | catalog | A | NULL | NULL | NULL | ++-+--+-+---+--- --+--++ As you can see, the data1,2,3,4,5 tables that make up the 'all_records' table have a primary key defined on ID. I was unable to define the ID field in my merged table as primary because it has to be non_unique. This may have caused the problem. Maybe making the id columns in data1,2,3,4,5 a normal key instead of PIMARY will solve the problem. (Filter : MySQL,database,SQL,etc) - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Adams, Bill TQO Sent: Thursday, August 30, 2001 1:11 PM To: Sergei Golubchik Cc: Johnny Withers; Mysql-List Subject: Re: MERGE TABLES I have been playing around with merge tables. You MUST have the columns and indexes in the same order. Eg. Do SHOW INDEX FROM real_table; SHOW INDEX FROM merge_table; If the Column_name order is different you will get either no records or a bunch of null records when you select on a column that is indexed. b. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MERGE TABLES
Monty helped me with this, and this is my understanding. Regardless of what order you generate the keys, MySQL puts the primary key first, then the UNIQUE keys, then the others. When you add a new UNIQUE key, it puts it after the primary key but before the other existing UNIQUE keys. In your case you have only non-UNIQUE keys in the merge table so you just need to add them in the same order as is in the dataN tables and MySQL will maintain the order: ALTER TABLE all_records DROP INDEX id_kex, DROP INDEX catalog_key; ALTER TABLE all_records ADD INDEX id_key (id_key), ADD INDEX catalog_key (catalog_key); --Bill Johnny Withers wrote: Well, I don't know if you can specify what order to put these in, but mine just happen to not be in the same order: mysql show index from all_records; ++-+--- --+-+---+--- --+--++ | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | ++-+- +-+---+--- --+--++ | 1 | catalog_key |1 | catalog | A | NULL | NULL | NULL | | 1 | id_key |1 | id | A | NULL | NULL | NULL | ++ +--+-+---+--- --+--++ 2 rows in set (0.00 sec) mysql show index from data1[,2,3,4,5]; ++-+--- --+-+---+--- --+--++ | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | ++-+- +-+---+--- --+--++ | 0 | PRIMARY |1 | id | A | 93 | NULL | NULL | | 1 | catalog_key |1 | catalog | A | NULL | NULL | NULL | ++ +--+-+---+--- --+--++ As you can see, the data1,2,3,4,5 tables that make up the 'all_records' table have a primary key defined on ID. I was unable to define the ID field in my merged table as primary because it has to be non_unique. This may have caused the problem. Maybe making the id columns in data1,2,3,4,5 a normal key instead of PIMARY will solve the problem. (Filter : MySQL,database,SQL,etc) - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Adams, Bill TQO Sent: Thursday, August 30, 2001 1:11 PM To: Sergei Golubchik Cc: Johnny Withers; Mysql-List Subject: Re: MERGE TABLES I have been playing around with merge tables. You MUST have the columns and indexes in the same order. Eg. Do SHOW INDEX FROM real_table; SHOW INDEX FROM merge_table; If the Column_name order is different you will get either no records or a bunch of null records when you select on a column that is indexed. b. -- Bill Adams TriQuint Semiconductor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MERGE TABLES
I'm not sure if this is a bug or if this is the way MERGE TABLES works in MySQL. It seems that if I have an INDEX in a field (id for instance), and I try to SELECT using a WHERE id=NUMBER, it will fail, even though that NUMBER is a valid id. ID is an int unsigned field with a key on it. However, when I drop the key, everything works as expected.. Is this a bug? (also if I tried to ORDER BY id, while indexed, that didn't work either) mysql desc all_records; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(10) unsigned | | | 0 | | | field1| varchar(48) | YES | | NULL| | | field2| varchar(6) | YES | | NULL| | | field3| varchar(25) | YES | | NULL| | | field4| varchar(10) | YES | | NULL| | | field5| int(10) unsigned | | | 0 | | | field6| int(10) unsigned | | | 0 | | | pages | int(10) unsigned | | | 0 | | | file_name | varchar(10) | YES | | NULL| | | catalog | int(10) unsigned | | | 0 | | | keywords | varchar(255) | YES | | NULL| | +---+--+--+-+-+---+ Here is some output from mysql client (before dropping keys): mysql select id from all_records where id=1; ++ | id | ++ | 13 | ++ 1 row in set (0.00 sec) mysql select * from all_records where id=12; Empty set (0.01 sec) however... mysql select id from all_records; +-+ | id | +-+ | .. | | 12 | | .. | - 336 rows in set (0.01 sec) ID 12 does exist in all_records.. - mysql alter table all_records drop key id_key; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 After dropping keys: mysql select * from all_records where id=240; +-+ | id | +-+ | 240 | +-+ 1 row in set (0.00 sec) mysql select * from all_records where id=158; +-+ | id | +-+ | 158 | +-+ 1 row in set (0.00 sec) everything works as expected... System Information: (mysql installed from RPM) mysql Ver 11.15 Distrib 3.23.39, for pc-linux-gnu (i686) RedHat 7.0 Linux x.xx.xxx 2.4.8 #3 SMP Tue Aug 14 09:47:47 CDT 2001 i686 unknown - Johnny Withers [EMAIL PROTECTED] p. 601.853.0211 c. 601.209.4985 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MERGE TABLES
Hi! On Aug 29, Johnny Withers wrote: I'm not sure if this is a bug or if this is the way MERGE TABLES works in MySQL. It seems that if I have an INDEX in a field (id for instance), and I try to SELECT using a WHERE id=NUMBER, it will fail, even though that NUMBER is a valid id. ID is an int unsigned field with a key on it. However, when I drop the key, everything works as expected.. Is this a bug? (also if I tried to ORDER BY id, while indexed, that didn't work either) Both look like a bug. Could you create a repeatable test case ? Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem: MERGE tables for different databases
HEllo! Is it possible to create a merge table for tables from different databases. I wrote this script for database collection: CREATE TABLE dict ( url_id int(11) DEFAULT '0' NOT NULL, word varchar(32) DEFAULT '' NOT NULL, intag int(11) DEFAULT '0' NOT NULL, KEY url_id (url_id), KEY word_url (word) ) TYPE=MERGE UNION=(www.url,dbms.url); MySQL took it without any errors. However, when I try to access MERGE table in collection database, it fails: -- /usr/home/bar mysql collection Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 210 to server version: 3.23.29a-gamma-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer mysql select * from dict; ERROR 1017: Can't find file: 'dict.MRG' (errno: 2) mysql --- I think this may helps in solving: In /usr/local/mysql/var/collection/dict.MRG I can see this: url url I supposed it should be www.url dbms.url Is that a bug? I suggest this to be fixed in future release. I use 3.23.29a-gamma. Please make a copy to me when replying... Thanks! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MERGE tables and UNION joins
I'm having trouble getting MySQL's union join to work when merging tables
from different databases. MySQL complains with ERROR 1017: Can't find file:
'total.MRG' (errno: 2) even though this file exists.
Here are the commands I use to produce the error. Please let me know how I
can get union joins working. Thanks!
create database v1;
use v1;
create table v1 (type varchar(10), price double);
insert into v1 (type, price) values ('cheap','10');
create database v2;
use v2;
create table v2 (type varchar(10), price double);
insert into v2 (type, price) values ('pricy','50');
create database total;
use total;
create table total (type varchar(10), price double)
type=merge union=(v1.v1, v2.v2);
Up to this point MySQL is happy. Then, when I say:
select * from total;
MySQL responds with:
ERROR 1017: Can't find file: 'total.MRG' (errno: 2)
The file exists. I have permissions to use it. Again, how can I do union
joins across multiple databases?
Thanks
David Newman
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with symbolic links and merge tables on NT4
I am testing mysql for merge tables and symbolic links on NT4. But it seems not working. First I tried doing exactly what is there in the manual, then searched mailing list and added use-symbolic-link in my.cnf file in C:\ directory. Mysql recognise that there is a database but can't read contents. On merging two tables with same parameters and contraints as specified in manual gives me null value when querying from merge table. BTW I am using mysql client only no PHP perl. Any help? Cheers Tito __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MERGE Tables
Hi, Ok, the first bug (incorrect COUNT, etc. for MERGE and individual tables) was fixed some time ago. Though, it is possible that you found another bug, the probability is low. I thought I might be encountering actual bugs in the code in my 3.23.32 version. I'd taken a look at the changelog to see if any fixes seemed to apply to this situation, but it wasn't very clear. As for the second - ALTER TABLE ... UNION = () is absolutely legal syntax and MySQL does support it (if you have source distribution, look at mysql-test/t/merge.test). So, let's upgrade now, and then we'll see. I had no doubt it was supported - so I knew MySQL must've been lying when it said it didn't support that operation... :) I assume this has been fixed also in later versions, yes? So, it looks like I need to upgrade my server. I've just been looking at the changelog again and I notice there are some entries for 3.23.38 - is there a new release imminent? Should I wait for this, or just upgrade to 3.23.37 now? Regards, Basil Hussain ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MERGE Tables
Hi! On May 01, Basil Hussain wrote: Hi, Ok, the first bug (incorrect COUNT, etc. for MERGE and individual tables) was fixed some time ago. Though, it is possible that you found another bug, the probability is low. I thought I might be encountering actual bugs in the code in my 3.23.32 version. I'd taken a look at the changelog to see if any fixes seemed to apply to this situation, but it wasn't very clear. So, it looks like I need to upgrade my server. I've just been looking at the changelog again and I notice there are some entries for 3.23.38 - is there a new release imminent? Should I wait for this, or just upgrade to 3.23.37 now? The bug was fixed in 3.23.35. Sorry for incomplete Changelog. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MERGE Tables
Hi! On Apr 30, Matthew Shaw wrote: Hi, The merge table created is called asic_cache. It has exactly the same structure as above except there are no keys what so ever. My understanding is that there is no need for keys on the merge table as the table handler uses the keys on each individual table when doing the lookups. You're only partially right. Table handler for MERGE can use keys of underlying tables for key lookups, but in fact it never will, as SQL optimizer will never ask to. Specifying keys for MERGE table is for SQL optimizer to able to chose the best way to execute your SELECTs. No real keys are created, as MERGE handler ignores requests for key creation - but SQL optimizer doesn't know about MERGE handler specific - and it shouldn't. So, please, specify the same keys for MERGE table, you specified for underlying tables. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MERGE Tables
Hi, Table handler for MERGE can use keys of underlying tables for key lookups, but in fact it never will, as SQL optimizer will never ask to. Specifying keys for MERGE table is for SQL optimizer to able to chose the best way to execute your SELECTs. No real keys are created, as MERGE handler ignores requests for key creation - but SQL optimizer doesn't know about MERGE handler specific - and it shouldn't. So, please, specify the same keys for MERGE table, you specified for underlying tables. I have been wondering about the use of indexes with MERGE tables. I came to the same conclusion as what you have just explained, but by trial and error. I would like to say that I think the section in the manual on MERGE tables could do with some improvement! Anyway, while we're on the subject, I seem to be having some trouble with my indexes on MERGE tables. I posted a message a while ago, but no-one seems to have noticed it... Basically, if I create my MERGE table with indexes on the same columns as the underlying tables, I then get strange results from some queries. Below I quote part of my original message that illustrates the problem. mysql select count(*) from eventlog_36; +--+ | count(*) | +--+ | 389959 | +--+ 1 row in set (0.00 sec) mysql select count(*) from eventlog_all where bannerid = 36; +--+ | count(*) | +--+ |1 | +--+ 1 row in set (0.01 sec) (The table 'eventlog_36' is one of the tables mapped to 'eventlog_all' which holds only records with a 'bannerid' column value of 36.) They don't match, so it's clearly not correct! It seems as though it's not looking at the indexes of the mapped tables correctly, although I have no idea why! Also, I get the following error when I try to alter the mapping of the MERGE table: mysql ALTER TABLE eventlog_all UNION=(eventlog_11, ..lots of tables..., eventlog_88); ERROR 1031: Table handler for '#sql-1f1_ea6d9' doesn't have this option My MERGE table seems to be completely screwed! Could you explain what's going on here? Is it because I'm running only version 3.23.32 of MySQL? Do I need to upgrade? Any assistance would be appreciated, as no-one else seems to be able to help. Regards, Basil Hussain ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MERGE Tables
Hi! On Apr 30, Basil Hussain wrote: Hi, Anyway, while we're on the subject, I seem to be having some trouble with my indexes on MERGE tables. I posted a message a while ago, but no-one seems to have noticed it... Basically, if I create my MERGE table with indexes on the same columns as the underlying tables, I then get strange results from some queries. Below I quote part of my original message that illustrates the problem. mysql select count(*) from eventlog_36; +--+ | count(*) | +--+ | 389959 | +--+ 1 row in set (0.00 sec) mysql select count(*) from eventlog_all where bannerid = 36; +--+ | count(*) | +--+ |1 | +--+ 1 row in set (0.01 sec) (The table 'eventlog_36' is one of the tables mapped to 'eventlog_all' which holds only records with a 'bannerid' column value of 36.) They don't match, so it's clearly not correct! It seems as though it's not looking at the indexes of the mapped tables correctly, although I have no idea why! Also, I get the following error when I try to alter the mapping of the MERGE table: mysql ALTER TABLE eventlog_all UNION=(eventlog_11, ..lots of tables..., eventlog_88); ERROR 1031: Table handler for '#sql-1f1_ea6d9' doesn't have this option My MERGE table seems to be completely screwed! Could you explain what's going on here? Is it because I'm running only version 3.23.32 of MySQL? Do I need to upgrade? Any assistance would be appreciated, as no-one else seems to be able to help. Regards, Basil Hussain ([EMAIL PROTECTED]) Ok, the first bug (incorrect COUNT, etc. for MERGE and individual tables) was fixed some time ago. Though, it is possible that you found another bug, the probability is low. As for the second - ALTER TABLE ... UNION = () is absolutely legal syntax and MySQL does support it (if you have source distribution, look at mysql-test/t/merge.test). So, let's upgrade now, and then we'll see. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MERGE Tables
Hi, I have already posted this message but I got no reply I am really stuck on this topic - if anybody knows anything help would be greatly appreciated. I have only just joined this list today any help would be greatly appreciated. I have implemented the new MERGE table feature as part of my new install on version 3.23. I am finding a few small problems with usage of this functionality as follows. There are 4 identical tables namely asic_cache1, asic_cache2, asic_cache3, asic_cache4. Each table is structured identically as follows: field_index int(11)PRI DEFAULT 0 subject varchar(16)PRI search_type varchar(8) PRI segmentchar(3) grp_code int(11) DEFAULT 0 field_nr int(11) DEFAULT 0 value text DEFAULT NULL segment_header int(1) DEFAULT 0 record_nr int(11) DEFAULT 0 There is one index on each table and that is the primary key (subject, search_type, field_index). Each table contains approximately 20 000 000 rows. Hence my reason for using merge :) The merge table created is called asic_cache. It has exactly the same structure as above except there are no keys what so ever. My understanding is that there is no need for keys on the merge table as the table handler uses the keys on each individual table when doing the lookups. The keys on each table are of type ref. Here's the interesting part: I performed the following query and got a result in 0.2 secs approx. Qry = select * from asic_cache where subject = '10920411' and search_type = 'CE C' limit 10. When I run the following query without the limit clause mysql appears to go into a tail spin and the process runs on my linux machine for over 5 minutes! Qry = select * from asic_cache where subject = '10920411' and search_type = 'CE C'. I am guessing that it is doing a full table scan on each of the four table but I'm not sure and if so why? Thirdly, the following query has the same effect as above and blows the time of the query over 5 mintues. Qry = select * from asic_cache where subject = '10920411' and search_type = 'CE C' order by subject, search_type, field_index. Looking at the mysql doco on merge tables on one hand it says there is no need for indexes on the merge table itself but in the example in the doco it uses a key? This has got me really confused and I'm not sure if this is contributing to the above issues. Has anyone got any ideas on the matter?? Thanks in advance, Matthew. -- Matthew Shaw IT Specialist / Project Leader Australian Business Research (ABR) Ph. (07) 3837 1391 Mob. 0402 894 797 Email. [EMAIL PROTECTED]
MERGE Tables
Hi, I have only just joined this list today any help would be greatly appreciated. I have implemented the new MERGE table feature as part of my new install on version 3.23. I am finding a few small problems with usage of this functionality as follows. There are 4 identical tables namely asic_cache1, asic_cache2, asic_cache3, asic_cache4. Each table is structured identically as follows: field_index int(11)PRI DEFAULT 0 subject varchar(16)PRI search_type varchar(8) PRI segmentchar(3) grp_code int(11) DEFAULT 0 field_nr int(11) DEFAULT 0 value text DEFAULT NULL segment_header int(1) DEFAULT 0 record_nr int(11) DEFAULT 0 There is one index on each table and that is the primary key (subject, search_type, field_index). Each table contains approximately 20 000 000 rows. Hence my reason for using merge :) The merge table created is called asic_cache. It has exactly the same structure as above except there are no keys what so ever. My understanding is that there is no need for keys on the merge table as the table handler uses the keys on each individual table when doing the lookups. The keys on each table are of type ref. Here's the interesting part: I performed the following query and got a result in 0.2 secs approx. Qry = select * from asic_cache where subject = '10920411' and search_type = 'CE C' limit 10. When I run the following query without the limit clause mysql appears to go into a tail spin and the process runs on my linux machine for over 5 minutes! Qry = select * from asic_cache where subject = '10920411' and search_type = 'CE C'. I am guessing that it is doing a full table scan on each of the four table but I'm not sure and if so why? Thirdly, the following query has the same effect as above and blows the time of the query over 5 mintues. Qry = select * from asic_cache where subject = '10920411' and search_type = 'CE C' order by subject, search_type, field_index. Looking at the mysql doco on merge tables on one hand it says there is no need for indexes on the merge table itself but in the example in the doco it uses a key? This has got me really confused and I'm not sure if this is contributing to the above issues. Has anyone got any ideas on the matter?? Thanks in advance, Matthew. -- Matthew Shaw IT Specialist / Project Leader Australian Business Research (ABR) Ph. (07) 3837 1391 Mob. 0402 894 797 Email. [EMAIL PROTECTED]
Merge Tables Oversight
Hi all, I think I may have come up against a slight niggling omission concerning Merge tables. How does one find out what physical tables are mapped, other than by looking at the contents of the .MRG file? Surely this information should be displayed either when you do SHOW TABLE STATUS or by some other means? For example, I am currently using a Merge table to map 68 stats logging tables into one giant table (3.5 million rows!) so that overall averages, etc. can be calculated. As the mapping for this Merge table changes often, it would be handy for me to be able to check the current state of the mapping without leaving the MySQL client. I think a list of the mapped tables (or perhaps rather the UNION() statement) could be shown in the Create_options field of the SHOW TABLE STATUS output, as I would assume this is the appropriate place, yes? Maybe someone can tell me if there is any particular reason why this can't be shown, or is it just an oversight? Regards, Basil Hussain ([EMAIL PROTECTED]) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
