RE: Table query and column overlap
Thanks for posting about the command window sizes. It's been something that I have frustratingly accepted in silence for a while now (especially having a 20 inch monitor at work).. Hooray!! I can finally get some horizontal real estate!! +1 ! I think this should be documented somewhere ! I am sure there is a lot of people out there thinking that it is some kind of command-prompt limitation. Thanks a lot ! Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join subquerie rand problem
i have lets say 1000 different fruits and 1000 different animals with many to man relations now i want to extract 100 differnt fruits held by 100 different animals without dupes of fruit or animal That's a nice one ! I'll give it a try : The point is to get 100 random couples of (id_fruits, id_animals), with unique id_fruits and unique id_animals, right ? SELECT id_fruits AS my_id_fruits, (SELECT id_animals FROM fruits_animals WHERE id_fruits = my_id_fruits ORDER BY RAND() LIMIT 1) AS my_id_animals FROM fruits_animals GROUP BY my_id_fruits ORDER BY RAND() LIMIT 100; You have your unique many-to-many relations' table, you just have to join this with the animals and fruits tables. Regards, Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join subquerie rand problem
Oops, this was not correct, excuse me ! You can have duplicate id_animals with this query. You can do it like this : SELECT my_id_fruits, my_id_animals FROM (SELECT id_fruits AS my_id_fruits, (SELECT id_animals FROM fruits_animals WHERE id_fruits = my_id_fruits ORDER BY RAND() LIMIT 1) AS my_id_animals FROM fruits_animals GROUP BY my_id_fruits) AS tmpQuery GROUP BY tmpQuery.my_id_animals ORDER BY whatever you want LIMIT 100; On 20 Jul 2004 at 16:36, Arnaud [EMAIL PROTECTED] wrote: i have lets say 1000 different fruits and 1000 different animals with many to man relations now i want to extract 100 differnt fruits held by 100 different animals without dupes of fruit or animal That's a nice one ! I'll give it a try : The point is to get 100 random couples of (id_fruits, id_animals), with unique id_fruits and unique id_animals, right ? SELECT id_fruits AS my_id_fruits, (SELECT id_animals FROM fruits_animals WHERE id_fruits = my_id_fruits ORDER BY RAND() LIMIT 1) AS my_id_animals FROM fruits_animals GROUP BY my_id_fruits ORDER BY RAND() LIMIT 100; You have your unique many-to-many relations' table, you just have to join this with the animals and fruits tables. Regards, Arnaud -- 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: Need help optimizing query
On 15 Jul 2004 at 6:27, Patrick Drouin [EMAIL PROTECTED] wrote: I'm having a hard time with the following query. It retrieves about 3K rows from a few tables. One of them contains over 40M rows. When run on a 3Ghz server with 1G of RAM it returns the rows in more than 1 mini. I don't think that's normal. Your indexes look good, but I see that you have some varchar fields. Maybe you could run an optimize table on these tables? Also, you are talking about a 40M rows table. If it is a read only MyISAM table, I might try compression. Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
cannot update read/write table with a read-only table in the where clause
Hi all ! Sorry for the long subject line, but I couldn't find a short one... Here is the problem : My database (mysql 4.1.1) consists mostly for compressed MyISAM tables. I wrote a script, relying on a temporary memory-based table. This table is, of course, writable. If try to do an update or a delete on this table (and only on this table), but with a join on a compressed table in the where clause, I get an error : 'table [mycompressedtable] is read only'. The problem is that I am not trying to write to this table ! For example, let's say I have a memory based table 'tmpTable', and a compressed table 'storageTable'. All these queries give me errors : UPDATE tmpTable, storageTable SET tmpTable.Field1 = 'whatever' WHERE (tmpTAble.Field2 = storageTable.Field2) AND (storageTable.Field3 = 'something') or DELETE tmpTAble FROM tmpTable INNER JOIN storageTable ON (tmpTAble.Field2 = storageTable.Field2) WHERE (storageTable = 'something') or DELETE FROM tmpTAble USING tmpTable, storageTable WHERE (tmpTAble.Field2 = storageTable.Field2) AND (storageTable = 'something') So, it looks like the query analyzer does not even check if the table being updated or deleted is writable, it only checks if there is a read only table in the query, and rejects it in this case... I have seen no mention of this in the documentation, should I report this as a bug ? I can work around this using a subquery, but it is a lot less optimized than using joins, I think... Thanks for helping ! Arnaud Lesauvage -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: create threads rate
Hello, Do you have add in your my.cnf skip-name-resolve ? If not maybe the problem come from here. Also upgrade to 4.0.17 will fix some bug on FreeBSD if i remenber right (check maybe changelog) At 11:42 26/01/2004, Varshavchick Alexander wrote: Hi, I have a 4.0.7 mysql server which services requests from several virtual web sites. Most of the times all works well but periodically the mysql server suddently gets loaded to impossibly high values - load average up to several hundred. During such periods the server sleeps and is not responding. I suspect that the cause of this can be in a spontaneous very high rate of new connections when many new threads are being created and running, and it is too much for the server. The question is if there is some mechanism in mysql which can control the rate in which the new threads are created, something similar to MAX_SPAWN_RATE option in apache web server daemon, or something of the sort? Server mysql 4.0.7 with linux threads runs under FreeBSD 4.6.2. Thanks Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) -- 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: Repeated 100% CPU problem in FreeBSD
Hi Ken, I have compile MySQL with this param : -D SKIP_DNS_CHECK As i have upgrade with portupgrade and forget to repass param, i have put dns off on my.cnf I will check if make -D SKIP_DNS_CHECK work. If yes, the problem is not dns resolve. I hope it's bad param pass to makefile :) Thanks for answer. Best Regards, At 16:59 20/01/2004, you wrote: Hi Arnaud, I did not see an answer to this so; I think the info you need is here http://jeremy.zawodny.com/blog/archives/000203.html and here http://jeremy.zawodny.com/blog/archives/000697.html But I think you want skip-name-resolve to be specified in my.cnf. Ken I have included the relevant section from Jeremeys blog: ___ 1. Non-thread safe DNS Lookups Certain operations are not thread-safe on FreeBSD. A fine example of that is gethostbyname(), which MySQL calls to convert host names in to IP addresses. Usually this happens for each new connection to the server and whenever MySQL needs to contact another machine--typically a replication slave connecting to its master. Based on our testing, the only truly safe way to operate is to use the --skip-name-resolve flag for starting mysqld AND specifying the IP address of the master instead of the hostname. That virtually eliminates the need for MySQL to call gethostbyname(). The symptom of this problem is that the mysqld will consume all the available CPU time even when there are few (if any) queries running. You can try and kill -6 the mysqld process and then run it thru gdb to get a backtrace. You'll likely see something like this: #0 0x829c94c in _thread_kern_sched_state_unlock () at ./cp/tinfo2.cc:300 #1 0x829c0e0 in _thread_kern_sched () at ./cp/tinfo2.cc:300 #2 0x829c787 in _thread_kern_sched_state () at ./cp/tinfo2.cc:300 #3 0x82c5fdc in kevent () at ./cp/tinfo2.cc:300 #4 0x82c5a4f in res_send () at ./cp/tinfo2.cc:300 #5 0x82a4308 in res_query () at ./cp/tinfo2.cc:300 #6 0x82a4737 in res_querydomain () at ./cp/tinfo2.cc:300 #7 0x82a44bb in res_search () at ./cp/tinfo2.cc:300 #8 0x82a9a00 in _gethostbydnsname () at ./cp/tinfo2.cc:300 #9 0x82a8739 in gethostbyname2 () at ./cp/tinfo2.cc:300 #10 0x82a86d4 in gethostbyname () at ./cp/tinfo2.cc:300 #11 0x8275fc4 in my_gethostbyname_r ( name=0x1b5f79a8 your_hostanme, result=0x9fa659b8, buffer=0x9fa651b8 \032, buflen=2048, h_errnop=0x9fa651b0) at my_gethostbyname.c:108 #12 0x80d6fbd in mc_mysql_connect () #13 0x80d6b37 in mc_mysql_reconnect () #14 0x80d4506 in safe_reconnect () #15 0x80d3fb8 in handle_slave () #16 0x828ffa5 in _thread_start () at ./cp/tinfo2.cc:300 #17 0x0 in ?? () If you see that, get rid of DNS lookups. - Original Message - From: Arnaud Pignard [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, January 16, 2004 7:06 PM Subject: Re: Repeated 100% CPU problem in FreeBSD Hi ! Anyone find a solution for this problem ? We have 2 mysql server with same problem. Happen like every 1-2 months. Regards, -- Arnaud Pignard ([EMAIL PROTECTED]) Frontier Online - Opérateur Internet -- 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]
Re: Repeated 100% CPU problem in FreeBSD
Hi ! Anyone find a solution for this problem ? We have 2 mysql server with same problem. Happen like every 1-2 months. Regards, -- Arnaud Pignard ([EMAIL PROTECTED]) Frontier Online - Opérateur Internet -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot find an index that will be used for SELECT
Ed, You should still have a WHERE clause : SELECT * FROM ROWS AS R1 LEFT JOIN ROWS AS R2 ON (R1.index=R2.index AND R2.X=Right) LEFT JOIN ROWS AS R3 ON (R1.index=R3.index AND R3.X=Left) LEFT JOIN ROWS AS R4 ON (R1.index=R4.index AND R4.Y=Bottom) WHERE R1.Y=Top Does this work? Arnaud - Original Message - From: Ed McNierney [EMAIL PROTECTED] To: Arnaud [EMAIL PROTECTED]; mysql List [EMAIL PROTECTED] Sent: jeudi 20 novembre 2003 18:20 Subject: RE: Cannot find an index that will be used for SELECT Arnaud - Thanks very much for a suggestion! Unfortunately, when I do that (I'm not much of a JOIN expert g) I end up selecting ALL the rows in the table. - Ed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using temporary / using filesort and disk tables
Hi Matt, thanks for answezring! A disk-based temp table is used if you're SELECTing a column [that can be] longer than 255 characters (BLOB/TEXT to MySQL). This is because the in memory HEAP tables don't currently support variable length rows. Using something like LEFT(text_col, 255), if feasible, will get around this problem. OK I'll try this out. My SELECT Looks like : SELECT *, CONCAT(someField, 'µZ') As myTemporaryField FROM blablabla... Could it be that mysql prefers to assume that myTemporaryField could be more than 255 characters, event if it is never the case? (the myTemporaryField is there to order my results ascendently, with Null values beeing placed at the end, but maybe there is a better solution for this?) Also could be disk based if the query examines many rows (large temp table), but your tmp_table_size would probably cover that. I think it does. The explain gave me less than 2000 rows scanned (2000 * 1 * 1 * 1) BTW, 512M is very, very high for tmp_table_size! Do you have enough memory for 512M * number of connections? :-) Yes I know this is some crazy setting, but it is just there for testing purpose. I am the only user of this server, and since I couldn't find the reason for this disk tables, I tried some 'huge' settings... ;-) Thanks again for helping! Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cannot find an index that will be used for SELECT
Hi Ed, May you could join the table to itself, using a join criteria of index and position : SELECT * FROM ROWS AS R1 LEFT JOIN ROWS AS R1 ON (R1.index=R2.index AND R2.X=Right) LEFT JOIN ROWS AS R3 ON (R1.index=R3.index AND R3.X=Left) LEFT JOIN etc etc... Does that help? Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using temporary / using filesort and disk tables
Hi All! I think I am getting close to the solution. In fact, mysql creates tmp tables beacause I GROUP BY a query that joins tables. According to the manual, group bys on joins always creates a temporary table. Now the question is, why is this table a temporary disk table??? Thanks in advance. Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using temporary / using filesort and disk tables
Probably cause your table ends up being bigger than: SHOW VARIABLES LIKE 'tmp_table%'; I don't think so. As I stated before (sorry I didn't include my first post), I have : tmp_table_size = 512M max_heap_table_size=512M From my calculation, I would have ~2000 rows in the result of my query, and that would definitively not make 512M ! Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
temporary table always created on disk
Hi all. I alreaedy posted about this, but I get more and more confused ! My temporary tables are ALWAYS created as temporary disk tables ! Here is the query : SELECT commerces.enseigne FROM cellules INNER JOIN commerces ON (cellules.indexCelluleCommerce=commerces.indexCelluleCommerce) WHERE cellules.CodeSite = SIT7401011 GROUP BY cellules.indexcellulecommerce the fields commerces.enseigne and cellules.CodeSite are CHAR(255), cellules.indexcellulecommerce is INT(11) Here is the explain : EXPLAIN SELECT commerces.enseigne FROM cellules INNER JOIN commerces ON (cellules.indexCelluleCommerce=commerces.indexCelluleCommerce) WHERE cellules.CodeSite = SIT7401011 GROUP BY cellules.indexcellulecommerce Result : | cellules | ref | CodeSite,IndexCelluleCommerce | CodeSite | 21 | const | 34 | where used; Using temporary | | commerces | ref | IndexCelluleCommerce | IndexCelluleCommerce | 5 | cellules.IndexCelluleCommerce | 20 | where used | All the fields in this query are indexed. (not unique) Now my server variables : tmp_table_size = 384M max_heap_table_size = 384M version = 3.23.54-nt And the status (the annoying part!!!) : created_tmp_disk_tables 1 created_disk_tables 1 (I checked before and after the query, they both get increased by one) I understand that the temporary table is created because of the GROUP BY clause, but why on disk ??? How could I work around this? Thanks a lot if anyone can help! Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using temporary / using filesort and disk tables
Hi! I have a query that allways creates temporary tables to disk (the ratio created_tmp_disk_tables/created_tmp_tables is close to 0.8, but I think it is 1 for this particular query). This query joins 4 tables, groups by a field a orders by another field (or sometimes an sql variable). When I analyze it, I get where used, using temporary, using filesort for the first table of the join (whatever the order of the join), and where used for the other ones. I have only 2000 rows scanned forthe first table, and 1 for the 3 other ones. The variables tmp_table_size and max_heap_table_size are both set very high (~512M). I would like to get rid of those disk tables, to improve the performance of my query. I understand that using a group by and order by on different fields implies the use of a temporary table. What I don't understand is why this table is created on disk, and not in memory? Is it because of the filesort? If yes, how could I get rid of the filesort? If this is not clear enough, I can post a sample query and the result of the explain. Thanks in advance! Arnaud -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is there any file .frm, .MYD, .MID on mySQL in HPUX Plateform?
Hi everybody, On Windows mySQL database stores its record in 3 files per tables (table.frm, table.MYD, table.MYI) in this directory c:\mysql\data\database_name\ I use this file to make a backup. I want to know if these files exist in mySQL on HPUX? I hope someone can help me. Thanks. __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ - 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
Database backup on Windows HPUX
On windows, I copy the 2 MYD MYI of a database to another directory as a way of backup. It seems to function properly. I just want to know if the same file exists on HPUX and if I can do the same thing. Any help appreciate. Thanks. __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ - 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
.frm, .MYD .MYI exists HPUX?
I know that this files exist on mySQL Windows, (3 files by Tables) someone can tell me if this files exist on mySQL HPUX? I use this files to make a rollback. Thanks. __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ - 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
