Re: Seriously.. When are we going to get subqueries?!
Jochem van Dieten wrote: Also, let's not mistake the means for the goal. Using indexes is just a way to solve it and there may be other fixes. The goal is to improve performance. no.. using indexes is THE way to fix it :) I don't want a subquery scanning all 700 million rows in my table where an index would reduce that to... 10... Kevin -- Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. See irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Newbie] Slow slave update.
2005/6/10, Atle Veka [EMAIL PROTECTED]: The speed of the drive will have a lot to say on how long the queries run. You haven't said anything about what type of master you have, OS, mysqld Thank you all for your answers. As I said before, I was only curious, as fortunately I don't have to rely on my laptop for that data :-).( However, it is a P4m with 512 Mb Ram, while the server is a dual p4, 2Gb and raid5 scsi disk. They are both running some 4.1.x mysql server).. Now, I am thinking that probably the delay is due to the laptop antivirus. I know that my McAfee slow things a bit when you do some heavy file access, and I guess this is the case. Thanks again for your answers and your help. -- Ciao Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DELAYED and NOW()
Eric Bergen wrote: How about something like this: mysql select @t := now(); +-+ | @t := now() | +-+ | 2005-06-09 09:55:49 | +-+ 1 row in set (0.00 sec) if the timestamp is not needed on the client with : mysql do @t := now(); you would spare a bit of bandwidth my 2cts. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please reply me
Hai Here is my Query. When the LAN goes down the client mysql process does not give any exception.Instead of that it hangs for Long time in UNIX.But in windows it raise exception immediately.How to configure in Linux or Unix to behave as like in windows. Please reply me .Iam in urgent. With regards Ganesh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Open source DBMS evaluation study
For those interested, this makes a good read. http://www.fabalabs.org/research/papers/FabalabsResearchPaper-OSDBMS-Eval.pdf The Institute for Applied Knowledge Processing (http://www.faw.at) in cooperation with Fabalabs Software GmbH (http://www.fabalabs.org) have made a new Open Source DBMS evaluation document available, comparing Firebird 1.5.2, Ingres r3 3.0.1, MaxDB 7.5.0.23, MySQL 4.1.10 and PostgreSQL 8.0.1 in different areas. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
when to use Indexes
i have a table with several thousand records. i want to know if adding
an index will improve things, example:
-
| id | item | text | runtime | dateline
-
now say i create this query:
$stats = mysql_query(
SELECT COUNT(*) as total, AVG(runtime) as avgtime
FROM table WHERE dateline .strtotime('6 hours ago').
);
should dateline be an index?
basically, i would like to know when i should create an index, when an
index is not needed, and what columns should be indexes..
any urls which explains all this would be helpful.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting warnings from mysqlimport with MySQL v4.0.15-standard-log
Hi The subject says it all! My mysqlimport command reports 43 warnings, but I have no idea how to access them. SHOW WARNINGS was only implemented after MySQL version 4.1, and I have 4.0.15-standard-log. Any help? Many thanks Mick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MERGE Table Question
Hello everyone, We're going to be using the MERGE TABLE option of Mysql when we build our Data Warehouse. Basically (for you Oracle folks), we're going to mimic PARTITIONS. My question is this: Other than dumping syntax through a utility and comparing text, is there any way I can read the structure directly through the *.FRM or *.MYD files to make certain that ALL tables match exactly so the MERGE will work? Regards, George __ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: when to use Indexes
Sebastian wrote:
i have a table with several thousand records. i want to know if adding
an index will improve things, example:
-
| id | item | text | runtime | dateline
-
now say i create this query:
$stats = mysql_query(
SELECT COUNT(*) as total, AVG(runtime) as avgtime
FROM table WHERE dateline .strtotime('6 hours ago').
);
should dateline be an index?
Yes, because that's the only condition of your WHERE query, and the most
discriminant one.
basically, i would like to know when i should create an index, when an
index is not needed, and what columns should be indexes..
you should basically the columns you use in your WHERE condition,
regarding which is the most relevant columns. Optimization is quiet a
large field, and leads to endless discussion.
any urls which explains all this would be helpful.
http://dev.mysql.com/doc/mysql/en/mysql-optimization.html
You want to read, the one on optimizing Select and the one on columns
indexes.
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MERGE Table Question
At 09:00 AM 6/10/2005, you wrote: Hello everyone, We're going to be using the MERGE TABLE option of Mysql when we build our Data Warehouse. Basically (for you Oracle folks), we're going to mimic PARTITIONS. My question is this: Other than dumping syntax through a utility and comparing text, is there any way I can read the structure directly through the *.FRM or *.MYD files to make certain that ALL tables match exactly so the MERGE will work? Regards, George George, You could try things like: show tables like myMergeTable% to get a list of tables making up the merge. (Assuming the name of the merge tables all start with the similar name) Then with the results of that query execute for each table name returned: show columns from tablex and show index from tablex You could then compare each tablex definition with each other, probably using a program or even SQL if that is the route you wanted to take. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting warnings from mysqlimport with MySQL v4.0.15-standard-log
Hello. Similar questions are often asked on the list, but I don't remember any solution for old versions. michael watson (IAH-C) [EMAIL PROTECTED] wrote: Hi The subject says it all! My mysqlimport command reports 43 warnings, but I have no idea how to access them. SHOW WARNINGS was only implemented after MySQL version 4.1, and I have 4.0.15-standard-log. Any help? Many thanks Mick -- 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: Please reply me
Hello. Here are some notes: http://bugs.mysql.com/bug.php?id=9678 http://bugs.mysql.com/bug.php?id=4143 Ganesh [EMAIL PROTECTED] wrote: Hai Here is my Query. When the LAN goes down the client mysql process does not give any exception.Instead of that it hangs for Long time in UNIX.But in windows it raise exception immediately.How to configure in Linux or Unix to behave as like in windows. Please reply me .Iam in urgent. With regards Ganesh -- 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: Seg Fault php and MySql ODBC on Linux.
Hello. Check that you have the latest version. There is a bug: http://bugs.mysql.com/bug.php?id=7040 Santino [EMAIL PROTECTED] wrote: Hello, I have a segmentation fault using MySql with PHP/ODBC on Linux. I do a query with exec and I get a segmentation fault in my_SQLPrepare. If I use the isql command line, all works fine. Is it a MySql problem or a Php problem? Thank you. Santino Cusimano -- 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: when to use Indexes
Hello.
This is a good start point:
http://dev.mysql.com/doc/mysql/en/mysql-optimization.html
Sebastian [EMAIL PROTECTED] wrote:
i have a table with several thousand records. i want to know if adding
an index will improve things, example:
-
| id | item | text | runtime | dateline
-
now say i create this query:
$stats = mysql_query(
SELECT COUNT(*) as total, AVG(runtime) as avgtime
FROM table WHERE dateline .strtotime('6 hours ago').
);
should dateline be an index?
basically, i would like to know when i should create an index, when an
index is not needed, and what columns should be indexes..
any urls which explains all this would be helpful.
--
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: when to use Indexes
From: Sebastian
i have a table with several thousand records. i want to know if adding
an index will improve things, example:
-
| id | item | text | runtime | dateline
-
now say i create this query:
$stats = mysql_query(
SELECT COUNT(*) as total, AVG(runtime) as avgtime
FROM table WHERE dateline .strtotime('6 hours ago').
);
should dateline be an index?
basically, i would like to know when i should create an index, when an
index is not needed, and what columns should be indexes..
It is very hard to give general guidelines, as you might have noticed in the
MySQL documentation. Many pages contain clues and pointers about using
indexes in various situations. But the actual solution depends on the data
itself (how many different values exist in a column), the table type
(different engines handle indexes differently), the query, the
serverhardware, etc.
In your example the first thing you can improve is probably the selection of
the records that will be used to build the result of the query. Dateline is
a candidate in this example. It will improve the query from a 'full table
scan' to a 'range scan'. If the selection would retrieve more than approx.
30% of the records MySQL will not use an index anyway, because a full table
scan (sequential read of the records) will probably be faster than using an
index (random read of the records).
http://dev.mysql.com/doc/mysql/en/query-speed.html is a good chapter to find
out about optimizing queries.
http://dev.mysql.com/doc/mysql/en/optimizing-database-structure.html
contains various chapters about using indexes.
When tables get bigger, you might need other configuration settings than the
standard my.conf your probably now working with; dig through
http://dev.mysql.com/doc/mysql/en/optimizing-the-server.html
One of the more valuable tools in MySQL (besides loads of experience and
trial-and-error) is the EXPLAIN statement
(http://dev.mysql.com/doc/mysql/en/explain.html).
Because you need basic information about indexes, it might be useful to read
a good book on MySQL. Most books cover the basic use of indexes.
Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MERGE Table Question
If you're concerned about subtle data type differences you can also run one show create table myMergeTable statement for each source table and diff them. Ed -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Friday, June 10, 2005 8:35 AM To: mySQL list Subject: Re: MERGE Table Question At 09:00 AM 6/10/2005, you wrote: Hello everyone, We're going to be using the MERGE TABLE option of Mysql when we build our Data Warehouse. Basically (for you Oracle folks), we're going to mimic PARTITIONS. My question is this: Other than dumping syntax through a utility and comparing text, is there any way I can read the structure directly through the *.FRM or *.MYD files to make certain that ALL tables match exactly so the MERGE will work? Regards, George George, You could try things like: show tables like myMergeTable% to get a list of tables making up the merge. (Assuming the name of the merge tables all start with the similar name) Then with the results of that query execute for each table name returned: show columns from tablex and show index from tablex You could then compare each tablex definition with each other, probably using a program or even SQL if that is the route you wanted to take. Mike -- 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]
Which users eat cpu on Mysql
Hi Everybosy , I red user-resources but I could't find any mysql user cpu usage limitation, instead of this How can I find out Who is sending too much query and How much cpu and memory eat ?! You know top showing only mysql process average but I want to learn inside of this process How can I do ?! Thanks Best Regards Vahric MUHTARYAN
Re: Open source DBMS evaluation study
On 10/06/2005, at 8:09 PM, Martijn Tonies wrote: For those interested, this makes a good read. http://www.fabalabs.org/research/papers/FabalabsResearchPaper-OSDBMS- Eval.pdf The Institute for Applied Knowledge Processing (http://www.faw.at) in cooperation with Fabalabs Software GmbH (http://www.fabalabs.org) have made a new Open Source DBMS evaluation document available, comparing Firebird 1.5.2, Ingres r3 3.0.1, MaxDB 7.5.0.23, MySQL 4.1.10 and PostgreSQL 8.0.1 in different areas. An interesting read, they obviously noted the drawback of no full text searching on INNODB :| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which users eat cpu on Mysql
Hi, SHOW PROCESSLIST Peter Vahric MUHTARYAN wrote: Hi Everybosy , I red user-resources but I could't find any mysql user cpu usage limitation, instead of this How can I find out Who is sending too much query and How much cpu and memory eat ?! You know top showing only mysql process average but I want to learn inside of this process How can I do ?! Thanks Best Regards Vahric MUHTARYAN -- Best regards, Peter http://AboutSupport.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seg Fault php and MySql ODBC on Linux.
At 16:47 +0300 10-06-2005, Gleb Paharenko wrote: Hello. Check that you have the latest version. There is a bug: http://bugs.mysql.com/bug.php?id=7040 I downloaded yesterday the latest version of ODBC Connector. Version: mysql-max-4.1.8-pc-linux-i686 + MyODBC-3.51.11-2.i586.rpm (libmyodbc3-3.51.11.so) Give me a seg fault. mysql-max-4.1.8-pc-linux-i686 + libmyodbc-2.50.39.so Works (if I change the socket to '/var/lib/mysql/mysql.sock' from '/tmp/mysql.sock' Santino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Which users eat cpu on Mysql
Haven't used it in a while, but mytop is handy (think that was the name) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, June 10, 2005 12:09 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: Which users eat cpu on Mysql Hi, SHOW PROCESSLIST Peter Vahric MUHTARYAN wrote: Hi Everybosy , I red user-resources but I could't find any mysql user cpu usage limitation, instead of this How can I find out Who is sending too much query and How much cpu and memory eat ?! You know top showing only mysql process average but I want to learn inside of this process How can I do ?! Thanks Best Regards Vahric MUHTARYAN -- Best regards, Peter http://AboutSupport.com -- 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: Open source DBMS evaluation study
Interesting document. The correct link is http://www.fabalabs.org/research/papers/FabalabsResearchPaper-OSDBMS-Eval.pdf PB - Dan Rossi wrote: On 10/06/2005, at 8:09 PM, Martijn Tonies wrote: For those interested, this makes a good read. http://www.fabalabs.org/research/papers/FabalabsResearchPaper-OSDBMS- Eval.pdf "The "Institute for Applied Knowledge Processing" (http://www.faw.at) in cooperation with "Fabalabs Software GmbH" (http://www.fabalabs.org) have made a new Open Source DBMS evaluation document available, comparing Firebird 1.5.2, Ingres r3 3.0.1, MaxDB 7.5.0.23, MySQL 4.1.10 and PostgreSQL 8.0.1 in different areas. " An interesting read, they obviously noted the drawback of no full text searching on INNODB :| No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.6.6 - Release Date: 6/8/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIMIT error
Hello, I'm using the MySQL C API and I got the following error for this query: UPDATE tracks SET track_state='-2', track_cost='1.50' WHERE track_flynum='10' AND track_testcase='45' ORDER BY track_step DESC LIMIT 0,1 SQL Error 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 If I put LIMIT 1 instead of LIMIT 0,1 I get the correct result. This seems to be a weird problem and according to the documentation, should be equivalent, but it seems not. Running on Win2k, MySQL 4.1.11, mysqld-nt.exe Thanks David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIMIT error
I think you might be confusing UPDATE and SELECT syntax. As far as I ever knew, you couldn't specify a limit offset in an update statement. I don't see in mysql update docs where it indicates offset is allowed. kgt David Legault wrote: Hello, I'm using the MySQL C API and I got the following error for this query: UPDATE tracks SET track_state='-2', track_cost='1.50' WHERE track_flynum='10' AND track_testcase='45' ORDER BY track_step DESC LIMIT 0,1 SQL Error 1064 : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1' at line 1 If I put LIMIT 1 instead of LIMIT 0,1 I get the correct result. This seems to be a weird problem and according to the documentation, should be equivalent, but it seems not. Running on Win2k, MySQL 4.1.11, mysqld-nt.exe Thanks David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Newbie] Slow slave update.
On Fri, 10 Jun 2005, Nico Alberti wrote: 2005/6/10, Atle Veka [EMAIL PROTECTED]: The speed of the drive will have a lot to say on how long the queries run. You haven't said anything about what type of master you have, OS, mysqld Thank you all for your answers. As I said before, I was only curious, as fortunately I don't have to rely on my laptop for that data :-).( However, it is a P4m with 512 Mb Ram, while the server is a dual p4, 2Gb and raid5 scsi disk. They are both running some 4.1.x mysql server).. Ok, so your laptop has a p4, that doesn't really mean much. What really matters is that your laptop probably has a 4200rpm IDE drive while your server has a 10/15k rpm SCSI drive. I would doubt that your antivirus software has much to do with this, the biggest problem is your disk. :) Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to control database size in MySQL Windows?
I think the answer to this is You can't. So I guess what I can do is run a query once every while and get the sizes of all the databases and if any exceeds a predetermined size, revoke insert and update privilages. What's is the SQL query like to get a database size and the SQL to get the names of all the databases? Salama -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INSERT DELAYED and NOW()
More queries yes but not more disk i/o. The first query will never touch a disk. [EMAIL PROTECTED] wrote: Eric Bergen [EMAIL PROTECTED] wrote on 06/09/2005 12:56:59 PM: How about something like this: mysql select @t := now(); +-+ | @t := now() | +-+ | 2005-06-09 09:55:49 | +-+ 1 row in set (0.00 sec) mysql insert delayed into t set t = @t; Query OK, 1 row affected (0.00 sec) mysql select * from t; +-+ | t | +-+ | 2005-06-09 09:55:49 | +-+ 1 row in set (0.01 sec) This way you get the current time of the call and it doesn't matter how long the insert delayed sits for. Jochem van Dieten wrote: On 6/9/05, Jeremiah Gowdy wrote: Does this seem to break SQL / application logic in some fashion? Not worse then it is currently broken :) According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a synonym for NOW(), is supposed to have a value that does not change during a transaction. At which point during the transaction that value is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section 6.31) Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere, but I would prefer any solution/hack not to complicate MySQL ever becomming standard compliant in this regard (and standard compliance is an official goal). Does the standard specify when the timestamp is evaluated? During the transaction. I agree that it might be better for it to be a seperate function, but since DELAYED isn't part of the standard, I'm not sure there's anything that keeps an implementation from evaluating the CURRENT_TIMESTAMP for a query upon receipt of the query from the network, rather than when the SQL statement is evaluated. Let me reiterate: Since both NOW() and INSERT DELAYED are MySQL extensions I don't particularly care how they behave/interfere. If I wrote a SQL server from scratch, would this not be a valid implementation, to timestamp upon network receive of a complete query, rather than upon finding the CURRENT_TIMESTAMP (or NOW()) function while parsing a query? That depends on some more implementation issues: perceivably your network receive could even be before the start of the transaction. Evaluate CURRENT_TIMESTAMP only once per transaction, between the start of the transaction and the end of the transaction. Jochem The problem with that is that you have just doubled the query count at the central logging server. That's a lot of traffic it can probably do without. I like the QNOW() approach. (Use an extension, the new function, to deal with a side effect of an extension, DELAYED. It's a universal balance kind of thing.) Some alternative names: QUEUEDNOW(), QUEUEDTIMESTAMP(), RECEIVEDTIME(), RECEIVEDTIMESTAMP(), ARRIVALTIMESTAMP() Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql Ver 11.16 Distrib 3.23.49
Yes, I know it is old obsolete. How do I query mysql to determine which hosts would benefit from a FLUSH HOSTS command? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to control database size in MySQL Windows?
If you are on 5.0.n there is an INFORMATION_SCHEMA which you can query like this. A casual scan of the mysql tables don't show any sizes and I don't know of a way to get table/database size via SQL. mysql select table_schema, sum(DATA_LENGTH) from information_schema.tables group by 1; ++--+ | table_schema | sum(DATA_LENGTH) | ++--+ | information_schema | 0| | mailprint | 2523448288 | | mysql | 275126 | | test | 16510| ++--+ 4 rows in set, 79 warnings (6.22 sec) -Original Message- From: Salama hussein [mailto:[EMAIL PROTECTED] Sent: Friday, June 10, 2005 1:31 PM To: mysql@lists.mysql.com Subject: How to control database size in MySQL Windows? I think the answer to this is You can't. So I guess what I can do is run a query once every while and get the sizes of all the databases and if any exceeds a predetermined size, revoke insert and update privilages. What's is the SQL query like to get a database size and the SQL to get the names of all the databases? Salama -- 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]
PHP MySQL connection problem
Hi list, I am getting slow connections between php4 and MySQL 4.1.9. There are some connections that are very fast, but others can take from 5 to 20 second or more (usually it takes less than 0.1 second to create a new connection). I tried to identify if there is a pattern but it seems that it does not exist, in other words, it can create 5 connections and then the 6th takes too much time. In another time, it process 13 connections fast and the 14th is slow again. Does anyone has any idea what is going on? Can this be related to the MySQL connections limit? Thanks for any help. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to control database size in MySQL Windows?
Hi, in versions less than 5.x, show table status gives informations about rows and avg_row_length. The product gives you a correct approximation if the stats are analyzed. This is for actions from the client. another thing is the OS commands from your datadir (du -k). Hope that helps. Mathias Selon Gordon Bruce [EMAIL PROTECTED]: If you are on 5.0.n there is an INFORMATION_SCHEMA which you can query like this. A casual scan of the mysql tables don't show any sizes and I don't know of a way to get table/database size via SQL. mysql select table_schema, sum(DATA_LENGTH) from information_schema.tables group by 1; ++--+ | table_schema | sum(DATA_LENGTH) | ++--+ | information_schema | 0| | mailprint | 2523448288 | | mysql | 275126 | | test | 16510| ++--+ 4 rows in set, 79 warnings (6.22 sec) -Original Message- From: Salama hussein [mailto:[EMAIL PROTECTED] Sent: Friday, June 10, 2005 1:31 PM To: mysql@lists.mysql.com Subject: How to control database size in MySQL Windows? I think the answer to this is You can't. So I guess what I can do is run a query once every while and get the sizes of all the databases and if any exceeds a predetermined size, revoke insert and update privilages. What's is the SQL query like to get a database size and the SQL to get the names of all the databases? Salama -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP MySQL connection problem
On 6/10/05, Andre Matos [EMAIL PROTECTED] wrote: I am getting slow connections between php4 and MySQL 4.1.9. There are some connections that are very fast, but others can take from 5 to 20 second or more (usually it takes less than 0.1 second to create a new connection). I tried to identify if there is a pattern but it seems that it does not exist, in other words, it can create 5 connections and then the 6th takes too much time. In another time, it process 13 connections fast and the 14th is slow again. If your going across network you might check your network cards. Had this happen to me once. Reseated the card in the database server, fixed it right up. -- Greg Donald Zend Certified Engineer http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Load Balancing Mysql with Foundry ServerIron Switches????
Hi All, Has anybody implemented Load Balancing Mysql with Foundry Load Balancers, I know that the ServerIron series switches dont have a way to do a Layer 7 health check for mysql, as it is not a default port profile, any help from who has dome some work on foundry switches would be greatly appreciated.. Any other successful implementaion of Mysql HA/Load balancing Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem report (could not start MySQL 4.1.12)
Hi there, Attached is my problem report. Please help. Thanks, Tan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
