Passing Infinity to a FLOAT value.
Dear MySQL, I have need to pass an INFINITY value to a FLOAT, as defined in: http://research.microsoft.com/~hollasch/cgindex/coding/ieeefloat.html Can you please tell me if this is possible using SQL, and if so, how this is done? Kind regards, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: In search of a good MySQL GUI client
Karam Chand said: I use SQLyog. It does allow you to create/delete FKs with ON UPDATE criterias. Yeah, it does not allow you to create Fks in graphical manner like MS SQL Server but does my purpose. One feature I really miss is Editing of FKs. Yeah, I also tried SQLyog. It's a very good program indeed, a very nice feature is the structure syncronization tool. However, I said every program I tried has problems, and SQLyog is no exception. I downloaded SQLyog v3.71, I'm using MySQL 4.1.4-gamma-standard. When I manage relationships (F10 key), the FK's not even show up on the list... If I now press New.. to create a relationship, I can fill in the blanks and press Create, however SQLyog didn't show the new relationship either. I ended up creating 5 (five) equal relationships since SQLyog wasn't showing them in the list. A nasty GUI bug, sure, but this renders SQLyog unuseable to me. Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple MysQL servers with different IP address on same machine
An IP socket is the unique combination of an IP address and a port number. I don't see why you couldn't run those separate instances of your db servers on the same port but each with their own addresses as they would each have their own unique IP socket. I don't think you would create any collisions or contentions beyond that of pure network congestion. My tentative answer is: It should work fine but I have not tried it yet so I don't know from personal experience Shawn Green Database Administrator Unimin Corporation - Spruce Pine Sanjeev Sagar [EMAIL PROTECTED] wrote on 09/08/2004 05:04:38 PM: Hello All, MySQL : Standar Binary 4.0.20 O/S : Red Hat Linux release 9 (Shrike) Linux 2.4.20-31.9smp #1 SMP Tue Apr 13 17:40:10 EDT 2004 i686 i686 i386 GNU/Linux I already have setup of Three Multiple MySQL servers listening on different ports and sockets on same machine Option File: [mysqld1] server-id =1 port=3306 socket=/tmp/mysql.sock datadir=data1 [mysqld2] server-id=2 port=3307 socket=/tmp/mysql.sock2 datadir=data2 [mysqld3] server-id=3 port=3308 socket=/tmp/mysql.sock3 datadir=data3 All three servers started with no problem. Question is if I don't want to use different ports or scokets, can I use the different I.P. Addresses on same machine for three servers with same default port or socket. /etc/hosts file === 127.0.0.100 s1 127.0.0.101 s2 127.0.0.102 s3 Can I start three servers on same port (3306), same socket (/tmp/mysql.sock) on same machine by using above IP addresses? If yes then HOW? Can I use the replication in b/w them? keeping datadir and log-bin directory differtent is not a problem. Appreciate it.
Re: Multiple MysQL servers with different IP address on same machine
I need to add to my previous post -- You asked about using the SAME operating system socket as well as using separate addresses with the same port number (different IP sockets) My answer to that is NOT ON YOUR LIFE. Think of the chaos. If one client tried to connect to an OS socket that 3 different servers were listening to... Which one gets the connection? Which one validates the client? If for some reason the client *were* able to validate against all three servers at the same time, how could it sort out the 3 different responses to a query? NO each server must have it's own socket. It doesn't matter if we are discussing IP sockets or OS sockets the answer is still the same. Sorry for the previous oversight, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Sanjeev Sagar [EMAIL PROTECTED] wrote on 09/08/2004 05:04:38 PM: Hello All, MySQL : Standar Binary 4.0.20 O/S : Red Hat Linux release 9 (Shrike) Linux 2.4.20-31.9smp #1 SMP Tue Apr 13 17:40:10 EDT 2004 i686 i686 i386 GNU/Linux I already have setup of Three Multiple MySQL servers listening on different ports and sockets on same machine Option File: [mysqld1] server-id =1 port=3306 socket=/tmp/mysql.sock datadir=data1 [mysqld2] server-id=2 port=3307 socket=/tmp/mysql.sock2 datadir=data2 [mysqld3] server-id=3 port=3308 socket=/tmp/mysql.sock3 datadir=data3 All three servers started with no problem. Question is if I don't want to use different ports or scokets, can I use the different I.P. Addresses on same machine for three servers with same default port or socket. /etc/hosts file === 127.0.0.100 s1 127.0.0.101 s2 127.0.0.102 s3 Can I start three servers on same port (3306), same socket (/tmp/mysql.sock) on same machine by using above IP addresses? If yes then HOW? Can I use the replication in b/w them? keeping datadir and log-bin directory differtent is not a problem. Appreciate it.
MYSQL CONNECT ISSUE
I have been getting following error. [polaris] ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) How to fix that? What is default port for mysqlserver? thx -seena - Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage!
Re: ODBC problem
Even the most recent version of the ODBC drivers act as though they are pre-4.1 clients. Now that you know that, what you read in this article should make better sense: http://dev.mysql.com/doc/mysql/en/Old_client.html It contains links to other details on the issue and some suggestions to work around your problem. Let's all say it together: The manual is your friend ;-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine Danesh Daroui [EMAIL PROTECTED] wrote on 09/08/2004 07:59:04 PM: Hi all, I have a Linux Database Server which runs MySQL 4.1 and a Windows Client machine which runs MySQL 4.1 too. I have installed the latest version of MyODBC on both machines and they have been installed successfully. Now, at the Administrative Tools in the Control Panel on my Windows system (Client) when I try to connect to my Linux system by using a valid user it doesn't connect and returns an error message which is: [MySQL][ODBC 3.51 Driver] Client does not supportauthentication protocol requested by server; consider upgrade MySQL client. What is the problem ? What should I do now ? I am totaly confuse, please help... Regards, Danesh Daroui
Re: Compilation Error
Hi, I am trying to compile MySQL. I know that it is possible to install this as a binary, that is not my goal. My environment consists of the following: If there are other tools that I need to specify please let me know. gcc-3.2-7 libgcc-3.2-7 gcc-c++-3.2-7 I run configure as specified at the MySQL site: From config.log: ./configure --prefix=/usr/local/mysql --with-extra-charset=complex --enable-thread-safe-client --enable-local-infile --enable-assembler --disable-shared --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static When I run make I receive the following output: libmysql.c:1850: warning: passing arg 5 of `gethostbyname_r' from incompatibleointer type libmysql.c:1850: too few arguments to function `gethostbyname_r' libmysql.c:1850: warning: assignment makes pointer from integer without a castmake[2]: *** [libmysql.lo] Error 1 I have googled this error message and the result include the following recommendation from MySQL: This is known problem with RedHat. In order to build MySQL you need to have g++ installed from separate RPM In RedHat 8.0 RPM in quesiton is gcc-c++-3.2-7.i386.rpm It appears that I have installed gcc-c++-3.2-7.i386.rpm as you can see above from my build environment obtained by: rpm -qa | grep gcc Any help or pointers would be greatly appreciated. Andrew Looks like you have the right stuff. Have you tried the following: (run make distclean before configuring again to be sure you get a clean start.) CFLAGS=-O3 -mcpu=pentiumpro CXX=gcc CXXFLAGS=-O3 -mcpu=pentiumpro \ -felide-constructors -fno-exceptions -fno-rtti \ ./configure --your options here change -mcpu=pentiumpro to suite your system (i386, i486, i586, i686, pentium, pentiumpro, k6, or athlon). Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: An SQL question about using multiple tables
I don't know the source of the INTERSECT command that keeps popping up on the list but this is a straight-forward JOIN situation if I have ever seen one. Please read for more details: http://dev.mysql.com/doc/mysql/en/JOIN.html SELECT A.*, E.* FROM A INNER JOIN B ON A.ID = B.parentid INNER JOIN C ON A.ID = C.parentid INNER JOIN D ON A.ID = D.parentid LEFT JOIN E ON A.ID = E.parentid WHERE B.name = 'xxx' AND C.name = 'YYY' AND D.name = 'ZZZ'; Since E has optional information, it's LEFT JOINed to the group. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Sandip Bhattacharya [EMAIL PROTECTED] wrote on 09/09/2004 12:11:22 AM: Background: I have one master table A, and other supplementary tables B,C and D such that for every row of A there can be one or more corresponding rows in B,C,D. There is another supplementary table E with which A has a one-to-one relationship. Problem: Given three search criteria resulting in AB, AC, and AD respectively, I need to display results so that I get ( AB intersection AC intersection AD) and I need to display unique rows of A on teh screen joined with corresponding row of E. A typical multiple parameter search operation in any database with normalized tables. Constraints: Am using (sigh) mysql 3.23. No subqueries, no INTERSECT. What I have tried till now: Creating three temporary tables for AB, AC and AD respectively. Now how do I find out the intersection of these? Stuck there. The SQL with subqueries will probably be something like: select A.*, E.* from A inner join E on A.id=E.parentid where A.id in (select distinct A.id from A inner join B on A.id=B.parentid where B.name='XXX') and A.id in (select distinct A.id from A inner join C on A.id=C.parentid where C.name='YYY') and A.id in (select distinct A.id from A inner join D on A.id=D.parentid where D.name='ZZZ'); === This is most probably impossible to do in one statement in mysql. But how do I do it at all? Any pointers willl be nice. Excuse me if I am doing something terribly wrong. This is the first time I am getting my hands really dirty with SQL. - Sandip -- Sandip Bhattacharya*Puroga Technologies * [EMAIL PROTECTED] Work: http://www.puroga.com* Home: http://www.sandipb.net PGP/GPG Signature: 51A4 6C57 4BC6 8C82 6A65 AE78 B1A1 2280 A129 0FF3 Woolsey-Swanson Rule: People would rather live with a problem they cannot solve rather than accept a solution they cannot understand. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL CONNECT ISSUE
I have been getting following error. [polaris] ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) How to fix that? What is default port for mysqlserver? thx -seena Is this a new install? Have you set the password yet? Have you forgotten the password? The default port is 3306 Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
referencing MySQL
Anybody knows how to reference MySQL in a scientific paper? Thanks Bernd
Re: referencing MySQL
- Original Message - From: Bernd Jagla [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Thursday, September 09, 2004 10:44 AM Subject: referencing MySQL Anybody knows how to reference MySQL in a scientific paper? What do you mean? Are you asking how to put a hyperlink to the MySQL website in a paper written in HTML? Or what the official product name is for use in a footnote? Or something else altogether? Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ODBC problem
The manual's often a friend, but not always, a case in point being the suggestion on that manual page to Upgrade all client programs to use a 4.1.1 or newer client library--you will correct me if I am mistaken on this, I hope, that one's only option now using ODBC and MySQL 4.1 or later is to revert to the old password protocol and thus create problems of the sort ODBC was meant to solve. PB - Original Message - From: [EMAIL PROTECTED] To: Danesh Daroui Cc: [EMAIL PROTECTED] Sent: Thursday, September 09, 2004 9:01 AM Subject: Re: ODBC problem Even the most recent version of the ODBC drivers act as though they are pre-4.1 clients. Now that you know that, what you read in this article should make better sense: http://dev.mysql.com/doc/mysql/en/Old_client.html It contains links to other details on the issue and some suggestions to work around your problem. Let's all say it together: The manual is your friend ;-D Shawn Green Database Administrator Unimin Corporation - Spruce Pine Danesh Daroui [EMAIL PROTECTED] wrote on 09/08/2004 07:59:04 PM: Hi all, I have a Linux Database Server which runs MySQL 4.1 and a Windows Client machine which runs MySQL 4.1 too. I have installed the latest version of MyODBC on both machines and they have been installed successfully. Now, at the Administrative Tools in the Control Panel on my Windows system (Client) when I try to connect to my Linux system by using a valid user it doesn't connect and returns an error message which is: [MySQL][ODBC 3.51 Driver] Client does not supportauthentication protocol requested by server; consider upgrade MySQL client. What is the problem ? What should I do now ? I am totaly confuse, please help... Regards, Danesh Daroui
help urgent please
-- help please please tell me how to extract a script file of a database from mysql commandprompt. s.deepak This life is a hard fact; work your way through it boldly, though it may be adamantine; no matter, the soul is stronger Swami Vivekananda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query very slow - Using temporary; Using filesort
I'm having a bit of a problem with a query that takes a very long time (up to 1 minute) when many matching rows are found. The tables are all indexed and the explain seems to indicate that mysql is using the indexes but it is still painfully slow: mysql SELECT COUNT(TP.thread_id) AS num_posts, MAX(TP.post_date) AS latest_date,T.thread_id, T.thread_name, i.post_image FROM category AS C - INNER JOIN thread_link AS TL USE INDEX(CAT_INDEX) ON C.category_id = TL.category_id - INNER JOIN thread AS T ON TL.thread_id = T.thread_id - INNER JOIN thread_image AS i ON TL.thread_id = i.thread_id - INNER JOIN thread_post AS TP USE INDEX(thread_id) ON TL.thread_id = TP.thread_id - WHERE C.category_id = '8759' AND i.display_type = 'thumbnail' AND TP.rating 0 - GROUP BY TL.thread_id - ORDER BY 'num_posts' DESC - LIMIT 0 , 20; ... results ... 20 rows in set (37.37 sec) The above query is a hypothetical query (hint: I'm not doing a forum db) but it pretty much matches what I'm doing. In this case there are many categories 'C' and a thread 'T' can be in multiple categories that link together with a thread_link 'TL'. Thread posts 'TP' contain the individual posts within a thread topic. I want to select the top 20 thread topics 'T', for a particular category based on the number of posts within that thread 'TP'. I also want to calculate the latest post date (when the last post was added). The tables work fine, the results are fine... it just ISN'T FAST! Especially if there are a lot of threads for that particular category. Here is the explain data: +---++---++-+-+--+-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---++---++-+-+--+-+ | C | const | PRIMARY | PRIMARY| 4 | const |1 | Using temporary; Using filesort | | TL| ref| CAT_INDEX | CAT_INDEX | 4 | const | 105 | Using where | | T | eq_ref | PRIMARY | PRIMARY| 4 | TL.product_id |1 | | | i | eq_ref | PRIMARY | PRIMARY| 5 | TL.thread_id,const |1 | Using where | | TP| ref| thread_id| thread_id | 4 | TL.thread_id |2 | Using where | +---++---++-+-+--+-+ 5 rows in set (0.00 sec) I think the problem may be with 'Using Temporary; Using Filesort' probably due to the GROUP BY??? Here are the indexes: thread: - thread_id (PK) thread_link: - thread_id, category_id (combined PK) - category_id (CAT_INDEX) thread_post: - thread_id, post_id (combined PK) category: - category_id thread_image: - thread_id,display_type (combined PK) - thread_id (INDEX) Major kudos to whom ever can help me out with this!! - John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: In search of a good MySQL GUI client
Probably you should put it as a bug in SQLyogs forums. Karam --- Jose_Miguel_Pérez [EMAIL PROTECTED] wrote: Karam Chand said: I use SQLyog. It does allow you to create/delete FKs with ON UPDATE criterias. Yeah, it does not allow you to create Fks in graphical manner like MS SQL Server but does my purpose. One feature I really miss is Editing of FKs. Yeah, I also tried SQLyog. It's a very good program indeed, a very nice feature is the structure syncronization tool. However, I said every program I tried has problems, and SQLyog is no exception. I downloaded SQLyog v3.71, I'm using MySQL 4.1.4-gamma-standard. When I manage relationships (F10 key), the FK's not even show up on the list... If I now press New.. to create a relationship, I can fill in the blanks and press Create, however SQLyog didn't show the new relationship either. I ended up creating 5 (five) equal relationships since SQLyog wasn't showing them in the list. A nasty GUI bug, sure, but this renders SQLyog unuseable to me. Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help urgent please
-- help please please tell me how to extract a script file of a database from mysql commandprompt. s.deepak This life is a hard fact; work your way through it boldly, though it may be adamantine; no matter, the soul is stronger Swami Vivekananda Are you looking for mysqldump?? If you want to produce a dump file of a table: mysqldump -u yourusername -p --add-drop-table dbname tablename tablename.sql to do the whole database: mysqldump -u yourusername -p --add-drop-table dbname dbname.sql also do man mysqldump or see http://dev.mysql.com/doc/mysql/en/mysqldump.html The --add-drop-table will add a DROP TABLE IF EXISTS tablename to your script before creating and populating the tables. This is useful when restoring a possibly corrupt table. Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help urgent please
One thing Jim didn't mention is that mysqldump is not a mysql client command but a standalone executable. Run it from a shell prompt (DOS prompt if you are using windows) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jim Grill [EMAIL PROTECTED] wrote on 09/09/2004 11:47:55 AM: -- help please please tell me how to extract a script file of a database from mysql commandprompt. s.deepak This life is a hard fact; work your way through it boldly, though it may be adamantine; no matter, the soul is stronger Swami Vivekananda Are you looking for mysqldump?? If you want to produce a dump file of a table: mysqldump -u yourusername -p --add-drop-table dbname tablename tablename.sql to do the whole database: mysqldump -u yourusername -p --add-drop-table dbname dbname.sql also do man mysqldump or see http://dev.mysql.com/doc/mysql/en/mysqldump.html The --add-drop-table will add a DROP TABLE IF EXISTS tablename to your script before creating and populating the tables. This is useful when restoring a possibly corrupt table. Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multiple MysQL servers with different IP address on same machine
Hi We have a machine with 2 IP addresses and mysql 3.23 on one and 4.10 on the other. Both using port 3306 One instance listens on localhost, which maps to 127.0.0.1, and also on one of the public IP addreses and the other listens to the other IP address. I use the IP address in the connection string and so far it works fine. I am in the process of setting up the server, and only have phpmyadmin installed (twice - one installation per mysql server) but that works correctly, so I expect everything else will. HTH Peter -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 09 September 2004 14:53 To: Sanjeev Sagar Cc: [EMAIL PROTECTED]; Sanjeev Sagar Subject: Re: Multiple MysQL servers with different IP address on same machine I need to add to my previous post -- You asked about using the SAME operating system socket as well as using separate addresses with the same port number (different IP sockets) My answer to that is NOT ON YOUR LIFE. Think of the chaos. If one client tried to connect to an OS socket that 3 different servers were listening to... Which one gets the connection? Which one validates the client? If for some reason the client *were* able to validate against all three servers at the same time, how could it sort out the 3 different responses to a query? NO each server must have it's own socket. It doesn't matter if we are discussing IP sockets or OS sockets the answer is still the same. Sorry for the previous oversight, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Sanjeev Sagar [EMAIL PROTECTED] wrote on 09/08/2004 05:04:38 PM: Hello All, MySQL : Standar Binary 4.0.20 O/S : Red Hat Linux release 9 (Shrike) Linux 2.4.20-31.9smp #1 SMP Tue Apr 13 17:40:10 EDT 2004 i686 i686 i386 GNU/Linux I already have setup of Three Multiple MySQL servers listening on different ports and sockets on same machine Option File: [mysqld1] server-id =1 port=3306 socket=/tmp/mysql.sock datadir=data1 [mysqld2] server-id=2 port=3307 socket=/tmp/mysql.sock2 datadir=data2 [mysqld3] server-id=3 port=3308 socket=/tmp/mysql.sock3 datadir=data3 All three servers started with no problem. Question is if I don't want to use different ports or scokets, can I use the different I.P. Addresses on same machine for three servers with same default port or socket. /etc/hosts file === 127.0.0.100 s1 127.0.0.101 s2 127.0.0.102 s3 Can I start three servers on same port (3306), same socket (/tmp/mysql.sock) on same machine by using above IP addresses? If yes then HOW? Can I use the replication in b/w them? keeping datadir and log-bin directory differtent is not a problem. Appreciate it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: In search of a good MySQL GUI client
On Thursday, September 09, 2004 - Karam Chand said: Probably you should put it as a bug in SQLyogs forums. I have put a bug request into the MySQL Query Browser bug track instead. I like this tool very much, it has some very nice features like the multithreaded results fetching. Cheers, Jose Miguel. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query very slow - Using temporary; Using filesort
I know you said this was a translation of your original query. Assuming that it is a faithful translation, I have the following suggestions: Do not enclose numbers with quotes (category_id is a number, right? No quotes are needed) You do not need include the table category in this query. You select no data from it and refer to it only using it's category_id. That value you already have on the thread_link table so you do not need any other tables to be able to use it. Rev 1: SELECT Count(TP.thread_ID) as num_posts , Max(TP.post_date) as latest_date , T.thread_ID , T.thread_name , i.post_image FROM thread as T INNER JOIN thread_link as TL ON TL.thread_id = t.thread_id AND TL.category_id = 8759 INNER JOIN thread_post as TP ON TP.thread_id = t.thread_id AND tp.rating 0 INNER JOIN thread_image as i ON i.thread_id = T.thread_id AND i.display_type = 'thumbnail' You need the count of # of posts and the latest date based on the Thread ID, and you know which threads to aggregate based on the category it's in. So, you could start by collecting into a temp table only the basic information you need for your report. This minimizes the size of the intermediate tables so that the GROUP BY can go much faster. Then join to your temp table any other tables that you need in order to fill in the rest of your columns Rev 2: CREATE TEMPORARY TABLE tmpStats (KEY(thread_id)) SELECT TP.thread_ID Count(TP.thread_ID) as num_posts , Max(TP.post_date) as latest_date FROM thread_posts as TP INNER JOIN thread_link TL ON TP.thread_id = TL.thread_id WHERE TL.category_ID = 8759 GROUP BY 1 ORDER BY 2,3 DESC; SELECT ts.num_posts, ts.latest_date, ts.thread_id, t.thread_name, i.post_image FROM tmpStats ts INNER JOIN thread t on t.thread_id = ts.thread_id INNER JOIN thread_image i on i.thread_id = ts.thread_id; drop table tmpStats; You get the same results as the all-in-one query but by breaking it into smaller steps, you save the engine a metric butt-load (trust me, it's a rather large unit of measure) of intermediate processing. Just the difference in joining 20 records (and not the entire thread_posts table) to the thread and thread_image tables will save you several seconds. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 09/09/2004 11:36:18 AM: I'm having a bit of a problem with a query that takes a very long time (up to 1 minute) when many matching rows are found. The tables are all indexed and the explain seems to indicate that mysql is using the indexes but it is still painfully slow: mysql SELECT COUNT(TP.thread_id) AS num_posts, MAX(TP.post_date) AS latest_date,T.thread_id, T.thread_name, i.post_image FROM category AS C - INNER JOIN thread_link AS TL USE INDEX(CAT_INDEX) ON C. category_id = TL.category_id - INNER JOIN thread AS T ON TL.thread_id = T.thread_id - INNER JOIN thread_image AS i ON TL.thread_id = i.thread_id - INNER JOIN thread_post AS TP USE INDEX(thread_id) ON TL. thread_id = TP.thread_id - WHERE C.category_id = '8759' AND i.display_type = 'thumbnail' AND TP.rating 0 - GROUP BY TL.thread_id - ORDER BY 'num_posts' DESC - LIMIT 0 , 20; ... results ... 20 rows in set (37.37 sec) The above query is a hypothetical query (hint: I'm not doing a forum db) but it pretty much matches what I'm doing. In this case there are many categories 'C' and a thread 'T' can be in multiple categories that link together with a thread_link 'TL'. Thread posts 'TP' contain the individual posts within a thread topic. I want to select the top 20 thread topics 'T', for a particular category based on the number of posts within that thread 'TP'. I also want to calculate the latest post date (when the last post was added). The tables work fine, the results are fine... it just ISN'T FAST! Especially if there are a lot of threads for that particular category. Here is the explain data: +---++---++- +-+--+-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---++---++- +-+--+-+ | C | const | PRIMARY | PRIMARY| 4 | const |1 | Using temporary; Using filesort | | TL| ref| CAT_INDEX | CAT_INDEX | 4 | const | 105 | Using where | | T | eq_ref | PRIMARY | PRIMARY| 4 | TL. product_id |1 | | | i | eq_ref | PRIMARY | PRIMARY| 5 | TL. thread_id,const |1 | Using where | | TP| ref| thread_id| thread_id
RE: Multiple MysQL servers with different IP address on same machine
Actually mysqld parameter bind-address work great for different IP addresses on same port for different servers on same machine. One can use -h hostname for clients connection to a specific MySQL database server. Thanks ! -Original Message- From: Peter Lovatt [mailto:[EMAIL PROTECTED] Sent: Thu 9/9/2004 9:51 AM To: [EMAIL PROTECTED]; Sanjeev Sagar Cc: [EMAIL PROTECTED]; Sanjeev Sagar Subject: RE: Multiple MysQL servers with different IP address on same machine Hi We have a machine with 2 IP addresses and mysql 3.23 on one and 4.10 on the other. Both using port 3306 One instance listens on localhost, which maps to 127.0.0.1, and also on one of the public IP addreses and the other listens to the other IP address. I use the IP address in the connection string and so far it works fine. I am in the process of setting up the server, and only have phpmyadmin installed (twice - one installation per mysql server) but that works correctly, so I expect everything else will. HTH Peter -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 09 September 2004 14:53 To: Sanjeev Sagar Cc: [EMAIL PROTECTED]; Sanjeev Sagar Subject: Re: Multiple MysQL servers with different IP address on same machine I need to add to my previous post -- You asked about using the SAME operating system socket as well as using separate addresses with the same port number (different IP sockets) My answer to that is NOT ON YOUR LIFE. Think of the chaos. If one client tried to connect to an OS socket that 3 different servers were listening to... Which one gets the connection? Which one validates the client? If for some reason the client *were* able to validate against all three servers at the same time, how could it sort out the 3 different responses to a query? NO each server must have it's own socket. It doesn't matter if we are discussing IP sockets or OS sockets the answer is still the same. Sorry for the previous oversight, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Sanjeev Sagar [EMAIL PROTECTED] wrote on 09/08/2004 05:04:38 PM: Hello All, MySQL : Standar Binary 4.0.20 O/S : Red Hat Linux release 9 (Shrike) Linux 2.4.20-31.9smp #1 SMP Tue Apr 13 17:40:10 EDT 2004 i686 i686 i386 GNU/Linux I already have setup of Three Multiple MySQL servers listening on different ports and sockets on same machine Option File: [mysqld1] server-id =1 port=3306 socket=/tmp/mysql.sock datadir=data1 [mysqld2] server-id=2 port=3307 socket=/tmp/mysql.sock2 datadir=data2 [mysqld3] server-id=3 port=3308 socket=/tmp/mysql.sock3 datadir=data3 All three servers started with no problem. Question is if I don't want to use different ports or scokets, can I use the different I.P. Addresses on same machine for three servers with same default port or socket. /etc/hosts file === 127.0.0.100 s1 127.0.0.101 s2 127.0.0.102 s3 Can I start three servers on same port (3306), same socket (/tmp/mysql.sock) on same machine by using above IP addresses? If yes then HOW? Can I use the replication in b/w them? keeping datadir and log-bin directory differtent is not a problem. Appreciate it.
when to use backquote in SQL
Could anyone link me or explain the purposes of backquotes in an SQL statement. I tried searching the manual and googling it but couldn't find a simple explaination. ``` vs. regular single quotes'''. Thanks, Lee G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: when to use backquote in SQL
In the last episode (Sep 09), leegold said: Could anyone link me or explain the purposes of backquotes in an SQL statement. I tried searching the manual and googling it but couldn't find a simple explaination. ``` vs. regular single quotes'''. Thanks, Lee G. Backquotes are used to delimit table or field names; they aren't used to delimit SQL strings the way ' or are. You'll almost never need to use them unless you have spaces or other strange characters in your table/field names. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: when to use backquote in SQL
Dan Nelson wrote: In the last episode (Sep 09), leegold said: Could anyone link me or explain the purposes of backquotes in an SQL statement. I tried searching the manual and googling it but couldn't find a simple explaination. ``` vs. regular single quotes'''. Thanks, Lee G. Backquotes are used to delimit table or field names; they aren't used to delimit SQL strings the way ' or are. You'll almost never need to use them unless you have spaces or other strange characters in your table/field names. ...or when you chose a reserved MySQL keyword as a column name - for example. ...which might happen automatically when you upgrade to a new version of MySQL :-)) Your column name suddenly becoming a keyword is a _lot_ fun ;-), so 'don't forget your backticks'. - Cs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
WHY this query keeps failure?
Hello, I was working on a table, doing a simple update on table. Query is: Update temp SET Q1 = 14, REVIEWCOMMENTS = CASE WHEN REVIEWCOMMENTS='WHO2' THEN '' WHEN REVIEWCOMMENTS LIKE '%,WHO2' THEN TRIM(TRAILING ',WHO2' FROM REVIEWCOMMENTS) WHEN REVIEWCOMMENTS LIKE 'WHO2,%' THEN TRIM(LEADING 'WHO2,' FROM REVIEWCOMMENTS) ELSE REPLACE(REVIEWCOMMENTS, 'WHO2,', '') END WHERE QID IN (3029,3041,3053,3076,3120,3121,3128,3133,3134); It runs well, shows how many rows was affected. Then I did query to pull out all updated records: select qid, qd5,q1, reviewcomments from temp where qid IN (3029,3041,3053,3076,3120,3121,3128,3133,3134) order by qid asc; There is no records return. The table is empty. This happened second time. So Im wondering it might have some problem with my query. Thanks a lot Monet __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: when to use backquote in SQL
In the last episode (Sep 09), leegold said: Could anyone link me or explain the purposes of backquotes in an SQL statement. I tried searching the manual and googling it but couldn't find a simple explaination. ``` vs. regular single quotes'''. Thanks, Lee G. Backquotes are used to delimit table or field names; they aren't used to delimit SQL strings the way ' or are. You'll almost never need to use them unless you have spaces or other strange characters in your table/field names. -- Dan Nelson [EMAIL PROTECTED] Backtics can also be useful to avoid SQL injections if an application your working on requires table names or field names to be supplied from user input (always a bad idea) like a select box. Jim Grill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: when to use backquote in SQL
Single and double quotes are usually string identifiers (double quoted strings can sometimes also refer to database objects) Backticks (backquotes) always refer to database objects (columns, tables, indexes, databases, etc.). Here is the page in the manual that explains it all. http://dev.mysql.com/doc/mysql/en/Legal_names.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine leegold [EMAIL PROTECTED] wrote on 09/09/2004 02:00:32 PM: Could anyone link me or explain the purposes of backquotes in an SQL statement. I tried searching the manual and googling it but couldn't find a simple explaination. ``` vs. regular single quotes'''. Thanks, Lee G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query very slow - Using temporary; Using filesort
Thanks Shawn, I'm going to give the temporary table idea a try. I did omit the 'category' table while testing but when I used EXPLAIN the # of rows for the thread_link join increased from 105 to 16326 so I decided to leave the category table in. But I agree, it isn't needed. My other idea I had was to store the number of 'thread_posts' for a thread inside the thread table itself. Obviously this wouldn't be normalized and would have to be maintained or updated frequently to be accurate... It might be a last resort if the temp table doesn't work out. ... But I like to play by normalization rules as much as possible. Thanks again, I'll post any performance improvements. - John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: when to use backquote in SQL
In the last episode (Sep 09), Fagyal Csongor said: Dan Nelson wrote: In the last episode (Sep 09), leegold said: Could anyone link me or explain the purposes of backquotes in an SQL statement. I tried searching the manual and googling it but couldn't find a simple explaination. ``` vs. regular single quotes'''. Thanks, Lee G. Backquotes are used to delimit table or field names; they aren't used to delimit SQL strings the way ' or are. You'll almost never need to use them unless you have spaces or other strange characters in your table/field names. ...or when you chose a reserved MySQL keyword as a column name - for example. ...which might happen automatically when you upgrade to a new version of MySQL :-)) Your column name suddenly becoming a keyword is a _lot_ ;-), fun so 'don't forget your backticks'. I forgot about that case, which is probably why glue drivers like MyODBC end up quoting everything. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
License question
Hi, Sorry to ask this in here. If it's not the right place please ignore the post. I want to know if someone could claim a license upon an application wich was developed using Php and a non-commercially-licensed copy of MySql. I mean, the application is designed to work only with MySql as database engine and the MySql package delivered with the application, is licensed under GPL. Could the developer claim License rights upon the use of such a combination ? Thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHY this query keeps failure?
- Original Message - From: Monet [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Thursday, September 09, 2004 2:13 PM Subject: WHY this query keeps failure? Hello, I was working on a table, doing a simple update on table. Query is: Update temp SET Q1 = 14, REVIEWCOMMENTS = CASE WHEN REVIEWCOMMENTS='WHO2' THEN '' WHEN REVIEWCOMMENTS LIKE '%,WHO2' THEN TRIM(TRAILING ',WHO2' FROM REVIEWCOMMENTS) WHEN REVIEWCOMMENTS LIKE 'WHO2,%' THEN TRIM(LEADING 'WHO2,' FROM REVIEWCOMMENTS) ELSE REPLACE(REVIEWCOMMENTS, 'WHO2,', '') END WHERE QID IN (3029,3041,3053,3076,3120,3121,3128,3133,3134); It runs well, shows how many rows was affected. Then I did query to pull out all updated records: select qid, qd5,q1, reviewcomments from temp where qid IN (3029,3041,3053,3076,3120,3121,3128,3133,3134) order by qid asc; There is no records return. The table is empty. Do you mean that your *result set* from the query is empty? Or that the *table* you are reading from (temp) is empty? You said 'table' but I *think* you mean 'result set', right? If temp is empty, your result set from the Select will certainly be empty; that should be obvious: the question is WHY temp is empty. Your table, temp, should not be empty as a result of your update statement because Update does not remove rows and your Update didn't change the 'qid' value. If Update changed 9 rows and MySQL told you that 9 rows were changed, you should still have at least those 9 rows in the table after the update has completed. You can verify that by doing: select count(*) from temp; immediately after running the update. If it returns a value of 0, your table is empty. Otherwise there are rows in the table. This happened second time. So I'm wondering it might have some problem with my query. I don't see anything in the Update or the Select that explains this problem. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Perl with MySQL
Hello: I am trying to install Perl support with MySQL. After installing MySQL (v4.0.20)I run the following commands: % echo $PATH % perl -MCPAN -e shell Note: Answer no to auto-configure perl. cpan install Data::Dumper (Upto this point. Following commands are not run yet.) cpan install Bundle::DBI cpan install Bundle::DBD::mysql cpan quit Today, when I tried to install Perl using the above sequence of commands. However, after I entered the third command cpan install Data::Dumper;, a message was displayed indicating that there is a new version of perl it canbe installed by using the command cpan install Bundle::CPAN. Well, I changed the commands to as follows: % echo $PATH % perl -MCPAN -e shell Note: Answer no to auto-configure perl. cpan install Data::Dumper cpan install Bundle::CPAN cpan install Bundle::DBI cpan install Bundle::DBD::mysql cpan quit I am not sure if the above command sequence is correct or not? I know about Perl as much as I know about brain surgery. However, I am willing to read if I know where. Thanks in advance. Kirti PS: I have no idea id I posted this or not. So if it is duplicate, please ignore. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Estimating Query Performance
G'day all, I was hoping to leech from your amalgamated knowledge: I've been asked to estimate the query performance of several SQL queries that power our Reporting system. At the moment we're preparing to scale up enormously the amount of data we're using in our system, and therefore I'm trying to estimate the length of time these queries will take. We are using Red Hat 7.2/MySQL 3.23.49a I believe (I know, I know - dont ask why). Refering to the manual, section 7.2.2 Estimating Performance (http://dev.mysql.com/doc/mysql/en/Estimating_performance.html). I have an issue with the values for the equation given: log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1 seeks to find a row. Lets take one of my example tables: row_count - 1,024,306 (will soon be ~23,250,000) rows. index_block_length - ? index_length - ? data_pointer_length - ? * data_pointer_length index_block_length :- I know the manual states: MySQL an index block is usually 1024 bytes and the data pointer is usually 4 bytes My issue is, what is meant by *usually*. How can I check. Should I just use these. * index_length :- For this table, it has the following indexes: PRIMARY KEY (`ID`), - ID is INT(11) KEY `LogTimeIdx` (`LogTime`), - LogTime is TIMESTAMP(14) KEY `signid` (`SignID`) - SignID is INT(11) So what would be my index length? * SHOW TABLE STATUS tells me: rows: 1,024,306 avg_row_length: 51 data_length: 52,543,348 index_length: 32,238,592 Any help with the values I should be using or any guidance on estimating a Queries Performance would be unimaginably appreciated. Regards, Matt. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
state my question more clearly Re: WHY this query keeps failure?
Yes, you're right. Let me explain it more clearly. Before UPDATE, there are 45 records in table temp and I updated 9 of them. Mysql returns how many rows were affected which is 9 rows. Then, I opened the table temp and found that table is empty!No records at all. Therefore, that is why I feel so wired. after a simple update, all records has been erased. does anyone have same problem before? Thanks, Monet --- Rhino [EMAIL PROTECTED] wrote: - Original Message - From: Monet [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Thursday, September 09, 2004 2:13 PM Subject: WHY this query keeps failure? Hello, I was working on a table, doing a simple update on table. Query is: Update temp SET Q1 = 14, REVIEWCOMMENTS = CASE WHEN REVIEWCOMMENTS='WHO2' THEN '' WHEN REVIEWCOMMENTS LIKE '%,WHO2' THEN TRIM(TRAILING ',WHO2' FROM REVIEWCOMMENTS) WHEN REVIEWCOMMENTS LIKE 'WHO2,%' THEN TRIM(LEADING 'WHO2,' FROM REVIEWCOMMENTS) ELSE REPLACE(REVIEWCOMMENTS, 'WHO2,', '') END WHERE QID IN (3029,3041,3053,3076,3120,3121,3128,3133,3134); It runs well, shows how many rows was affected. Then I did query to pull out all updated records: select qid, qd5,q1, reviewcomments from temp where qid IN (3029,3041,3053,3076,3120,3121,3128,3133,3134) order by qid asc; There is no records return. The table is empty. Do you mean that your *result set* from the query is empty? Or that the *table* you are reading from (temp) is empty? You said 'table' but I *think* you mean 'result set', right? If temp is empty, your result set from the Select will certainly be empty; that should be obvious: the question is WHY temp is empty. Your table, temp, should not be empty as a result of your update statement because Update does not remove rows and your Update didn't change the 'qid' value. If Update changed 9 rows and MySQL told you that 9 rows were changed, you should still have at least those 9 rows in the table after the update has completed. You can verify that by doing: select count(*) from temp; immediately after running the update. If it returns a value of 0, your table is empty. Otherwise there are rows in the table. This happened second time. So I'm wondering it might have some problem with my query. I don't see anything in the Update or the Select that explains this problem. Rhino __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: License question
At 16:30 -0700 9-09-2004, Mauricio Pellegrini wrote: Hi, Sorry to ask this in here. If it's not the right place please ignore the post. I want to know if someone could claim a license upon an application wich was developed using Php and a non-commercially-licensed copy of MySql. I mean, the application is designed to work only with MySql as database engine and the MySql package delivered with the application, is licensed under GPL. Could the developer claim License rights upon the use of such a combination ? Thanks Mauricio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Yes GPL said that the source code must be delivered with the application (or it is available as FTP, mail, ...). The user can distribute the application without asking for a fee. About two years ago I read a FAQ that asserts about a moderate costs. The license is more restrictive about copyright and distribution but it doesn't contain any money issue. If you have some question you can write to gnu.org or you can report an abuse. See http://www.gnu.org Santino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there any performance reason to use unique index
Hi: I was wondering if there is any performance-related reason to use a unique index versus a standard index? Is the only benefit of a unique index that it will prevent duplicate values from being inserted into a table unless explicitly allowed? I have a column, colA, that I know contains only unique values because I create the table using a 'GROUP BY colA' clause. Before I use the table for any more work, I want to index colA. This table will have no further rows added to it, so I don't need to worry about a potential duplicate value being inserted. I was just wondering if it was a performance gain/hit to use a unique index, or if the difference was negligible. Thanks. Wes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Estimating Query Performance
On Fri, 10 Sep 2004 05:54:42 +1000, Matthew Boulter [EMAIL PROTECTED] wrote: snip/ Any help with the values I should be using or any guidance on estimating a Queries Performance would be unimaginably appreciated. This is an area in which I felt better armed when I used Oracle. I'm curious to see whether anyone has come up with some practical ideas here, too. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there any performance reason to use unique index
The uniqueness constraint would only be enforced during an INSERT or an UPDATE. If your table is read-only, declaring the index as UNIQUE will be overkill. I can't tell you about any kind of performance hit during reading but I try to follow the maxim don't ask for it if you won't need it. I would use just a straight index. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Wesley Furgiuele [EMAIL PROTECTED] wrote on 09/09/2004 04:36:50 PM: Hi: I was wondering if there is any performance-related reason to use a unique index versus a standard index? Is the only benefit of a unique index that it will prevent duplicate values from being inserted into a table unless explicitly allowed? I have a column, colA, that I know contains only unique values because I create the table using a 'GROUP BY colA' clause. Before I use the table for any more work, I want to index colA. This table will have no further rows added to it, so I don't need to worry about a potential duplicate value being inserted. I was just wondering if it was a performance gain/hit to use a unique index, or if the difference was negligible. Thanks. Wes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: state my question more clearly Re: WHY this query keeps failure?
I don't think your problem has anything to do with your Update statement or Select statements, assuming you reported them accurately. Could another user of the system have emptied your table? Could you have inadvertently executed a statement or a script that would have emptied it? Those seem like the obvious explanations to me. If neither of those is the cause, you may have stumbled on a really serious bug. Rhino - Original Message - From: Monet [EMAIL PROTECTED] To: Rhino [EMAIL PROTECTED]; mysql [EMAIL PROTECTED] Sent: Thursday, September 09, 2004 4:02 PM Subject: state my question more clearly Re: WHY this query keeps failure? Yes, you're right. Let me explain it more clearly. Before UPDATE, there are 45 records in table temp and I updated 9 of them. Mysql returns how many rows were affected which is 9 rows. Then, I opened the table temp and found that table is empty!No records at all. Therefore, that is why I feel so wired. after a simple update, all records has been erased. does anyone have same problem before? Thanks, Monet --- Rhino [EMAIL PROTECTED] wrote: - Original Message - From: Monet [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Thursday, September 09, 2004 2:13 PM Subject: WHY this query keeps failure? Hello, I was working on a table, doing a simple update on table. Query is: Update temp SET Q1 = 14, REVIEWCOMMENTS = CASE WHEN REVIEWCOMMENTS='WHO2' THEN '' WHEN REVIEWCOMMENTS LIKE '%,WHO2' THEN TRIM(TRAILING ',WHO2' FROM REVIEWCOMMENTS) WHEN REVIEWCOMMENTS LIKE 'WHO2,%' THEN TRIM(LEADING 'WHO2,' FROM REVIEWCOMMENTS) ELSE REPLACE(REVIEWCOMMENTS, 'WHO2,', '') END WHERE QID IN (3029,3041,3053,3076,3120,3121,3128,3133,3134); It runs well, shows how many rows was affected. Then I did query to pull out all updated records: select qid, qd5,q1, reviewcomments from temp where qid IN (3029,3041,3053,3076,3120,3121,3128,3133,3134) order by qid asc; There is no records return. The table is empty. Do you mean that your *result set* from the query is empty? Or that the *table* you are reading from (temp) is empty? You said 'table' but I *think* you mean 'result set', right? If temp is empty, your result set from the Select will certainly be empty; that should be obvious: the question is WHY temp is empty. Your table, temp, should not be empty as a result of your update statement because Update does not remove rows and your Update didn't change the 'qid' value. If Update changed 9 rows and MySQL told you that 9 rows were changed, you should still have at least those 9 rows in the table after the update has completed. You can verify that by doing: select count(*) from temp; immediately after running the update. If it returns a value of 0, your table is empty. Otherwise there are rows in the table. This happened second time. So I'm wondering it might have some problem with my query. I don't see anything in the Update or the Select that explains this problem. Rhino __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is there any performance reason to use unique index
A unique index can actually be faster than a regular index. The reason is that MySQL knows that there can only be a single matching row for each value. In particular, this allows you to get const and eq_ref for the type in an EXPLAIN, which are two of the fastest methods of table access. Even if you aren't use these access methods, it will never be any slower to access than a regular index. Keep in mind that it will take longer to build the index in the first place, and make your decision appropriately. Regards, Harrison On Thursday, September 9, 2004, at 05:01 PM, [EMAIL PROTECTED] wrote: The uniqueness constraint would only be enforced during an INSERT or an UPDATE. If your table is read-only, declaring the index as UNIQUE will be overkill. I can't tell you about any kind of performance hit during reading but I try to follow the maxim don't ask for it if you won't need it. I would use just a straight index. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Wesley Furgiuele [EMAIL PROTECTED] wrote on 09/09/2004 04:36:50 PM: Hi: I was wondering if there is any performance-related reason to use a unique index versus a standard index? Is the only benefit of a unique index that it will prevent duplicate values from being inserted into a table unless explicitly allowed? I have a column, colA, that I know contains only unique values because I create the table using a 'GROUP BY colA' clause. Before I use the table for any more work, I want to index colA. This table will have no further rows added to it, so I don't need to worry about a potential duplicate value being inserted. I was just wondering if it was a performance gain/hit to use a unique index, or if the difference was negligible. Thanks. Wes -- 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]
3 tables
Dears, I have a bank with 3 tables. tbl_1 : username password tbl_2 : personal information tbl_3 : user accountting Each row related one.Record 3 from tbl_1 related to recorde 3 from another tbl. If i want to reduce overhead,How i do it? Please guide me. Yours,Mohsen = -DIGITAL SIGNATURE--- ///Mohsen Pahlevanzadeh Network administrator programmer My home phone is: +98213810146 My email address is m_pahlevanzadeh at yahoo dot com My website is: http://webnegar.net __ Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
List of MySQL Keywords
Hello All: I would like to make up a complete (if possible) list of MySQL query keywords. I would appreciate pointers to documentation that might hold such a list, without too much extraneous or extra text. In my current documentation, the Manual Function Index is a good source, but if I could find something with less extra text, that would be great. TIA tim -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: List of MySQL Keywords
In the last episode (Sep 09), Tim Johnson said: Hello All: I would like to make up a complete (if possible) list of MySQL query keywords. I would appreciate pointers to documentation that might hold such a list, without too much extraneous or extra text. In my current documentation, the Manual Function Index is a good source, but if I could find something with less extra text, that would be great. Take a look at sql/lex.h in the source. There are two arays: symbols[] and sql_functions[]. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: List of MySQL Keywords
- Original Message - From: Tim Johnson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 09, 2004 9:36 PM Subject: List of MySQL Keywords Hello All: I would like to make up a complete (if possible) list of MySQL query keywords. I would appreciate pointers to documentation that might hold such a list, without too much extraneous or extra text. In my current documentation, the Manual Function Index is a good source, but if I could find something with less extra text, that would be great. How about the table on this page? http://dev.mysql.com/doc/mysql/en/Reserved_words.html Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query very slow - Using temporary; Using filesort
After Shawn's guidance, I tried inserting with the temporary table method using this: CREATE TEMPORARY TABLE tmpStats (KEY(product_id)) SELECT TP.thread_id, COUNT(TP.thread_id) AS num_posts, MAX(TP.post_date) AS last_update FROM thread_post AS TP INNER JOIN thread_link AS TL ON TP.thread_id = TL.thread_id WHERE TL.category_id = 123456 AND TP.post_date 1999 GROUP BY TP.thread_id; SELECT TS.num_posts, TS.last_update, TS.thread_id, T.thread_name, i.thread_image FROM tmpStats AS TS INNER JOIN thread AS T ON T.tread_id = TS.thread_id INNER JOIN image AS iON i.thread_id = TS.thread_id AND i.display_type = 'thumbnail' ORDER BY TS.num_posts DESC LIMIT 0, 20; I did some testing and here is the performance on various categories in the form of: #Rows - Time for insert statement - Time for select statement --- 310 rows - 1.56 sec - 0.20 sec 1964 - 4.71 - 0.08 1264 - 1.98 - 0.17 51677- 43.31- 0.12 Then I went back to the old way but it was about 3 to 5 times slower than the above!! I couldn't think of any reason for the extreme slowness (it wasn't that slow before) so I restarted mysql (service mysql restart). Somewhat surprisingly, queries started running much faster. After the restart I got these stats: #Rows - INSERT - SELECT -- 680 - 1.91 - 0.18 1373 - 1.77 - 0.10 4518 - 2.99 - 0.04 6131 - 5.29 - 0.08 6938 - 2.86 - 0.27 6993 - 3.69 - 0.04 9133 - 10.45 - 0.02 18793 - 9.80 - 0.02 24783 - 6.36 - 0.02 The old non-temp table query produced the following: #Rows - SELECT TIME -- 317 - 1.78 388 - 0.89 3721 - 1.93 6025 - 1.83 51677 - 8.54 Neither query seems to be blazing fast. It's also strange to me that restarting mysql would have such a performance benefit. The server is a dedicated mysql server: dual 2 GHz Xeon with 2GB RAM, no raid, no slaves (yet). Seems like queries involving only a few thousand rows should execute faster..??? - John I know you said this was a translation of your original query. Assuming that it is a faithful translation, I have the following suggestions: Do not enclose numbers with quotes (category_id is a number, right? No quotes are needed) You do not need include the table category in this query. You select no data from it and refer to it only using it's category_id. That value you already have on the thread_link table so you do not need any other tables to be able to use it. Rev 1: SELECT Count(TP.thread_ID) as num_posts , Max(TP.post_date) as latest_date , T.thread_ID , T.thread_name , i.post_image FROM thread as T INNER JOIN thread_link as TL ON TL.thread_id = t.thread_id AND TL.category_id = 8759 INNER JOIN thread_post as TP ON TP.thread_id = t.thread_id AND tp.rating 0 INNER JOIN thread_image as i ON i.thread_id = T.thread_id AND i.display_type = 'thumbnail' You need the count of # of posts and the latest date based on the Thread ID, and you know which threads to aggregate based on the category it's in. So, you could start by collecting into a temp table only the basic information you need for your report. This minimizes the size of the intermediate tables so that the GROUP BY can go much faster. Then join to your temp table any other tables that you need in order to fill in the rest of your columns Rev 2: CREATE TEMPORARY TABLE tmpStats (KEY(thread_id)) SELECT TP.thread_ID Count(TP.thread_ID) as num_posts , Max(TP.post_date) as latest_date FROM thread_posts as TP INNER JOIN thread_link TL ON TP.thread_id = TL.thread_id WHERE TL.category_ID = 8759 GROUP BY 1 ORDER BY 2,3 DESC; SELECT ts.num_posts, ts.latest_date, ts.thread_id, t.thread_name, i.post_image FROM tmpStats ts INNER JOIN thread t on t.thread_id = ts.thread_id INNER JOIN thread_image i on i.thread_id = ts.thread_id; drop table tmpStats; You get the same results as the all-in-one query but by breaking it into smaller steps, you save the engine a metric butt-load (trust me, it's a rather large unit of measure) of intermediate processing. Just the difference in joining 20 records (and not the entire thread_posts table) to the thread and thread_image tables will save you several seconds. Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 09/09/2004 11:36:18 AM: I'm having a bit of a problem with a query that takes a very long time (up to 1 minute) when many matching rows are found. The tables are all indexed and the explain seems to indicate that mysql is using the indexes but it is still painfully slow: mysql SELECT COUNT(TP.thread_id) AS num_posts, MAX(TP.post_date) AS latest_date,T.thread_id, T.thread_name, i.post_image FROM category AS C - INNER JOIN thread_link AS TL USE INDEX(CAT_INDEX) ON C. category_id = TL.category_id -
MySQL 4.0.21 has been released
Hi, MySQL 4.0.21, a new version of the popular Open Source/Free Software Database Management System, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bug fix release for the current production version. Please also note that this is the first 4.0.x version to have our FLOSS licensing exception. This exception allows license compatibility with important Open Source/Free Software projects. More information about our FLOSS licensing exception can be found at: http://dev.mysql.com/doc/mysql/en/MySQL_FLOSS_License_Exception.html Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed: * Print VERSION_COMMENT (from `./configure --comment' during compilation) when starting the server. E.g.: `Version: '4.0.21-debug' socket: '/tmp/mysql.sock' port: 0 Official MySQL Binary' * Made the MySQL server not react to signals `SIGHUP' and `SIGQUIT' on Mac OS X 10.3. This is needed because under this OS, the MySQL server receives lots of these signals (reported as Bug #2030). * On Windows, the `mysqld-nt' and `mysqld-max-nt' servers now write error messages to the Windows event log in addition to the MySQL error log. * Renamed the `innodb.status.PID' files (created in the data directory) to `innodb_status.PID'. This avoids problems on filesystems that do not allow multiple periods in filenames. * Added `innodb_status_file' system variable to `mysqld' to control whether output from `SHOW INNODB STATUS' is written to a `innodb_status.PID' file in the data directory. By default, the file is not created. To create it, start `mysqld' with the `--innodb_status_file=1' option. Bugs fixed: * Fixed an old bug in concurrent accesses to `MERGE' tables (even one `MERGE' table and `MyISAM' tables), that could've resulted in a crash or hang of the server. (Bug #2408) * Fixed a bug that caused incorrect results from `GROUP BY' queries with expression in `HAVING' clause that refers to a `BLOB' (`TEXT', `TINYBLOB', etc) fields. (Bug #4358) * Fixed a bug when memory was not released when `HEAP' table is dropped. It could only happen on Windows when a symlink file (.sym) is used and if that symlink file contained double backslashes (\\). (Bug #4973) * Fixed a bug which prevented `TIMESTAMP(19)' fields from being created. (Bug #4491) * Fixed a bug that caused wrong results in queries that were using index to search for `NULL' values in `BLOB' (`TINYBLOB', `TEXT', `TINYTEXT', etc) columns of `MyISAM' tables. (Bug #4816) * Fixed a bug in the function `ROUND()' reporting incorrect metadata (number of digits after the decimal point). It can be seen, for example, in `CREATE TABLE t1 SELECT ROUND(1, 34)'. (Bug #4393) * Fixed precision loss bug in some mathematical functions such as `SQRT()' and `LOG()'. (Bug #4356) * Fixed a long-standing problem with `LOAD DATA' with the `LOCAL' option. The problem occurs when an error happens during the `LOAD DATA' operation. Previously, the connection was broken. Now the error message is returned and connection stays open. * Optimizer now treats `col IN (val)' the same way it does for `col = val'. * Fixed a problem with `net_buffer_length' when building the `DBD::mysql' Perl module. (Bug #4206) * `lower_case_table_names=2' (keep case for table names) was not honored with `ALTER TABLE' and `CREATE/DROP INDEX'. (Bug #3109) * Fixed a crash on declaration of `DECIMAL(0,...)' column. (Bug #4046) * Fixed a bug in `IF()' function incorrectly determining the result type if aggregate functions were involved. (Bug #3987) * Fixed bug in privilege checking where, under some conditions, one was able to grant privileges on the database, he has no privileges on. (Bug #3933) * Fixed crash in `MATCH ... AGAINST()' on a phrase search operator with a missing closing double quote. (Bug #3870) * Fixed a bug with truncation of big values ( 4294967295) of 64-bit system variables. (Bug #3754) * If `server-id' was not set using startup options but with `SET GLOBAL', the replication slave still complained that it was not set. (Bug #3829) * Fixed potential memory overrun in `mysql_real_connect()' (which required a compromised DNS server and certain operating systems). (Bug #4017) * During the installation process of the server RPM on Linux, `mysqld' was run as the `root' system user, and if
