mysql: 15 tables equi-join problems
Hi, Do anyone know there may be a limitation in mysql to do a 15 tables equi-join? I did some experiment with it if I only do 10 tables equi-join, it takes seconds to retrieve the results, but if I do more than 10 tables, it took forever and run with 100% CPU time to execute the query. The tables I am trying to join together do NOT contain more than 100 records in each of them (the whole TEST database only 1.7M). So the table size is not a problem at all. Plus I am running this query in a 512M memory Linux box. It should handle it. The following is the query I tried to run: select sum(RPT_SPEND_AMT) FROM FACT_SPEND_TRANSACTION_MONTH A, FLATTENED_COMMODITY B, FLATTENED_GL_ACCOUNT C, FLATTENED_COST_CENTER D, FLATTENED_GEOGRAPHY E, FLATTENED_VENDOR F, FLATTENED_AP_SYSTEM G, FLATTENED_VENDOR_APPROVAL H, FLATTENED_CPC_CAPABLE I, FLATTENED_INVOICE_DATE_RANGE J, FLATTENED_PAYMENT_METHOD K, FLATTENED_REQUISITION_TYPE L, FLATTENED_VENDOR_SPEND_GROUP M, FLATTENED_TRANSACTION_RANGE_COUNT N, FLATTENED_TRANSACTION_RANGE_SPEND O WHERE A.COMMODITY_ID = B.COMMODITY_ID AND A.GL_ACCOUNT_ID = C.GL_ACCOUNT_ID AND A.COST_CENTER_ID = D.COST_CENTER_ID AND A.GEOGRAPHY_ID = E.GEOGRAPHY_ID AND A.VENDOR_ID = F.VENDOR_ID AND A.AP_SYSTEM_ID = G.AP_SYSTEM_ID AND A.VENDOR_APPROVAL_ID = H.VENDOR_APPROVAL_ID AND A.CPC_CAPABLE_ID = I.CPC_CAPABLE_ID AND A.INVOICE_DATE_RANGE_ID = J.INVOICE_DATE_RANGE_ID AND A.PAYMENT_METHOD_ID = K.PAYMENT_METHOD_ID AND A.REQUISITION_TYPE_ID = L.REQUISITION_TYPE_ID AND A.VENDOR_SPEND_GROUP_ID = M.VENDOR_SPEND_GROUP_ID AND A.TRANSACTION_RANGE_COUNT_ID = N.TRANSACTION_RANGE_COUNT_ID AND A.TRANSACTION_RANGE_SPEND_ID = O.TRANSACTION_RANGE_SPEND_ID; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql: how to copy table with varchar datatype?
Hi, Does anybody know how to copy an existing mySQL table schema with varchar datatype my sql statement is: CREATE TABLE new_table AS SELECT * FROM old_table where 1=0; All the text columns in the old_table are in VARCHAR datatype, however, after I copy to a new_table, all columns change to CHAR format. Please help! Thanks, Wai - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Problems installing mysql-4.0-alpha on my Redhat
Hi, It happens to me, just make sure you are not using any symbolic link, when I try to use symbolic link to mysql 4.0.0-aplha and it won't work. In my case, I just do mv mysql-4.0.0-alpha-pc-linux-gnu-i686 mysql and it works. Wai Lee Zeborg Inc. -Original Message- From: Carl Troein [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 13, 2001 6:59 AM To: [EMAIL PROTECTED] Subject: Re: Problems installing mysql-4.0-alpha on my Redhat Nilesh Parmar writes: I get the following messages on the console. [1] 28102 $ starting mysql daemon with databases from /var/lib/mysql 011213 :16:56:43 mysqld ended I've not idea why do I get the message mysqld ended. Because it did. You'll have to look in the log file to find out why. Make sure you've taken all the steps necessary for installation, such as setting the right owner for the data directory and running mysql_install_db Error 2002: Can't connect to MySQL server through socket /var/lib/mysql/mysql.sock. Well, since the server fails to start that error is to be expected. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to do similar query like Oracle using not exists
Hi, I try to do an Oralce query in mySQL insert into geoTable select '011852','HK' from dual where not exists (select * from geoTable where geoID = '011852') In Oracle, this query will check the new geoID is not exist before inserting into the table. In mySQL, I do create temporary table tmp1 select '011852' geoID, 'HK' countryName; create temporary table tmp2 select a.geoID, a.countryName from tmp1 a left join geoTable b on a.geoID = b.geoID where a.geoID is null; insert into geoTable select * from tmp2; Do anyone know mySQL has a similar function like Oracle to check NOT EXISTS or there is a better queries than what I use here. Thanks Wai Lee - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Indexing Problem
Hi all, Can someone tell me how to speed up the index creation??? I am trying to build an index for a 13,875,354 records(13 million) table with 176,322 distinct vendor_id(VARCHAR(40)) in the table. the existing size of the transaction table: transaction.MYD = 2,128,954,624 bytes transaction.MYI = 1,096,510,464 bytes (there are already 5 indexes built in this table) transaciton.frm = 9,450 bytes I dig through the manual and changed any possible settings set-variable= max_heap_table_size=2000M set-variable= key_buffer=2500M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2500M set-variable= join_buffer=2500M set-variable= record_buffer=2500M set-variable= myisam_sort_buffer_size=2500M set-variable= myisam_max_sort_file_size=2500M set-variable= myisam_max_extra_sort_file_size=2500M set-variable= thread_cache=8 We are running mySQL in a 4G memory linux box, I believe the buffer size I allocated to mySQL should cache the whole table. Instead, I am wrong, while building a new index, I saw there was temporary files created for the transaction table (the write disk activities were very source consuming) and the single index creation took 4.5 hours to finished Unbelievable Any suggestions will be greatly helpful!!! Lee Zeborg Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Indexing Problem
Hi, Thanks for the ones reply this email. The SHOW PROCESSLIST had a state Repair by sorting while creating the index. I assume the set-variable= myisam_max_sort_file_size=2500M set-variable= myisam_max_extra_sort_file_size=2500M is large enough to cache the index. My question is when creating the index, mysql first will write the original table data file to temporary file (something like #sql-.MYD) and sort the temporary file by the index column, and build the index file. Is it true? I also thinking to use a partial column for the index, now the column I use is VARCHAR(40), and I am not sure when I use create index idx1 on transaction(vendor_id(20)); will help??? -Original Message- From: Sergei Golubchik [mailto:[EMAIL PROTECTED]] Sent: Friday, October 26, 2001 10:53 AM To: Wai Lee Cc: [EMAIL PROTECTED] Subject: Re: Indexing Problem Hi! On Oct 26, Wai Lee wrote: Hi all, Can someone tell me how to speed up the index creation??? I am trying to build an index for a 13,875,354 records(13 million) table with 176,322 distinct vendor_id(VARCHAR(40)) in the table. the existing size of the transaction table: transaction.MYD = 2,128,954,624 bytes transaction.MYI = 1,096,510,464 bytes (there are already 5 indexes built in this table) transaciton.frm = 9,450 bytes I dig through the manual and changed any possible settings set-variable= max_heap_table_size=2000M set-variable= key_buffer=2500M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2500M set-variable= join_buffer=2500M set-variable= record_buffer=2500M set-variable= myisam_sort_buffer_size=2500M set-variable= myisam_max_sort_file_size=2500M set-variable= myisam_max_extra_sort_file_size=2500M set-variable= thread_cache=8 We are running mySQL in a 4G memory linux box, I believe the buffer size I allocated to mySQL should cache the whole table. Instead, I am wrong, while building a new index, I saw there was temporary files created for the transaction table (the write disk activities were very source consuming) and the single index creation took 4.5 hours to finished Unbelievable Any suggestions will be greatly helpful!!! What did SHOW PROCESSLIST show ? Was it repair by sort or repair with keycache ? If the latter - increase myisam_max_sort_file_size (and, may be, myisam_max_extra_sort_file_size). Anyway, huge i/o indicates something wrong with variable settings. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Very Slow Performance to Build Index in Large mySQL Table
Hi, I am experiencing a very slow performance while I am building an index on a table. The following are some facts of system and the table I am dealing with: Table size: 13,857,354 Number of distinct value in the column I use as index: 176,322 Platform: Linux system with dual CPUs 1G Hz and 4G in memory. Time to build: 3 hours mySQL Configuration setting: key_buffer_size | 1073737728 sort_buffer | 268435448 myisam_sort_buffer_size | 67108864 myisam_max_extra_sort_file_size | 256 | myisam_max_sort_file_size | 2047 Any help will be greatly appreciated. Wai Zeborg Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
