Re: MySQL Performance Analysis tools
Bernd Jagla wrote: sar will give you some basic information about what happens on the system... (see e.g.: http://linux.die.net/man/1/sar)... Munin (http://munin.projects.linpro.no/) will generate graphs and stats over time for system usage (cpu, mem load, disk usage etc) and includes good support for mysql graphing throughput, queries, threads and slow queries. Example here: http://munin.ping.uio.no/ping.uio.no/dahl.ping.uio.no.html mark | MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? -Original Message- |From: thomas Armstrong [mailto:[EMAIL PROTECTED] |Sent: Monday, November 19, 2007 6:42 AM |To: mysql@lists.mysql.com |Subject: MySQL Performance Analysis tools | |Hi. | |Using MySQL on Linux, I'd like to analyze the performance and know how |resources (memory, threads) are used during a period of time. | |Do you know any tool to carry it out? Thank you very much. | |-- |MySQL General Mailing List |For list archives: http://lists.mysql.com/mysql |To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: misconfigured mysql ?
Hi Vanish, redhat by default installs a old version of mysql. execute this command and check if two version 's of mysql are there rpm -qpl MySQL-server-VERSION On 11/20/07, vc [EMAIL PROTECTED] wrote: i install mysql 5.0.22 with php 5.2 on my redhat 9 installation. after i first lunch phpmyadmin , it says Your PHP MySQL library version 5.0.22 differs from your MySQL server version 3.23.32. i am quite sure there is no mysql 3.23.32 installed in my system . anyone has the same kind of trouble and any idea to solve it ? thanks!
misconfigured mysql ?
i install mysql 5.0.22 with php 5.2 on my redhat 9 installation. after i first lunch phpmyadmin , it says Your PHP MySQL library version 5.0.22 differs from your MySQL server version 3.23.32. i am quite sure there is no mysql 3.23.32 installed in my system . anyone has the same kind of trouble and any idea to solve it ? thanks!
InnoDB ANALYZE and locks
Hi all, just a simple question : Does the query ANALYZE position reads and/or writes locks ? I read these two pages but I didn't find the answer... http://www.mysql.com/news-and-events/newsletter/2003-04/a000155.html http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html Thanks all
Re: InnoDB ANALYZE and locks
It locks the table for both. regards anandkl On 11/20/07, Thomas Raso [EMAIL PROTECTED] wrote: Hi all, just a simple question : Does the query ANALYZE position reads and/or writes locks ? I read these two pages but I didn't find the answer... http://www.mysql.com/news-and-events/newsletter/2003-04/a000155.html http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html Thanks all
MySQL University session on November 22
Hi, this Thursday, Sergey Petrunia will give a MySQL University session on: Interaction Between Optimizer and Storage Engine Please register for this session by filling in your name on the session Wiki page that you can find here: http://forge.mysql.com/wiki/Interaction_Between_Optimizer_and_Storage_Engine Registering is not required but appreciated. Thanks! That Wiki page also contains a section to post questions. Please use it! Those planning to attend a MySQL University session for the very first time should probably read the instructions for attendees, http://forge.mysql.com/wiki/Instructions_for_Attendees. -- Regards, Stefan Hinz [EMAIL PROTECTED], MySQL AB Documentation Manager Berlin, Germany (UTC +1:00/winter, +2:00/summer) Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Giant database vs unlimited databases
Mohammad wrk wrote:
Hi Eric,
In the case of a yes answer to the second question below, can't we
still use something like VPD (Virtual Private Database) in MySQL?
Thanks,
Mohammad
Hi,
I don't know much about Oracle, but I looked this up. MySQL can't do it,
that I know of. Here is the final example of such a setup that I found
at http://www.oracle-base.com/articles/8i/VirtualPrivateDatabases.php
CONNECT user1/[EMAIL PROTECTED];
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES('User1', 1);
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES('User2',2);
COMMIT;
CONNECT user2/[EMAIL PROTECTED]
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES ('User 1', 1);
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES ('User 2', 2);
COMMIT;
CONNECT schemaowner/[EMAIL PROTECTED]
SELECT * FROM schemaowner.user_data;
CONNECT user1/[EMAIL PROTECTED];
SELECT * FROM schemaowner.user_data;
CONNECT user2/[EMAIL PROTECTED]
SELECT * FROM schemaowner.user_data;
Notice that:
* When connected to USER1, only the first insert will work.
* When connected to USER2, only the second insert will work.
* The failing inserts produce the error:
ORA-28115: policy with check option violation
You can setup column level privileges on MySQL, but I wonder if it would
be buggy considering I have never heard of anyone doing this before.
Plus from what I understand the above example is a lot more than column
privileges. user1 can only insert data if the insert statement's data
sets user_id to 1, for example. Pretty cool, but scary in a way. I find
this much logic in the DB to be scary(esp if not well documented), but
then I use MySQL :)
So as to which way you should go is most defiantly a matter of opinion I
think. But, going back to my opinion(which is not at all informed as to
all the details), if question 2 is a YES, then I would tend to go with
separate DBs.
Thanks for the Oracle lesson :)
Thanks,
Eric
- Original Message
From: Eric Frazier [EMAIL PROTECTED]
To: Mohammad wrk [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Monday, November 19, 2007 7:42:13 AM
Subject: Re: Giant database vs unlimited databases
Mohammad wrk wrote:
Hi,
I'm working on a web 2.0 project that targeting small to medium size
companies for providing business services. Companies simply register to
the site and then start their business by loading their data, sharing
and discussing them with others.
The design/architectural decision now we are facing from database
perspective is how we should store companies' specific data? One way is
to put all of them in a single database and partition them by company-id
and the other one is to create, on the fly, a new database per company
. The justification for the latter is that MySQL is not powerful enough
(compare to Oracle or DB2) to handle large amount of data and concurrent
users.
I'm new to MySQL and don't know that much about it and this is why
I'd like to discuss this concern here.
Funny, I thought you asked the question, should I separate my customers
into their own databases, or use one big DB? Not MySQL sucks, Oracle is
better. :)
Issues I would ask about on this:
1. Is there a chance that given their separation, these DBs will ever
diverge in design because of differences between customers?
2. Could they ever need to be separated for legal reasons? (like one bad
query causing customer data be compromised)
3. Is there any other reason you may do something vastly different from
one customer to another?
If you answer yes to any of these, then you might be best off separating
dbs. But, if you never want to, or expect for any of these things to
happen, you will just be creating headaches for yourself. Backup,
replication, and the need for cross DB queries, will all be a pain in
comparison to a single DB.
I am sure there is more to consider, but these are the points that come
to my mind right away.
Thanks,
Eric
Instant message from any web browser! Try the new * Yahoo! Canada
Messenger for the Web BETA*
http://ca.messenger.yahoo.com/webmessengerpromo.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Giant database vs unlimited databases
No one probably wants to go through the trouble to code this solution but it
is possible to use MySQL Proxy to filter the SQL statements and results.
MySQL Proxy sits in between MySQL Server and the MySQL Client.
It can read queries, modify them, send queries to the server or deny them all
together, and even read results and modify them as well, or deny the results to
be sent back to the client.
Perhaps if you can resolve to a less complicated set up, but still lean towards
the VPD idea, MySQL Proxy might work for you.
I just wanted to throw this solution out in case it was useful.
-RG
Eric Frazier wrote:
Mohammad wrk wrote:
Hi Eric,
In the case of a yes answer to the second question below, can't we
still use something like VPD (Virtual Private Database) in MySQL?
Thanks,
Mohammad
Hi,
I don't know much about Oracle, but I looked this up. MySQL can't do it,
that I know of. Here is the final example of such a setup that I found
at http://www.oracle-base.com/articles/8i/VirtualPrivateDatabases.php
CONNECT user1/[EMAIL PROTECTED];
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES('User1', 1);
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES('User2',2);
COMMIT;
CONNECT user2/[EMAIL PROTECTED]
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES ('User 1', 1);
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES ('User 2', 2);
COMMIT;
CONNECT schemaowner/[EMAIL PROTECTED]
SELECT * FROM schemaowner.user_data;
CONNECT user1/[EMAIL PROTECTED];
SELECT * FROM schemaowner.user_data;
CONNECT user2/[EMAIL PROTECTED]
SELECT * FROM schemaowner.user_data;
Notice that:
* When connected to USER1, only the first insert will work.
* When connected to USER2, only the second insert will work.
* The failing inserts produce the error:
ORA-28115: policy with check option violation
You can setup column level privileges on MySQL, but I wonder if it would
be buggy considering I have never heard of anyone doing this before.
Plus from what I understand the above example is a lot more than column
privileges. user1 can only insert data if the insert statement's data
sets user_id to 1, for example. Pretty cool, but scary in a way. I find
this much logic in the DB to be scary(esp if not well documented), but
then I use MySQL :)
So as to which way you should go is most defiantly a matter of opinion I
think. But, going back to my opinion(which is not at all informed as to
all the details), if question 2 is a YES, then I would tend to go with
separate DBs.
Thanks for the Oracle lesson :)
Thanks,
Eric
- Original Message
From: Eric Frazier [EMAIL PROTECTED]
To: Mohammad wrk [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Monday, November 19, 2007 7:42:13 AM
Subject: Re: Giant database vs unlimited databases
Mohammad wrk wrote:
Hi,
I'm working on a web 2.0 project that targeting small to medium
size companies for providing business services. Companies simply
register to the site and then start their business by loading their
data, sharing and discussing them with others.
The design/architectural decision now we are facing from database
perspective is how we should store companies' specific data? One way
is to put all of them in a single database and partition them by
company-id and the other one is to create, on the fly, a new database
per company . The justification for the latter is that MySQL is not
powerful enough (compare to Oracle or DB2) to handle large amount of
data and concurrent users.
I'm new to MySQL and don't know that much about it and this is why
I'd like to discuss this concern here.
Funny, I thought you asked the question, should I separate my customers
into their own databases, or use one big DB? Not MySQL sucks, Oracle is
better. :)
Issues I would ask about on this:
1. Is there a chance that given their separation, these DBs will ever
diverge in design because of differences between customers?
2. Could they ever need to be separated for legal reasons? (like one bad
query causing customer data be compromised)
3. Is there any other reason you may do something vastly different from
one customer to another?
If you answer yes to any of these, then you might be best off separating
dbs. But, if you never want to, or expect for any of these things to
happen, you will just be creating headaches for yourself. Backup,
replication, and the need for cross DB queries, will all be a pain in
comparison to a single DB.
I am sure there is more to consider, but these are the points that come
to my mind right away.
Thanks,
Eric
Instant message from any web browser! Try the new * Yahoo! Canada
Messenger for the Web BETA*
Re: Giant database vs unlimited databases
Russell E Glaue wrote: No one probably wants to go through the trouble to code this solution but it is possible to use MySQL Proxy to filter the SQL statements and results. MySQL Proxy sits in between MySQL Server and the MySQL Client. It can read queries, modify them, send queries to the server or deny them all together, and even read results and modify them as well, or deny the results to be sent back to the client. Perhaps if you can resolve to a less complicated set up, but still lean towards the VPD idea, MySQL Proxy might work for you. I just wanted to throw this solution out in case it was useful. -RG Hi Russel, That sounds like a cool idea and makes sense. That is what made me feel oogy about the idea of trying to do something like this with MySQL privileges. I read tons of things that say the real auth layer should be separate. And that VPD example was a good example of how fine grained and therefore complex auth schemes can get. I would guess that following your idea further, it could end up being more scalable(sorry I hate that word it is so overused) that is easy to change and upgrade. I am interested in the many dbs vs one big db issue because I followed the many db choice at one time. It did make sense because I could answer all three of the questions in my previous post a big YES. But, it was a lot of extra work, esp over time. I also discovered you can do cross DB joins, but that makes some DBAs shriek in horror :)As it should. Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need query: records inserted on Monday?
Afan, you'll need to have a date and time column in the database storing a created at value, that is set on INSERT and then not changed. Assuming you have such a column - let's call it created_at - you can run queries like this: /* to get count of records created on Mondays */ SELECT COUNT(*) FROM table t WHERE DAYOFWEEK(t.created_at) = 2; /* to get count created on a given date between 8 AM and 4 PM */ SELECT COUNT(*) FROM table t WHERE t.created_at = 2007-11-20 8:00 AND t.created_at = 2007-11-20 16:00; MySQL's docs on date and time functions are here: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html HTH, Dan On Nov 20, 2007 12:16 PM, Afan Pasalic [EMAIL PROTECTED] wrote: Hi, I have to build a report - when (date and/or time) the records are inserted. E.g., number of records inserted on Monday - doesn't matter what month. Or, number of records inserted on specific date between 8am and 4pm. Thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need query: records inserted on Monday?
Hi, I have to build a report - when (date and/or time) the records are inserted. E.g., number of records inserted on Monday - doesn't matter what month. Or, number of records inserted on specific date between 8am and 4pm. Thanks for any help. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need query: records inserted on Monday?
Yup! That's it! Thanks Dan. ;) The link is really helpful. I was looking for it on mysql but was able to find. Looks like I didn't try hard. :) -afan Dan Buettner wrote: Afan, you'll need to have a date and time column in the database storing a created at value, that is set on INSERT and then not changed. Assuming you have such a column - let's call it created_at - you can run queries like this: /* to get count of records created on Mondays */ SELECT COUNT(*) FROM table t WHERE DAYOFWEEK(t.created_at) = 2; /* to get count created on a given date between 8 AM and 4 PM */ SELECT COUNT(*) FROM table t WHERE t.created_at = 2007-11-20 8:00 AND t.created_at = 2007-11-20 16:00; MySQL's docs on date and time functions are here: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html HTH, Dan On Nov 20, 2007 12:16 PM, Afan Pasalic [EMAIL PROTECTED] wrote: Hi, I have to build a report - when (date and/or time) the records are inserted. E.g., number of records inserted on Monday - doesn't matter what month. Or, number of records inserted on specific date between 8am and 4pm. Thanks for any help. -afan -- 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]
IMAGES/PICTURES-MYSQL
Hello to everybody! I have read some messages about this topic but i haven´t resolved my problem yet. I am using mySQL Query Browser to make de schema. And i have a table (product) where there are some attributes (producto, precio, nUds,tipo)(spanish). All are relationated with one product (ex: heineken beer). So, i want to put another atribute image where i can put the image of all the products. But i have read that is imposible, that i have to put the path of the jpeg or gif, but i don´t know how. If anyone could help me i will be grateful. Attached is the image of my DB: http://www.nabble.com/file/p13863505/7.jpg Thank you very much! *Sorry for my english -- View this message in context: http://www.nabble.com/IMAGES-PICTURES-MYSQL-tf4845682.html#a13863505 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IMAGES/PICTURES-MYSQL
Hi, I have read some messages about this topic but i haven´t resolved my problem yet. I am using mySQL Query Browser to make de schema. And i have a table (product) where there are some attributes (producto, precio, nUds,tipo)(spanish). All are relationated with one product (ex: heineken beer). So, i want to put another atribute image where i can put the image of all the products. But i have read that is imposible, that i have to put the path of the jpeg or gif, but i don´t know how. If anyone could help me i will be grateful. Attached is the image of my DB: What makes you think it's impossible to store the actual image inside a blob field? 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: IMAGES/PICTURES-MYSQL
Hello! I have read that. But i have not idea what i have to do. Because i´m amateur in this topic. Thank you -- View this message in context: http://www.nabble.com/IMAGES-PICTURES-MYSQL-tf4845682.html#a13865352 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: IMAGES/PICTURES-MYSQL
It all comes down to what you are most comfortable with. What I have done is to name each image file with a serial number (1.jpg, 2.gif, 3.png, ...) and store the path along with the real name of the image in a table row. Doing it this way means that you don't need to program logic to convert a blob in order to serve it up with a script, you just need to programmatically create a link to the image file and let the web server do it. I find that easier to work with, and certainly easier to debug. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: puntapari [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 3:38 PM To: mysql@lists.mysql.com Subject: Re: IMAGES/PICTURES-MYSQL Hello! I have read that. But i have not idea what i have to do. Because i´m amateur in this topic. Thank you -- View this message in context: http://www.nabble.com/IMAGES-PICTURES- MYSQL-tf4845682.html#a13865352 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why is Delete slow on a Merge Table?
I have a merge table that is a union of 20 1 million row tables. Select rows from it is quite fast. However if I want to delete all the rows as in: delete from MyMergeTable; it takes just over 3 minutes. I could execute 20 separate delete statements for each of the 20 tables and it would complete in under a second. Why is a Delete on a merge table so inefficient? It appears it is deleting all the rows individually. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why is Delete slow on a Merge Table?
Does TRUNCATE work on a merge table? I honestly don't know, but it should be faster than a DELETE for removing all of the records. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 4:13 PM To: mysql@lists.mysql.com Subject: Why is Delete slow on a Merge Table? I have a merge table that is a union of 20 1 million row tables. Select rows from it is quite fast. However if I want to delete all the rows as in: delete from MyMergeTable; it takes just over 3 minutes. I could execute 20 separate delete statements for each of the 20 tables and it would complete in under a second. Why is a Delete on a merge table so inefficient? It appears it is deleting all the rows individually. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: misconfigured mysql ?
Thanks Ananda , I tried , i tried to uninstall the maybe-preinstalled mysql, but rpm tells me that there is no mysql installed in my system . I listed all the rpm packages in my system . I am quite sure there is no rpm mysql package . I installed the php 5.2.0, mysql 5.0.22, apache2.2.6 , myphpadmin 2.11.2.1 all from source code . my myphpadmin's error message is : Your PHP MySQL library version 5.0.22 differs from your MySQL server version 3.23.32. This may cause unpredictable behavior. and make me more puzzled is it displays : localhost --server version: 3.23.32 , MySQl client version :5.022 . how could this happen ? maybe i misconfigured the mysql source code tree ? or phpmyadmin's bug? - Original Message - From: Ananda Kumar To: vc Cc: mysql@lists.mysql.com Sent: Tuesday, November 20, 2007 6:13 PM Subject: Re: misconfigured mysql ? Hi Vanish, redhat by default installs a old version of mysql. execute this command and check if two version 's of mysql are there rpm -qpl MySQL-server-VERSION On 11/20/07, vc [EMAIL PROTECTED] wrote: i install mysql 5.0.22 with php 5.2 on my redhat 9 installation. after i first lunch phpmyadmin , it says Your PHP MySQL library version 5.0.22 differs from your MySQL server version 3.23.32. i am quite sure there is no mysql 3.23.32 installed in my system . anyone has the same kind of trouble and any idea to solve it ? thanks!
Re: misconfigured mysql ?
vc wrote: Thanks Ananda , I tried , i tried to uninstall the maybe-preinstalled mysql, but rpm tells me that there is no mysql installed in my system . I listed all the rpm packages in my system . I am quite sure there is no rpm mysql package . I installed the php 5.2.0, mysql 5.0.22, apache2.2.6 , myphpadmin 2.11.2.1 all from source code . my myphpadmin's error message is : Your PHP MySQL library version 5.0.22 differs from your MySQL server version 3.23.32. This may cause unpredictable behavior. and make me more puzzled is it displays : localhost --server version: 3.23.32 , MySQl client version :5.022 . how could this happen ? maybe i misconfigured the mysql source code tree ? or phpmyadmin's bug? How did you install the mysql server (rpm, source) ? How did you install the mysql client (rpm, source) ? If by rpm's: rpm -qa | grep -i mysql It's definitely not a phpmyadmin bug, it's using what the mysql client and server are telling it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why is Delete slow on a Merge Table?
mos wrote: I have a merge table that is a union of 20 1 million row tables. Select rows from it is quite fast. However if I want to delete all the rows as in: delete from MyMergeTable; it takes just over 3 minutes. I could execute 20 separate delete statements for each of the 20 tables and it would complete in under a second. Why is a Delete on a merge table so inefficient? It appears it is deleting all the rows individually. That's the way delete works - it deletes the rows one at a time. It also has to update the indexes as it goes to remove links between the data index. http://dev.mysql.com/doc/refman/5.0/en/delete.html http://dev.mysql.com/doc/refman/5.0/en/delete-speed.html If you're clearing the whole table, use truncate as Jerry suggested. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
