RE: Python related MySQL question
Look at db.use_result() and db.store_result() here : http://www.birgerblixt.com/doc/packages/python-mysql/MySQLdb-2.html#ss2. 2 Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Smelly Socks [mailto:[EMAIL PROTECTED] Sent: mardi 26 avril 2005 06:20 To: mysql@lists.mysql.com Subject: Re: Python related MySQL question Hi! I am porting a function library app I wrote in PHP to Python. At work they only use Python. I've researched how to connect to a MySql database using Python, and how to retrieve rows. However, I am wondering how to do the following: I can do this in Python = $da=MYSQL_QUERY(select * from prefs where user_name='$user_name' ); I can do this in Python = $peek=mysql_fetch_array($da); I cannot do the following: $title =$peek[4]; //title window $logic =$peek[5]; //logic window Can anyone shed light on how to get the pieces of the array and stick them into variables? Thanks very much! Cheers! -Warren - Original Message - From: Spenser [EMAIL PROTECTED] To: David Bailey [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Thursday, April 14, 2005 12:27 PM Subject: Re: book advice Check out MySQL Tutorial by Luke Welling (MySQL Press). It's easy to understand and not overwhelming. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ibdata1 grows beyound borders..
Hi, I've been running a innodbdatabase for a while, and it works nice, however, I've noticed that it have grown beyound the specifications in the my.cnf file. I did define autoextend so its not very strange, however, I was wondering, how far can it grow, and is it a bad idea to just let it grow? the my.cnf looks like: [mysqld] datadir=/usr/local/mysql/data/ socket=/tmp/mysql.sock set-variable = max_connections=200 set-variable = thread_cache_size=20 innodb_data_home_dir = innodb_data_file_path = /usr/local/mysql/data/ibdata1:2000M:autoextend set-variable = innodb_buffer_pool_size=200M set-variable = innodb_additional_mem_pool_size=20M innodb_log_group_home_dir = /usr/local/mysql/data/innodb/ innodb_log_arch_dir = /usr/local/mysql/data/innodb/ set-variable = innodb_log_files_in_group=2 set-variable = innodb_log_file_size=50M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 set-variable = long_query_time=15 #log-long-format log-slow-queries = /var/log/mysqld.slow.log [mysql.server] user=mysql basedir=/usr/local/mysql [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/usr/local/mysql/data/db.bcmanga.se.pid The ibdata1 is now around 7gb and it keeps growing.. How big can it be? Is it innodb dependent or os dependent? I'm currently running Fedora Core release 3 (Heidelberg) on a dual xeon 3ghz with 3gb ram. Can I just define some more ibdata files and it will re-arrange the data by itself, or do I need to export and them import it? Or whats the best practice here? Thanks in advance, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database languages supported by MySQL
Hallo, I would like to know whether there are any other languages supported by MySQL apart from SQL. e.g. MsAccess provides VB for aplications. Does MySQL support any such lower level language? Regards Pamela -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE and STR_TO_DATE
Hi, using mysql 4.0.24 I'm stuck with a problem importing a CSV-file converting the date in the CSV from MM/DD/ to a mysql table.. I'm trying: LOAD DATA INFILE '/tmp/mydata.csv' INTO TABLE mytable FIELDSTERMINATED BY ','OPTIONALLY ENCLOSED BY '' (recordType, serviceType,STR_TO_DATE(gmtDate,'%m/%d/%Y'); The date in the CSV-file field #3 is in format MM/DD/ and therefore not importet correctly; Obvisouly I'm missing something on how to use functions for columns with LOAD DATA INFILE ? TIA, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database languages supported by MySQL
From: Ochungo, Pamela (ILRI) I would like to know whether there are any other languages supported by MySQL apart from SQL. e.g. MsAccess provides VB for aplications. Does MySQL support any such lower level language? MySQL is a database management system (DBMS), a so called backend system. MS Access is a DBMS (backend) plus graphical frontend. The frontend of MS Access has VBA for all kinds of scripting purposes. If you use Perl or PHP as a frontend you can use those languages to process query results, etc. If you use something like OpenOffice.org as a frontend you can use the Basic language that is built into OpenOffice.org. MySQL provides a very, very basic frontend through the MySQL client that provides the MySQL prompt, but this frontend (client) does not contain any scripting language. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE and STR_TO_DATE
using mysql 4.0.24 I'm stuck with a problem importing a CSV-file converting the date in the CSV from MM/DD/ to a mysql table.. I'm trying: LOAD DATA INFILE '/tmp/mydata.csv' INTO TABLE mytable FIELDSTERMINATED BY ','OPTIONALLY ENCLOSED BY '' (recordType, serviceType,STR_TO_DATE(gmtDate,'%m/%d/%Y'); The date in the CSV-file field #3 is in format MM/DD/ and therefore not importet correctly; Obvisouly I'm missing something on how to use functions for columns with LOAD DATA INFILE ? I'm afraid that the syntax states that you can only use a list of column names. I think the best you can do is make an extra varchar column to hold the raw 'date' value from the csv and use an extra query to convert it into a format suitable for the gmtDate column. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database languages supported by MySQL
On 4/25/05, Ochungo, Pamela (ILRI) [EMAIL PROTECTED] wrote: Hallo, I would like to know whether there are any other languages supported by MySQL apart from SQL. e.g. MsAccess provides VB for aplications. Does MySQL support any such lower level language? I believe you are asking about so called procedural languages, as seen in most other DBs (Oracle's PLSQL, PostgreSQL PLpgSQL/plperl/pltcl/plpython, DB2's SQL PL, etc.). If you want them you have to use bleeding edge :) MySQL version 5.0 or later (see: http://dev.mysql.com/doc/mysql/en/stored-procedures.html). Haven't used it though. Regards, Dawid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ibdata1 grows beyound borders..
Hello. Searching in the archives says you could get worse performance, because of extending during transactions: http://lists.mysql.com/mysql/180037 http://lists.mysql.com/mysql/170946 Eric Persson [EMAIL PROTECTED] wrote: Hi, I've been running a innodbdatabase for a while, and it works nice, however, I've noticed that it have grown beyound the specifications in the my.cnf file. I did define autoextend so its not very strange, however, I was wondering, how far can it grow, and is it a bad idea to just let it grow? the my.cnf looks like: [mysqld] datadir=/usr/local/mysql/data/ socket=/tmp/mysql.sock set-variable = max_connections=200 set-variable = thread_cache_size=20 innodb_data_home_dir = innodb_data_file_path = /usr/local/mysql/data/ibdata1:2000M:autoextend set-variable = innodb_buffer_pool_size=200M set-variable = innodb_additional_mem_pool_size=20M innodb_log_group_home_dir = /usr/local/mysql/data/innodb/ innodb_log_arch_dir = /usr/local/mysql/data/innodb/ set-variable = innodb_log_files_in_group=2 set-variable = innodb_log_file_size=50M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 set-variable = innodb_lock_wait_timeout=50 set-variable = long_query_time=15 #log-long-format log-slow-queries = /var/log/mysqld.slow.log [mysql.server] user=mysql basedir=/usr/local/mysql [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/usr/local/mysql/data/db.bcmanga.se.pid The ibdata1 is now around 7gb and it keeps growing.. How big can it be? Is it innodb dependent or os dependent? I'm currently running Fedora Core release 3 (Heidelberg) on a dual xeon 3ghz with 3gb ram. Can I just define some more ibdata files and it will re-arrange the data by itself, or do I need to export and them import it? Or whats the best practice here? Thanks in advance, Eric -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
If my englsih is so bad, i'll try to explain and stop this thread now. That's not what was being said. I'm not teaching, i'm answering questions. If someone wants to read docs, he (she) doesn't ask a question on the list. So if i answer, i answer the question, just the question. You want to know my level of knowledgne, 10 years, oracle, sybase, sqlserver, db2. I can help for migration from or to... I said don't use joins for the query given in the example or queries using just the joining columns from the first table. Normal forms is bla bla here ... 10 yrs? Time to read a book then. -- Martijn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ibdata1 grows beyound borders..
Gleb Paharenko wrote: Hello. Searching in the archives says you could get worse performance, because of extending during transactions: Yes, read a few of those, but how about the fix for this, will the data rearrange itself automatically when I specify more files? //Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: modifying InnoDB storage
Unfortunately no response from the list anymore :-( Can we ever get rid of the autoexpanding ibdata file without completely rebuilding the databases? Regards, Jigal. - Original Message - From: Jigal van Hemert [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 20, 2005 12:31 PM Subject: Re: modifying InnoDB storage From: Gleb Paharenko Hi Gleb, Thanks for your response. http://dev.mysql.com/doc/mysql/en/adding-and-removing.html If it currently reads: innodb_data_file_path = /ibdata/ibdata1:2000M;/ibdata/ibdata2:2000M;/ibdata/ibdata3:2000M;/ibdata/ib data4:10M:autoextend Can you change this into: = /ibdata/ibdata1:2000M;/ibdata/ibdata2:2000M;/ibdata/ibdata3:2000M;/ibdata/ib data4:2000M;/ibdata/ibdata5:10M:autoexten or = /ibdata/ibdata1:2000M;/ibdata/ibdata2:2000M;/ibdata/ibdata3:2000M;/ibdata/ib data5:2000M;/ibdata/ibdata4:10M:autoexten ??? Think also about per-table tablespaces. See: Sorry, it's still 4.0.x that our sysadmin wants... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query question
Yes ten years and forgot mysql certified. I can offer i-am-a-dummy to you if you lack. I've never imagined find so bad people on the list. But i'll write to the moderator to see who is on. But i'm pleased to help people wihout naz mentality than yours. Best Regards Mathias FATENE Hope that helps *This not an official mysql support answer -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: mardi 26 avril 2005 12:29 To: mysql@lists.mysql.com Subject: Re: Query question If my englsih is so bad, i'll try to explain and stop this thread now. That's not what was being said. I'm not teaching, i'm answering questions. If someone wants to read docs, he (she) doesn't ask a question on the list. So if i answer, i answer the question, just the question. You want to know my level of knowledgne, 10 years, oracle, sybase, sqlserver, db2. I can help for migration from or to... I said don't use joins for the query given in the example or queries using just the joining columns from the first table. Normal forms is bla bla here ... 10 yrs? Time to read a book then. -- Martijn -- 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: Query question
Mathias There are no *bad* people on this list - different point of view, yes. Participating on this and other lists requires give AND take - taking advice as well as giving it... Participating is always going to be a two way process so just accept it, and if you can't - unsubscribe. Hope this helps Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why NOT NULL in PRIMARY key??
http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). Why is this? I actually do need NULL values (they really are not equal to '0', etc.), but also need this column as part of the PRIMARY key in an InnoDB table... It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: why NOT NULL in PRIMARY key??
[snip] http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). Why is this? I actually do need NULL values (they really are not equal to '0', etc.), but also need this column as part of the PRIMARY key in an InnoDB table... It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. [/snip] Since NULL is the absence of a value and PRIMARY keys must have a value a NULL column cannot be included as a portion of a PRIMARY key. AFAIK this is the case with every RDBMS out there. Asking the development team might get you a more informative response. There is a vast difference between a zero value, blank value and NULL (absence of value). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database languages supported by MySQL
The manual is your friend. See this link: http://dev.mysql.com/doc/mysql/en/features.html. Rhino - Original Message - From: Ochungo, Pamela (ILRI) [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, April 25, 2005 3:20 AM Subject: Database languages supported by MySQL Hallo, I would like to know whether there are any other languages supported by MySQL apart from SQL. e.g. MsAccess provides VB for aplications. Does MySQL support any such lower level language? Regards Pamela -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 21/04/2005 -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 21/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: extract numeric value from a string.
Eamon Daly wrote: Easy enough. Get the numeric part via CONVERT, then get the rest of the string from the length of the numeric part, plus one: SELECT tag, @num := CONVERT(tag, SIGNED) AS num_part, SUBSTRING(tag, LENGTH(@num) + 1) AS rest_of_string from tags; ++--++ | tag| num_part | rest_of_string | ++--++ | 1foo |1 | foo| | 23bar | 23 | bar| | 234baz | 234 | baz| ++--++ 3 rows in set (0.00 sec) Eamon Daly Unless the string starts wit a '0'. - Original Message - From: dixie [EMAIL PROTECTED] To: MySQL mysql@lists.mysql.com Sent: Friday, April 22, 2005 6:18 PM Subject: extract numeric value from a string. Hi at all, I've this necessity. In a table I've a field popolated by a string where the first (not costant lenght) part are number and the second part caracter. I want extract, in other field, the first part and the second in another field. There is a function to obtained it? Tks in advance Paolo -- dixie [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: why NOT NULL in PRIMARY key??
Not every DBMS... MSSQL: Create Unique Index Microsoft(r) SQL Server(tm) checks for duplicate values when the index is created (if data already exists) and checks each time data is added with an INSERT or UPDATE statement. If duplicate key values exist, the CREATE INDEX statement is canceled and an error message giving the first duplicate is returned. Multiple NULL values are considered duplicates when UNIQUE index is created. SYBASE: Create Unique Index Prohibits duplicate index (also called key) values. The system checks for duplicate key values when the index is created (if data already exists), and checks each time data is added with an insert or update. If there is a duplicate key value or if more than one row contains a null value, the command is aborted and an error message giving the duplicate is printed. Ed -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 26, 2005 6:50 AM To: Jigal van Hemert; mysql@lists.mysql.com Subject: RE: why NOT NULL in PRIMARY key?? Since NULL is the absence of a value and PRIMARY keys must have a value a NULL column cannot be included as a portion of a PRIMARY key. AFAIK this is the case with every RDBMS out there. Asking the development team might get you a more informative response. There is a vast difference between a zero value, blank value and NULL (absence of value). -- 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: why NOT NULL in PRIMARY key??
http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). Why is this? I actually do need NULL values (they really are not equal to '0', etc.), but also need this column as part of the PRIMARY key in an InnoDB table... It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. Ehm... it might be me - but what sense does it make to have a NULL in a PK? If you need this, then your primary key probably isn't a primary key. Care to explain why and how you're designing your database? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
From: Jay Blanchard Since NULL is the absence of a value and PRIMARY keys must have a value a NULL column cannot be included as a portion of a PRIMARY key. AFAIK this is the case with every RDBMS out there. Asking the development team might get you a more informative response. There is a vast difference between a zero value, blank value and NULL (absence of value). The situation is pretty challenging: - because of high concurrency and huge tables I need to use InnoDB - InnoDB really needs a PRIMARY key for fast results - a PRIMARY key does not allow NULL values as part of the key Anyhow, we something to think about... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
performance problem on INSERT into MyISAM table
I have been trying to run an fairly large INSERT into an empty table joining two other tables now for several weeks and have not been able to get the query to run to completion even when sub-seting the data into smaller ranges. I have tried this at MySQL releases 4.1.8a and 4.1.10a with no noticable improvement. The server is running Linux 2.4.21-4 Elsmp RedHat 3.2.3.-20. The server is dedicated to MySQL. The my.cnf file is attached. There are 8 Gbytes of RAM and 2, Hyperthreaded CPUs (top shows 4 processors). SHOW STATUS shows very good buffer hit ratio. Current stats are attached. Server was re-booted last Friday. It has been running the INSERT query since Saturday mid-morning and has yet to complete. Table 1 230 million rows total compund PK index 4 columns range 1 should select 35 million rows. Explain plan shows it to be using the PK Table 2 598 million rows compound PK index 4 columns and one secondary index. Range 1 should select about 130 million rows Table 3 Empty table unindexed. Two keys from Table 1 and 17 columns from table 2 populate this table The general form of the query is: INSERT INTO Table 3 (col1, . Col18) SELECT Col1,,.col18 FROM Table 1 a INNER JOIN Table 2 b ON (PK columns and range selection) WHERE a.col5 = b.col5 AND . AND AND a.col18 = b.col18 Any suggestions are welcome. Ed Sweeney Variable_name Value Aborted_clients 52 Aborted_connects5 Binlog_cache_disk_use 0 Binlog_cache_use0 Bytes_received 337148 Bytes_sent 40680011 Com_admin_commands 0 Com_alter_db0 Com_alter_table 0 Com_analyze 0 Com_backup_table0 Com_begin 0 Com_change_db 38 Com_change_master 0 Com_check 0 Com_checksum0 Com_commit 0 Com_create_db 0 Com_create_function 0 Com_create_index0 Com_create_table1 Com_dealloc_sql 0 Com_delete 0 Com_delete_multi0 Com_do 0 Com_drop_db 0 Com_drop_function 0 Com_drop_index 0 Com_drop_table 0 Com_drop_user 0 Com_execute_sql 0 Com_flush 3 Com_grant 4 Com_ha_close0 Com_ha_open 0 Com_ha_read 0 Com_help0 Com_insert 0 Com_insert_select 4 Com_kill2 Com_load0 Com_load_master_data0 Com_load_master_table 0 Com_lock_tables 0 Com_optimize0 Com_preload_keys0 Com_prepare_sql 0 Com_purge 0 Com_purge_before_date 0 Com_rename_table0 Com_repair 0 Com_replace 0 Com_replace_select 0 Com_reset 0 Com_restore_table 0 Com_revoke 0 Com_revoke_all 0 Com_rollback0 Com_savepoint 0 Com_select 90 Com_set_option 15 Com_show_binlog_events 0 Com_show_binlogs0 Com_show_charsets 1 Com_show_collations 34 Com_show_column_types 0 Com_show_create_db 0 Com_show_create_table 2 Com_show_databases 3 Com_show_errors 0 Com_show_fields 25 Com_show_grants 0 Com_show_innodb_status 0 Com_show_keys 20 Com_show_logs 0 Com_show_master_status 0 Com_show_new_master 0 Com_show_open_tables0 Com_show_privileges 0 Com_show_processlist6603 Com_show_slave_hosts0 Com_show_slave_status 0 Com_show_status 6619 Com_show_storage_engines0 Com_show_tables 5 Com_show_variables 29 Com_show_warnings 0 Com_slave_start 0 Com_slave_stop 0 Com_truncate0 Com_unlock_tables 0 Com_update 0 Com_update_multi0 Connections 89 Created_tmp_disk_tables 2 Created_tmp_files 3 Created_tmp_tables 4 Delayed_errors 0 Delayed_insert_threads 0 Delayed_writes 0 Flush_commands 1 Handler_commit 0 Handler_delete 0 Handler_discover0 Handler_read_first 14 Handler_read_key34664442 Handler_read_next 2460468597 Handler_read_prev 0 Handler_read_rnd42 Handler_read_rnd_next 184703062 Handler_rollback0 Handler_update 1 Handler_write 348602337 Key_blocks_not_flushed 0 Key_blocks_unused 590180 Key_blocks_used 1811829 Key_read_requests 1691116639 Key_reads 16737509 Key_write_requests 148881620 Key_writes 1949178 Max_used_connections5 Not_flushed_delayed_rows0 Open_files 43 Open_streams0 Open_tables 23 Opened_tables 51 Qcache_free_blocks 1 Qcache_free_memory 25094216 Qcache_hits 14 Qcache_inserts 15 Qcache_lowmem_prunes0 Qcache_not_cached 47 Qcache_queries_in_cache 14
Re: why NOT NULL in PRIMARY key??
On 4/26/05, Jigal van Hemert wrote: http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). Why is this? Because the SQL standard says so. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: why NOT NULL in PRIMARY key??
At 08:49 AM 4/26/05, Jay Blanchard wrote: [snip] http://dev.mysql.com/doc/mysql/en/silent-column-changes.html mentions that Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). Why is this? I actually do need NULL values (they really are not equal to '0', etc.), but also need this column as part of the PRIMARY key in an InnoDB table... It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. [/snip] Since NULL is the absence of a value and PRIMARY keys must have a value a NULL column cannot be included as a portion of a PRIMARY key. AFAIK this is the case with every RDBMS out there. Asking the development team might get you a more informative response. I'm not on dev team, but my understanding of RDMS theory is that retrieving data via PK will always return a single row from the table. Since NULL values are never considered equal to each other, allowing them in a column that is part of PK would bypass this expected behaviour. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: modifying InnoDB storage
Jigal, - Alkuperinen viesti - Lhettj: Jigal van Hemert [EMAIL PROTECTED] Vastaanottaja: mysql@lists.mysql.com Kopio: Gleb Paharenko [EMAIL PROTECTED]; Heikki Tuuri [EMAIL PROTECTED] Lhetetty: Tuesday, April 26, 2005 2:09 PM Aihe: Fw: modifying InnoDB storage Unfortunately no response from the list anymore :-( Can we ever get rid of the autoexpanding ibdata file without completely rebuilding the databases? unfortunately no. ibdata files cannot be removed or truncated. You should consider using innodb_file_per_table if disk space is sparse. Regards, Jigal. Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ - Original Message - From: Jigal van Hemert [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, April 20, 2005 12:31 PM Subject: Re: modifying InnoDB storage From: Gleb Paharenko Hi Gleb, Thanks for your response. http://dev.mysql.com/doc/mysql/en/adding-and-removing.html If it currently reads: innodb_data_file_path = /ibdata/ibdata1:2000M;/ibdata/ibdata2:2000M;/ibdata/ibdata3:2000M;/ibdata/ib data4:10M:autoextend Can you change this into: = /ibdata/ibdata1:2000M;/ibdata/ibdata2:2000M;/ibdata/ibdata3:2000M;/ibdata/ib data4:2000M;/ibdata/ibdata5:10M:autoexten or = /ibdata/ibdata1:2000M;/ibdata/ibdata2:2000M;/ibdata/ibdata3:2000M;/ibdata/ib data5:2000M;/ibdata/ibdata4:10M:autoexten ??? Think also about per-table tablespaces. See: Sorry, it's still 4.0.x that our sysadmin wants... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to make a virtual SELECT?
Hello, I have a big problem, I only want to check if it's the minute 45 currently. I want to make a virtual SELECT without tables: mysql SELECT MINUTE(CURRENT_TIMESTAMP()) WHERE MINUTE(CURRENT_TIMESTAMP()) = 45; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE MINUTE(CURRENT_TIMESTAMP()) = 45' at line 1 mysql It works only when I put a FROM with an existing table on it. Is there a solution to do it without FROM or to use a virtual table? Thanks in advance for your help. -- Best regards, Stephan Ferraro NOOFS - Network Object Oriented File System for UNIX platforms. Core Developper - http://noofs.com/ GnuPG public key: gpg --keyserver www.keyserver.net --recv-key 94B2664F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: why NOT NULL in PRIMARY key??
Ed, UNIQUE indexes (which you quote) and PRIMARY KEYS are similar but are NOT the same thing. Both types of keys guard against duplication of values for the tuple defining the index. However, PRIMARY KEYs hold special significance in that many RDBMS storage engines will use the PK to uniquely identify each row and not use an internally generated ROWID (at least that happens for InnoDB). PKs are critical to data integrity (and usually internal table organization,too) and are frequently used as one side of a Foreign Key (FK) relationship. http://dev.mysql.com/doc/mysql/en/table-and-index.html = If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. If there is no such index in the table, InnoDB internally generates a clustered index where the rows are ordered by the row ID that InnoDB assigns to the rows in such a table. = http://dev.mysql.com/doc/mysql/en/create-table.html = In MySQL, a UNIQUE index is one in which all values in the index must be distinct. An error occurs if you try to add a new row with a key that matches an existing row. The exception to this is that if a column in the index is allowed to contain NULL values, it can contain multiple NULL values. This exception does not apply to BDB tables, for which an indexed column allows only a single NULL. = A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and an application asks for the PRIMARY KEY in your tables, MySQL returns the first UNIQUE index that has no NULL columns as the PRIMARY KEY. = Notice that UNIQUE indexes in MySQL allow multiple nullable columns. However because of their critical importance to record identification, PRIMARY KEYs cannot contain null values. This is true even for the other systems you quoted http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_888k.asp (MSDN online documentation for MS SQL Server) A table usually has a column or combination of columns whose values uniquely identify each row in the table. This column (or columns) is called the primary key of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or alter a table. A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints ensure unique data, they are often defined for identity column. === http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/21064 (online Sybase Transact-SQL User's guide) = You can declare unique or primary key constraints to ensure that no two rows in a table have the same values in the specified columns. Both constraints create unique indexes to enforce this data integrity. However, primary key constraints are more restrictive than unique constraints. Columns with primary key constraints cannot contain a NULL value. You normally use a table's primary key constraint in conjunction with referential integrity constraints defined on other tables. = Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 04/26/2005 09:16:03 AM: Not every DBMS... MSSQL: Create Unique Index Microsoft(r) SQL Server(tm) checks for duplicate values when the index is created (if data already exists) and checks each time data is added with an INSERT or UPDATE statement. If duplicate key values exist, the CREATE INDEX statement is canceled and an error message giving the first duplicate is returned. Multiple NULL values are considered duplicates when UNIQUE index is created. SYBASE: Create Unique Index Prohibits duplicate index (also called key) values. The system checks for duplicate key values when the index is created (if data already exists), and checks each time data is added with an insert or update. If there is a duplicate key value or if more than one row contains a null value, the command is aborted and an error message giving the duplicate is printed. Ed -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 26, 2005 6:50 AM To: Jigal van Hemert; mysql@lists.mysql.com Subject: RE: why NOT NULL in PRIMARY key?? Since NULL is the absence of a
Re: How to make a virtual SELECT?
[EMAIL PROTECTED] wrote on 26/04/2005 14:46:37: Hello, I have a big problem, I only want to check if it's the minute 45 currently. I want to make a virtual SELECT without tables: mysql SELECT MINUTE(CURRENT_TIMESTAMP()) WHERE MINUTE(CURRENT_TIMESTAMP()) = 45; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE MINUTE(CURRENT_TIMESTAMP()) = 45' at line 1 mysql It works only when I put a FROM with an existing table on it. Is there a solution to do it without FROM or to use a virtual table? What response do you want, exactly? Try either of the two formulations below, depending upon whether you want to know what the minute is, or just whether or not it is 45 (returns 1 if it is). mysql select minute(now()) ; +---+ | minute(now()) | +---+ |58 | +---+ 1 row in set (0.06 sec) mysql select minute(now()) = 45 ; ++ | minute(now()) = 45 | ++ | 0 | ++ 1 row in set (0.00 sec) Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
From: Martijn Tonies Ehm... it might be me - but what sense does it make to have a NULL in a PK? If you need this, then your primary key probably isn't a primary key. Care to explain why and how you're designing your database? Martijn, The table contains an variable number of integer parameters for accounts: id INT(11) - accountID name VARCHAR(32) - parameter name value INT(11) - parameter value Other tables contain string, datetime, etc. parameters. Since most searches are made for a value (or range) of one or more parameters, a usable primary key is: name-value-id (on might argue that a part of the name would be sufficient, but that is not the issue here). Indeed almost all queries are very fast. A NULL value indicates that it is a mandatory field that was not filled with (a valid) value or that the value was reset, or... Deleting the record is also not very nice, since it will prevent the use of (inner) joins. I have to think back why we dropped the left joins and started using NULL values, but the first thing that came to mind was an increase in speed. An extra challenge: the queries are generated because of the large number of different queries that might be used. We can switch between left joins and inner joins quickly though. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
At 15:20 +0200 4/26/05, Jigal van Hemert wrote: From: Jay Blanchard Since NULL is the absence of a value and PRIMARY keys must have a value a NULL column cannot be included as a portion of a PRIMARY key. AFAIK this is the case with every RDBMS out there. Asking the development team might get you a more informative response. There is a vast difference between a zero value, blank value and NULL (absence of value). The situation is pretty challenging: - because of high concurrency and huge tables I need to use InnoDB - InnoDB really needs a PRIMARY key for fast results - a PRIMARY key does not allow NULL values as part of the key Anyhow, we something to think about... Regards, Jigal. A primary key absolutely forbids duplicate values. Indexes created with the UNIQUE keyword do not allow duplicates, except for the special case that multiple NULL values are allowed. Such a unique index therefore cannot be a primary key. However, for the purposes you describe above, it sounds like the solution is simply to define your columns as allowing NULL, and to create your index using UNIQUE rather than PRIMARY KEY. -- 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]
mysqldump privileges
Hello, what type of privileges need to be assigned and/or changed when upgrading form MySQL 4.0.24 to 4.1.10 in order to allow users use of the mysqldump utility. Users where allowed to use the mysqldump to backup their databases and worked fine with 4.0.24, till the upgrade to 4.1.10 which no will not allow them access to their respective database any more. We get alot of access denied to databases... lock tables.. or other options. TIA -- Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://thunder-rain.com/ Tel: 1.712.395.0670 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to make a virtual SELECT?
[EMAIL PROTECTED] wrote on 04/26/2005 09:46:37 AM: Hello, I have a big problem, I only want to check if it's the minute 45 currently. I want to make a virtual SELECT without tables: mysql SELECT MINUTE(CURRENT_TIMESTAMP()) WHERE MINUTE(CURRENT_TIMESTAMP()) = 45; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE MINUTE(CURRENT_TIMESTAMP()) = 45' at line 1 mysql It works only when I put a FROM with an existing table on it. Is there a solution to do it without FROM or to use a virtual table? Thanks in advance for your help. -- Best regards, Stephan Ferraro NOOFS - Network Object Oriented File System for UNIX platforms. Core Developper - http://noofs.com/ GnuPG public key: gpg --keyserver www.keyserver.net --recv-key 94B2664F There are several ways to approach this. My preferred method (because it is less version dependent) is to do your logical comparison AS your SELECT term and check for 0 or 1 as a result in your code SELECT (MINUTE(CURRENT_TIMESTAMP())=45); However, if you are stuck using the full select statement try FROM Dual. It's a compatibility enhancement (I think to keep converted Oracle code working) that was added in of 4.1.0. SELECT MINUTE(CURRENT_TIMESTAMP()) FROM dual WHERE MINUTE(CURRENT_TIMESTAMP()) = 45; (http://dev.mysql.com/doc/mysql/en/select.html) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: why NOT NULL in PRIMARY key??
In article [EMAIL PROTECTED], Jigal van Hemert [EMAIL PROTECTED] writes: From: Martijn Tonies Ehm... it might be me - but what sense does it make to have a NULL in a PK? If you need this, then your primary key probably isn't a primary key. Care to explain why and how you're designing your database? Martijn, The table contains an variable number of integer parameters for accounts: id INT(11) - accountID name VARCHAR(32) - parameter name value INT(11) - parameter value Other tables contain string, datetime, etc. parameters. Since most searches are made for a value (or range) of one or more parameters, a usable primary key is: name-value-id That's a horrible denormalization. If one named parameter can hold only one INT value for one account id, then (id, name) could be a primary key; otherwise, you'd need a surrogate primary key. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
From: Paul DuBois Hi Paul, A primary key absolutely forbids duplicate values. Indexes created with the UNIQUE keyword do not allow duplicates, except for the special case that multiple NULL values are allowed. I realise that it may (and is) defined in such a way, but it still does not explain *why* part of a PRIMARY key might not be NULL. If the combination of parts in the PRIMARY key is such that it can uniquely identify a record it would be sufficient for a primary key IMHO. It could well be a UNIQUE index with the restriction that the complete key (the parts combined) may not be NULL... Such a unique index therefore cannot be a primary key. However, for the purposes you describe above, it sounds like the solution is simply to define your columns as allowing NULL, and to create your index using UNIQUE rather than PRIMARY KEY. Unfortunately ther is a big difference in performance between the primary and secudary indexes in InnoDB. We made (secundary) indexes and didn't have a primary index at all (so MySQL used a 64-bit integer as primary key). After we changed the index to primary the performance increased considerably. Some queries turned out to be quite slow and we found that these relied on NULL values. Converting the index to primary silently converted all NULL constraints to NOT NULL for the columns that are part of the primary key and converted all NULL values in the db to the appropriate default values for the various column types. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
Not every DBMS... MSSQL: Create Unique Index Microsoft(r) SQL Server(tm) checks for duplicate values when the index is created (if data already exists) and checks each time data is added with an INSERT or UPDATE statement. If duplicate key values exist, the CREATE INDEX statement is canceled and an error message giving the first duplicate is returned. Multiple NULL values are considered duplicates when UNIQUE index is created. SYBASE: Create Unique Index Prohibits duplicate index (also called key) values. The system checks for duplicate key values when the index is created (if data already exists), and checks each time data is added with an insert or update. If there is a duplicate key value or if more than one row contains a null value, the command is aborted and an error message giving the duplicate is printed. An unique index is not a primary key constraint. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
From: Harald Fuchs id INT(11) - accountID name VARCHAR(32) - parameter name value INT(11) - parameter value Other tables contain string, datetime, etc. parameters. Since most searches are made for a value (or range) of one or more parameters, a usable primary key is: name-value-id That's a horrible denormalization. If one named parameter can hold only one INT value for one account id, then (id, name) could be a primary key; otherwise, you'd need a surrogate primary key. And what if you want to find the IDs which have a certain value in a parameter? Then you're searching for name and value and only need the id as the glue for joins. The sitution is that I have to store a variable (and changing) number of parameters for a large number of IDs. The traditional construction of a column for each parameter is not usable anymore: - modifying the table structure for new parameters requires a lot of time - all unused parameters will still take space - design of index(es) is virtually impossible because searches are made on various combinations of parameters The parameter table solution is rather simple, but has some performance complications once you have 26,000,000 records for 475,000 accounts in a 5,3GB database... How would *you* normalize this situation? All suggestions are welcome! Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
On 4/26/05, Jigal van Hemert [EMAIL PROTECTED] wrote: And http://dev.mysql.com/doc/mysql/en/create-table.html tells me that A PRIMARY KEY is a unique KEY where all key columns must be defined as NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares them so implicitly (and silently). Why is this? I actually do need NULL values (they really are not equal to '0', etc.), but also need this column as part of the PRIMARY key in an InnoDB table... It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. Because it is a PRIMARY KEY. I mean phrase 'PRIMARY KEY' means a key with which each row can be explicitly addressed. So if you have 2000 rows in a table, you can write 2000 SELECT statemens which will use columns in primary key and each of these SELECT statements will return exactly one (different) row. Since PRIMARY KEY is a primary key it cannot have NULL values. And there can be only one primary key on the table, for the same reason. If your PRIMARY KEY would allow NULL values, it would not be possible to address these rows with NULL values (*) and therefore it would not be a real primary key, by definiton. It would be a unique key. (*). Supposedly if there could be only one NULL value per column it might be possible, but since NULL means unknown, it should not be mixed with real values. From what you are saying, you need a UNIQUE key, not a PRIMARY KEY. Regards, Dawid -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance problem on INSERT into MyISAM table
I must assume you have all the proper indexes setup and your configuration variables are fairly optimal. First, I would run just the select part with an explain in front of it to see what MySQL is trying to do. I've had MySQL run a query for an inordinate amount of time on a fairly small data set because of a typo and no index in use for a join. Second, I would check the size of the data file to see if it is growing. At least you'll be able to see if something is going on. Third, I would try just the select part with a limit of say 10 or 20. This will show you how long MySQL is taking to do the search and joins, eliminating data transfer time. You need to find where it's bottlenecking. It may very well be that you are I/O bound (iostat). MySQL is reading from the tables, certainly using temporary tables with a data set of that size and trying to load data into a new table. That's a lot of reads and writes going on at once, your disk(s) may be getting hammered, especially if you are not using RAID. You could also try disabling indexes on table 3 (if you have any) and then enabling after the import is done. That will speed up the import process. Lastly, instead of doing and insert select, just select to a file (INTO OUTFILE). Then import the data using load data infile. A two step processing, but something that will allow you to control all the I/O that's going on. On Apr 26, 2005, at 9:22 AM, Ed Sweeney wrote: I have been trying to run an fairly large INSERT into an empty table joining two other tables now for several weeks and have not been able to get the query to run to completion even when sub-seting the data into smaller ranges. I have tried this at MySQL releases 4.1.8a and 4.1.10a with no noticable improvement. The server is running Linux 2.4.21-4 Elsmp RedHat 3.2.3.-20. The server is dedicated to MySQL. The my.cnf file is attached. There are 8 Gbytes of RAM and 2, Hyperthreaded CPUs (top shows 4 processors). SHOW STATUS shows very good buffer hit ratio. Current stats are attached. Server was re-booted last Friday. It has been running the INSERT query since Saturday mid-morning and has yet to complete. Table 1 230 million rows total compund PK index 4 columns range 1 should select 35 million rows. Explain plan shows it to be using the PK Table 2 598 million rows compound PK index 4 columns and one secondary index. Range 1 should select about 130 million rows Table 3 Empty table unindexed. Two keys from Table 1 and 17 columns from table 2 populate this table The general form of the query is: INSERT INTO Table 3 (col1, . Col18) SELECT Col1,,.col18 FROM Table 1 a INNER JOIN Table 2 b ON (PK columns and range selection) WHERE a.col5 = b.col5 AND . AND AND a.col18 = b.col18 Any suggestions are welcome. Ed Sweeney show_status.txt-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
A primary key absolutely forbids duplicate values. Indexes created with the UNIQUE keyword do not allow duplicates, except for the special case that multiple NULL values are allowed. I realise that it may (and is) defined in such a way, but it still does not explain *why* part of a PRIMARY key might not be NULL. Because a value in a PK should uniquely identify a row. Using one or more NULLs negates this... The best systems don't use NULLs at all. NULL is an abomination. You should only store values that are true. Eg: employee with number 1 has a name of Martijn. If salary is optional, do not make it nullable, but create a separate table employee_salaries. This way, there's never NULL confusion either. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
From: Jochem van Dieten Why is this? Because the SQL standard says so. A true observation, but still no explanation or reason why ;-P MySQL doesn't follow the standard in every situation, so that's not an excuse... (no offense!) There must be a good reason other than because our ancestors always did it this way. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
At 16:25 +0200 4/26/05, Jigal van Hemert wrote: From: Paul DuBois Hi Paul, A primary key absolutely forbids duplicate values. Indexes created with the UNIQUE keyword do not allow duplicates, except for the special case that multiple NULL values are allowed. I realise that it may (and is) defined in such a way, but it still does not explain *why* part of a PRIMARY key might not be NULL. If the combination of parts in the PRIMARY key is such that it can uniquely identify a record it would be sufficient for a primary key IMHO. It could well be a UNIQUE index with the restriction that the complete key (the parts combined) may not be NULL... I'm not sure I understand what you are saying. But if part of a PRIMARY KEY could be NULL, then it _wouldn't_ uniquely identify records. Such a unique index therefore cannot be a primary key. However, for the purposes you describe above, it sounds like the solution is simply to define your columns as allowing NULL, and to create your index using UNIQUE rather than PRIMARY KEY. Unfortunately ther is a big difference in performance between the primary and secudary indexes in InnoDB. We made (secundary) indexes and didn't have a primary index at all (so MySQL used a 64-bit integer as primary key). After we changed the index to primary the performance increased considerably. Some queries turned out to be quite slow and we found that these relied on NULL values. Converting the index to primary silently converted all NULL constraints to NOT NULL for the columns that are part of the primary key and converted all NULL values in the db to the appropriate default values for the various column types. Regards, Jigal. -- 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]
Re: extract numeric value from a string.
Good point. I assumed that number meant a real number. This should work for leading zeroes: SELECT tag, @num := CONVERT(tag, SIGNED) AS cast_num, SUBSTRING(tag, 1, LOCATE(@num, tag) + LENGTH(@num) - 1) AS num_part, SUBSTRING(tag, LOCATE(@num, tag) + LENGTH(@num)) AS txt_part FROM tags; +-+--+--+--+ | tag | cast_num | num_part | txt_part | +-+--+--+--+ | 1foo|1 | 1| foo | | 23bar | 23 | 23 | bar | | 234baz | 234 | 234 | baz | | 001quux |1 | 001 | quux | +-+--+--+--+ Same concept, but the LOCATE finds the first occurrence of the casted number. Then add the length of the casted number et voila. Eamon Daly - Original Message - From: gerald_clark [EMAIL PROTECTED] To: Eamon Daly [EMAIL PROTECTED] Cc: dixie [EMAIL PROTECTED]; MySQL mysql@lists.mysql.com Sent: Tuesday, April 26, 2005 8:10 AM Subject: Re: extract numeric value from a string. Eamon Daly wrote: Easy enough. Get the numeric part via CONVERT, then get the rest of the string from the length of the numeric part, plus one: SELECT tag, @num := CONVERT(tag, SIGNED) AS num_part, SUBSTRING(tag, LENGTH(@num) + 1) AS rest_of_string from tags; ++--++ | tag| num_part | rest_of_string | ++--++ | 1foo |1 | foo| | 23bar | 23 | bar| | 234baz | 234 | baz| ++--++ 3 rows in set (0.00 sec) Eamon Daly Unless the string starts wit a '0'. - Original Message - From: dixie [EMAIL PROTECTED] To: MySQL mysql@lists.mysql.com Sent: Friday, April 22, 2005 6:18 PM Subject: extract numeric value from a string. Hi at all, I've this necessity. In a table I've a field popolated by a string where the first (not costant lenght) part are number and the second part caracter. I want extract, in other field, the first part and the second in another field. There is a function to obtained it? Tks in advance Paolo -- dixie [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
From: Dawid Kuroczko It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. Because it is a PRIMARY KEY. I mean phrase 'PRIMARY KEY' means a key with which each row can be explicitly addressed. So if you have 2000 rows in a table, you can write 2000 SELECT statemens which will use columns in primary key and each of these SELECT statements will return exactly one (different) row. With the NULL values included it will still uniquely identify each row... I would understand it if it would mean that the key as a whole could not be NULL, but the restriction that each column that is part of a PRIMARY KEY must have the NOT NULL constraint is not logical. If your PRIMARY KEY would allow NULL values, it would not be possible to address these rows with NULL values (*) and therefore it would not be a real primary key, by definiton. It would be a unique key. That would be true for the entire key, but not for each part of the key... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
From: Paul DuBois I realise that it may (and is) defined in such a way, but it still does not explain *why* part of a PRIMARY key might not be NULL. If the combination of parts in the PRIMARY key is such that it can uniquely identify a record it would be sufficient for a primary key IMHO. It could well be a UNIQUE index with the restriction that the complete key (the parts combined) may not be NULL... I'm not sure I understand what you are saying. But if part of a PRIMARY KEY could be NULL, then it _wouldn't_ uniquely identify records. It would IMHO if the other parts combined would be unique. In this case it's a table that contains account_id, parameter_name and value. Account_id and parameter_name would be sufficient to uniquely identify a records (only one parameter with the same name per account allowed). But since searches use the parameter_name/value combination in almost all cases I would define the key as: parameter_name-value-account_id. InnoDB is very fast if you use the primary key and a lot slower if you use secudary key(s), so queries can get considerably faster if you use a primary key. My combined key would be able to uniquely identify records. I know the SQL standard defines a PRIMARY KEY as a combination between UNIQUE and NOT NULL, but it's still not clear to me why this implies that all *parts* of the primary key *must* also have the NOT NULL constraint. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
At 16:47 +0200 4/26/05, Jigal van Hemert wrote: From: Dawid Kuroczko It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. Because it is a PRIMARY KEY. I mean phrase 'PRIMARY KEY' means a key with which each row can be explicitly addressed. So if you have 2000 rows in a table, you can write 2000 SELECT statemens which will use columns in primary key and each of these SELECT statements will return exactly one (different) row. With the NULL values included it will still uniquely identify each row... I would understand it if it would mean that the key as a whole could not be NULL, but the restriction that each column that is part of a PRIMARY KEY must have the NOT NULL constraint is not logical. Sure it is. If any part could be NULL, then it could contain duplicate NULL values, thus compromising uniqueness of that part, and of the entire key as well. Suppose you have a two part key on columns a and b, with b allowed to be NULL. Then you could have these values: x y x NULL x z x NULL Now, how do you uniquely identify the 2nd and 4th rows? If your PRIMARY KEY would allow NULL values, it would not be possible to address these rows with NULL values (*) and therefore it would not be a real primary key, by definiton. It would be a unique key. That would be true for the entire key, but not for each part of the key... You have it backwards. Any individual column in a primary key might contain duplicates. But the combination of values in all of the columns must be unique. Regards, Jigal. -- 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]
Re: why NOT NULL in PRIMARY key??
It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. Because it is a PRIMARY KEY. I mean phrase 'PRIMARY KEY' means a key with which each row can be explicitly addressed. So if you have 2000 rows in a table, you can write 2000 SELECT statemens which will use columns in primary key and each of these SELECT statements will return exactly one (different) row. With the NULL values included it will still uniquely identify each row... I would understand it if it would mean that the key as a whole could not be NULL, but the restriction that each column that is part of a PRIMARY KEY must have the NOT NULL constraint is not logical. It isn't? Imagine this: mytable with PK (id, value) 1, NULL 1, NULL 2, value 2, NULL now, given that NULL NULL ... what row would 1, NULL identify? Or would you only allow a single NULL for each part of the PK? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
Jigal van Hemert [EMAIL PROTECTED] wrote on 04/26/2005 10:35:06 AM: From: Harald Fuchs id INT(11) - accountID name VARCHAR(32) - parameter name value INT(11) - parameter value Other tables contain string, datetime, etc. parameters. Since most searches are made for a value (or range) of one or more parameters, a usable primary key is: name-value-id That's a horrible denormalization. If one named parameter can hold only one INT value for one account id, then (id, name) could be a primary key; otherwise, you'd need a surrogate primary key. And what if you want to find the IDs which have a certain value in a parameter? Then you're searching for name and value and only need the id as the glue for joins. The sitution is that I have to store a variable (and changing) number of parameters for a large number of IDs. The traditional construction of a column for each parameter is not usable anymore: - modifying the table structure for new parameters requires a lot of time - all unused parameters will still take space - design of index(es) is virtually impossible because searches are made on various combinations of parameters The parameter table solution is rather simple, but has some performance complications once you have 26,000,000 records for 475,000 accounts in a 5,3GB database... How would *you* normalize this situation? All suggestions are welcome! Regards, Jigal. I think what tripped us up (and raised the red flags) was that you called your index on (id, name, value) a PRIMARY KEY. However, the unique combination of values on your parameters table should have only been for (id, name), right? The id-name combination will uniquely identify a parameter for an account (and each account cannot have the same parameter more than once, can it?). So your PK should be on (id, name) and a regular index could be placed on (id, name, value) as a covering index. However, your covering index will be a complete duplicate of your table and based on the numbers you give us, it probably won't fit into memory. You would end up trying to use paged memory to search an index to get at the value of the 'value' column just to avoid an extra trip to the disk to get the value based on an PK match. I don't think the extra effort (and disk space used and working memory consumed) would actually help your retrieval speed. If it were me, I would not index that table beyond the PK on just (id, name). But, I would still test it both ways just to be sure. I had a similar situation as you and chose a similar solution. My data storage challenge was to make millions of laboratory reports accessible for statistical analysis. Each report can contain any number of analyses on them (from 2 to 60) depending on what tests were requested when the sample was submitted to the lab. I also created a table for reports and a separate table for each test-result pair (fortunately for me, all of my results were numeric or could be represented with numbers). The two tables are linked through a FK (I am also using InnoDB). Not only is this saving space but the design is flexible because I do not need to change my tables every time the lab comes up with a new analysis it can perform (for the exact same reasons you described above). Don't get discouraged, OK? ;-) Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: why NOT NULL in PRIMARY key??
At 16:56 +0200 4/26/05, Jigal van Hemert wrote: From: Paul DuBois I realise that it may (and is) defined in such a way, but it still does not explain *why* part of a PRIMARY key might not be NULL. If the combination of parts in the PRIMARY key is such that it can uniquely identify a record it would be sufficient for a primary key IMHO. It could well be a UNIQUE index with the restriction that the complete key (the parts combined) may not be NULL... I'm not sure I understand what you are saying. But if part of a PRIMARY KEY could be NULL, then it _wouldn't_ uniquely identify records. It would IMHO if the other parts combined would be unique. If the other parts are unique, then you don't need the nullable column in your primary key. Just define it with the other columns. In this case it's a table that contains account_id, parameter_name and value. Account_id and parameter_name would be sufficient to uniquely identify a records (only one parameter with the same name per account allowed). But since searches use the parameter_name/value combination in almost all cases I would define the key as: parameter_name-value-account_id. InnoDB is very fast if you use the primary key and a lot slower if you use secudary key(s), so queries can get considerably faster if you use a primary key. My combined key would be able to uniquely identify records. I know the SQL standard defines a PRIMARY KEY as a combination between UNIQUE and NOT NULL, but it's still not clear to me why this implies that all *parts* of the primary key *must* also have the NOT NULL constraint. Regards, Jigal. -- 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]
Re: why NOT NULL in PRIMARY key??
Jigal, create table YourTable ( id INT(11), name VARCHAR(32), value INT(11), PRIMARY KEY(id,name,value) ) let's assume that PRIMARY KEY works like you want (accept NULLs) and we have a row in your table: (id,name,value) = (1,NULL,12) Then you insert a new row: insert into YourTable (id,name,value) values (1,NULL,12). Before inserting MySQL will try to find a record with the same values. But since comparison with NULL value returns always FALSE MySQL will think that there is no such record in the table. After this point you will get 2 identical records in the table. Mikhail. - Original Message - From: Jigal van Hemert [EMAIL PROTECTED] To: Dawid Kuroczko [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, April 26, 2005 4:47 PM Subject: Re: why NOT NULL in PRIMARY key?? From: Dawid Kuroczko It can't have anything to do with the 'uniqueness' of the data, since I can have a lot of 'zero'-values in the column, as long as the combination of columns in the PRIMARY key results in unique values. Because it is a PRIMARY KEY. I mean phrase 'PRIMARY KEY' means a key with which each row can be explicitly addressed. So if you have 2000 rows in a table, you can write 2000 SELECT statemens which will use columns in primary key and each of these SELECT statements will return exactly one (different) row. With the NULL values included it will still uniquely identify each row... I would understand it if it would mean that the key as a whole could not be NULL, but the restriction that each column that is part of a PRIMARY KEY must have the NOT NULL constraint is not logical. If your PRIMARY KEY would allow NULL values, it would not be possible to address these rows with NULL values (*) and therefore it would not be a real primary key, by definiton. It would be a unique key. That would be true for the entire key, but not for each part of the key... Regards, Jigal. -- 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: why NOT NULL in PRIMARY key??
From: Paul DuBois I would understand it if it would mean that the key as a whole could not be NULL, but the restriction that each column that is part of a PRIMARY KEY must have the NOT NULL constraint is not logical. Sure it is. If any part could be NULL, then it could contain duplicate NULL values, thus compromising uniqueness of that part, and of the entire key as well. Suppose you have a two part key on columns a and b, with b allowed to be NULL. Then you could have these values: x y x NULL x z x NULL Now, how do you uniquely identify the 2nd and 4th rows? The same is true for any other value... Now that the columns have a NOT NULL constraint the records that previously contained NULL now hold '0'. x y x 0 x z x 0 Now, how do you uniquely identify the 2nd and 4th rows? If your PRIMARY KEY would allow NULL values, it would not be possible to address these rows with NULL values (*) and therefore it would not be a real primary key, by definiton. It would be a unique key. That would be true for the entire key, but not for each part of the key... You have it backwards. Any individual column in a primary key might contain duplicates. But the combination of values in all of the columns must be unique. Which is the case! The same key definition would be valid as a UNIQUE key plus no combined key value is NULL. It just seems a matter of a definition in the SQL standard... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
Jigal, I would define the key as: parameter_name-value-account_id. InnoDB is very fast if you use the primary key and a lot slower if you use secudary key(s), so queries can get considerably faster if you use a primary key. One reason the PK is faster is that the engine needn't handle NULLs in it. My combined key would be able to uniquely identify records. What!?? NULL has no length. In key values like stringvalueNULLintvalue, what position in the key value is the engine supposed to consider the beginning of intvalue!? I know the SQL standard defines a PRIMARY KEY as a combination between UNIQUE and NOT NULL, but it's still not clear to me why this implies that all *parts* of the primary key *must* also have the NOT NULL constraint. Allow NULLs as PK components and you destroy two things at once, PK logic and performance. PB - Jigal van Hemert wrote: From: "Paul DuBois" I realise that it may (and is) defined in such a way, but it still does not explain *why* part of a PRIMARY key might not be NULL. If the combination of parts in the PRIMARY key is such that it can uniquely identify a record it would be sufficient for a primary key IMHO. It could well be a UNIQUE index with the restriction that the complete key (the parts combined) may not be NULL... I'm not sure I understand what you are saying. But if part of a PRIMARY KEY could be NULL, then it _wouldn't_ uniquely identify records. It would IMHO if the other parts combined would be unique. In this case it's a table that contains account_id, parameter_name and value. Account_id and parameter_name would be sufficient to uniquely identify a records (only one parameter with the same name per account allowed). But since searches use the parameter_name/value combination in almost all cases I would define the key as: parameter_name-value-account_id. InnoDB is very fast if you use the primary key and a lot slower if you use secudary key(s), so queries can get considerably faster if you use a primary key. My combined key would be able to uniquely identify records. I know the SQL standard defines a PRIMARY KEY as a combination between UNIQUE and NOT NULL, but it's still not clear to me why this implies that all *parts* of the primary key *must* also have the NOT NULL constraint. Regards, Jigal. No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: why NOT NULL in PRIMARY key??
[snip] The same is true for any other value... Now that the columns have a NOT NULL constraint the records that previously contained NULL now hold '0'. x y x 0 x z x 0 Now, how do you uniquely identify the 2nd and 4th rows? [/snip] The database would have thrown an error when you tried to create row 4. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
In article [EMAIL PROTECTED], Jigal van Hemert [EMAIL PROTECTED] writes: From: Harald Fuchs id INT(11) - accountID name VARCHAR(32) - parameter name value INT(11) - parameter value Other tables contain string, datetime, etc. parameters. Since most searches are made for a value (or range) of one or more parameters, a usable primary key is: name-value-id That's a horrible denormalization. If one named parameter can hold only one INT value for one account id, then (id, name) could be a primary key; otherwise, you'd need a surrogate primary key. How would *you* normalize this situation? All suggestions are welcome! Your table structure is fine AFAICS, but a primary key should span a minimum number of columns uniquely identifying a single row. value does not meet that need. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
I would understand it if it would mean that the key as a whole could not be NULL, but the restriction that each column that is part of a PRIMARY KEY must have the NOT NULL constraint is not logical. Sure it is. If any part could be NULL, then it could contain duplicate NULL values, thus compromising uniqueness of that part, and of the entire key as well. Suppose you have a two part key on columns a and b, with b allowed to be NULL. Then you could have these values: x y x NULL x z x NULL Now, how do you uniquely identify the 2nd and 4th rows? The same is true for any other value... Now that the columns have a NOT NULL constraint the records that previously contained NULL now hold '0'. x y x 0 x z x 0 Now, how do you uniquely identify the 2nd and 4th rows? Yes well, exactly the point: this is not possible in a primary key :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why NOT NULL in PRIMARY key??
On 4/26/05, Jigal van Hemert wrote: From: Jochem van Dieten Why is this? Because the SQL standard says so. A true observation, but still no explanation or reason why ;-P I consider it a good enough explanation of why MySQL doesn't allow it. As to why the SQL standard doesn't allow it: NULL doesn't fit particularly well in relational theory and there has probably been considerable pressure from certain vendors (imagine the problems when an empty string is indistinguishable from a NULL so both '' = '' and NULL NULL must be true, but now not just for some wacky varchar but for your primary key). Just speculation of course :) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fw: modifying InnoDB storage
Hello. According to this: If your last data file was defined with the keyword autoextend, the procedure to edit my.cnf must take into account the size to which the last data file has grown. You have to look at the size of the data file, round the size downward to the closest multiple of 1024 * 1024 bytes (= 1MB), and specify the rounded size explicitly in innodb_data_file_path. Then you can add another data file. I think you can. Jigal van Hemert [EMAIL PROTECTED] wrote: Unfortunately no response from the list anymore :-( Can we ever get rid of the autoexpanding ibdata file without completely rebuilding the databases? Regards, Jigal. - Original Message --- -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ibdata1 grows beyound borders..
Hello. I don't think so. As I've understood InnoDB doesn't do it. See: http://dev.mysql.com/doc/mysql/en/innodb-file-space.html Eric Persson [EMAIL PROTECTED] wrote: Gleb Paharenko wrote: Hello. Searching in the archives says you could get worse performance, because of extending during transactions: Yes, read a few of those, but how about the fix for this, will the data rearrange itself automatically when I specify more files? //Eric -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Get a Random Row on a HUGE db
I am wanting to display a random page from my site, But I have over 12,000 articles right now and we add over 150 per day. What I wound up doing was a Virtual DOS attack on my own server because the 40 mb db was being loaded to many times. I have tons of memory and a Dell Dual Xeon 2.8 gig. Can someone think up a better way of doing this? I wish Mysql would just bring me back 1 valid random row It could be used in so many ways it should just be a part of MySql anyway. ?phpini_set("display_errors", '1');header("Pragma: private");header("Cache-Control: post-check=0, pre-check=0", false); header("Cache-Control: no-cache, must-revalidate");require_once("firebase.conf.php");$dbi = new DBI(DB_URL);$stmt = "Select * from firebase_content Order By rand() DESC Limit 0, 1";$result = $dbi-query($stmt);while($row = $result-fetchRow()){$title = $row-title;$cate = $row-category;$get = "Select cat_url from firebase_categories where cat_name='$cate'";$now = $dbi-query($get);$rows = $now-fetchRow();$url = "">$link = $url . $title;}header("Location: http://www.prnewsnow.com/$link");exit;/* Sudo code that I am trying to create to relieve server stress.function randomRow(table, column) {var maxRow = query("SELECT MAX($column) AS maxID FROM $table");var randomID;var randomRow;do {randomID = randRange(1, maxRow.maxID);randomRow = query("SELECT * FROM $table WHERE $column = $randomID");} while (randomRow.recordCount == 0); return randomRow;}*/? ThanksDonny LairsonPresident29 GunMuse LaneP.O. box 166Lakewood NM 88254http://www.gunmuse.com469 228 2183
RE: Get a Random Row on a HUGE db
[snip] I am wanting to display a random page from my site, But I have over 12,000 articles right now and we add over 150 per day. What I wound up doing was a Virtual DOS attack on my own server because the 40 mb db was being loaded to many times. I have tons of memory and a Dell Dual Xeon 2.8 gig. Can someone think up a better way of doing this? I wish Mysql would just bring me back 1 valid random row It could be used in so many ways it should just be a part of MySql anyway. ?php ini_set(display_errors, '1'); header(Pragma: private); header(Cache-Control: post-check=0, pre-check=0, false); header(Cache-Control: no-cache, must-revalidate); require_once(firebase.conf.php); $dbi = new DBI(DB_URL); $stmt = Select * from firebase_content Order By rand() DESC Limit 0, 1; $result = $dbi-query($stmt); while($row = $result-fetchRow()) { $title = $row-title; $cate = $row-category; $get = Select cat_url from firebase_categories where cat_name='$cate'; $now = $dbi-query($get); $rows = $now-fetchRow(); $url = $rows-cat_url; $link = $url . $title; } header(Location: http://www.prnewsnow.com/$link;); exit; /* Sudo code that I am trying to create to relieve server stress. function randomRow(table, column) { var maxRow = query(SELECT MAX($column) AS maxID FROM $table); var randomID; var randomRow; do { randomID = randRange(1, maxRow.maxID); randomRow = query(SELECT * FROM $table WHERE $column = $randomID); } while (randomRow.recordCount == 0); return randomRow; } */ ? [/snip] Try this ... SELECT * FROM foo ORDER BY RAND(NOW()) LIMIT 1; 12000 rows is not huge at all, so this should be pretty quick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get a Random Row on a HUGE db
How about using the rand() function built into MySQL? You could use it to generate a random number, then find the row whose primary key equals that random number, then do a single-row select on that row. Rhino - Original Message - From: [EMAIL PROTECTED] To: Mysql Sent: Tuesday, April 26, 2005 11:33 AM Subject: Get a Random Row on a HUGE db I am wanting to display a random page from my site, But I have over 12,000 articles right now and we add over 150 per day. What I wound up doing was a Virtual DOS attack on my own server because the 40 mb db was being loaded to many times. I have tons of memory and a Dell Dual Xeon 2.8 gig. Can someone think up a better way of doing this? I wish Mysql would just bring me back 1 valid random row It could be used in so many ways it should just be a part of MySql anyway. ?phpini_set("display_errors", '1');header("Pragma: private");header("Cache-Control: post-check=0, pre-check=0", false); header("Cache-Control: no-cache, must-revalidate");require_once("firebase.conf.php");$dbi = new DBI(DB_URL);$stmt = "Select * from firebase_content Order By rand() DESC Limit 0, 1";$result = $dbi-query($stmt);while($row = $result-fetchRow()){$title = $row-title;$cate = $row-category;$get = "Select cat_url from firebase_categories where cat_name='$cate'";$now = $dbi-query($get);$rows = $now-fetchRow();$url = "">$link = $url . $title;}header("Location: http://www.prnewsnow.com/$link");exit;/* Sudo code that I am trying to create to relieve server stress.function randomRow(table, column) {var maxRow = query("SELECT MAX($column) AS maxID FROM $table");var randomID;var randomRow;do {randomID = randRange(1, maxRow.maxID);randomRow = query("SELECT * FROM $table WHERE $column = $randomID");} while (randomRow.recordCount == 0); return randomRow;}*/? ThanksDonny LairsonPresident29 GunMuse LaneP.O. box 166Lakewood NM 88254http://www.gunmuse.com469 228 2183 No virus found in this incoming message.Checked by AVG Anti-Virus.Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 21/04/2005 No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 21/04/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database design question
I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Efficient select/insert
I would like to select several rows from one table and insert them into another nearly identical table using Perl/DBI: my @array = $q-param(); # HTML checkboxes foreach my $element (@array) { my $sql = select col2, col3, col4 from table1 where col1 = ?; my $sth = $dbh-prepare($sql); $sth-execute($element) or die $sth-errstr(); my @row = $sth-fetchrow_array; $sql = insert table2 (col1, col2, col3, col4) values (NULL, ?, ?, ?); $sth = $dbh-prepare($sql); $sth-execute($row[0], $row[1], $row[2]) or die $sth-errstr(); } Is this efficient db interaction, or is there a better way? This is 3.23 but can upgrade if necessary. Thanks, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: extract numeric value from a string.
Hi all, The trick i can see if the string start with '0' is to make it starting with a positive number. FOr example if my string is '0123FOO' : set @a='0123FOO'; set @b=substring(0+concat('0',@a),-length(0+concat('0',@a))+1); select @b,replace(@a,@b,''); Mathias Selon gerald_clark [EMAIL PROTECTED]: Eamon Daly wrote: Easy enough. Get the numeric part via CONVERT, then get the rest of the string from the length of the numeric part, plus one: SELECT tag, @num := CONVERT(tag, SIGNED) AS num_part, SUBSTRING(tag, LENGTH(@num) + 1) AS rest_of_string from tags; ++--++ | tag| num_part | rest_of_string | ++--++ | 1foo |1 | foo| | 23bar | 23 | bar| | 234baz | 234 | baz| ++--++ 3 rows in set (0.00 sec) Eamon Daly Unless the string starts wit a '0'. - Original Message - From: dixie [EMAIL PROTECTED] To: MySQL mysql@lists.mysql.com Sent: Friday, April 22, 2005 6:18 PM Subject: extract numeric value from a string. Hi at all, I've this necessity. In a table I've a field popolated by a string where the first (not costant lenght) part are number and the second part caracter. I want extract, in other field, the first part and the second in another field. There is a function to obtained it? Tks in advance Paolo -- dixie [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error running mysql_install_db on Solaris10 x86
Description: Running mysql_install_db generates an error. As follows: bash-3.00# scripts/mysql_install_db --user=mysql Installing all prepared tables Illegal Instruction - core dumped Installation of system tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: ./bin/mysqld --skip-grant You can use the command line tool ./bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the ./bin/mysqlbug script! bash-3.00# How-To-Repeat: Install mysql-standard-5.0.4-beta or mysql-max-5.0.4-beta on Solaris 10 x86 using the pkg.gz files and then run mysql_install_db. Fix: Submitter-Id: Originator:Super-User Organization: MySQL support: none Synopsis:Error running mysql_install_db on Solaris10 Severity:critical Priority:high Category:mysql Class:sw-bug Release:mysql-5.0.4-beta-standard (MySQL Community Edition - Standard (GPL)) C compiler: C++ compiler: Environment: System: SunOS Sol1 5.10 Generic i86pc i386 i86pc Architecture: i86pc Some paths: /usr/bin/perl /usr/local/bin/make /opt/sfw/bin/gmake /opt/sfw/bin/gcc /usr/ucb/cc GCC: Reading specs from /opt/sfw/lib/gcc/i386-pc-solaris2.10/3.4.2/specs Configured with: ../gcc-3.4.2/configure --prefix=/opt/sfw --with-ld=/usr/ccs/bin/ld --with-gnu-as --with-as=/opt/sfw/bin/gas --enable-shared --disable-libgcj Thread model: posix gcc version 3.4.2 Compilation info: CC='cc' CFLAGS='-xO3 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all -xlibmil -xlibmopt -xtarget=native' CXX='CC' CXXFLAGS='-xO3 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all -xlibmil -xlibmopt -xtarget=native' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 9 Feb 17 16:29 /lib/libc.so - libc.so.1 -rwxr-xr-x 1 root bin 1031152 Jan 23 02:18 /lib/libc.so.1 lrwxrwxrwx 1 root root 19 Feb 17 16:16 /usr/lib/libc.so - ../../lib/libc.so.1 lrwxrwxrwx 1 root root 19 Feb 17 16:16 /usr/lib/libc.so.1 - ../../lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=MySQL Community Edition - Standard (GPL)' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--with-named-curses=-lcurses' '--disable-shared' '--with-big-tables' '--with-readline' '--with-embedded-server' '--with-archive-storage-engine' '--with-innodb' 'CC=cc' 'CFLAGS=-xO3 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all -xlibmil -xlibmopt -xtarget=native' 'CXXFLAGS=-xO3 -mt -fsimple=1 -ftrap=%none -nofstore -xbuiltin=%all -xlibmil -xlibmopt -xtarget=native' 'CXX=CC' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query question
sorry Chris again, i mean in what they speak about. i try help if i can, just that. :o) Mathias Selon Chris Ramsay [EMAIL PROTECTED]: Mathias There are no *bad* people on this list - different point of view, yes. Participating on this and other lists requires give AND take - taking advice as well as giving it... Participating is always going to be a two way process so just accept it, and if you can't - unsubscribe. Hope this helps Chris -- 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: JBoss queries aren't cached by MySQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Rafal Kedziorski wrote: Hi, we have the problem, that queries generated by JBoss or our code which runns under JBoss will be not cached by MySQL. The same query sendet from an external application or MySQLFront will be cached by the same MySQL. I'm using JBoss 3.2.5 with JDBC 3.0.16 and MySQL 4.0.23a. Any idea why? Regards, Rafal Rafal, Define what you mean by not cached by MySQLDo you mean the query cache? If so, what do your query cache settings look like? -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCbm1JtvXNTca6JD8RAtT9AJ9k8HnIkRh+U9UE3ROf+eff5ZudRgCgxNNI KeS6Iiq5ttoKjZsaDlyXV74= =gCL+ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: extract numeric value from a string.
I just did the oposite :o) set @a='0123FOO'; set @b=substring(0+concat('9',@a),-length(0+concat('9',@a))+1); select @b,replace(@a,@b,''); have to concat a positive number !!! Selon [EMAIL PROTECTED]: Hi all, The trick i can see if the string start with '0' is to make it starting with a positive number. FOr example if my string is '0123FOO' : set @a='0123FOO'; set @b=substring(0+concat('0',@a),-length(0+concat('0',@a))+1); select @b,replace(@a,@b,''); Mathias Selon gerald_clark [EMAIL PROTECTED]: Eamon Daly wrote: Easy enough. Get the numeric part via CONVERT, then get the rest of the string from the length of the numeric part, plus one: SELECT tag, @num := CONVERT(tag, SIGNED) AS num_part, SUBSTRING(tag, LENGTH(@num) + 1) AS rest_of_string from tags; ++--++ | tag| num_part | rest_of_string | ++--++ | 1foo |1 | foo| | 23bar | 23 | bar| | 234baz | 234 | baz| ++--++ 3 rows in set (0.00 sec) Eamon Daly Unless the string starts wit a '0'. - Original Message - From: dixie [EMAIL PROTECTED] To: MySQL mysql@lists.mysql.com Sent: Friday, April 22, 2005 6:18 PM Subject: extract numeric value from a string. Hi at all, I've this necessity. In a table I've a field popolated by a string where the first (not costant lenght) part are number and the second part caracter. I want extract, in other field, the first part and the second in another field. There is a function to obtained it? Tks in advance Paolo -- dixie [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: why NOT NULL in PRIMARY key??
Martin, Shawn, you are correct. An oversight on my part...this is why I still follow this list, I am always able to learn something and never cease to be humbled. Ed -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 26, 2005 8:01 AM To: emierzwa; mysql@lists.mysql.com Subject: Re: why NOT NULL in PRIMARY key?? Not every DBMS... MSSQL: Create Unique Index Microsoft(r) SQL Server(tm) checks for duplicate values when the index is created (if data already exists) and checks each time data is added with an INSERT or UPDATE statement. If duplicate key values exist, the CREATE INDEX statement is canceled and an error message giving the first duplicate is returned. Multiple NULL values are considered duplicates when UNIQUE index is created. SYBASE: Create Unique Index Prohibits duplicate index (also called key) values. The system checks for duplicate key values when the index is created (if data already exists), and checks each time data is added with an insert or update. If there is a duplicate key value or if more than one row contains a null value, the command is aborted and an error message giving the duplicate is printed. An unique index is not a primary key constraint. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database design question
james tu [EMAIL PROTECTED] wrote on 04/26/2005 12:06:34 PM: I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James I would properly index each table and UNION the results of the 4 searches. Have you considered creating a Full Text index for your keyword fields? Say your 4 tables are called: running, swimming, jumping, and walking SELECT 'running', column list FROM running WHERE keywords search condition UNION SELECT 'swimming', column list FROM swimming WHERE keywords search condition UNION SELECT 'jumping', column list FROM jumping WHERE keywords search condition UNION SELECT 'walking', column list FROM walking WHERE keywords search condition; I used the first column only to identify which table each match comes from. That way if you have records in each table with matching PK values, you know which table to go back to in order to get any additional information. The only problem with this type of search is that your column list columns must be compatible between each of the tables. If the second column is numeric in your first query then the second column will be coerced to numeric for each of the remaining 3 queries. If for some reason that fails, then the whole UNION fails and you get an error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: mysql threads and debian woody
Francesc Guaschwrote: Gleb Paharenko wrote: I thought it was OS related, and the mysql version was not the problem. So I tweaked the limit of bash and linux. In addition I don't want to loose the deb dependency. Is there something else I can try before ? I'm having the same problem, and I'm using mysql official binary. I have two servers, one is webserver and the other is the database server. The webserver connects to the mysql server over tcp. at some times there are many connections and mysql creates more than 1000 threads and then just can't create any more -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database design question
I tried that and maybe I'm doing something wrong but... -I have to select the same number of columns...for each UNION -And each of the records from the union fall under the same column headings as the first SELECT... I even tried to define column aliases.. SELECT `running` as `running_blah`... -James At 1:03 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: james tu [EMAIL PROTECTED] wrote on 04/26/2005 12:06:34 PM: I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James I would properly index each table and UNION the results of the 4 searches. Have you considered creating a Full Text index for your keyword fields? Say your 4 tables are called: running, swimming, jumping, and walking SELECT 'running', column list FROM running WHERE keywords search condition UNION SELECT 'swimming', column list FROM swimming WHERE keywords search condition UNION SELECT 'jumping', column list FROM jumping WHERE keywords search condition UNION SELECT 'walking', column list FROM walking WHERE keywords search condition; I used the first column only to identify which table each match comes from. That way if you have records in each table with matching PK values, you know which table to go back to in order to get any additional information. The only problem with this type of search is that your column list columns must be compatible between each of the tables. If the second column is numeric in your first query then the second column will be coerced to numeric for each of the remaining 3 queries. If for some reason that fails, then the whole UNION fails and you get an error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- -James Tu --- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) ---
Re: Replication - is there a server lag?
On Mon, 25 Apr 2005, Fagyal Csongor wrote: Hi, I am new to replication so excuse me if my question is stupid. The manual recommends that a nice scenario to take advantage of replication in MySQL is to send all updating queries to the master server, and reading from the slave. I would like to use this setup (as usual, I have many more selects than inserts/updates) but I am a little concerned what happens if the slave is behind the master in updating its DB. Say I do like this: 1. update something set `a`=1 where c=d (using the master server) 2. update something set `a`=2 where c=d (using the master server) and then immediately 3. select `a` from something where c=d (using the slave) What if #3 fetches the value of `a` from the slave before `a`=2 takes place? Is it possible that I get `a`==1? Or does replication take care of that? If your replication setup is all local to your network, you shouldn't have any problems. Replication is pretty much instant. We have setups where there is 60k/s replication traffic (450 updates/s avg) and the slave is very rarely lagging behind. Other than that: does anybody here have a Nagios script that checks if replication is running O.K.? :-) I just put the one I use up on the web, use it at your own risk! :) http://byveka.com/nagios/ Regards, Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database design question
If you posted your actual table structures (SHOW CREATE TABLE xx\G) I think I could be more helpful. Right now I am just shooting in the dark. Shawn Green Database Administrator Unimin Corporation - Spruce Pine James [EMAIL PROTECTED] wrote on 04/26/2005 02:15:49 PM: I tried that and maybe I'm doing something wrong but... -I have to select the same number of columns...for each UNION -And each of the records from the union fall under the same column headings as the first SELECT... I even tried to define column aliases.. SELECT `running` as `running_blah`... -James At 1:03 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: james tu [EMAIL PROTECTED] wrote on 04/26/2005 12:06:34 PM: I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James I would properly index each table and UNION the results of the 4 searches. Have you considered creating a Full Text index for your keyword fields? Say your 4 tables are called: running, swimming, jumping, and walking SELECT 'running', column list FROM running WHERE keywords search condition UNION SELECT 'swimming', column list FROM swimming WHERE keywords search condition UNION SELECT 'jumping', column list FROM jumping WHERE keywords search condition UNION SELECT 'walking', column list FROM walking WHERE keywords search condition; I used the first column only to identify which table each match comes from. That way if you have records in each table with matching PK values, you know which table to go back to in order to get any additional information. The only problem with this type of search is that your column list columns must be compatible between each of the tables. If the second column is numeric in your first query then the second column will be coerced to numeric for each of the remaining 3 queries. If for some reason that fails, then the whole UNION fails and you get an error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- -James Tu --- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) ---
RE: Get a Random Row on a HUGE db
Thanks for that I implemented to my Random code. Same problem that select * portion is just a nightmare. Remember I selecting 38mb of data when I do that. What I want to do is jump to a Valid random row. Now If I didn't delete content often that would be easy grab the last autoincremented row_id and get a random number between 1 and End Jump to that row to create the link. Very fast. Zero load So what I am trying is this. $last_row =SELECT from firebase_content LAST_INSERT_ID(); $last_row_query = $dbi-query($last_row); $last_row_result = $row-id; But what I am seeing is this: Object id #9 and not the number that is in the database. What am I sending to this variable that is wrong? [snip] I am wanting to display a random page from my site, But I have over 12,000 articles right now and we add over 150 per day. What I wound up doing was a Virtual DOS attack on my own server because the 40 mb db was being loaded to many times. I have tons of memory and a Dell Dual Xeon 2.8 gig. Can someone think up a better way of doing this? I wish Mysql would just bring me back 1 valid random row It could be used in so many ways it should just be a part of MySql anyway. ?php ini_set(display_errors, '1'); header(Pragma: private); header(Cache-Control: post-check=0, pre-check=0, false); header(Cache-Control: no-cache, must-revalidate); require_once(firebase.conf.php); $dbi = new DBI(DB_URL); $stmt = Select * from firebase_content ORDER BY RAND(NOW()) LIMIT 1; $result = $dbi-query($stmt); while($row = $result-fetchRow()) { $title = $row-title; $cate = $row-category; $get = Select cat_url from firebase_categories where cat_name='$cate'; $now = $dbi-query($get); $rows = $now-fetchRow(); $url = $rows-cat_url; $link = $url . $title; } header(Location: http://www.prnewsnow.com/$link;); exit; /* Sudo code that I am trying to create to relieve server stress. function randomRow(table, column) { var maxRow = query(SELECT MAX($column) AS maxID FROM $table); var randomID; var randomRow; do { randomID = randRange(1, maxRow.maxID); randomRow = query(SELECT * FROM $table WHERE $column = $randomID); } while (randomRow.recordCount == 0); return randomRow; } */ ? [/snip] Try this ... SELECT * FROM foo ORDER BY RAND(NOW()) LIMIT 1; 12000 rows is not huge at all, so this should be pretty quick -- 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: Get a Random Row on a HUGE db
Gunmuse, SELECT from firebase_content LAST_INSERT_ID() In that cmd, 'from ...' ain't right. I didn't understand either what's wrong with ORDER BY RAND() LIMIT 1. Also check the Perl manual for how to retrieve a single value. PB - [EMAIL PROTECTED] wrote: Thanks for that I implemented to my Random code. Same problem that select * portion is just a nightmare. Remember I selecting 38mb of data when I do that. What I want to do is jump to a Valid random row. Now If I didn't delete content often that would be easy grab the last autoincremented row_id and get a random number between 1 and End Jump to that row to create the link. Very fast. Zero load So what I am trying is this. $last_row =SELECT from firebase_content LAST_INSERT_ID(); $last_row_query = $dbi-query($last_row); $last_row_result = $row-id; But what I am seeing is this: Object id #9 and not the number that is in the database. What am I sending to this variable that is wrong? [snip] I am wanting to display a random page from my site, But I have over 12,000 articles right now and we add over 150 per day. What I wound up doing was a Virtual DOS attack on my own server because the 40 mb db was being loaded to many times. I have tons of memory and a Dell Dual Xeon 2.8 gig. Can someone think up a better way of doing this? I wish Mysql would just bring me back 1 valid random row It could be used in so many ways it should just be a part of MySql anyway. ?php ini_set(display_errors, '1'); header(Pragma: private); header(Cache-Control: post-check=0, pre-check=0, false); header(Cache-Control: no-cache, must-revalidate); require_once(firebase.conf.php); $dbi = new DBI(DB_URL); $stmt = Select * from firebase_content ORDER BY RAND(NOW()) LIMIT 1; $result = $dbi-query($stmt); while($row = $result-fetchRow()) { $title = $row-title; $cate = $row-category; $get = Select cat_url from firebase_categories where cat_name='$cate'; $now = $dbi-query($get); $rows = $now-fetchRow(); $url = $rows-cat_url; $link = $url . $title; } header(Location: http://www.prnewsnow.com/$link;); exit; /* Sudo code that I am trying to create to relieve server stress. function randomRow(table, column) { var maxRow = query(SELECT MAX($column) AS maxID FROM $table); var randomID; var randomRow; do { randomID = randRange(1, maxRow.maxID); randomRow = query(SELECT * FROM $table WHERE $column = $randomID); } while (randomRow.recordCount == 0); return randomRow; } */ ? [/snip] Try this ... SELECT * FROM foo ORDER BY RAND(NOW()) LIMIT 1; 12000 rows is not huge at all, so this should be pretty quick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Get a Random Row on a HUGE db
What I had to do was do this for my navigation db and not my content db. My server can easily handle lots of calls to a 4mb table then tell it to fetch the content once that has been achieved. The reason I bringing this up is this seems to be a patched way of doing this. If I have 40,000 items in db that get updated and row_ids change for a catalog and want to randomly display a product. I should be able to ask Mysql for a Random valid row. It indexes with a Primary so it knows what valid at that time. There is too much jumping around just to say get random and be fair about it, so no one row comes up every time or more often that others. Gunmuse, SELECT from firebase_content LAST_INSERT_ID() In that cmd, 'from ...' ain't right. I didn't understand either what's wrong with ORDER BY RAND() LIMIT 1. Also check the Perl manual for how to retrieve a single value. PB - [EMAIL PROTECTED] wrote: Thanks for that I implemented to my Random code. Same problem that select * portion is just a nightmare. Remember I selecting 38mb of data when I do that. What I want to do is jump to a Valid random row. Now If I didn't delete content often that would be easy grab the last autoincremented row_id and get a random number between 1 and End Jump to that row to create the link. Very fast. Zero load So what I am trying is this. $last_row =SELECT from firebase_content LAST_INSERT_ID(); $last_row_query = $dbi-query($last_row); $last_row_result = $row-id; But what I am seeing is this: Object id #9 and not the number that is in the database. What am I sending to this variable that is wrong? [snip] I am wanting to display a random page from my site, But I have over 12,000 articles right now and we add over 150 per day. What I wound up doing was a Virtual DOS attack on my own server because the 40 mb db was being loaded to many times. I have tons of memory and a Dell Dual Xeon 2.8 gig. Can someone think up a better way of doing this? I wish Mysql would just bring me back 1 valid random row It could be used in so many ways it should just be a part of MySql anyway. ?php ini_set(display_errors, '1'); header(Pragma: private); header(Cache-Control: post-check=0, pre-check=0, false); header(Cache-Control: no-cache, must-revalidate); require_once(firebase.conf.php); $dbi = new DBI(DB_URL); $stmt = Select * from firebase_content ORDER BY RAND(NOW()) LIMIT 1; $result = $dbi-query($stmt); while($row = $result-fetchRow()) { $title = $row-title; $cate = $row-category; $get = Select cat_url from firebase_categories where cat_name='$cate'; $now = $dbi-query($get); $rows = $now-fetchRow(); $url = $rows-cat_url; $link = $url . $title; } header(Location: http://www.prnewsnow.com/$link;); exit; /* Sudo code that I am trying to create to relieve server stress. function randomRow(table, column) { var maxRow = query(SELECT MAX($column) AS maxID FROM $table); var randomID; var randomRow; do { randomID = randRange(1, maxRow.maxID); randomRow = query(SELECT * FROM $table WHERE $column = $randomID); } while (randomRow.recordCount == 0); return randomRow; } */ ? [/snip] Try this ... SELECT * FROM foo ORDER BY RAND(NOW()) LIMIT 1; 12000 rows is not huge at all, so this should be pretty quick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- 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: Get a Random Row on a HUGE db
This difference between using a 40 mb table and 4mb table with the same traffic was a 70 server load versus a .9 server load. So it was the amount of data that I was selecting that was choking this feature. - [EMAIL PROTECTED] wrote: Thanks for that I implemented to my Random code. Same problem that select * portion is just a nightmare. Remember I selecting 38mb of data when I do that. What I want to do is jump to a Valid random row. Now If I didn't delete content often that would be easy grab the last autoincremented row_id and get a random number between 1 and End Jump to that row to create the link. Very fast. Zero load So what I am trying is this. $last_row =SELECT from firebase_content LAST_INSERT_ID(); $last_row_query = $dbi-query($last_row); $last_row_result = $row-id; But what I am seeing is this: Object id #9 and not the number that is in the database. What am I sending to this variable that is wrong? [snip] I am wanting to display a random page from my site, But I have over 12,000 articles right now and we add over 150 per day. What I wound up doing was a Virtual DOS attack on my own server because the 40 mb db was being loaded to many times. I have tons of memory and a Dell Dual Xeon 2.8 gig. Can someone think up a better way of doing this? I wish Mysql would just bring me back 1 valid random row It could be used in so many ways it should just be a part of MySql anyway. ?php ini_set(display_errors, '1'); header(Pragma: private); header(Cache-Control: post-check=0, pre-check=0, false); header(Cache-Control: no-cache, must-revalidate); require_once(firebase.conf.php); $dbi = new DBI(DB_URL); $stmt = Select * from firebase_content ORDER BY RAND(NOW()) LIMIT 1; $result = $dbi-query($stmt); while($row = $result-fetchRow()) { $title = $row-title; $cate = $row-category; $get = Select cat_url from firebase_categories where cat_name='$cate'; $now = $dbi-query($get); $rows = $now-fetchRow(); $url = $rows-cat_url; $link = $url . $title; } header(Location: http://www.prnewsnow.com/$link;); exit; /* Sudo code that I am trying to create to relieve server stress. function randomRow(table, column) { var maxRow = query(SELECT MAX($column) AS maxID FROM $table); var randomID; var randomRow; do { randomID = randRange(1, maxRow.maxID); randomRow = query(SELECT * FROM $table WHERE $column = $randomID); } while (randomRow.recordCount == 0); return randomRow; } */ ? [/snip] Try this ... SELECT * FROM foo ORDER BY RAND(NOW()) LIMIT 1; 12000 rows is not huge at all, so this should be pretty quick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- 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: Efficient select/insert
- Original Message - From: Jonathan Mangin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, April 26, 2005 11:26 AM Subject: Efficient select/insert I would like to select several rows from one table and insert them into another nearly identical table using Perl/DBI: my @array = $q-param(); # HTML checkboxes foreach my $element (@array) { my $sql = select col2, col3, col4 from table1 where col1 = ?; my $sth = $dbh-prepare($sql); $sth-execute($element) or die $sth-errstr(); my @row = $sth-fetchrow_array; $sql = insert table2 (col1, col2, col3, col4) values (NULL, ?, ?, ?); $sth = $dbh-prepare($sql); $sth-execute($row[0], $row[1], $row[2]) or die $sth-errstr(); } Is this efficient db interaction, or is there a better way? This is 3.23 but can upgrade if necessary. Thanks, Jon Further... I thought I could use fetchrow_arrayref and push an array of arrays. The DBI docs say: Note that the same array reference is returned for each fetch, so don't store the reference and then use it after a later fetch. Sounds like I can't use that. Now I see execute_for_fetch. Does this sound like a job for execute_for_fetch? --J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Efficient select/insert
Jonathan Mangin [EMAIL PROTECTED] wrote on 04/26/2005 12:26:20 PM: I would like to select several rows from one table and insert them into another nearly identical table using Perl/DBI: my @array = $q-param(); # HTML checkboxes foreach my $element (@array) { my $sql = select col2, col3, col4 from table1 where col1 = ?; my $sth = $dbh-prepare($sql); $sth-execute($element) or die $sth-errstr(); my @row = $sth-fetchrow_array; $sql = insert table2 (col1, col2, col3, col4) values (NULL, ?, ?, ?); $sth = $dbh-prepare($sql); $sth-execute($row[0], $row[1], $row[2]) or die $sth-errstr(); } Is this efficient db interaction, or is there a better way? This is 3.23 but can upgrade if necessary. Thanks, Jon Why not use an INSERT...SELECT instead of splitting up the two steps? Is there a reason you need to see the data before it goes into the other table? (http://dev.mysql.com/doc/mysql/en/insert-select.html) foreach my $element (@array) { my $sql = INSERT table2 (col2, col3, col4) select col2, col3, col4 from table1 where col1 = ?; my $sth = $dbh-prepare($sql); $sth-execute($element) or die $sth-errstr(); } This would be even faster if you could concatenate all of the elements of @array into a single list then you could say: #My PERL skills are non existent so you need to write this part. #I am assuming that @array is holding a list of string values. foreach my $element (@array) { @araylist += ' + $element + ',; } @arraylist = left(@arraylist, length(@arraylist) -1) #that's to remove the trailing comma at the end of the list my $sql = select col2, col3, col4 from table1 where col1 IN (?); my $sth = $dbh-prepare($sql); $sth-execute(@arraylist) or die $sth-errstr(); Like I said, I have no (zero, zilch, nil) PERL skills (this is really simple code and I still got it wrong) but you should get the idea... That last statement should move all of your records in one big batch. Notice I didn't INSERT to table2.col1. By leaving it out of the INSERT clause it's as though I inserted a NULL in that column for each record and if col1 were an auto_increment, it should count up as expected. Generally, if you don't have to look at the data in your application (maybe because you need to massage it in some way) don't bring it back to your client. Let the engine handle it at the server and things will go much faster. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Efficient select/insert
- Original Message - From: [EMAIL PROTECTED] To: Jonathan Mangin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, April 26, 2005 3:20 PM Subject: Re: Efficient select/insert Jonathan Mangin [EMAIL PROTECTED] wrote on 04/26/2005 12:26:20 PM: I would like to select several rows from one table and insert them into another nearly identical table using Perl/DBI: my @array = $q-param(); # HTML checkboxes foreach my $element (@array) { my $sql = select col2, col3, col4 from table1 where col1 = ?; my $sth = $dbh-prepare($sql); $sth-execute($element) or die $sth-errstr(); my @row = $sth-fetchrow_array; $sql = insert table2 (col1, col2, col3, col4) values (NULL, ?, ?, ?); $sth = $dbh-prepare($sql); $sth-execute($row[0], $row[1], $row[2]) or die $sth-errstr(); } Is this efficient db interaction, or is there a better way? This is 3.23 but can upgrade if necessary. Thanks, Jon Why not use an INSERT...SELECT instead of splitting up the two steps? Is there a reason you need to see the data before it goes into the other table? (http://dev.mysql.com/doc/mysql/en/insert-select.html) foreach my $element (@array) { my $sql = INSERT table2 (col2, col3, col4) select col2, col3, col4 from table1 where col1 = ?; my $sth = $dbh-prepare($sql); $sth-execute($element) or die $sth-errstr(); } This would be even faster if you could concatenate all of the elements of @array into a single list then you could say: I thought that's what I already had. @array contains selected primary keys from table 1. #My PERL skills are non existent so you need to write this part. #I am assuming that @array is holding a list of string values. foreach my $element (@array) { @araylist += ' + $element + ',; } @arraylist = left(@arraylist, length(@arraylist) -1) #that's to remove the trailing comma at the end of the list my $sql = select col2, col3, col4 from table1 where col1 IN (?); my $sth = $dbh-prepare($sql); $sth-execute(@arraylist) or die $sth-errstr(); Oh, I see. A List. Hmmm. And did you forget insert or are you practicing black magic? ;) Like I said, I have no (zero, zilch, nil) PERL skills (this is really simple code and I still got it wrong) but you should get the idea... That last statement should move all of your records in one big batch. Notice I didn't INSERT to table2.col1. By leaving it out of the INSERT clause it's as though I inserted a NULL in that column for each record and if col1 were an auto_increment, it should count up as expected. I noticed and wondered. Thanks for clarifying. Generally, if you don't have to look at the data in your application (maybe because you need to massage it in some way) don't bring it back to your client. Let the engine handle it at the server and things will go much faster. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Excellent! Thanks a lot, Shawn. --Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database design question
I haven't created real project tables yet. But here are the test ones that I'm experimenting with. CREATE TABLE east ( id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, east_1 varchar(255) default NULL, PRIMARY KEY (id) ) ; CREATE TABLE north ( north_id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, north_1 varchar(255) default NULL, north_2 varchar(255) default NULL, north_3 varchar(255) default NULL, PRIMARY KEY (north_id) ) ; CREATE TABLE south ( id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, south_1 varchar(255) default NULL, south_2 varchar(255) default NULL, south_3 varchar(255) default NULL, timestamp timestamp(14) NOT NULL, PRIMARY KEY (id) ) ; CREATE TABLE west ( west_id int(11) NOT NULL auto_increment, keywords varchar(255) default NULL, west_1 varchar(255) default NULL, PRIMARY KEY (west_id) ); I want to search on the keywords in all of these tables and retrieve the records from each table that fits the WHERE clause. The question is...should I just: (1) Make four queries and programmatically keep track of the results from each table? ...or (2) Create another table (let's call it `keywords`) and pull out the keywords into this new table...and store an ID that exists in north,south, east, west...and also store a column that tells us which table this ID is from?...Then we do a query on this table? I guess either way I would have to programmatically at some point fetch with four queries... At 2:46 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: If you posted your actual table structures (SHOW CREATE TABLE xx\G) I think I could be more helpful. Right now I am just shooting in the dark. Shawn Green Database Administrator Unimin Corporation - Spruce Pine James [EMAIL PROTECTED] wrote on 04/26/2005 02:15:49 PM: I tried that and maybe I'm doing something wrong but... -I have to select the same number of columns...for each UNION -And each of the records from the union fall under the same column headings as the first SELECT... I even tried to define column aliases.. SELECT `running` as `running_blah`... -James At 1:03 PM -0400 4/26/05, [EMAIL PROTECTED] wrote: james tu [EMAIL PROTECTED] wrote on 04/26/2005 12:06:34 PM: I have four different activities. Each has its own set of data that I want to save. So, I made four different tables to hold the saved data. Each record also has 'keywords' field (essentially this is the only field that all tables have in common.) Later on, I want to search all the keywords in these tables...and then retrieve the saved information from the four different tables. Question: Should I just search each of the tables individually? Or should I create another table that will hold the keywords, the tablename, and the ID of the saved record in that particular table...and then perform my search on this NEW table? Thanks. -- -James I would properly index each table and UNION the results of the 4 searches. Have you considered creating a Full Text index for your keyword fields? Say your 4 tables are called: running, swimming, jumping, and walking SELECT 'running', column list FROM running WHERE keywords search condition UNION SELECT 'swimming', column list FROM swimming WHERE keywords search condition UNION SELECT 'jumping', column list FROM jumping WHERE keywords search condition UNION SELECT 'walking', column list FROM walking WHERE keywords search condition; I used the first column only to identify which table each match comes from. That way if you have records in each table with matching PK values, you know which table to go back to in order to get any additional information. The only problem with this type of search is that your column list columns must be compatible between each of the tables. If the second column is numeric in your first query then the second column will be coerced to numeric for each of the remaining 3 queries. If for some reason that fails, then the whole UNION fails and you get an error. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- -James Tu --- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) --- -- -James Tu --- ESI Design 111 Fifth Avenue 12th floor New York, NY 10003 (212) 989-3993 ext. 357 (212) 673-4061 (fax) ---
Re: Get a Random Row on a HUGE db
Why don't you generate a random integer in your code and select for an article? If there is no article there, do it again. Even if you have to call it 50 times it may be faster than doing a full scan on the table. It may not work so well if there are lots of gaps in your autoincrement. In perl (don't know about PHP), you could pass your MAX(article_id) to RAND to limit the outside of the random number generated. You may need to call int() on it though as it may be a float. On 4/26/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: This difference between using a 40 mb table and 4mb table with the same traffic was a 70 server load versus a .9 server load. So it was the amount of data that I was selecting that was choking this feature. - [EMAIL PROTECTED] wrote: Thanks for that I implemented to my Random code. Same problem that select * portion is just a nightmare. Remember I selecting 38mb of data when I do that. What I want to do is jump to a Valid random row. Now If I didn't delete content often that would be easy grab the last autoincremented row_id and get a random number between 1 and End Jump to that row to create the link. Very fast. Zero load So what I am trying is this. $last_row =SELECT from firebase_content LAST_INSERT_ID(); $last_row_query = $dbi-query($last_row); $last_row_result = $row-id; But what I am seeing is this: Object id #9 and not the number that is in the database. What am I sending to this variable that is wrong? [snip] I am wanting to display a random page from my site, But I have over 12,000 articles right now and we add over 150 per day. What I wound up doing was a Virtual DOS attack on my own server because the 40 mb db was being loaded to many times. I have tons of memory and a Dell Dual Xeon 2.8 gig. Can someone think up a better way of doing this? I wish Mysql would just bring me back 1 valid random row It could be used in so many ways it should just be a part of MySql anyway. ?php ini_set(display_errors, '1'); header(Pragma: private); header(Cache-Control: post-check=0, pre-check=0, false); header(Cache-Control: no-cache, must-revalidate); require_once(firebase.conf.php); $dbi = new DBI(DB_URL); $stmt = Select * from firebase_content ORDER BY RAND(NOW()) LIMIT 1; $result = $dbi-query($stmt); while($row = $result-fetchRow()) { $title = $row-title; $cate = $row-category; $get = Select cat_url from firebase_categories where cat_name='$cate'; $now = $dbi-query($get); $rows = $now-fetchRow(); $url = $rows-cat_url; $link = $url . $title; } header(Location: http://www.prnewsnow.com/$link;); exit; /* Sudo code that I am trying to create to relieve server stress. function randomRow(table, column) { var maxRow = query(SELECT MAX($column) AS maxID FROM $table); var randomID; var randomRow; do { randomID = randRange(1, maxRow.maxID); randomRow = query(SELECT * FROM $table WHERE $column = $randomID); } while (randomRow.recordCount == 0); return randomRow; } */ ? [/snip] Try this ... SELECT * FROM foo ORDER BY RAND(NOW()) LIMIT 1; 12000 rows is not huge at all, so this should be pretty quick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multi Table Delete in 3.23.47
Hi, The following query runs fine in recent versions of mysql 4.1x etc delete from t2 using tbl_User as t1,tbl_UserTopic as t2 where (t1.txtemail='[EMAIL PROTECTED]') and (t1.intID = t2.intID); But not in 3.23.47 I can't find any reference in the manuals to why this might be. Is there any change in syntax which will make the query work in 3.23.47? -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL to XML
Mathias Thanks for your help, I really appreciated it. And I was just wondering if MySQL has another statment (besides show create table) that only displays the foreign key, but I see that only with the show create table MyTable could get this. Greetings From: [EMAIL PROTECTED] To: Mikel - [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: RE: MYSQL to XML Date: Mon, 25 Apr 2005 17:41:47 +0200 MIME-Version: 1.0 X-Originating-IP: 213.41.126.253 Received: from lists.mysql.com ([213.136.52.31]) by mc5-f35.hotmail.com with Microsoft SMTPSVC(6.0.3790.211); Mon, 25 Apr 2005 08:44:24 -0700 Received: (qmail 21182 invoked by uid 109); 25 Apr 2005 15:41:56 - Received: (qmail 21160 invoked from network); 25 Apr 2005 15:41:55 - Received: pass (lists.mysql.com: local policy) X-Message-Info: JGTYoYF78jHafVH/hFUFqKtbfGVCQG07u9r/IzplGqc= Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:mysql@lists.mysql.com List-Archive: http://lists.mysql.com/mysql/183082 Delivered-To: mailing list mysql@lists.mysql.com References: [EMAIL PROTECTED] User-Agent: Internet Messaging Program (IMP) 3.2.5 X-Virus-Checked: Checked Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 25 Apr 2005 15:44:24.0943 (UTC) FILETIME=[A82A1FF0:01C549AD] Hi Mikel, Show create table shows a line CONSTRAINT ... FORIEGN KEY ... you can add a grep on this line. But this will be difficult. You can construct another desc2xml using just show create table to have it easier. Mathias Selon Mikel - [EMAIL PROTECTED]: Thanx Mathias for your quick and effective response, I see that your program almost display the format that I need, the thing is that I need the foreign key information too, Does MySQL have a statement besides show create table to display this information?Thanks again for your suggestions and help Greetings From: mathias fatene [EMAIL PROTECTED] To: mysql@lists.mysql.com CC: mysql@lists.mysql.com Subject: RE: MYSQL to XML Date: Sun, 24 Apr 2005 21:39:14 +0200 MIME-Version: 1.0 Received: from lists.mysql.com ([213.136.52.31]) by mc3-f23.hotmail.com with Microsoft SMTPSVC(6.0.3790.211); Sun, 24 Apr 2005 12:42:26 -0700 Received: (qmail 15912 invoked by uid 109); 24 Apr 2005 19:40:50 - Received: (qmail 15893 invoked from network); 24 Apr 2005 19:40:50 - Received: pass (lists.mysql.com: local policy) X-Message-Info: JGTYoYF78jEQFMtosA6GPW/w+/WF28t94KBGDmreITY= Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:mysql@lists.mysql.com List-Archive: http://lists.mysql.com/mysql/183030 Delivered-To: mailing list mysql@lists.mysql.com X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.2616 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Checked: Checked Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 24 Apr 2005 19:42:26.0285 (UTC) FILETIME=[BE1839D0:01C54905] Hi Mikel, There are a lot of possibilities including commercial (:o)) products. I suggest you those solutions. The output should be reparsed for your needs : 1. the -X on client : C:\Mysqlmysql -u mathias world -X -e desc country ?xml version=1.0? resultset statement=desc country row FieldCode/Field Typechar(3)/Type Null/Null KeyPRI/Key Default/Default Extra/Extra /row row FieldName/Field Typechar(52)/Type Null/Null Key/Key Default/Default Extra/Extra /row row FieldContinent/Field ... ... 2. install perl DBI and DBIx-XML_RDB modules : #!perl -w # --- # Describe2xml # Author : Mathias FATENE # Date : 24 april 2005 # --- use DBIx::XML_RDB; my $userid='root'; my $password='**'; my $dbname='world'; my $dsn = DBI:mysql:database=$dbname;host=localhost; my $xmlout = DBIx::XML_RDB-new($dsn,'mysql',$userid, $password) || die Failed to make new xmlout; $xmlout-DoSql(describe country); print $xmlout-GetData; C:\Mysqlperl describe.pl ?xml version=1.0? DBI driver=DBI:mysql:database=world;host=localhost RESULTSET statement=describe country ROW FieldCode/Field Typechar(3)/Type Null/Null KeyPRI/Key Default/Default Extra/Extra /ROW ROW FieldName/Field
Re: Multi Table Delete in 3.23.47
On Tue, 26 Apr 2005 23:22:46 +0100, wrote: Hi, The following query runs fine in recent versions of mysql 4.1x etc delete from t2 using tbl_User as t1,tbl_UserTopic as t2 where (t1.txtemail='[EMAIL PROTECTED]') and (t1.intID = t2.intID); But not in 3.23.47 I can't find any reference in the manuals to why this might be. Is there any change in syntax which will make the query work in 3.23.47? I mean how did you do this before 4.x?? -- zzapper vim -c :%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg? http://www.rayninfo.co.uk/tips/ vim, zsh success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update and select question
i use this mail first . (B (Bi have a problem in under sql program: (B (B (BUPDATE (BNGLDENHDT (BSET (BEDT_HUK_FLG = :EDT_HUK_FLG (BWHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM = (SELECT MAX(EDT_NUM) (B FROM NGLDENHDT (B WHERE KAI_CDE = :KAI_CDE_T1 (B AND EDT_NUM != ''); (B (Bthe error message was: (B (BYou can't specify target table 'NGLDENHDT' for update in FROM (Bclause (B (B (Bwhat can i do ,help me ? (B (B (B thank you very much!!
update and select question
(Bi use this mail first . (B (Bi have a problem in under sql program: (B (B (BUPDATE (BNGLDENHDT (BSET (BEDT_HUK_FLG = :EDT_HUK_FLG (BWHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM = (SELECT MAX(EDT_NUM) (B FROM NGLDENHDT (B WHERE KAI_CDE = :KAI_CDE_T1 (B AND EDT_NUM != ''); (B (Bthe error message was: (B (BYou can't specify target table 'NGLDENHDT' for update in FROM (Bclause (B (B (Bwhat can i do ,help me ? (B (B (B thank you very much!!
FW: update and select question
http://dev.mysql.com/doc/mysql/en/update.html (B (BSee the last line on the page. You cannot, in the most current stable (Bversion of MySQL, SELECT from the table you are trying to UPDATE. (B (BJ.R. (B (B-Original Message- (BFrom: $B2+9bJv(B [mailto:[EMAIL PROTECTED] (BSent: Tuesday, April 26, 2005 11:26 PM (BTo: mysql@lists.mysql.com (BSubject: update and select question (B (B (Bi use this mail first . (B (Bi have a problem in under sql program: (B (B (BUPDATE (BNGLDENHDT (BSET (BEDT_HUK_FLG = :EDT_HUK_FLG (BWHERE (B KAI_CDE = :KAI_CDE (B AND EDT_NUM = (SELECT MAX(EDT_NUM) (B FROM NGLDENHDT (B WHERE KAI_CDE = :KAI_CDE_T1 (B AND EDT_NUM != ''); (B (Bthe error message was: (B (BYou can't specify target table 'NGLDENHDT' for update in FROM (Bclause (B (B (Bwhat can i do ,help me ? (B (B (B thank you very much!! smime.p7s Description: S/MIME cryptographic signature