Reporting tools for summary data
Hi James, I saw your email about are primary keys always essential ?. And that your app is essentially creating summary reports from large amounts of summary data. May I ask what reporting tool you use for summary data ? TIA, Cor Vegelin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help Connecting
Mark Phillips wrote: David, This is what I got: [EMAIL PROTECTED]:~$ aliases bash: aliases: command not found Your shell is bash, so the correct command is `alias`. [EMAIL PROTECTED]:~$ which mysql /usr/bin/mysql Since you are using bash, it's a better idea to use `type` instead of `which`. On some systems (Solaris 7, for example), `which` can give bogus results in bash. So, try type mysql [EMAIL PROTECTED]:~$ $PATH bash: /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games: No such file or directory [EMAIL PROTECTED]:~$ Something is wrong with mark's PATH. See the error at the end? I don't have a command 'aliases', but the other tests seem to say all I have is mysql running as mysql. When I am logged in as 'emily' I get: [EMAIL PROTECTED]:/home/mark$ which mysql /usr/bin/mysql [EMAIL PROTECTED]:/home/mark$ echo $PATH /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games [EMAIL PROTECTED]:/home/mark$ Any other thoughts? The error message from mysql when I try to log in is strange. Why all the spaces? mysql: unknown option '--user mark' I don't believe that error came from mysql. Indeed, mysql doesn't care which unix user runs it, it only cares which mysql user you say you are. Because it works as expected when Emily runs it, I don't believe mysql is the problem. I believe David is correct that there is something in mark's environment that is causing the problem. Check the output of `alias` and `type mysql`, and check mark's .my.cnf file, if he has one. Thanks! Mark Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reporting tools for summary data
At 2:30 pm + 22/12/05, C.R.Vegelin wrote: Hi James, I saw your email about are primary keys always essential ?. And that your app is essentially creating summary reports from large amounts of summary data. May I ask what reporting tool you use for summary data ? My client sells (well, they will in ten days time) various trade-flow reports from their web site, so it's all web-based. I use Lasso http://www.omnipilot.com/ as my middleware. Regards, James Harvard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
*very* long timeout for 'slave stop io_thread'
Hello. I have mysql-4.1.14 and 4.1.15 on PLD's. When the Slave_IO_State is in 'Connecting to Master' state because of the unreachable master host (timeouting) the 'slave stop' or 'slave stop io_thread' tooks minimum 2 minutes. fe.: mysql stop slave io_thread; Query OK, 0 rows affected (2 min 52.11 sec) the processlist when waiting for death of thread: *** 1. row *** Id: 6 User: mysql Host: 192.168.100.55:1148 db: NULL Command: Query Time: 9 State: Killing slave Info: slave stop *** 3. row *** Id: 19 User: system user Host: db: NULL Command: Connect Time: 16 State: Connecting to master Info: NULL *** 4. row *** Id: 20 User: system user Host: db: NULL Command: Connect Time: 16 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL If I do remember correctly then mysql versions 4.0.x was waiting about 30 secs to end the slave's threads. I would be very grateful if sb will suggest something to cure this situation. Best Regards, Irek Slonina -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help Connecting
Here are the results of alias and type [EMAIL PROTECTED]:~$ alias alias ls='ls --color=auto' [EMAIL PROTECTED]:~$ type mysql mysql is /usr/bin/mysql And for the emily account: [EMAIL PROTECTED]:/home/mark$ alias alias ls='ls --color=auto' [EMAIL PROTECTED]:/home/mark$ type mysql mysql is /usr/bin/mysql The error from my PATH is from how I used it. I left out the 'echo' command. I get the same results in the emily account [EMAIL PROTECTED]:/home/mark$ $PATH bash: /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games: No such file or directory [EMAIL PROTECTED]:/home/mark$ echo $PATH /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games However, Michael hit the nail on the head! I have a .my.cnf file that was causing the problem. I had the line usermark which should be user=mark That also explains the funny error message I was getting. Thanks for all your help, everything is now working!! Mark On Thursday 22 December 2005 06:28 am, Michael Stassen wrote: Mark Phillips wrote: David, This is what I got: [EMAIL PROTECTED]:~$ aliases bash: aliases: command not found Your shell is bash, so the correct command is `alias`. [EMAIL PROTECTED]:~$ which mysql /usr/bin/mysql Since you are using bash, it's a better idea to use `type` instead of `which`. On some systems (Solaris 7, for example), `which` can give bogus results in bash. So, try type mysql [EMAIL PROTECTED]:~$ $PATH bash: /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games: No such file or directory [EMAIL PROTECTED]:~$ Something is wrong with mark's PATH. See the error at the end? I don't have a command 'aliases', but the other tests seem to say all I have is mysql running as mysql. When I am logged in as 'emily' I get: [EMAIL PROTECTED]:/home/mark$ which mysql /usr/bin/mysql [EMAIL PROTECTED]:/home/mark$ echo $PATH /usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games [EMAIL PROTECTED]:/home/mark$ Any other thoughts? The error message from mysql when I try to log in is strange. Why all the spaces? mysql: unknown option '--user mark' I don't believe that error came from mysql. Indeed, mysql doesn't care which unix user runs it, it only cares which mysql user you say you are. Because it works as expected when Emily runs it, I don't believe mysql is the problem. I believe David is correct that there is something in mark's environment that is causing the problem. Check the output of `alias` and `type mysql`, and check mark's .my.cnf file, if he has one. Thanks! Mark Michael -- Mark Phillips [EMAIL PROTECTED] 602 524-0376 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INSTALLING MYSQL 5.0 ON LINUX DEBIAN 2.8
All I am trying to build the mysql 5.0 from the source When I run the configure scripts it error out saying it can not find termcap data base also, can not find tegenent in any library I am running under debian linux 2.8 Does any one have nay idea on what I should do John
Scheduling Backup
I am trying to schedule automatic MySQL Backups. I have used MySQL Administrator to do so, but when it starts to execute, I get an error in the Event Viewer in Windows: Connection -bpBackupData cannot be found. Here is what it placed in the Scheduled Tasks: C:\Program Files\MySQL\MySQL Administrator 1.0\MySQLAdministrator.exe -UDC:\Documents and Settings\Jesse Castleberry\Application Data\MySQL\ -c -bpBackupData -btC:\junk\MySQLBackup -bxBackupData Thinking that there needed to be a space between -bp and BackupData, I did that, but ended up with the same error, except it indicated -bp BackupData instead. So, what am I doing wrong here? Or, is there a better way to do this? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INSTALLING MYSQL 5.0 ON LINUX DEBIAN 2.8
There is no such thing as Debian 2.8--see http://www.us.debian.org/releases/. -Original Message- From: John Galatti [mailto:[EMAIL PROTECTED] Sent: Thursday, December 22, 2005 8:53 AM To: mysqlList Subject: INSTALLING MYSQL 5.0 ON LINUX DEBIAN 2.8 All I am trying to build the mysql 5.0 from the source When I run the configure scripts it error out saying it can not find termcap data base also, can not find tegenent in any library I am running under debian linux 2.8 Does any one have nay idea on what I should do John INFORMATION IN THIS MESSAGE, INCLUDING ANY ATTACHMENTS, IS INTENDED FOR THE PERSONAL AND CONFIDENTIAL USE OF THE INTENDED RECIPIENT(S) NAMED ABOVE. If you are not an intended recipient of this message, or an agent responsible for delivering it to an intended recipient, you are hereby notified that you have received this message in error, and that any review, dissemination, distribution, or copying of this message is strictly prohibited. If you received this message in error, please notify the sender immediately, delete the message, and return any hard copy print-outs. This message has been scanned for viruses by McAfee's Groupshield. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need help
You will experience the same problem with old-client vs. new-server authentication as you did when you first set up your user accounts for 4.1 but other than that , it should be compatible. http://dev.mysql.com/doc/refman/5.0/en/old-client.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 12/21/2005 10:18:24 AM: Hi, I am presently using MySQL ODBC driver version 3.51.06 with MySQL 4.1. Now I need to upgrade the MySQL to latest i.e 5.0.17. Can I still use the old ODBC driver (3.51.06) with the latest MySQL? Thanks in advance. Regards, Abdul Rasheed. The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com
Foreign Key Help
I am trying to add referential integrity to my database. I'm trying to add a foreign key reference to one of my tables, but I'm getting an error when I try to do so. Here's what I'm executing: ALTER TABLE Campers ADD CONSTRAINT FK_Activities FOREIGN KEY FK_Activities (ID) REFERENCES ActivitySelections (PersonID) ON DELETE CASCADE ON UPDATE CASCADE; When I execute this in the command line utility, I get the error, Can't create table '.\fccamp\#sql-33c_30.frm' (errno: 150) Does anyone know what this means, and how I can fix it? I'm using MySQL 5.?? on a Windows XP Pro (development machine). Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Help
Jesse [EMAIL PROTECTED] wrote on 12/22/2005 02:51:21 PM: I am trying to add referential integrity to my database. I'm trying to add a foreign key reference to one of my tables, but I'm getting an error when I try to do so. Here's what I'm executing: ALTER TABLE Campers ADD CONSTRAINT FK_Activities FOREIGN KEY FK_Activities (ID) REFERENCES ActivitySelections (PersonID) ON DELETE CASCADE ON UPDATE CASCADE; When I execute this in the command line utility, I get the error, Can't create table '.\fccamp\#sql-33c_30.frm' (errno: 150) Does anyone know what this means, and how I can fix it? I'm using MySQL 5.?? on a Windows XP Pro (development machine). Thanks, Jesse the Simplified Rules for creating foreign keys: a) both tables must be InnoDB b) all columns involved (in both parent and child tables) must be the leftmost portion of at least one index. It's preferable if the parent column(s) is/are part of a PK or UNIQUE index. c) there can be no data already in the child table that would otherwise violate the key you are trying to create. To see more details about this error (or any other problem going on in InnoDB), run the command SHOW InnoDb STATUS. There will be about 40 or 50 lines of output so if it scrolls off of the screen and your screen buffer isn't big enough, you will not be able to see the details of the error because what you want to look at is near the top of the report. Resize your buffer and try again. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Foreign Key Help
One other restriction that I found was that both columns must be of the same type. That was my problem. I had one column set to INTEGER and the other set to INT(10). I set the INT(10) to INTEGER, and it worked fine. Thanks, Jesse - Original Message - From: [EMAIL PROTECTED] To: Jesse Cc: mysql@lists.mysql.com Sent: Thursday, December 22, 2005 3:00 PM Subject: Re: Foreign Key Help Jesse [EMAIL PROTECTED] wrote on 12/22/2005 02:51:21 PM: I am trying to add referential integrity to my database. I'm trying to add a foreign key reference to one of my tables, but I'm getting an error when I try to do so. Here's what I'm executing: ALTER TABLE Campers ADD CONSTRAINT FK_Activities FOREIGN KEY FK_Activities (ID) REFERENCES ActivitySelections (PersonID) ON DELETE CASCADE ON UPDATE CASCADE; When I execute this in the command line utility, I get the error, Can't create table '.\fccamp\#sql-33c_30.frm' (errno: 150) Does anyone know what this means, and how I can fix it? I'm using MySQL 5.?? on a Windows XP Pro (development machine). Thanks, Jesse the Simplified Rules for creating foreign keys: a) both tables must be InnoDB b) all columns involved (in both parent and child tables) must be the leftmost portion of at least one index. It's preferable if the parent column(s) is/are part of a PK or UNIQUE index. c) there can be no data already in the child table that would otherwise violate the key you are trying to create. To see more details about this error (or any other problem going on in InnoDB), run the command SHOW InnoDb STATUS. There will be about 40 or 50 lines of output so if it scrolls off of the screen and your screen buffer isn't big enough, you will not be able to see the details of the error because what you want to look at is near the top of the report. Resize your buffer and try again. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Scheduling Backup
Hello. Check that you're using the latest version of MySQL Administrator. This is a bug with similar error message, though it is related to Query Browser it might be helpful: http://bugs.mysql.com/bug.php?id=4423 Jesse wrote: I am trying to schedule automatic MySQL Backups. I have used MySQL Administrator to do so, but when it starts to execute, I get an error in the Event Viewer in Windows: Connection -bpBackupData cannot be found. Here is what it placed in the Scheduled Tasks: C:\Program Files\MySQL\MySQL Administrator 1.0\MySQLAdministrator.exe -UDC:\Documents and Settings\Jesse Castleberry\Application Data\MySQL\ -c -bpBackupData -btC:\junk\MySQLBackup -bxBackupData Thinking that there needed to be a space between -bp and BackupData, I did that, but ended up with the same error, except it indicated -bp BackupData instead. So, what am I doing wrong here? Or, is there a better way to do this? Thanks, Jesse -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: INSTALLING MYSQL 5.0 ON LINUX DEBIAN 2.8
Hello. Are you sure that configure is saying that it is unable to find 'termcap data base'? I haven't seen this message inside the configure script. May be it is looking for termcap library? In this case, you should install some termcap development package for Debian (I not a Debian user, so I don't know exacty which package you need). BTW, why don't you want to use official binaries? All I am trying to build the mysql 5.0 from the source When I run the configure scripts it error out saying it can not find termcap data base also, can not find tegenent in any library I am running under debian linux 2.8 Does any one have nay idea on what I should do John Galatti wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: mysql 4.1.16 shell atl charset input problem
Privet! MySQL 4.1 and 5.0 should work fine with koi8-r and may other character sets. Please, read : http://dev.mysql.com/doc/refman/5.0/en/charset.html in case you haven't done this yet. And if you don't solve you problems, send to the list output of the following statements: show variables like '%char%'; show character set; Kirill Sapelkin wrote: Hello, The mysql 4.1.16 shell does not accept alternate character set (koi8r - russian) keyboard input. It accepts koi8r input fine in non interactive mode as: mysql our_database our_command.sql and it displays both english and koi8r fine on screen. It was comiled: configure --with-charset=koi8r mysq 5.0.16 acts the same. 4.0 and earlier always worked fine. Is there a way to disable the utf-i in 4.1.16? Thanks for any help or direction. Kirill -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: *very* long timeout for 'slave stop io_thread'
Hello. I was unable to reproduce this behavior on official binaries of 4.1.16 and 5.0.17 versions. I'm not sure about possible reasons for you of such a delay. Check if the problem still exists on binaries of the latest releases from mysql.com. Play with different versions of glibc. Find out if decreasing of different %timeout% variables affects the length of the delay before dieing of Slave_IO thread. Irek S³onina wrote: Hello. I have mysql-4.1.14 and 4.1.15 on PLD's. When the Slave_IO_State is in 'Connecting to Master' state because of the unreachable master host (timeouting) the 'slave stop' or 'slave stop io_thread' tooks minimum 2 minutes. fe.: mysql stop slave io_thread; Query OK, 0 rows affected (2 min 52.11 sec) the processlist when waiting for death of thread: *** 1. row *** Id: 6 User: mysql Host: 192.168.100.55:1148 db: NULL Command: Query Time: 9 State: Killing slave Info: slave stop *** 3. row *** Id: 19 User: system user Host: db: NULL Command: Connect Time: 16 State: Connecting to master Info: NULL *** 4. row *** Id: 20 User: system user Host: db: NULL Command: Connect Time: 16 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL If I do remember correctly then mysql versions 4.0.x was waiting about 30 secs to end the slave's threads. I would be very grateful if sb will suggest something to cure this situation. Best Regards, Irek Slonina -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Re: need help
Hello. In my opinion, if it works in general with 5.0. MySQL tries to keep backward compatibility for its products as much as possible. So it should work, however, not all features of 5.0 could be available. MyODBC 3.51.12 is suitable for use with any MySQL version including MySQL 4.1 or 5.0 (from Announcement message). Upgrade if it is possible in your production environment. I am presently using MySQL ODBC driver version 3.51.06 with MySQL 4.1. Now I need to upgrade the MySQL to latest i.e 5.0.17. Can I still use the old ODBC driver (3.51.06) with the latest MySQL? [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Insert fails with Foreign Keys
I'm experimenting with Foreign Keys to maintain referential integrity. I have just added a Foreign Key to one of my tables, but now when I attempt to add a new record, I get this error: #23000Cannot add or update a child row: a foreign key constraint fails (`fccamp/families`, CONSTRAINT `FK_Campers` FOREIGN KEY (`ID`) REFERENCES `campers` (`FamilyID`) ON DELETE CASCADE ON UPDATE CASCADE) Did I get something backwards here? Does a child record have to exist before I can add a parent record? I thought that a Foreign key referred to a child record? In other words, the Foreign component is the child table. Is that not right? If it is, how do I add records? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Insert fails with Foreign Keys
Jesse [EMAIL PROTECTED] wrote on 12/22/2005 05:09:12 PM: I'm experimenting with Foreign Keys to maintain referential integrity. I have just added a Foreign Key to one of my tables, but now when I attempt to add a new record, I get this error: #23000Cannot add or update a child row: a foreign key constraint fails (`fccamp/families`, CONSTRAINT `FK_Campers` FOREIGN KEY (`ID`) REFERENCES `campers` (`FamilyID`) ON DELETE CASCADE ON UPDATE CASCADE) Did I get something backwards here? Does a child record have to exist before I can add a parent record? I thought that a Foreign key referred to a child record? In other words, the Foreign component is the child table. Is that not right? If it is, how do I add records? Thanks, Jesse You have to have the parent record first. The FK ensures that the value you add to the child table is one of the valid values listed in the parent table. So with the key you defined, you would have to have a record in the `campers` table before you could create a `family` record. I think you defined it backwards because the constraint should be defined on the child table, not the parent table. I think it should have been defined on the `camper` table like this ...FOREIGN KEY (`familyID`) references family(`id`)... Hey, I did it too (once a lng time ago). Don't feel bad. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Do I need to tweak my server variables for this SELECT statement?
Now that I know what's causing the slow queries, what can I do to fix it? The only thing I can suggest is breaking up the query into two parts - the first part to retrieve just the product codes and salesrank, and sort and limit that.. save in a temp table or use application code to retrieve and print the rest of the product info. Sorting 300,000+ records in that huge result set is going to take some time (although it shouldn't take 10 minutes). -Hank -- 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]
