what does Rows_examined mean exactly?
slow.log: # Query_time: 14 Lock_time: 0 Rows_sent: 30 Rows_examined: 771327 SELECT * FROM `post` LIMIT 771297, 30; i dp have privmary key on table `post` does 'Rows_examined: 771327' means mysqlserver read through those 771327 rows to get 30 rows? it takes 14 seconds! possible to show 'Rows_examined' in a explain or other commands? slow log is hard to debug.. and possible to optimize? _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what does Rows_examined mean exactly?
tinys xuefer wrote: slow.log: # Query_time: 14 Lock_time: 0 Rows_sent: 30 Rows_examined: 771327 SELECT * FROM `post` LIMIT 771297, 30; i dp have privmary key on table `post` does 'Rows_examined: 771327' means mysqlserver read through those 771327 rows to get 30 rows? it takes 14 seconds! possible to show 'Rows_examined' in a explain or other commands? slow log is hard to debug.. and possible to optimize? But you didn't use the primary key! In fact, you didn't ask for any order at all. Mysql does not try to guess that you meant to order by the primary key, it simpply does what you tell it. Your query, in effect, tells mysql to pick 771327 rows from post in any order and send you the last 30. Assuming your primary key column is named id, you need to change this query to SELECT * FROM post ORDER BY id LIMIT 771297, 30; With the explicit ORDER BY on the primary key, mysql will use the index to quickly find the 30 rows you want. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't start server.
Okay, I've got MySQL installed but now I can't start the server deamon, when I try to start it as the root user, I get the following: Warning: asked for 196608 stack space but got 126976 but on the user mysql I get: bash: mysqld: command not found. What do I need to do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multiple table delete syntax question
I have a question about the multiple table delete syntax. First the documentation on the website is very clear. My question is why not how. The 'delete from using' is not ambiguous (to me). My question is about the form: delete t1 from t1,t2 where ... I would take this to mean remove matching records from t2. I assume the reason records are removed from t1 is SQL language consistency. But as an SQL newbie, I can not see it. Thanks for any thoughts. _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-4.0.20 configure fails mac os x 10.3.1 client
Environment: OS: Mac OS X 10.3.1, client mysql: 4.0.20 compiler: gcc version 3.3 20030304 (Apple Computer, Inc. build 1495) Symptom: Small section of configure log showing first errors shown below. There is a lot more of this, too much to post here. I've chopped the beginning and the end. I'm wondering if the Mac OS X 10.3.1 client needs additional development files (libraries, etc.) installed because my box is the standard setup shipped from Apple. Thanks for any suggestions. Ron configure:2913: checking for gcc option to accept ANSI C configure:2974: gcc -cconftest.c 5 configure:2935:19: stdio.h: No such file or directory configure:2936:23: sys/types.h: No such file or directory configure:2937:22: sys/stat.h: No such file or directory configure:2940: error: parse error before '*' token configure:2940: warning: data definition has no type or storage class configure:2959: error: parse error before FILE configure:2959: error: `pairnames' declared as function returning a function configure:2959: error: parse error before int configure:2977: $? = 1 configure: failed program was: | #line 2920 configure | /* confdefs.h. */ . . . | Syntax error configure:5362: /lib/cpp conftest.cc ./configure: line 1: /lib/cpp: No such file or directory configure:5368: $? = 127 configure: failed program was: | #line 5348 configure | /* confdefs.h. */ | | #define PACKAGE_NAME | #define PACKAGE_TARNAME | #define PACKAGE_VERSION | #define PACKAGE_STRING | #define PACKAGE_BUGREPORT | #define PACKAGE mysql | #define VERSION 4.0.20 | #define PROTOCOL_VERSION 10 | #define DOT_FRM_VERSION 6 | #define SYSTEM_TYPE apple-darwin7.0.0 | #define MACHINE_TYPE powerpc | #ifdef __cplusplus | #include stdlib.h | #endif | /* end confdefs.h. */ | #ifdef __STDC__ | # include limits.h | #else | # include assert.h | #endif | Syntax error configure:5436: error: C++ preprocessor /lib/cpp fails sanity check See `config.log' for more details. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple table delete syntax question
[EMAIL PROTECTED] wrote: I have a question about the multiple table delete syntax. First the documentation on the website is very clear. My question is why not how. The 'delete from using' is not ambiguous (to me). My question is about the form: delete t1 from t1,t2 where ... I would take this to mean remove matching records from t2. I assume the reason records are removed from t1 is SQL language consistency. But as an SQL newbie, I can not see it. Thanks for any thoughts. _ Douglas Denault The point is to distinguish between the tables which are joined to pick the rows and the tables from which rows are to be deleted. You have 2 options: DELETE FROM t1 USING t1,t2 ... or DELETE t1 FROM t1,t2 ... Perhaps you are extrapolating from 'DELETE FROM t1...' to expect that the second form should delete from both tables, but note that the second form is not 'DELETE FROM t1,t2...', it's 'DELETE t1 FROM t1,t2...'. If you must relate it to something, I'd suggest 'SELECT t1.* FROM t1,t2 ...' is the natural parallel. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql-4.0.20 configure fails mac os x 10.3.1 client
First, I should point out that the simplest course would be to download the precompiled binary from mysql. That said, I admit I like to build from source, largely because whenever things go wrong, I always learn something. On first glance, I see a few problems: 1) You are running OS X 10.3.1, but current is 10.3.4. I don't believe that's the cause of the problem here, but there are some important security updates you are missing. I'd recommend running Software Update to install at least the security patches. 2) You appear to have Xcode 1.1, based on your gcc version. The error messages you are getting indicate that your system header files, which should be in /usr/include, cannot be found. Those are normally put in place by the Xcode installer along with gcc. 3) The second line is trying to compile with just `gcc -c`. If you use the recommended flags, you'd see `gcc -c -O3 -fno-omit-frame-pointer`. I'm guessing you haven't seen the configure recommendations in the manual http://dev.mysql.com/doc/mysql/en/MySQL_binaries.html. Based on those, I've created a file named .config with the following contents: CC=gcc \ CFLAGS=-O3 -fno-omit-frame-pointer \ CXX=gcc \ CXXFLAGS=-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti ./configure --prefix=/usr/local/mysql \ --localstatedir=/usr/local/mysql/data \ --with-extra-charsets=complex \ --enable-thread-safe-client \ --enable-local-infile \ --disable-shared (My mail client is determined to wrap the CXXFLAGS line. You should unwrap it to one line.) The only change I've made relative to the mysql recommended settings is the --localstatedir path. I've made .config executable (chmod +x .config), so each time I need to build a new version of mysql, I just copy .config into the source directory and run it with `. .config`. That saves me typing each time and keeps my settings constant across versions. 4) I expected to see configure:5362: gcc -E conftest.cc, but your output shows /lib/cpp instead of gcc -E. My config.log for mysql 4.0.20 contains no mention of /lib/cpp. At this point, it's hard to speculate, but I'm wondering if perhaps you either didn't do a full install of Xcode or moved/deleted parts of it afterward. I hope this is enough info to nudge you in the right direction. If not, let us know. Michael Ron Phelps wrote: Environment: OS: Mac OS X 10.3.1, client mysql: 4.0.20 compiler: gcc version 3.3 20030304 (Apple Computer, Inc. build 1495) Symptom: Small section of configure log showing first errors shown below. There is a lot more of this, too much to post here. I've chopped the beginning and the end. I'm wondering if the Mac OS X 10.3.1 client needs additional development files (libraries, etc.) installed because my box is the standard setup shipped from Apple. Thanks for any suggestions. Ron configure:2913: checking for gcc option to accept ANSI C configure:2974: gcc -cconftest.c 5 configure:2935:19: stdio.h: No such file or directory configure:2936:23: sys/types.h: No such file or directory configure:2937:22: sys/stat.h: No such file or directory configure:2940: error: parse error before '*' token configure:2940: warning: data definition has no type or storage class configure:2959: error: parse error before FILE configure:2959: error: `pairnames' declared as function returning a function configure:2959: error: parse error before int configure:2977: $? = 1 configure: failed program was: | #line 2920 configure | /* confdefs.h. */ . . . | Syntax error configure:5362: /lib/cpp conftest.cc ./configure: line 1: /lib/cpp: No such file or directory configure:5368: $? = 127 configure: failed program was: | #line 5348 configure | /* confdefs.h. */ | | #define PACKAGE_NAME | #define PACKAGE_TARNAME | #define PACKAGE_VERSION | #define PACKAGE_STRING | #define PACKAGE_BUGREPORT | #define PACKAGE mysql | #define VERSION 4.0.20 | #define PROTOCOL_VERSION 10 | #define DOT_FRM_VERSION 6 | #define SYSTEM_TYPE apple-darwin7.0.0 | #define MACHINE_TYPE powerpc | #ifdef __cplusplus | #include stdlib.h | #endif | /* end confdefs.h. */ | #ifdef __STDC__ | # include limits.h | #else | # include assert.h | #endif | Syntax error configure:5436: error: C++ preprocessor /lib/cpp fails sanity check See `config.log' for more details. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
List of associated records
I have come across this problem a few times and wondered how other people solved the problem. Let's say I have a table containing Members. Each Member can choose several items in which they are interested. Each of these items represent records in a separate table - Interests. How do you store which records from Interests the member has checked in their record of the Members table? Do you create a TEXT field in the Members table and save a comma-delimited string of InterestsIDs? Thanx. -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: Transitioning from MS Access to MYSQL
I am new to MYSQL and I want to set-up a database with two or more tables that need to be linked. I come from a basic MS Access background, and in Access linking two tables is done by creating a relationship. Is it done the same way in MYSQL? If so, could someone please shed some light on this matter? Is there a good MYSQL book out there for someone with my background? Thanks! Rodney -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Display field of selected record from full table recordset
I have a recordset that retrieves the full content of the table - all fields, all records. Depending upon the content of different fields in different tables, I need to display certain fields of certain records within the full recordset. What's the syntax for selecting a particular record for display relative to the field contents of another recordset? Thanx, -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't start server.
Levi Campbell said: Okay, I've got MySQL installed but now I can't start the server deamon, when I try to start it as the root user, I get the following: Warning: asked for 196608 stack space but got 126976 but on the user mysql I get: bash: mysqld: command not found. What do I need to do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] what distro are you running? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re: List of associated records
Create a seperate table called member_interests or something similar Store one member_id and one interest_id (or whatever you have your PKs named) in each row. This is similar to an order entry system, which typically has one table for order_headers and one for order_detail. The order_header table contains things like an order_id, the order_number, the customer, the selected address, etc.. The order_detail table contains the items that are on the order. On Sun, 25 Jul 2004 12:40:09 -0500, Robb Kerr [EMAIL PROTECTED] wrote: I have come across this problem a few times and wondered how other people solved the problem. Let's say I have a table containing Members. Each Member can choose several items in which they are interested. Each of these items represent records in a separate table - Interests. How do you store which records from Interests the member has checked in their record of the Members table? Do you create a TEXT field in the Members table and save a comma-delimited string of InterestsIDs? Thanx. -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't start server.
Debian, woody version. - Original Message - From: Jonathan Villamailto:[EMAIL PROTECTED] To: Levi Campbellmailto:[EMAIL PROTECTED] Cc: [EMAIL PROTECTED]mailto:[EMAIL PROTECTED] Sent: Sunday, July 25, 2004 2:06 PM Subject: Re: Can't start server. Levi Campbell said: Okay, I've got MySQL installed but now I can't start the server deamon, when I try to start it as the root user, I get the following: Warning: asked for 196608 stack space but got 126976 but on the user mysql I get: bash: mysqld: command not found. What do I need to do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysqlhttp://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]http://lists.mysql.com/[EMAIL PROTECTED] what distro are you running?
Re: Display field of selected record from full table recordset
You probably want to pick up a good SQL book. MySQL by Paul DuBois is a really good one. http://www.amazon.com/exec/obidos/tg/detail/-/0735712123/qid=1090786499/sr=8-2/ref=pd_ka_2/102-0741496-3072118?v=glances=booksn=507846 You want to use the WHERE clause of the select statement. SELECT table.some_column, table.another_column, ... FROM table WHERE some_column = 'some_value' see the manual: http://dev.mysql.com/doc/mysql/en/SELECT.html On Sat, 24 Jul 2004 13:52:53 -0500, Robb Kerr [EMAIL PROTECTED] wrote: I have a recordset that retrieves the full content of the table - all fields, all records. Depending upon the content of different fields in different tables, I need to display certain fields of certain records within the full recordset. What's the syntax for selecting a particular record for display relative to the field contents of another recordset? Thanx, -- Robb Kerr Digital IGUANA Helping Digital Artists Achieve their Dreams http://www.digitaliguana.com http://www.cancerreallysucks.org -- 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: List of associated records
Each member can have several interests, and each interest can be held by several members. The best way to do this is with a third table relating the two: CREATE TABLE member_interests (member_id INT, interest_id INT, UNIQUE INDEX mem_int_idx (member_id,interest_id); Each row in this table represents one interest for one member. If the table held these rows: member_id interest_id 1 1 1 5 1 17 2 5 2 13 then the member with ID=1 holds interests with IDs 1, 5, and 17, while the member with ID=2 holds interests with IDS 5 and 13. Michael Robb Kerr wrote: I have come across this problem a few times and wondered how other people solved the problem. Let's say I have a table containing Members. Each Member can choose several items in which they are interested. Each of these items represent records in a separate table - Interests. How do you store which records from Interests the member has checked in their record of the Members table? Do you create a TEXT field in the Members table and save a comma-delimited string of InterestsIDs? Thanx. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow server - any idea?
Hi eveybody, I've got problems since a few weeks with my mysql server. There are a lot of slow queries (about 1200 in less than 48 hours), even some that should absolutely not be slow like this one which is performing on a HEAP table : SELECT * FROM vb3_session WHERE sessionhash = '31d429cc3820a8bb141733de2cd306ba' AND lastactivity 1090778091 AND host = '65.50.5.140' AND idhash = '385f8c8da967afdd86399fb72d05'; I'm running a p4 2,4. 1Go RAM, DD IDE 80Go under FreeBSD and I've got the 4.0.20 version installed (anyway I tried to downgrade to 4.0.18 and it didn't changed anything). There are about 20 sites and a vb3 forum with 200 to 300 visitors at once. The server doesn't seem to consume much cpu as shown : 42992 mysql 2 0 226M 66256K poll 87:38 4.83% 4.83% mysqld Here is my config file : [mysqld] datadir=/var/db/mysql socket=/tmp/mysql.sock skip-locking skip-innodb query_cache_limit=1M query_cache_size=32M query_cache_type=1 max_connections=500 interactive_timeout=100 wait_timeout=100 connect_timeout=10 thread_cache_size=64 key_buffer=150M join_buffer=1M max_allowed_packet=2M table_cache=768 record_buffer=1M sort_buffer_size=1M read_buffer_size=1M #read_rnd_buffer_size=768K max_connect_errors=10 # Try number of CPU's*2 for thread_concurrency thread_concurrency=2 myisam_sort_buffer_size=64M #log-bin server-id=1 log_slow_queries=/var/log/slow-queries.log long_query_time=1 [mysql.server] user=mysql basedir=/usr/local [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/db/mysql/srv1.pid open_files_limit=8192 [mysqldump] quick max_allowed_packet=16M [mysql] no-auto-rehash #safe-updates [isamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [myisamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [mysqlhotcopy] interactive-timeout Here is the status : Created tmp disk tables 706 Created tmp tables 162301 Created tmp files 138 Delayed insert threads 0 Delayed writes 0 Delayed errors 0 Flush commands 1 Handler commit 0 Handler delete 62700 Handler read first 10465 Handler read key 53413365 Handler read next 20806399 Handler read prev 8431183 Handler read rnd 12619723 Handler read rnd next 670650172 Handler rollback 0 Handler update 2921336 Handler write 23073711 Key blocks used 108984 Key read requests 135302387 Key reads 107438 Key write requests 214624 Key writes 184195 Max used connections 41 Not flushed key blocks 0 Not flushed delayed rows 0 Open tables 768 Open files 1321 Open streams 0 Opened tables 9238 Qcache queries in cache 4900 Qcache inserts 954259 Qcache hits 1556783 Qcache lowmem prunes 143367 Qcache not cached 120513 Qcache free memory 7149624 Qcache free blocks 2438 Qcache total blocks 14367 Rpl status NULL Select full join 739 Select full range join 63 Select range 135410 Select range check 0 Select scan 415678 Slave open temp tables 0 Slave running OFF Slow launch threads 0 Slow queries 1280 Sort merge passes 69 Sort range 128597 Sort rows 13431446 Sort scan 200597 Table locks immediate 2514328 Table locks waited 7966 Threads cached 39 Threads created 42 Threads connected 3 Threads running 1 I also have got this kind of messages in mysqld.log : 040725 12:56:47 Aborted connection 250044 to db: 'mondespe_lineage2' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 12:58:40 Aborted connection 250285 to db: 'animelan' user: 'animelan' host: `localhost' (Got timeout reading communication packets) 040725 13:09:59 Aborted connection 251722 to db: 'mondespe_forums' user: 'mondespe' host: `localhost' (Got timeout reading communication packets) 040725 13:10:59 Aborted connection 251896 to db: 'unconnected' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:10:59 Aborted connection 251891 to db: 'vb3_fansite' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:11:06 Aborted connection 251914 to db: 'mysql' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:17:37 Aborted connection 252812 to db: 'mondespe_forums' user: 'mondespe' host: `localhost' (Got timeout reading communication packets) 040725 13:30:18 Aborted connection 254752 to db: 'mmoblogs' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:30:21 Aborted connection 254750 to db: 'mysql' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:32:37 Aborted connection 255067 to db: 'mysql' user: 'root' host: `localhost' (Got timeout reading communication packets) Any idea to solve the problem is welcome, Thanks to all, Julien.
Re: multiple table delete syntax question
I have no problem understanding the syntax, or how to do what I want (at least after my first mistake). It is more about if this is a consistant grammar. For example, unix commands have the form verb src object, except for ln -s (IMO). From a lexical view, I do not think the two forms are parallel. My question was, is this to be consistant with other sql constructs, or is it just the way it is? On Sun, 25 Jul 2004, Michael Stassen wrote: The point is to distinguish between the tables which are joined to pick the rows and the tables from which rows are to be deleted. You have 2 options: DELETE FROM t1 USING t1,t2 ... or DELETE t1 FROM t1,t2 ... Perhaps you are extrapolating from 'DELETE FROM t1...' to expect that the second form should delete from both tables, but note that the second form is not 'DELETE FROM t1,t2...', it's 'DELETE t1 FROM t1,t2...'. If you must relate it to something, I'd suggest 'SELECT t1.* FROM t1,t2 ...' is the natural parallel. Michael _ Douglas Denault http://www.safeport.com [EMAIL PROTECTED] Voice: 301-469-8766 Fax: 301-469-0601 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL book
Can anyone suggest o great book to learn MySQL inside out? I am thinking of getting: *MySQL By* Paul DuBois http://www.informit.com/safari/author_bio.asp?ISBN=0735709211 - New Riders Publishing -- Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Development.Multimedia.Branding emotionalize.conceptualize.visualize.realize Tel: +27125468436 Fax: +27125468436 email:[EMAIL PROTECTED] web: www.volume4.co.za This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL book
At 1:55 +0200 7/26/04, Schalk Neethling wrote: Can anyone suggest o great book to learn MySQL inside out? I am thinking of getting: *MySQL By* Paul DuBois http://www.informit.com/safari/author_bio.asp?ISBN=0735709211 - New Riders Publishing That's the first edition. I would suggest getting the second edition instead. :-) (http://www.kitebird.com/mysql-book/) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query to select only numeric portion of string
Using MySQL 4.0.20, I need to extract just the numeric portion of a string in a field and move it to another field in the same table, but I'd only like to do this if the value actually starts with a number. So, what I'm looking to accomplish is: UPDATE table SET field2 = VOODOO( field1 ) The fields are currently defined as: field2 INT( 10 ) field1 CHAR( 19 ) The table is relatively small, with about 55,000 records in it. Here is the type of data I find in field1 and next to it, what I'd like to wind up with in field2: 1234 - 1234 12345 - 12345 123456 - 123456 1234567 -1234567 1234NN - 1234 12345NN -12345 123456N - 123456 1234567 - 1234567 WWW - WWW NC - NC There is other data, but most of it follows that rule. If I can do this with MySQL, I would prefer it. If it's something I should farm out to PHP or Perl, I can do that, but I was trying to think of how to accomplish this just within MySQL and don't really know how to approach it. Any pointers would be appreciated. Thanks. Wes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table_cache size for large value in opened_tables
Hi all, My system reports: Open_tables 512 Opened_tables 24,429 The docs say that if the latter is high I should increase the table cache size. (currently at 512) How does one decide what size to increase it to? And is there a problem with one of the applications that's making this figure so high? Or is this normal behaviour? OS: RH9 Dual 2.4 Xeon 1 GIG RAM (btw, this kind of question i linked to my previous post for a performance tuning guide) Thanks! Here's my status Variable_name,Value, Aborted_clients,801, Aborted_connects,14, Bytes_received,1195564158, Bytes_sent,1491507399, Com_admin_commands,42960, Com_alter_table,317, Com_alter_db,0, Com_analyze,0, Com_backup_table,0, Com_begin,0, Com_change_db,1654089, Com_change_master,0, Com_check,0, Com_commit,59, Com_create_db,9, Com_create_function,0, Com_create_index,0, Com_create_table,465, Com_delete,100132, Com_delete_multi,21, Com_do,0, Com_drop_db,2, Com_drop_function,0, Com_drop_index,0, Com_drop_table,549, Com_flush,13, Com_grant,1, Com_ha_close,0, Com_ha_open,0, Com_ha_read,0, Com_help,0, Com_insert,6887163, Com_insert_select,48, Com_kill,0, Com_load,14, Com_load_master_data,0, Com_load_master_table,0, Com_lock_tables,15, Com_optimize,42, Com_purge,0, Com_purge_before_date,0, Com_rename_table,0, Com_repair,42, Com_replace,7873, Com_replace_select,0, Com_reset,0, Com_restore_table,0, Com_revoke,0, Com_rollback,17, Com_select,3152624, Com_set_option,27303, Com_show_binlog_events,0, Com_show_binlogs,0, Com_show_charsets,0, Com_show_column_types,0, Com_show_create_table,456, Com_show_create_db,0, Com_show_databases,89, Com_show_errors,0, Com_show_fields,1775, Com_show_grants,0, Com_show_keys,1643, Com_show_logs,0, Com_show_master_status,0, Com_show_new_master,0, Com_show_open_tables,0, Com_show_privileges,0, Com_show_processlist,73, Com_show_slave_hosts,0, Com_show_slave_status,0, Com_show_status,240, Com_show_innodb_status,0, Com_show_tables,2846, Com_show_table_types,0, Com_show_variables,96, Com_show_warnings,0, Com_slave_start,0, Com_slave_stop,0, Com_truncate,0, Com_unlock_tables,15, Com_update,5146761, Com_update_multi,0, Connections,108272, Created_tmp_disk_tables,71315, Created_tmp_tables,725937, Created_tmp_files,0, Delayed_insert_threads,0, Delayed_writes,0, Delayed_errors,0, Flush_commands,1, Handler_commit,219, Handler_delete,941394, Handler_read_first,483459, Handler_read_key,102941817, Handler_read_next,213625710, Handler_read_prev,448018710, Handler_read_rnd,3049520, Handler_read_rnd_next,222363654, Handler_rollback,16312, Handler_update,37894671, Handler_write,27010243, Key_blocks_used,353833, Key_read_requests,341367134, Key_reads,1172755, Key_write_requests,47942190, Key_writes,38265419, Max_used_connections,106, Not_flushed_key_blocks,0, Not_flushed_delayed_rows,0, Open_tables,512, Open_files,873, Open_streams,0, Opened_tables,30492, Questions,25298875, Qcache_queries_in_cache,23808, Qcache_inserts,2368548, Qcache_hits,8206562, Qcache_lowmem_prunes,29375, Qcache_not_cached,784005, Qcache_free_memory,40477464, Qcache_free_blocks,2311, Qcache_total_blocks,50436, Rpl_status,NULL, Select_full_join,12285, Select_full_range_join,16, Select_range,70254, Select_range_check,1, Select_scan,1618573, Slave_open_temp_tables,0, Slave_running,OFF, Slow_launch_threads,0, Slow_queries,201, Sort_merge_passes,0, Sort_range,132898, Sort_rows,2993422, Sort_scan,576536, Table_locks_immediate,17191738, Table_locks_waited,70304, Threads_cached,4, Threads_created,12439, Threads_connected,18, Threads_running,1, Uptime,1186406, Variable_name,Value, back_log,50, basedir,/usr/local/mysql/, binlog_cache_size,32768, bulk_insert_buffer_size,8388608, character_set,latin1, character_sets,big5 latin2_czech_ci dec8_swedish_ci cp850_general_ci latin1_german1_ci hp8_english_ci koi8r_general_ci latin1_swedish_ci latin2_general_ci swe7_swedish_ci ascii_general_ci ujis sjis cp1251_bulgarian_ci latin1_danish_ci hebrew tis620 euckr latin7_estonian_ci latin2_hungarian_ci koi8u_general_ci cp1251_ukrainian_ci gb2312 greek cp1250_general_ci latin2_croatian_ci gbk cp1257_lithuanian_ci latin5_turkish_ci latin1_german2_ci armscii8_general_ci utf8 cp1250_czech_ci ucs2 cp866_general_ci keybcs2 macce macroman cp852_general_ci latin7_general_ci latin7_general_cs macce_bin macce_ci macce_cs latin1_bin latin1_general_ci latin1_general_cs cp1251_bin cp1251_general_ci cp1251_general_cs macroman_bin macroman_ci macroman_cs cp1256_general_ci cp1257_bin cp1257_ci_ai cp1257_ci cp1257_cs binary armscii_bin ascii_bin cp1250_bin cp1256_bin cp866_bin dec8_bin greek_bin hebrew_bin hp8_bin keybcs2_bin koi8r_bin koi8u_bin latin2_bin latin5_bin latin7_bin cp850_bin cp852_bin swe7_bin utf8_bin, client_collation,latin1_swedish_ci, concurrent_insert,ON, connect_timeout,5, convert_result_charset,ON, datadir,/usr/local/mysql/data/, default_week_format,0, delay_key_write,ON, delayed_insert_limit,100, delayed_insert_timeout,300, delayed_queue_size,1000, expire_logs_days,0, flush,OFF,
Re: what does Rows_examined mean exactly?
hrm.. but i tried SELECT * FROM post ORDER BY postdate DESC LIMIT 771297, 30 postdate is not primary key but just an INDEX it still examined 771297 rows From: Michael Stassen [EMAIL PROTECTED] To: tinys xuefer [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: what does Rows_examined mean exactly? Date: Sun, 25 Jul 2004 10:32:49 -0400 MIME-Version: 1.0 Received: from out014.verizon.net ([206.46.170.46]) by mc4-f19.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Sun, 25 Jul 2004 07:32:49 -0700 Received: from verizon.net ([68.163.178.105]) by out014.verizon.net (InterMail vM.5.01.06.06 201-253-122-130-106-20030910) with ESMTP id [EMAIL PROTECTED]; Sun, 25 Jul 2004 09:32:49 -0500 X-Message-Info: JGTYoYF78jFocj+u73FHpy/MHvLpLYvD Message-ID: [EMAIL PROTECTED] User-Agent: Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; en-US; rv:1.4) Gecko/20030624 Netscape/7.1 X-Accept-Language: en-us, en References: [EMAIL PROTECTED] In-Reply-To: [EMAIL PROTECTED] X-Authentication-Info: Submitted using SMTP AUTH at out014.verizon.net from [68.163.178.105] at Sun, 25 Jul 2004 09:32:49 -0500 Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 25 Jul 2004 14:32:50.0020 (UTC) FILETIME=[43016240:01C47254] tinys xuefer wrote: slow.log: # Query_time: 14 Lock_time: 0 Rows_sent: 30 Rows_examined: 771327 SELECT * FROM `post` LIMIT 771297, 30; i dp have privmary key on table `post` does 'Rows_examined: 771327' means mysqlserver read through those 771327 rows to get 30 rows? it takes 14 seconds! possible to show 'Rows_examined' in a explain or other commands? slow log is hard to debug.. and possible to optimize? But you didn't use the primary key! In fact, you didn't ask for any order at all. Mysql does not try to guess that you meant to order by the primary key, it simpply does what you tell it. Your query, in effect, tells mysql to pick 771327 rows from post in any order and send you the last 30. Assuming your primary key column is named id, you need to change this query to SELECT * FROM post ORDER BY id LIMIT 771297, 30; With the explicit ORDER BY on the primary key, mysql will use the index to quickly find the 30 rows you want. Michael _ Help STOP SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL 5.0 compiling embedded server with VS 2003/Windows
I have not been able to link my app writen in Visual Objects to lymysqld (version 4.1.13) I got an error in the sever_init mysql_server_init(0, NULL_PTR, NULL_PTR); the prototype is defined as _dll function mysql_server_init( nInt as Int, arg as PTR, grp as PTR) as Int Pascal:libmysqld.mysql_server_init I decided to recompile the library, particularly version 5.0 (I read also in this group that version 4.1.13 is not compilable with VS) of mysql and I got the following errors after compilining it with Visual studio 2003: Linking\libmysqld.def(2) : warning LNK4017: DESCRIPTION statement not supported for the target platform; ignored Creating library ../lib_debug/libmysqld.lib and object ../lib_debug/libmysqld.expclient.obj : error LNK2019: unresolved external symbol _vio_new_win32shared_memory referenced in function _create_shared_memoryclient.obj : error LNK2019: unresolved external symbol _vio_description referenced in function _net_safe_readclient.obj : error LNK2019: unresolved external symbol _vio_poll_read referenced in function _cli_mysql_real_connectclient.obj : error LNK2019: unresolved external symbol _vio_new referenced in function _cli_mysql_real_connectclient.obj : error LNK2019: unresolved external symbol _vio_new_win32pipe referenced in function _cli_mysql_real_connectnet_serv.obj : error LNK2019: unresolved external symbol _vio_fd referenced in function _my_net_init../lib_debug/libmysqld.dll : fatal error LNK1120: 6 unresolved externals I included the two preprocessors as stated elsewhere in this newgroup: SAFEMALLOC andSAFE_MUTEX IS version 4.1.13 or 5.0 compilable with Visual studio? Do I need to run a script mysql_config since version 4.1.13 and version 5.0 of mysql already includes a folder libmysqld for embedded server? If I have to run mysql_config, ho do I run it in windows (it is not recognized as a batch script) Regards Hernando - Do you Yahoo!? New and Improved Yahoo! Mail - Send 10MB messages!
run-all-test
Have done a fresh install of 4.0.20 and am getting some odd errors when
running as root when I peform the tests:
***
C:\mysql\benchperl run-all-tests --user=root --password=tops3cr3t
Benchmark DBD suite: 2.15
Date of test:2004-07-25 22:11:28
Running tests on:Windows NT 5.1 x86
Arguments:
Comments:
Limits from:
Server version: MySQL 4.0.20a nt
Optimization:None
Hardware:
alter-table: Total time: 32 wallclock secs ( 0.06 usr 0.02 sys + 0.00 cusr
0.
00 csys = 0.08 CPU)
ATIS: Total time: 35 wallclock secs (12.09 usr 5.26 sys + 0.00 cusr 0.00
csys
= 17.36 CPU)
big-tables: Total time: 29 wallclock secs (10.31 usr 10.36 sys + 0.00 cusr
0.0
0 csys = 20.67 CPU)
connect: DBI connect('database=test;host=localhost','root',...) failed:
Can't co
nnect to MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
DBI connect('database=test;host=localhost','root',...) failed: Can't connect
to
MySQL server on 'localhost' (10048) at ./test-connect line 69
Got error 'Can't connect to MySQL server on 'localhost' (10048)' after 3940
conn
ects at ./test-connect line 79.
Warning: Can't execute connect. Check the file
'output/connect-mysql-NT_5.1'
Failed (output/connect-mysql-NT_5.1)
create: Got error: 'Can't connect to MySQL server on 'localhost' (10048)'
when c
onnecting to DBI:mysql:database=test;host=localhost with user: 'root'
password:
'tops3cr3t'
Compilation failed in require at ./test-create line 41.
Warning: Can't execute create. Check the file 'output/create-mysql-NT_5.1'
Failed (output/create-mysql-NT_5.1)
insert: Got error: 'Can't connect to MySQL server on 'localhost' (10048)'
when c
onnecting to DBI:mysql:database=test;host=localhost with
