Re: Performance of different length/size datatypes
Just wondering: this link seems to refer to Firebird/InterBase, not MySQL. In particular, MySQL stores Blobs in row, the text describes the out of row storage of Blobs. As a result, I don't think there is much difference in the performance of BLOB and VARCHAR(255) in MySQL (VARCHAR is probably a tick faster). Regards, Paul On Oct 31, 2006, at 8:06 AM, Visolve DB Team wrote: Hi, To BLOB or not to BLOB | Whether to store string in BLOB, or CHAR, or VARCHAR pls play the link: http://www.volny.cz/iprenosil/interbase/ip_ib_strings.htm Thanks ViSolve DB Team. - Original Message - From: Chris W. Parker [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, October 27, 2006 10:35 PM Subject: Performance of different length/size datatypes Hello, Originally I had this long explanation of what I'm doing and why I'm asking this question but I thought I'd just cut to the chase and ask... For a db that doesn't get a lot queries is there much of a performance difference between BLOB and VARCHAR(255)? Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: finding NULL records
Of course this will return an empty set because you have only selected the test_id, try this; SELECT * FROM tests WHERE test_id IS NULL Empty set empty column Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: finding NULL records
SELECT * FROM tests WHERE test_id IS NULL Yes, that works, but I was also trying SELECT * instead of just the key field (just a typo in the example). The problem was in the equal sign versus the 'IS' operator. Any reason why MySQL does not honor field=NULL? Seems kind of odd. Sql standard says you use IS NULL. [/snip] And the reason for that is because nothing is actually equal to NULL. For a field to be equal to NULL it would actually have to contain NULL, in which case it would not be actually NULL. What? NULL doesn't equal NULL because NULL means unknown. A column can have 2 states: known or unknown (NULL). If the case of a known state, the column has a value, in cause of unknown, it's NULL. You cannot compare to unknown. So this is why the SQL standard says: you either ask for a specific value (eg: myid = 1002) or you ask for a state: myid IS NULL or myid IS NOT NULL (= filled). It has nothing to do with contain NULL or whatever. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL memory allocation
Hi, Could you please tell how I can tell how much memory does mysql server allocate on a linux box? I tried doing: top -b -n 1 | grep mysql But, it printed out bunch of processes for mysql. Are these all using shared memory so each line gives you the total amount for mysql? How can one interpret the results of this top, i.e. how should I read Virtual/Resident/Shared columns for all these processes and find out how much shared + (resident + swap: virtual) memory it uses? Results of TOP for mysql: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 8205 mysql 12 0 75524 28M 26896 S 4.8 0.7 3:15 1 mysqld 2150 mysql 9 0 75524 28M 26896 S 0.4 0.7 3:04 1 mysqld 32011 root 9 0 628 500 500 S 0.0 0.0 0:00 0 mysqld_safe 32033 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:08 0 mysqld 32035 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:00 0 mysqld 32036 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:04 0 mysqld 1321 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:51 1 mysqld 1323 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:17 0 mysqld 2112 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:20 0 mysqld 2131 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:04 0 mysqld 2132 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:34 1 mysqld 2133 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:04 1 mysqld 2134 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:12 0 mysqld 2135 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:51 0 mysqld 2136 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:59 1 mysqld 2137 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:43 0 mysqld 2142 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:12 0 mysqld 8080 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:58 0 mysqld 8082 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:52 1 mysqld 8197 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:43 1 mysqld 8204 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:16 1 mysqld 16010 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:51 0 mysqld 16011 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:58 1 mysqld 16138 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:52 1 mysqld 4074 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:16 0 mysqld 17331 mysql 9 0 75524 28M 26896 S 0.0 0.7 1:44 0 mysqld 17337 mysql 9 0 75524 28M 26896 S 0.0 0.7 1:59 0 mysqld 22847 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:53 1 mysqld 22912 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:49 0 mysqld 22944 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:48 1 mysqld 23101 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:38 0 mysqld 23102 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:43 0 mysqld 23124 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:44 1 mysqld 23168 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:46 1 mysqld Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL memory allocation
On Tue, 2006-10-31 at 01:30 -0800, Cabbar Duzayak wrote: Hi, Could you please tell how I can tell how much memory does mysql server allocate on a linux box? I tried doing: top -b -n 1 | grep mysql But, it printed out bunch of processes for mysql. Are these all using shared memory so each line gives you the total amount for mysql? How can one interpret the results of this top, i.e. how should I read Virtual/Resident/Shared columns for all these processes and find out how much shared + (resident + swap: virtual) memory it uses? Results of TOP for mysql: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 8205 mysql 12 0 75524 28M 26896 S 4.8 0.7 3:15 1 mysqld Looks to me it is only using 28MB Resident Memory. (not sure why there are so many instances of mysqld) PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 26431 mysql 15 0 503m 340m S 0.0 72.0 466:05.74 mysqld I'm using 340M on mine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL memory allocation
Which thread library is the mysqld linked against? Linuxthreads shows each thread as a separate process in top or ps output. All threads share the same memory. From your output, it is likely that you are using linuxthreads(all pids having the same VSZ and RES memory). Regards, Ravi Cabbar Duzayak wrote: Hi, Could you please tell how I can tell how much memory does mysql server allocate on a linux box? I tried doing: top -b -n 1 | grep mysql But, it printed out bunch of processes for mysql. Are these all using shared memory so each line gives you the total amount for mysql? How can one interpret the results of this top, i.e. how should I read Virtual/Resident/Shared columns for all these processes and find out how much shared + (resident + swap: virtual) memory it uses? Results of TOP for mysql: PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 8205 mysql 12 0 75524 28M 26896 S 4.8 0.7 3:15 1 mysqld 2150 mysql 9 0 75524 28M 26896 S 0.4 0.7 3:04 1 mysqld 32011 root 9 0 628 500 500 S 0.0 0.0 0:00 0 mysqld_safe 32033 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:08 0 mysqld 32035 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:00 0 mysqld 32036 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:04 0 mysqld 1321 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:51 1 mysqld 1323 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:17 0 mysqld 2112 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:20 0 mysqld 2131 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:04 0 mysqld 2132 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:34 1 mysqld 2133 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:04 1 mysqld 2134 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:12 0 mysqld 2135 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:51 0 mysqld 2136 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:59 1 mysqld 2137 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:43 0 mysqld 2142 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:12 0 mysqld 8080 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:58 0 mysqld 8082 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:52 1 mysqld 8197 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:43 1 mysqld 8204 mysql 9 0 75524 28M 26896 S 0.0 0.7 3:16 1 mysqld 16010 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:51 0 mysqld 16011 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:58 1 mysqld 16138 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:52 1 mysqld 4074 mysql 9 0 75524 28M 26896 S 0.0 0.7 2:16 0 mysqld 17331 mysql 9 0 75524 28M 26896 S 0.0 0.7 1:44 0 mysqld 17337 mysql 9 0 75524 28M 26896 S 0.0 0.7 1:59 0 mysqld 22847 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:53 1 mysqld 22912 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:49 0 mysqld 22944 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:48 1 mysqld 23101 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:38 0 mysqld 23102 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:43 0 mysqld 23124 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:44 1 mysqld 23168 mysql 9 0 75524 28M 26896 S 0.0 0.7 0:46 1 mysqld Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.27
Hi, What impact does the MySQL 5.0.27 ABI changes have on various components available from the MySQL Website: * OBDC 3.51 * php pdo-mysql * php ext/mysql Does the ABI breakage which this release fixes cause issues when connecting from 5.0.26 clients or only for clients which use functionality which was broken by the MySQL ABI? Regards --jm -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include std/disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table not found
Hi! 2 level derived SQL like this: select field1,field2,(select sum(f3) from (select f3 from b1 where b1.f2=a1.f2) a3 ) field3 from a1 SQL error, table a1 not found , that is a1.f2 not found Regards! Shuming Wang Xtech Company limited Room 29H, 29/F,Plaza 2, Tiancheng Building,No 96 Longkouxi Road, Tianhe Guangzhou,Guangdong,China http://www.xtech.com.cn http://www.87595959.com Email:[EMAIL PROTECTED] QQ:100200651 , MSN:[EMAIL PROTECTED] Tel:86-20-87595959, 86-755-61302424 ---2001,2002
Re: Incemental backup
Philip Mather wrote: Taymour, I just bough innodb hotbackup, I need to do incremental backup as I have a database which is several GB. Is it ok that I enable the binary log and using innodb hotbackup to create a full backup and use the binary log for incremental ?, if yes how do I force mysqld to write a new log after the backup is complete ? That is indeed how I've done similar restores in the past. The command you are looking for is either *mysqladmin flush-logs *or just a flush logs as a query. The man pages are... http://dev.mysql.com/doc/refman/5.1/en/flush.html and http://dev.mysql.com/doc/refman/5.1/en/backup-strategy-example.html Regards, Phil How do I ensure consistency, if I start the ibbackup then after finishing, flush logs. -- Taymour A El Erian System Division Manager RHCE, LPIC, CCNA, MCSE, CNA TE Data E-mail: [EMAIL PROTECTED] Web: www.tedata.net Tel:+(202)-3320700 Fax:+(202)-3320800 Ext:1101 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
security impacts by Create_tmp_table_priv?
Hello, is it safe to give users in s shared hosting environment the Create_tmp_table_priv privilege? Are any other privileges granted by this option? Why does mysql store the temporary tables? What happens if the connection dies before mysql has deleted the temporary table? Does you user see it when he logs in to the database next time in that case? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
use multiple CPUs?
Apologies if this is covered elsewhere, but I can't seem to track down all the pieces... I just installed mysql (4.1.21) on a multi-cpu opteron system running fedora core. I used the binary distribution, mysql-standard-4.1.21-unknown-linux-gnu-x86_64-glibc23 instead of compiling it myself as I've done on other platforms. This is my first time running mysql on linux, so I'm still figuring things out. Anyway, I can't tell what thread library, etc, is in use in this compiled binary. I want to make sure that mysqld will use both CPUs when appropriate - in previous installations using pthreads (on netbsd) I needed to tweak an environment variable in rc.conf PTHREAD_CONCURRENCY=2 # use both CPUs in mysqld! export PTHREAD_CONCURRENCY in order to make this happen - and I only found that by accident, more or less. Do I have to do something similar with this installation? I haven't really pushed it hard enough to tell if it will try to use both CPUs or not (always a tricky thing). Thanks. I really wish this (libraries in use, how to run on multi-cpu machines) was better documented! Best, chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
transaction in mysql 5
Hi everyone I have a PHP script that will run every minute and do a lot of SELECT and UPDATE statments All my tables are InnoDB and I'm using PHP 5 and POD class ( http://php.net/pod ) to connect to mysql, in my script I start the transaction (using method beginTransaction() ) in the beginning of the script and commit in the end of the script ( also rollback if there is any problem) As I said , this script run every one minute, my question is : what will happen if I run the script more than one in the same time (like if I run it as a thread) ? is it possible that one of my script process will select the same data that the other process is selecting or updating ? or if I run a second process of my script it will not be able to read/write anything until the first script commit to the database or rollback ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MSSQL NoLock vs MySQL NoLock
In the last episode (Oct 31), Ow Mun Heng said: Just wanted to post this here to see if anyone knows the difference. In MSSQL, there is a hint which can be used to ask the DB to not lock the tables during queries and then to read un-commited/dirty data. This command is like select count(*) from MyTable nolock in MySQL, I found that I can do the same thing with the same syntax. Having read through this post in the forum, http://forums.mysql.com/read.php?60,81970,82201#msg-82201 I'm just curious to know if anyone knows that they are one and the same. I don't see anything in that thread indicating that MySQL supports a nolock keyword. The 2nd post says to run a set transaction isolation level READ UNCOMMITTED; statement before your query, which will put your session in a state similar to MSSQL's nolock mode. -- 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: finding NULL records
[snip] And the reason for that is because nothing is actually equal to NULL. For a field to be equal to NULL it would actually have to contain NULL, in which case it would not be actually NULL. What? NULL doesn't equal NULL because NULL means unknown. A column can have 2 states: known or unknown (NULL). If the case of a known state, the column has a value, in cause of unknown, it's NULL. You cannot compare to unknown. So this is why the SQL standard says: you either ask for a specific value (eg: myid = 1002) or you ask for a state: myid IS NULL or myid IS NOT NULL (= filled). It has nothing to do with contain NULL or whatever. [/snip] Philosophically this has been the argument concerning NULL for several decades when concerned with data operations. So Martin, you are correct here NULL is unknown and lacks state. Nothing can be equal to (=) something that lacks state, you can only check to see if state does or does not exist. Many more youthful database users do not fully understand NULL and its use. Some actually think (as appeared to be the case with the OP here) that the field contains a value of NULL. As you stated, a value has state and if the field has state it is certainly not NULL. 'IS NULL' and '= NULL' are two very different things. Sure I was being a little cheeky with my response, I should have taken the time to more carefully explain NULL. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: transaction in mysql 5
On Tue, Oct 31, 2006 at 08:58:04PM +0300, Ahmad Al-Twaijiry wrote: is it possible that one of my script process will select the same data that the other process is selecting or updating ? or if I run a second process of my script it will not be able to read/write anything until the first script commit to the database or rollback ? http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-isolation.html -- George-Cristian Bîrzan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: finding NULL records
I inherited a system in which the code was writing the string NULL into a CHAR field. It took me a long time to figure out what MySQL CLI was showing me. These kids today... Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
crystal reports andd mysql
hello everybody anybody had to deal with this? i have a windows form created on VB.net to get a report on embbeded crystal reports develop program im trying to customize the fields to show by using the CR form via Sql Sintax option for the fields but the formula editor seems does not understand mysql syntax any coments? please hlp!!
MyISAM vs InnoDB
Hi list, Question about MyISAM vs InnoDB ? What is the best to use, I have a large table contain around 10 millons of records. What is the best for me ? Use MyISAM or InnoDB ? Ty for reply ☺ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MyISAM vs InnoDB
Hello, Although the number of records is a consideration to weigh in your decision, there are many other (perhaps more important) factors to consider. For example, do you need foreign keys? transactions? row-level locks?...then InnoDB is your choice. Perhaps with more details concerning the characteristics of the data and your applications requirements, folks may be able to better help you with a design choice. Storage limits, efficiency in how space and memory is used, bulk insert speed, etc. might be other factors to consider. Take a look at: http://dev.mysql.com/doc/refman/5.0/en/storage-engines.html Thanks, Jimmy Guerrero MySQL, Inc -Original Message- From: Francis [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 31, 2006 2:25 PM To: mysql@lists.mysql.com Subject: MyISAM vs InnoDB Hi list, Question about MyISAM vs InnoDB ? What is the best to use, I have a large table contain around 10 millons of records. What is the best for me ? Use MyISAM or InnoDB ? Ty for reply ? -- 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: finding NULL records
I inherited a system in which the code was writing the string NULL into a CHAR field. It took me a long time to figure out what MySQL CLI was showing me. These kids today... Oh my, sounds like a candidate for www.thedailywtf.com :-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Max of Count
This should be simple, but I haven't used a subquery in quite this way, and have little experience with GROUP BY. I have 2 tables prod:prod_id /* a unique key */ prod_rel:prod_id /* values that tie prod_rel to prod */ A prod_id in prod might have 0 or more matching rows in prod_rel. I want to find the prod_id of the entry in prod that has the most dependent rows in prod_rel, and how many dependent rows it has. SELECT MAX(c) FROM (SELECT COUNT(prod_rel.prod_id) AS c FROM prod, prod_rel WHERE prod.prod_id = prod_rel.prod_id GROUP BY prod_rel.prod_id) AS t1; gives me ++ | MAX(c) | ++ | 7 | ++ That's all fine and dandy, but I haven't figured out how to get the actual value of prod_id that goes with that maximum value. I tried mysql SELECT MAX(c), id FROM - (SELECT COUNT(prod_rel.prod_id) as c, prod_rel.prod_id as id - FROM prod, prod_rel - WHERE prod.prod_id = prod_rel.prod_id GROUP BY prod_rel.prod_id) - AS t; and got ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause I had a feeling that I couldn't just use id in the outer select, but how do I get it? I tried adding GROUP BY t.prod_id, but that just gave me the same error. Please help me find my way. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
New DBManager Standard Edition 3.2.1
DBTools Software is pleased to announce the new DBManager Standard Edition. This is a bug fix with some new features and improvements. New Features and Improvements a.. Better support for SQlite Unicode databases b.. Now the Freeware Edition can load as many servers as available (1 Database Engine Only) c.. Added ContextMenu for Default Servers d.. Implemented AutoConfigureServers on first run Fixes a.. crash when connecting to Workgroup when no catalog is opened b.. View Selected and Double Clicked in Objects Bar generated error c.. SQL Designer (Failed to added [View]) d.. Firebird/Interbase not have autoinc on table designer e.. Procedure Builder was acumulating error messages on compilation f.. Server Manager didn't show default database g.. Server Manager Loosing some parameters when editing an existent connection h.. several memory leaks i.. MySQL CreateIndex (missing columns quote) j.. Table Properties correctly shows foreign keys (PKTable was empty/schema) As requested by our users we have added a 20 day trial period, when you can test all the features available in the Standard Edition. After the period expires the application is automatically moved to Freeware Edition and you can choose which engine to use when the application starts. The registration is optional and at a very small fee. The new version is now available in our Downloads Center at http://www.dbtools.com.br/EN/downloads. What is DBManager Standard Edition? DBManager Standard Edition is a Windows application for database management. It includes all the basic objects management, which is suitable for all beginners. It supports MySQL, PostgreSQL, Interbase, Firebird, SQlite and DBF Tables. For a complete list of features and screenshots please check http://www.dbtools.com.br/EN/dbmanagerpro. Best Regards, DBTools Software http://www.dbtools.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow seach - Possible better query
I have a query that works and returns the correct results. However, it is very slow ( 6-12 seconds on 5000 row table). Since this table will grow to several hundred thousand rows very shortly, I am worried. Here is the query that works: SELECT id, subject, updated FROM mrldisc WHERE (id IN (SELECT thread_id FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR))) ORDER BY updated DESC LIMIT 50 Here is the table schema: CREATE TABLE `mrldisc` ( `id` int(14) NOT NULL auto_increment, `thread_id` int(14) default NULL, `author` varchar(100) NOT NULL default '', `state` varchar(25) NOT NULL default '', `subject` varchar(100) NOT NULL default '', `message` longtext NOT NULL, `date` varchar(50) NOT NULL default '', `jdate` varchar(50) default '-00-00 00:00:00', `statespecific` varchar(25) NOT NULL default '', `mainthread` char(1) NOT NULL default '', `email` varchar(100) NOT NULL default '', `threadcount` int(11) NOT NULL default '0', `updated` timestamp NOT NULL default CURRENT_TIMESTAMP, `ip_address` varchar(15) NOT NULL default '', PRIMARY KEY (`id`), KEY `thread_id` (`thread_id`), KEY `statespecific` (`statespecific`), KEY `state` (`state`), KEY `updated` (`updated`), KEY `email` (`email`), KEY `mainthread` (`mainthread`), KEY `jdate` (`jdate`), FULLTEXT KEY `author` (`author`), FULLTEXT KEY `message` (`message`), FULLTEXT KEY `subject` (`subject`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have tried other queries like: SELECT DISTINCT * FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) GROUP BY thread_id ORDER BY updated DESC LIMIT 50 This one was close, but returned the oldest row in the group rather than the most recent row. I've also thought about doing the search with a temporary table, but haven't gotten very far with that. Any pointers would be greatly appreciated. Thanks. Al Padley
Re: Slow seach - Possible better query
Albert, it seems like the first query could be simplified, like so: SELECT id, subject, updated FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) ORDER BY updated DESC LIMIT 50 This might help it hit the index you've created on the UPDATED column. I know there have been bugs here and there with the optimizer and IN subqueries not hitting indices, especially in earlier 4.1.x releases. If you have a large number of rows with recent values for the UPDATED column, MySQL may be doing a table scan. This is an instance where test/development scenarios don't always work quite as well as real data. However, 6-12 seconds for a 5000 row table does seem slow ... Can you post the output of EXPLAIN query ? That will help us see how MySQL is planning to run your query, and may also reveal a little bit about your data. Thanks, Dan On 10/31/06, Albert Padley [EMAIL PROTECTED] wrote: I have a query that works and returns the correct results. However, it is very slow ( 6-12 seconds on 5000 row table). Since this table will grow to several hundred thousand rows very shortly, I am worried. Here is the query that works: SELECT id, subject, updated FROM mrldisc WHERE (id IN (SELECT thread_id FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR))) ORDER BY updated DESC LIMIT 50 Here is the table schema: CREATE TABLE `mrldisc` ( `id` int(14) NOT NULL auto_increment, `thread_id` int(14) default NULL, `author` varchar(100) NOT NULL default '', `state` varchar(25) NOT NULL default '', `subject` varchar(100) NOT NULL default '', `message` longtext NOT NULL, `date` varchar(50) NOT NULL default '', `jdate` varchar(50) default '-00-00 00:00:00', `statespecific` varchar(25) NOT NULL default '', `mainthread` char(1) NOT NULL default '', `email` varchar(100) NOT NULL default '', `threadcount` int(11) NOT NULL default '0', `updated` timestamp NOT NULL default CURRENT_TIMESTAMP, `ip_address` varchar(15) NOT NULL default '', PRIMARY KEY (`id`), KEY `thread_id` (`thread_id`), KEY `statespecific` (`statespecific`), KEY `state` (`state`), KEY `updated` (`updated`), KEY `email` (`email`), KEY `mainthread` (`mainthread`), KEY `jdate` (`jdate`), FULLTEXT KEY `author` (`author`), FULLTEXT KEY `message` (`message`), FULLTEXT KEY `subject` (`subject`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have tried other queries like: SELECT DISTINCT * FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) GROUP BY thread_id ORDER BY updated DESC LIMIT 50 This one was close, but returned the oldest row in the group rather than the most recent row. I've also thought about doing the search with a temporary table, but haven't gotten very far with that. Any pointers would be greatly appreciated. Thanks. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max of Count
This might work for you, Jerry - you're on the right track, pretty close. SELECT prod.prod_id, COUNT(*) FROM prod, prod_rel WHERE prod.prod_id = prod_rel.prod_id GROUP BY prod.prod_id ORDER BY 2 DESC LIMIT 1 Be aware that using the LIMIT 1 may be misleading in that if you have multiple entries with the same highest number of relations, you'll only see one. That may or may not matter to you. HTH, Dan On 10/31/06, Jerry Schwartz [EMAIL PROTECTED] wrote: This should be simple, but I haven't used a subquery in quite this way, and have little experience with GROUP BY. I have 2 tables prod:prod_id /* a unique key */ prod_rel:prod_id /* values that tie prod_rel to prod */ A prod_id in prod might have 0 or more matching rows in prod_rel. I want to find the prod_id of the entry in prod that has the most dependent rows in prod_rel, and how many dependent rows it has. SELECT MAX(c) FROM (SELECT COUNT(prod_rel.prod_id) AS c FROM prod, prod_rel WHERE prod.prod_id = prod_rel.prod_id GROUP BY prod_rel.prod_id) AS t1; gives me ++ | MAX(c) | ++ | 7 | ++ That's all fine and dandy, but I haven't figured out how to get the actual value of prod_id that goes with that maximum value. I tried mysql SELECT MAX(c), id FROM - (SELECT COUNT(prod_rel.prod_id) as c, prod_rel.prod_id as id - FROM prod, prod_rel - WHERE prod.prod_id = prod_rel.prod_id GROUP BY prod_rel.prod_id) - AS t; and got ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause I had a feeling that I couldn't just use id in the outer select, but how do I get it? I tried adding GROUP BY t.prod_id, but that just gave me the same error. Please help me find my way. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -- 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: Slow seach - Possible better query
Dan, On Oct 31, 2006, at 3:41 PM, Dan Buettner wrote: Albert, it seems like the first query could be simplified, like so: SELECT id, subject, updated FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) ORDER BY updated DESC LIMIT 50 This query won't work. The table contains threaded messages. The thread_id refers back to the id of the first message in the thread. The above query would return a list of all messages in the last 48 hours when all I want is to return the original message in the thread. This might help it hit the index you've created on the UPDATED column. I know there have been bugs here and there with the optimizer and IN subqueries not hitting indices, especially in earlier 4.1.x releases. If you have a large number of rows with recent values for the UPDATED column, MySQL may be doing a table scan. This is an instance where test/development scenarios don't always work quite as well as real data. However, 6-12 seconds for a 5000 row table does seem slow ... In our test database it's probably less than 50 rows that have been updated in the last 48 hours. Can you post the output of EXPLAIN query ? That will help us see how MySQL is planning to run your query, and may also reveal a little bit about your data. 1 PRIMARY mrldisc index updated 4 4888Using where 2 DEPENDENT SUBQUERY mrldisc index_subquery thread_id,updated thread_id 5 func 8 Using index; Using where Thanks. Al Thanks, Dan On 10/31/06, Albert Padley [EMAIL PROTECTED] wrote: I have a query that works and returns the correct results. However, it is very slow ( 6-12 seconds on 5000 row table). Since this table will grow to several hundred thousand rows very shortly, I am worried. Here is the query that works: SELECT id, subject, updated FROM mrldisc WHERE (id IN (SELECT thread_id FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR))) ORDER BY updated DESC LIMIT 50 Here is the table schema: CREATE TABLE `mrldisc` ( `id` int(14) NOT NULL auto_increment, `thread_id` int(14) default NULL, `author` varchar(100) NOT NULL default '', `state` varchar(25) NOT NULL default '', `subject` varchar(100) NOT NULL default '', `message` longtext NOT NULL, `date` varchar(50) NOT NULL default '', `jdate` varchar(50) default '-00-00 00:00:00', `statespecific` varchar(25) NOT NULL default '', `mainthread` char(1) NOT NULL default '', `email` varchar(100) NOT NULL default '', `threadcount` int(11) NOT NULL default '0', `updated` timestamp NOT NULL default CURRENT_TIMESTAMP, `ip_address` varchar(15) NOT NULL default '', PRIMARY KEY (`id`), KEY `thread_id` (`thread_id`), KEY `statespecific` (`statespecific`), KEY `state` (`state`), KEY `updated` (`updated`), KEY `email` (`email`), KEY `mainthread` (`mainthread`), KEY `jdate` (`jdate`), FULLTEXT KEY `author` (`author`), FULLTEXT KEY `message` (`message`), FULLTEXT KEY `subject` (`subject`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have tried other queries like: SELECT DISTINCT * FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) GROUP BY thread_id ORDER BY updated DESC LIMIT 50 This one was close, but returned the oldest row in the group rather than the most recent row. I've also thought about doing the search with a temporary table, but haven't gotten very far with that. Any pointers would be greatly appreciated. Thanks. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow seach - Possible better query PROBLEM SOLVED
Dan, Actually you were on the right track. I changed your suggested query to the following and it seems to work and is a lot quicker. SELECT id, subject, updated FROM mrldisc WHERE updated SUBDATE(NOW (), INTERVAL 48 HOUR) AND mainthread = 'T' ORDER BY updated DESC LIMIT 50 Thanks. Al On Oct 31, 2006, at 4:01 PM, Albert Padley wrote: Dan, On Oct 31, 2006, at 3:41 PM, Dan Buettner wrote: Albert, it seems like the first query could be simplified, like so: SELECT id, subject, updated FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) ORDER BY updated DESC LIMIT 50 This query won't work. The table contains threaded messages. The thread_id refers back to the id of the first message in the thread. The above query would return a list of all messages in the last 48 hours when all I want is to return the original message in the thread. This might help it hit the index you've created on the UPDATED column. I know there have been bugs here and there with the optimizer and IN subqueries not hitting indices, especially in earlier 4.1.x releases. If you have a large number of rows with recent values for the UPDATED column, MySQL may be doing a table scan. This is an instance where test/development scenarios don't always work quite as well as real data. However, 6-12 seconds for a 5000 row table does seem slow ... In our test database it's probably less than 50 rows that have been updated in the last 48 hours. Can you post the output of EXPLAIN query ? That will help us see how MySQL is planning to run your query, and may also reveal a little bit about your data. 1 PRIMARY mrldisc index updated 4 4888Using where 2 DEPENDENT SUBQUERY mrldisc index_subquery thread_id,updated thread_id 5 func 8 Using index; Using where Thanks. Al Thanks, Dan On 10/31/06, Albert Padley [EMAIL PROTECTED] wrote: I have a query that works and returns the correct results. However, it is very slow ( 6-12 seconds on 5000 row table). Since this table will grow to several hundred thousand rows very shortly, I am worried. Here is the query that works: SELECT id, subject, updated FROM mrldisc WHERE (id IN (SELECT thread_id FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR))) ORDER BY updated DESC LIMIT 50 Here is the table schema: CREATE TABLE `mrldisc` ( `id` int(14) NOT NULL auto_increment, `thread_id` int(14) default NULL, `author` varchar(100) NOT NULL default '', `state` varchar(25) NOT NULL default '', `subject` varchar(100) NOT NULL default '', `message` longtext NOT NULL, `date` varchar(50) NOT NULL default '', `jdate` varchar(50) default '-00-00 00:00:00', `statespecific` varchar(25) NOT NULL default '', `mainthread` char(1) NOT NULL default '', `email` varchar(100) NOT NULL default '', `threadcount` int(11) NOT NULL default '0', `updated` timestamp NOT NULL default CURRENT_TIMESTAMP, `ip_address` varchar(15) NOT NULL default '', PRIMARY KEY (`id`), KEY `thread_id` (`thread_id`), KEY `statespecific` (`statespecific`), KEY `state` (`state`), KEY `updated` (`updated`), KEY `email` (`email`), KEY `mainthread` (`mainthread`), KEY `jdate` (`jdate`), FULLTEXT KEY `author` (`author`), FULLTEXT KEY `message` (`message`), FULLTEXT KEY `subject` (`subject`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 I have tried other queries like: SELECT DISTINCT * FROM mrldisc WHERE updated SUBDATE(NOW(), INTERVAL 48 HOUR) GROUP BY thread_id ORDER BY updated DESC LIMIT 50 This one was close, but returned the oldest row in the group rather than the most recent row. I've also thought about doing the search with a temporary table, but haven't gotten very far with that. Any pointers would be greatly appreciated. Thanks. Al Padley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MSSQL NoLock vs MySQL NoLock
On Tue, 2006-10-31 at 10:35 -0600, Dan Nelson wrote: In the last episode (Oct 31), Ow Mun Heng said: Just wanted to post this here to see if anyone knows the difference. In MSSQL, there is a hint which can be used to ask the DB to not lock the tables during queries and then to read un-commited/dirty data. This command is like select count(*) from MyTable nolock in MySQL, I found that I can do the same thing with the same syntax. Having read through this post in the forum, http://forums.mysql.com/read.php?60,81970,82201#msg-82201 I'm just curious to know if anyone knows that they are one and the same. I don't see anything in that thread indicating that MySQL supports a nolock keyword. The 2nd post says to run a Sorry it was another post somewhere http://bloggingabout.net/blogs/ramon/archive/2006/09/29/Change-the-isolation-leven-to-read-uncommitted-data-in-MSSQL.aspx and after playing around with it, seems that the information is incorrect. Sorry for the noise. set transaction isolation level READ UNCOMMITTED; statement before your query, which will put your session in a state similar to MSSQL's nolock mode. I understand that innodb don't need these locking hints (as it uses mvcc) but I'm looking for ways to have the same queries run in bot mssql and mysql with the minimum of changes in the SQL query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
