Re: Problem with CREATE TABLE/DROP TABLE
On Jun 24, 2008, at 10:13 AM, Rolando Edwards wrote: My first impression is to say: Sounds like the InnoDB internal data dictionary still has the table recorded somewhere. According to Page 566 Paragraph 3 of MySQL Administrator's Guide and Language Reference (ISBN 0-672-32870-4), InnoDB always needs the shared tablespace because it puts its internal data dictionary and undo logs there. This is may explain why the bug persists. Innobase Oy never got to fix it, and now Oracle owns Innobase Oy (I am still in mourning over that). This was my first thought, but the original bug report suggests that the bug occurs with other table types besides InnoDB. You may want to look into Falcon if you want ACID transaction supported tables. Transactional support isn't critical to my implementation, but foreign keys are. Also, it's my undertanding that Falcon is unsupported before MySQL 6, and I'm not prepared to upgrade that far yet. -- Gwynne, Daughter of the Code This whole world is an asylum for the incurable. -Original Message- From: Gwynne Raskind [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2008 2:58 AM To: mysql@lists.mysql.com Subject: Problem with CREATE TABLE/DROP TABLE I'm having the issue with CREATE TABLE described by Bug #30513 (http://bugs.mysql.com/bug.php?id=30513 ). To summarize, a table which previously existed, and then is dropped by DROP TABLE IF EXISTS, becomes randomly unable to be recreated. Here is my comment on that bug: Having this same issue using MySQL 5.1.24-rc and 5.1.25-rc and an InnoDB table. Only solution I found was to dump and recreate my database, which is a ridiculous inconvenience since I'm having the issue with a test table I need to drop and recreate often. Did NOT have this issue before upgrading from 5.0.51. There is NO stray .frm file in the database directory, and the InnoDB tablespace/table monitors show no errors. No unusual entries appear in the MySQL error log. The table in question has the structure: CREATE TABLE TestData ( nameVARCHAR(64) NOT NULL, dateFormat VARCHAR(32) NOT NULL, loginForOne INT(1) UNSIGNED NOT NULL, loginForTwo INT(1) UNSIGNED NOT NULL, indexText MEDIUMTEXT NOT NULL ) ENGINE=InnoDB DEFAULT CHARACTER SET 'utf8'; It is correct that the table has no indexes. I tried `-quoting the table name and changing engines and character sets to no avail. Changing the table's name only resulted in the same thing starting to happen again with the same table. The only special thing about the table is that it's at the end of a batch file. Has anyone else had this problem, and more especially, does anyone know a useable workaround for it? I'm at my wits' end, and downgrading to 5.0.51 isn't a viable option for my environment; this isn't a production system and I'm using some 5.1-specific features as of my upgrade. -- Gwynne, Daughter of the Code This whole world is an asylum for the incurable. -- 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]
I have a problem when I'm studying the full text search's expansion mode
The official table's data.
++---+-+
| id | title | body|
++---+-+
| 1 | MySQL Tutorial| DBMS stands for DataBase ...|
| 2 | How To Use MySQL Well | After you went through a ...|
| 3 | Optimizing MySQL | In this tutorial we will show ... |
| 4 | 1001 MySQL Tricks | 1. Never run mysqld as root. 2. ... |
| 6 | MySQL Security| When configured properly, MySQL ... |
++---+-+
But the expansion mode is explained as follows.
mysql *SELECT * FROM articles*
- *WHERE MATCH (title,body)*
- *AGAINST ('database' IN NATURAL LANGUAGE MODE);*
++---+--+
| id | title | body |
++---+--+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial| DBMS stands for DataBase ... |
++---+--+
2 rows in set (0.00 sec)
mysql *SELECT * FROM articles*
- *WHERE MATCH (title,body)*
- *AGAINST ('database' WITH QUERY EXPANSION);*
++---+--+
| id | title | body |
++---+--+
| 1 | MySQL Tutorial| DBMS stands for DataBase ... |
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 3 | Optimizing MySQL | In this tutorial we will show ...|
++---+--+
3 rows in set (0.00 sec)
Why not id equals 2 and 4 didn't display in the result.They all include the
word mysql.
--
I'm a MySQL DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
Problem with BLOB data.
Hi, I have a problem with the migration of a table that has a column with BLOB data. The source server is MySQL version: 4.0.16 and the destination server is MySQL version: 5.0.45 I tried with mysqldump and SELECT INTO but when import the data on the destination server, the BLOB data are corrupt. Someone can help me? Thanks -- Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma tel:0657060500 email:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index/Range Problem?
Dave schrieb: Hi all, I've been trying to optimize some of our queries against a large database and come up against an index problem I haven't been able to find any documentation on. I've cut the query down to the bare minimum, and found the following -- explain Select iname,domain,serv,time from log where date between '2008-05-10' and '2008-05-30'; ++-+---+---+---+--+-+--+ ---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+--+-+--+ ---+-+ | 1 | SIMPLE | log | range | date | date | 4 | NULL | 45178 | Using where | ++-+---+---+---+--+-+--+ ---+-+ As you can see in the above query, it uses type range and the key date is used. If I change it to -05-01 to -05-30 though it does not : explain Select iname,domain,serv,time from log where date between '2008-05-01' and '2008-05-30'; ++-+---+--+---+--+-+--+- ---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+- ---+-+ | 1 | SIMPLE | log | ALL | date | NULL | NULL| NULL | 353558 | Using where | ++-+---+--+---+--+-+--+- ---+-+ As you can see the type is now ALL and it doesn't work. as Ananda already wrote, it seems rows valid for '2008-05-01' to '2008-05-30' are exceed the threshold when MySQL thinks it is faster to scan the table instead of scan the index and than read the table What could be the cause of this? It seems like its limited to a specific number of rows? The rows in explain appear to be wrong... mysql Select count(id) from log where date between '2008-05-01' and '2008-05-30'; +---+ | count(id) | +---+ | 85232 | +---+ 1 row in set (0.97 sec) rows is how many rows MySQL thinks it must examine to execute the query, not the number of rows possible returned http://dev.mysql.com/doc/refman/5.1/en/using-explain.html -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index/Range Problem?
Hi, Thanks for the help; that makes sense I think you guys are right. Is it worth tuning such a thing? It seems to me like it would be much faster to use the index? Thanks again Dave Dave schrieb: Hi all, I've been trying to optimize some of our queries against a large database and come up against an index problem I haven't been able to find any documentation on. I've cut the query down to the bare minimum, and found the following -- explain Select iname,domain,serv,time from log where date between '2008-05-10' and '2008-05-30'; ++-+---+---+---+--+-+--+ ---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+--+-+--+ ---+-+ | 1 | SIMPLE | log | range | date | date | 4 | NULL | 45178 | Using where | ++-+---+---+---+--+-+--+ ---+-+ As you can see in the above query, it uses type range and the key date is used. If I change it to -05-01 to -05-30 though it does not : explain Select iname,domain,serv,time from log where date between '2008-05-01' and '2008-05-30'; ++-+---+--+---+--+-+--+- ---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+- ---+-+ | 1 | SIMPLE | log | ALL | date | NULL | NULL| NULL | 353558 | Using where | ++-+---+--+---+--+-+--+- ---+-+ As you can see the type is now ALL and it doesn't work. as Ananda already wrote, it seems rows valid for '2008-05-01' to '2008-05-30' are exceed the threshold when MySQL thinks it is faster to scan the table instead of scan the index and than read the table What could be the cause of this? It seems like its limited to a specific number of rows? The rows in explain appear to be wrong... mysql Select count(id) from log where date between '2008-05-01' and '2008-05-30'; +---+ | count(id) | +---+ | 85232 | +---+ 1 row in set (0.97 sec) rows is how many rows MySQL thinks it must examine to execute the query, not the number of rows possible returned http://dev.mysql.com/doc/refman/5.1/en/using-explain.html -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index/Range Problem?
Hi all, I've been trying to optimize some of our queries against a large database and come up against an index problem I haven't been able to find any documentation on. I've cut the query down to the bare minimum, and found the following -- explain Select iname,domain,serv,time from log where date between '2008-05-10' and '2008-05-30'; ++-+---+---+---+--+-+--+ ---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+--+-+--+ ---+-+ | 1 | SIMPLE | log | range | date | date | 4 | NULL | 45178 | Using where | ++-+---+---+---+--+-+--+ ---+-+ As you can see in the above query, it uses type range and the key date is used. If I change it to -05-01 to -05-30 though it does not : explain Select iname,domain,serv,time from log where date between '2008-05-01' and '2008-05-30'; ++-+---+--+---+--+-+--+- ---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+- ---+-+ | 1 | SIMPLE | log | ALL | date | NULL | NULL| NULL | 353558 | Using where | ++-+---+--+---+--+-+--+- ---+-+ As you can see the type is now ALL and it doesn't work. What could be the cause of this? It seems like its limited to a specific number of rows? The rows in explain appear to be wrong... mysql Select count(id) from log where date between '2008-05-10' and '2008-05-30'; +---+ | count(id) | +---+ | 45983 | +---+ 1 row in set (0.52 sec) mysql Select count(id) from log where date between '2008-05-01' and '2008-05-30'; +---+ | count(id) | +---+ | 85232 | +---+ 1 row in set (0.97 sec) Does anyone know what could be the cause of this or where to look next? Thanks Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index/Range Problem?
what is the total no. of records in the table. Index will be used , if the query selects between 5 to 10% of the total records in the table. If its more than that then, optimizer will doing ALL scan, as it assumes doing ALL scan is faster than an INDEX SCAN. On 6/9/08, Dave [EMAIL PROTECTED] wrote: Hi all, I've been trying to optimize some of our queries against a large database and come up against an index problem I haven't been able to find any documentation on. I've cut the query down to the bare minimum, and found the following -- explain Select iname,domain,serv,time from log where date between '2008-05-10' and '2008-05-30'; ++-+---+---+---+--+-+--+ ---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+---+--+-+--+ ---+-+ | 1 | SIMPLE | log | range | date | date | 4 | NULL | 45178 | Using where | ++-+---+---+---+--+-+--+ ---+-+ As you can see in the above query, it uses type range and the key date is used. If I change it to -05-01 to -05-30 though it does not : explain Select iname,domain,serv,time from log where date between '2008-05-01' and '2008-05-30'; ++-+---+--+---+--+-+--+- ---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+- ---+-+ | 1 | SIMPLE | log | ALL | date | NULL | NULL| NULL | 353558 | Using where | ++-+---+--+---+--+-+--+- ---+-+ As you can see the type is now ALL and it doesn't work. What could be the cause of this? It seems like its limited to a specific number of rows? The rows in explain appear to be wrong... mysql Select count(id) from log where date between '2008-05-10' and '2008-05-30'; +---+ | count(id) | +---+ | 45983 | +---+ 1 row in set (0.52 sec) mysql Select count(id) from log where date between '2008-05-01' and '2008-05-30'; +---+ | count(id) | +---+ | 85232 | +---+ 1 row in set (0.97 sec) Does anyone know what could be the cause of this or where to look next? Thanks Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dump problem
Hello list, I have a problem dumping a database. The problem is that the database uses the swedish charset (historical, hosting provider didn't have all sets). The data itself is Arabic (windows-1256), and although I have no idea how it is stored inside the database's files, the website's output is Arabic windows-1256. Now I am ready to move away from that provider, so I want to take a dump of the data. I've tried many mysqldump options, but I always get a file full of giberish. Using iconv on the file doesn't even work... So my question is: How do you take a dump of a swedish-based database, and end up with a windows-1256 dump file?? Thanks in advance. Noor
Re: Dump problem
נור דאוד schrieb: Hello list, I have a problem dumping a database. The problem is that the database uses the swedish charset (historical, hosting provider didn't have all sets). The data itself is Arabic (windows-1256), and although I have no idea how it is stored inside the database's files, the website's output is Arabic windows-1256. if you have stored another charsets in a filed than the field is declared as, than you need to change the charsets of this field without converting the content http://dev.mysql.com/doc/refman/5.1/en/charset-conversion.html Now I am ready to move away from that provider, so I want to take a dump of the data. I've tried many mysqldump options, but I always get a file full of giberish. Using iconv on the file doesn't even work... So my question is: How do you take a dump of a swedish-based database, and end up with a windows-1256 dump file?? with SET NAMES, you tell MySQL which charset it should use to return content to you, or which charset has the content you send to the server http://dev.mysql.com/doc/refman/5.1/en/charset-connection.html -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger problem
On May 15, 2008, at 4:30 AM, rustam ershtukaev wrote: I have been trying to write a trigger in mysql, but can't get it to work. It's really simple,i just need my trigger to add varchar value to a table on insert if postcode = 1000. Where does postcode come from? Your SELECT statement appears to have no relation to the row to be inserted. It also appear that it will always set v_postcode to 1000 if the departement table contains *any* rows with a postcode of 1000. this is how i did it: delimiter | drop trigger testdep| create trigger testdep before insert on departements for each row begin declare v_postcode INTEGER; declare v_place VARCHAR; select departement_postcode into v_postcode from departement where departement_postcode = 1000; IF v_postcode = 1000 then update departementen set departement_place = 'New York' where departement_postcode = 1000; END IF; END| but when i insert a new row i don't get my v_place value set so if there someone who has time to help me with this i would greatly appreciate this :) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible Mutex Problem
Under 5.0.41 I have having problems of CPU sitting at exactly 100% load on a single CPU for very long periods of time, like 10 hours. I traced this command: mysql SHOW MUTEX STATUS; Which returned 1281006 lines, ending with: +-+--+--+ | File| Line | OS_waits | +-+--+--+ ... ... | buf0buf.c | 497 |0 | | buf0buf.c | 494 |0 | | buf0buf.c | 497 |0 | | buf0buf.c | 494 |0 | | buf0buf.c | 545 | 1159932 | | fil0fil.c | 1293 | 398 | | srv0start.c | 1201 |0 | | srv0start.c | 1194 |0 | | srv0start.c | 1172 |0 | | dict0mem.c | 90 |0 | | dict0mem.c | 90 |0 | | srv0srv.c | 875 |50886 | | srv0srv.c | 872 | 113162 | | thr0loc.c | 229 |1 | | mem0pool.c | 205 |25482 | | sync0sync.c | 1289 |0 | +-+--+--+ (All other lines are as same as first two.) Is there an InnoDB expert who can tell me whether this is indicating a problem? Regards, Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order Problem
That is fine. On Thu, May 8, 2008 at 4:51 PM, Neil Tompkins [EMAIL PROTECTED] wrote: Perfect. It worked just how I wanted. Thanks for your help. Neil Date: Wed, 7 May 2008 19:54:39 +0200 To: [EMAIL PROTECTED] Subject: Re: Order Problem From: [EMAIL PROTECTED] Hi, You should look at the `FIND_IN_SET` function here: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set Your query could look like: SELECT ProductID FROM Products WHERE Enabled= ' Yes' AND ProductID IN(varProductID) ORDER BY FIND_IN_SET(ProductID, varProductID); Haven't tested it, though... Take care, Aleksandar _ Discover and Win with Live Search http://clk.atdmt.com/UKM/go/msnnkmgl001007ukm/direct/01/ -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Problem with install DBD
Hi, I have installed MySQL on an Opteron 64bit with operating system Scientific Linux CERN SLC release 4.6 (Beryllium) by an archive TAR, under the directory /opt/local/mysql50. The problem is that I want to install drivers DBD to use MySQLBenchmark but i run perl Makefile.PL --mysql_config=/opt/local/mysql50/bin/mysql_config then make and then make test But there are many errors of this type: t/warningsinstall_driver(mysql) failed: Can't load '/opt/BUILD/DBD/DBD-mysql-4.006/blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.15: cannot open shared object file: No such file or directory at /usr/lib64/perl5/5.8.5/x86_64-linux-thread-multi/DynaLoader.pm line 230 but the library there is in: /opt/local/mysql50/lib/mysql/libmysqlclient.so.15 and is located in the directory that is returned by the script /opt/local/mysql50/bin/mysql_config --libs -L/opt/local/mysql50/lib/mysql -lmysqlclient -lz -lcrypt -lnsl -lm Someone help me? Thanks -- Ing. Stefano Elmopi Gruppo Darco - Area ICT Sistemi Via Ostiense 131/L Corpo B, 00154 Roma tel:0657060500 email:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trigger problem
I have been trying to write a trigger in mysql, but can't get it to work. It's really simple,i just need my trigger to add varchar value to a table on insert if postcode = 1000. this is how i did it: delimiter | drop trigger testdep| create trigger testdep before insert on departements for each row begin declare v_postcode INTEGER; declare v_place VARCHAR; select departement_postcode into v_postcode from departement where departement_postcode = 1000; IF v_postcode = 1000 then update departementen set departement_place = 'New York' where departement_postcode = 1000; END IF; END| but when i insert a new row i don't get my v_place value set so if there someone who has time to help me with this i would greatly appreciate this :)
Re: Trigger problem
On Thu, May 15, 2008 at 2:30 AM, rustam ershtukaev [EMAIL PROTECTED] wrote: I have been trying to write a trigger in mysql, but can't get it to work. It's really simple,i just need my trigger to add varchar value to a table on insert if postcode = 1000. this is how i did it: delimiter | drop trigger testdep| create trigger testdep before insert on departements for each row begin declare v_postcode INTEGER; declare v_place VARCHAR; select departement_postcode into v_postcode from departement where departement_postcode = 1000; IF v_postcode = 1000 then update departementen set departement_place = 'New York' where departement_postcode = 1000; END IF; END| but when i insert a new row i don't get my v_place value set so if there someone who has time to help me with this i would greatly appreciate this :) If I had to make a guess it is because you are using before insert, and there are no other rows that match: select departement_postcode into v_postcode from departement where departement_postcode = 1000; so the if statement fails. Example: * Using after* mysql drop table if exists t1,t2; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE `t1` ( - `col1` int, - `col2` varchar(20) - ) ; Query OK, 0 rows affected (0.03 sec) mysql mysql CREATE TABLE `t2` ( - `col1` int, - `col2` varchar(20) - ); Query OK, 0 rows affected (0.03 sec) mysql mysql delimiter | mysql drop trigger if exists testdep| Query OK, 0 rows affected, 1 warning (0.00 sec) mysql create trigger testdep -after insert on t1 -for each row - begin -declare v_col1 INTEGER; - - select col1 -into v_col1 -from t1 -where col1 = 5; - - IF v_col1 = 5 then -update t2 set col2 = 'delta'; - END IF; - - END| Query OK, 0 rows affected (0.01 sec) mysql delimiter ; mysql mysql insert into t1 values(0,'alpha'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql mysql insert into t2 values(5,'bravo'); Query OK, 1 row affected (0.00 sec) mysql mysql insert into t1 values(5,'charlie'); Query OK, 1 row affected (0.00 sec) mysql mysql select * from t2; +--+---+ | col1 | col2 | +--+---+ |5 | delta | +--+---+ 1 row in set (0.00 sec) * On the other hand before* mysql drop table if exists t1,t2; Query OK, 0 rows affected (0.00 sec) mysql CREATE TABLE `t1` ( - `col1` int, - `col2` varchar(20) - ) ; Query OK, 0 rows affected (0.03 sec) mysql mysql CREATE TABLE `t2` ( - `col1` int, - `col2` varchar(20) - ); Query OK, 0 rows affected (0.05 sec) mysql mysql delimiter | mysql drop trigger if exists testdep| Query OK, 0 rows affected, 1 warning (0.00 sec) mysql create trigger testdep -before insert on t1 -for each row - begin -declare v_col1 INTEGER; - - select col1 -into v_col1 -from t1 -where col1 = 5; - - IF v_col1 = 5 then -update t2 set col2 = 'delta'; - END IF; - - END| Query OK, 0 rows affected (0.00 sec) mysql delimiter ; mysql mysql insert into t1 values(0,'alpha'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql mysql insert into t2 values(5,'bravo'); Query OK, 1 row affected (0.00 sec) mysql mysql insert into t1 values(5,'charlie'); Query OK, 1 row affected, 1 warning (0.00 sec) mysql mysql select * from t2; +--+---+ | col1 | col2 | +--+---+ |5 | bravo | +--+---+ 1 row in set (0.00 sec) I suggest you provide a complete example (ddl and dml, and btw is a trigger definition ddl or dml?) if you need more assistance. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Order Problem
Perfect. It worked just how I wanted. Thanks for your help. Neil Date: Wed, 7 May 2008 19:54:39 +0200 To: [EMAIL PROTECTED] Subject: Re: Order Problem From: [EMAIL PROTECTED] Hi, You should look at the `FIND_IN_SET` function here: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set Your query could look like: SELECT ProductID FROM Products WHERE Enabled= ' Yes' AND ProductID IN(varProductID) ORDER BY FIND_IN_SET(ProductID, varProductID); Haven't tested it, though... Take care, Aleksandar _ Discover and Win with Live Search http://clk.atdmt.com/UKM/go/msnnkmgl001007ukm/direct/01/
Order Problem
Hi All, I've the following query :SELECT ProductID FROM Products WHERE Enabled= ' Yes' AND ProductID IN(varProductID) This query works fine. However the query result is in a different order to what I passed in varProductID. How can I order the results based on my list like varProductID = 1000,2500,1500 At the moment the result is 1000 1500 2500 But I want 1000 2500 1500 Thanks, Neil _ Discover and Win with Live Search http://clk.atdmt.com/UKM/go/msnnkmgl001007ukm/direct/01/
Query problem
I am trying to display results from one or the other part of the query, however, at the moment it is showing results from both parts. The Replace part of the query works fine in that it chooses the correct data to display, but the content relevant to /8/ always displays even when I select, say, /13/ or /22/. I need to hide results for /8/ until it is selected. rsChannelArticles.Source = SELECT * FROM bunker01db1.tbl_allarticles WHERE (fld_category LIKE '%/ + Replace(rsChannelArticles__channel, ', '') + /%' AND fld_show = 1 AND fld_reldate =NOW()) OR (fld_category LIKE '%/8/%' AND fld_reldate =NOW()) ORDER BY fld_reldate DESC The reason for this is that the webpage displays content when a release date/time (or embargo) has passed, however, the section /8/ needs content to disappear once that date/time has passed. Any ideas? Thanks
Re: Problem with character set and connection collation
Hi Sulo, Open the file you are importing as it is probably that it contains references of another character set in the table creation. If so replace that character set for '' or 'utf8'. I hope it helps, Leandro sulochan acharya wrote: Hello all, here is my problem: I am trying to set mysql to unicode character, so that i can get my dictionary application to look at words in Nepali. here is my setting: mysql charset: utf-8 unicode when i make a new database: mysql connection collation is utf8-general-ci and my new database collation is also utf-general-ci i import tables into this database using mysql command : source /path to file when i browse the tables in this database i dont see nepali world instead it seems like mysql cant read the characters; please help!! best, sulo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with character set and connection collation
A week or so ago I explored this in depth because I was having the same problems. (It was affecting an English file that had some Windows (CP-1252) characters that didn't directly map to UTF-8. That message is at http://lists.mysql.com/mysql/212392. I didn't mention it in my posting, but latin1 is CP-1252. I couldn't get that to work, either. I don't know how it would apply directly to your situation, but I think you are running into a similar kind of problem. I also can't guarantee that my conclusions were entirely correct. Perhaps someone else can add to this. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: sulochan acharya [mailto:[EMAIL PROTECTED] Sent: Sunday, April 27, 2008 6:01 AM To: mysql@lists.mysql.com Subject: Problem with character set and connection collation Hello all, here is my problem: I am trying to set mysql to unicode character, so that i can get my dictionary application to look at words in Nepali. here is my setting: mysql charset: utf-8 unicode when i make a new database: mysql connection collation is utf8-general-ci and my new database collation is also utf-general-ci i import tables into this database using mysql command : source /path to file when i browse the tables in this database i dont see nepali world instead it seems like mysql cant read the characters; please help!! best, sulo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with character set and connection collation
On Mon, 28 Apr 2008, Jerry Schwartz [EMAIL PROTECTED] wrote: A week or so ago I explored this in depth because I was having the same problems. (It was affecting an English file that had some Windows (CP-1252) characters that didn't directly map to UTF-8. That message is at http://lists.mysql.com/mysql/212392. I didn't mention it in my posting, but latin1 is CP-1252. I couldn't get that to work, either. Latin-1 is not Microsoft Windows codepage 1252: Latin-1 has control characters in the 0x80 - 0x9F block of code points, but 1252 replaces some of those code points with printable characters. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with character set and connection collation
Well, if latin1 is not CP-1252, then that explains why it didn't fix my problem; but here's what 5.0.45-community-nt says: mysql show character set; +--+-+-++ | Charset | Description | Default collation | Maxlen | +--+-+-++ | big5 | Big5 Traditional Chinese| big5_chinese_ci | 2 | | dec8 | DEC West European | dec8_swedish_ci | 1 | | cp850| DOS West European | cp850_general_ci| 1 | | hp8 | HP West European| hp8_english_ci | 1 | | koi8r| KOI8-R Relcom Russian | koi8r_general_ci| 1 | | latin1 | cp1252 West European| latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 | | swe7 | 7bit Swedish| swe7_swedish_ci | 1 | | ascii| US ASCII| ascii_general_ci| 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci| 3 | | sjis | Shift-JIS Japanese | sjis_japanese_ci| 2 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | tis620 | TIS620 Thai | tis620_thai_ci | 1 | | euckr| EUC-KR Korean | euckr_korean_ci | 2 | | koi8u| KOI8-U Ukrainian| koi8u_general_ci| 1 | | gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 | | greek| ISO 8859-7 Greek| greek_general_ci| 1 | | cp1250 | Windows Central European| cp1250_general_ci | 1 | | gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 | | latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 | | armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 | | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 | | cp866| DOS Russian | cp866_general_ci| 1 | | keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 | | macce| Mac Central European| macce_general_ci| 1 | | macroman | Mac West European | macroman_general_ci | 1 | | cp852| DOS Central European| cp852_general_ci| 1 | | latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 | | cp1251 | Windows Cyrillic| cp1251_general_ci | 1 | | cp1256 | Windows Arabic | cp1256_general_ci | 1 | | cp1257 | Windows Baltic | cp1257_general_ci | 1 | | binary | Binary pseudo charset | binary | 1 | | geostd8 | GEOSTD8 Georgian| geostd8_general_ci | 1 | | cp932| SJIS for Windows Japanese | cp932_japanese_ci | 2 | | eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 | +--+-+-++ Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Tim McDaniel [mailto:[EMAIL PROTECTED] Sent: Monday, April 28, 2008 12:19 PM Cc: mysql@lists.mysql.com Subject: RE: Problem with character set and connection collation On Mon, 28 Apr 2008, Jerry Schwartz [EMAIL PROTECTED] wrote: A week or so ago I explored this in depth because I was having the same problems. (It was affecting an English file that had some Windows (CP-1252) characters that didn't directly map to UTF-8. That message is at http://lists.mysql.com/mysql/212392. I didn't mention it in my posting, but latin1 is CP-1252. I couldn't get that to work, either. Latin-1 is not Microsoft Windows codepage 1252: Latin-1 has control characters in the 0x80 - 0x9F block of code points, but 1252 replaces some of those code points with printable characters. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with character set and connection collation
Hello all, here is my problem: I am trying to set mysql to unicode character, so that i can get my dictionary application to look at words in Nepali. here is my setting: mysql charset: utf-8 unicode when i make a new database: mysql connection collation is utf8-general-ci and my new database collation is also utf-general-ci i import tables into this database using mysql command : source /path to file when i browse the tables in this database i dont see nepali world instead it seems like mysql cant read the characters; please help!! best, sulo
Re: a strange problem
liaojian_163 schrieb: hi,all. In my mysql server,I have a strange problem. can someone help me? Thank you. mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 2500 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2543 | 41 | 2008-04-22 21:55:22 | [...] 10 rows in set (0.00 sec) mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2540 | 41 | 2008-04-19 12:29:30 | [...] mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 0 order by id desc limit 10; Empty set (0.00 sec) did you tried to repair the table and/or rebuild the indexes? -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a strange problem
thank you Sebastian! I have re-created the table.there are not any problems in the table. if the table is new,need to rebuild de indexes? - Original Message - From: Sebastian Mendel [EMAIL PROTECTED] To: liaojian_163 [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, April 23, 2008 2:11 PM Subject: Re: a strange problem liaojian_163 schrieb: hi,all. In my mysql server,I have a strange problem. can someone help me? Thank you. mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 2500 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2543 | 41 | 2008-04-22 21:55:22 | [...] 10 rows in set (0.00 sec) mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2540 | 41 | 2008-04-19 12:29:30 | [...] mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 0 order by id desc limit 10; Empty set (0.00 sec) did you tried to repair the table and/or rebuild the indexes? -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a strange problem
liaojian_163 schrieb: thank you Sebastian! I have re-created the table.there are not any problems in the table. if the table is new,need to rebuild de indexes? no -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
a strange problem
hi,all. In my mysql server,I have a strange problem. can someone help me? Thank you. mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 2500 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2543 | 41 | 2008-04-22 21:55:22 | | 2540 | 41 | 2008-04-19 12:29:30 | | 2537 | 41 | 2008-04-18 17:38:50 | | 2536 | 41 | 2008-04-18 17:37:56 | | 2534 | 41 | 2008-04-18 12:22:24 | | 2533 | 41 | 2008-04-18 01:19:49 | | 2532 | 41 | 2008-04-18 01:18:42 | | 2527 | 41 | 2008-04-16 18:45:34 | | 2526 | 41 | 2008-04-16 18:43:03 | | 2523 | 41 | 2008-04-16 08:47:16 | +--+-+-+ 10 rows in set (0.00 sec) mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2540 | 41 | 2008-04-19 12:29:30 | | 2537 | 41 | 2008-04-18 17:38:50 | | 2536 | 41 | 2008-04-18 17:37:56 | | 2534 | 41 | 2008-04-18 12:22:24 | | 2533 | 41 | 2008-04-18 01:19:49 | | 2532 | 41 | 2008-04-18 01:18:42 | | 2527 | 41 | 2008-04-16 18:45:34 | | 2526 | 41 | 2008-04-16 18:43:03 | | 2523 | 41 | 2008-04-16 08:47:16 | | 2522 | 41 | 2008-04-15 15:34:55 | +--+-+-+ mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 0 order by id desc limit 10; Empty set (0.00 sec) desc phome_ecms_zhichang; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | | classid | smallint(6) | NO | MUL | 0 | | | onclick | int(11) | NO | | 0 | | | newspath| varchar(50) | NO | | | | | keyboard| varchar(255) | NO | | | | | keyid | varchar(255) | NO | | | | | userid | int(11) | NO | | 0 | | | username| varchar(30) | NO | | | | | ztid| varchar(255) | NO | | | | | checked | tinyint(1) | NO | MUL | 0 | | | istop | tinyint(4) | NO | | 0 | | | truetime| int(11) | NO | MUL | 0 | | | ismember| tinyint(1) | NO | | 0 | | | dokey | tinyint(1) | NO | | 0 | | | userfen | int(11) | NO | | 0 | | | isgood | tinyint(1) | NO | | 0 | | | titlefont | varchar(50) | NO | | | | | titleurl| varchar(200) | NO | | | | | filename| varchar(60) | NO | | | | | filenameqz | varchar(28) | NO | | | | | fh | tinyint(1) | NO | | 0 | | | groupid | smallint(6) | NO | | 0 | | | newstempid | smallint(6) | NO | | 0 | | | plnum | int(11) | NO | | 0 | | | firsttitle | tinyint(1) | NO | | 0 | | | isqf| tinyint(1) | NO | | 0 | | | totaldown | int(11) | NO | | 0 | | | title | varchar(200) | NO | | | | | newstime| datetime | NO | MUL | -00-00 00:00:00 | | | titlepic| varchar(200) | NO | | | | | closepl | tinyint(1) | NO | | 0 | | | havehtml| tinyint(1) | NO | | 0 | | | lastdotime | int(11) | NO | | 0 | | | haveaddfen | tinyint(1) | NO | | 0 | | | infopfen| int(11) | NO | | 0 | | | infopfennum | int(11) | NO | | 0 | | | votenum | int
Re: a strange problem
Not knowing your msqyl version, perhaps it's the form of your LIMIT clause. try LIMIT 0,10 instead. Phil 2008/4/22 liaojian_163 [EMAIL PROTECTED]: hi,all. In my mysql server,I have a strange problem. can someone help me? Thank you. mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 2500 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2543 | 41 | 2008-04-22 21:55:22 | | 2540 | 41 | 2008-04-19 12:29:30 | | 2537 | 41 | 2008-04-18 17:38:50 | | 2536 | 41 | 2008-04-18 17:37:56 | | 2534 | 41 | 2008-04-18 12:22:24 | | 2533 | 41 | 2008-04-18 01:19:49 | | 2532 | 41 | 2008-04-18 01:18:42 | | 2527 | 41 | 2008-04-16 18:45:34 | | 2526 | 41 | 2008-04-16 18:43:03 | | 2523 | 41 | 2008-04-16 08:47:16 | +--+-+-+ 10 rows in set (0.00 sec) mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2540 | 41 | 2008-04-19 12:29:30 | | 2537 | 41 | 2008-04-18 17:38:50 | | 2536 | 41 | 2008-04-18 17:37:56 | | 2534 | 41 | 2008-04-18 12:22:24 | | 2533 | 41 | 2008-04-18 01:19:49 | | 2532 | 41 | 2008-04-18 01:18:42 | | 2527 | 41 | 2008-04-16 18:45:34 | | 2526 | 41 | 2008-04-16 18:43:03 | | 2523 | 41 | 2008-04-16 08:47:16 | | 2522 | 41 | 2008-04-15 15:34:55 | +--+-+-+ mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 0 order by id desc limit 10; Empty set (0.00 sec) desc phome_ecms_zhichang; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | | classid | smallint(6) | NO | MUL | 0 | | | onclick | int(11) | NO | | 0 | | | newspath| varchar(50) | NO | | | | | keyboard| varchar(255) | NO | | | | | keyid | varchar(255) | NO | | | | | userid | int(11) | NO | | 0 | | | username| varchar(30) | NO | | | | | ztid| varchar(255) | NO | | | | | checked | tinyint(1) | NO | MUL | 0 | | | istop | tinyint(4) | NO | | 0 | | | truetime| int(11) | NO | MUL | 0 | | | ismember| tinyint(1) | NO | | 0 | | | dokey | tinyint(1) | NO | | 0 | | | userfen | int(11) | NO | | 0 | | | isgood | tinyint(1) | NO | | 0 | | | titlefont | varchar(50) | NO | | | | | titleurl| varchar(200) | NO | | | | | filename| varchar(60) | NO | | | | | filenameqz | varchar(28) | NO | | | | | fh | tinyint(1) | NO | | 0 | | | groupid | smallint(6) | NO | | 0 | | | newstempid | smallint(6) | NO | | 0 | | | plnum | int(11) | NO | | 0 | | | firsttitle | tinyint(1) | NO | | 0 | | | isqf| tinyint(1) | NO | | 0 | | | totaldown | int(11) | NO | | 0 | | | title | varchar(200) | NO | | | | | newstime| datetime | NO | MUL | -00-00 00:00:00 | | | titlepic| varchar(200) | NO | | | | | closepl | tinyint(1) | NO | | 0 | | | havehtml| tinyint(1) | NO | | 0 | | | lastdotime | int(11) | NO | | 0 | | | haveaddfen | tinyint(1) | NO | | 0 | | | infopfen| int(11) | NO | | 0 | | | infopfennum | int(11) | NO | | 0 | | | votenum | int(11) | NO | | 0 | | | ftitle | varchar(200
Re: a strange problem
I don't think that the limit cause the stange problem. thank you anyway. - Original Message - From: Phil [EMAIL PROTECTED] To: liaojian_163 [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, April 23, 2008 3:46 AM Subject: Re: a strange problem Not knowing your msqyl version, perhaps it's the form of your LIMIT clause. try LIMIT 0,10 instead. Phil 2008/4/22 liaojian_163 [EMAIL PROTECTED]: hi,all. In my mysql server,I have a strange problem. can someone help me? Thank you. mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 2500 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2543 | 41 | 2008-04-22 21:55:22 | | 2540 | 41 | 2008-04-19 12:29:30 | | 2537 | 41 | 2008-04-18 17:38:50 | | 2536 | 41 | 2008-04-18 17:37:56 | | 2534 | 41 | 2008-04-18 12:22:24 | | 2533 | 41 | 2008-04-18 01:19:49 | | 2532 | 41 | 2008-04-18 01:18:42 | | 2527 | 41 | 2008-04-16 18:45:34 | | 2526 | 41 | 2008-04-16 18:43:03 | | 2523 | 41 | 2008-04-16 08:47:16 | +--+-+-+ 10 rows in set (0.00 sec) mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 order by id desc limit 10; +--+-+-+ | id | classid | newstime| +--+-+-+ | 2540 | 41 | 2008-04-19 12:29:30 | | 2537 | 41 | 2008-04-18 17:38:50 | | 2536 | 41 | 2008-04-18 17:37:56 | | 2534 | 41 | 2008-04-18 12:22:24 | | 2533 | 41 | 2008-04-18 01:19:49 | | 2532 | 41 | 2008-04-18 01:18:42 | | 2527 | 41 | 2008-04-16 18:45:34 | | 2526 | 41 | 2008-04-16 18:43:03 | | 2523 | 41 | 2008-04-16 08:47:16 | | 2522 | 41 | 2008-04-15 15:34:55 | +--+-+-+ mysql select id,classid,newstime from phome_ecms_zhichang where classid=41 and id 0 order by id desc limit 10; Empty set (0.00 sec) desc phome_ecms_zhichang; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) | NO | PRI | NULL| auto_increment | | classid | smallint(6) | NO | MUL | 0 | | | onclick | int(11) | NO | | 0 | | | newspath| varchar(50) | NO | | | | | keyboard| varchar(255) | NO | | | | | keyid | varchar(255) | NO | | | | | userid | int(11) | NO | | 0 | | | username| varchar(30) | NO | | | | | ztid| varchar(255) | NO | | | | | checked | tinyint(1) | NO | MUL | 0 | | | istop | tinyint(4) | NO | | 0 | | | truetime| int(11) | NO | MUL | 0 | | | ismember| tinyint(1) | NO | | 0 | | | dokey | tinyint(1) | NO | | 0 | | | userfen | int(11) | NO | | 0 | | | isgood | tinyint(1) | NO | | 0 | | | titlefont | varchar(50) | NO | | | | | titleurl| varchar(200) | NO | | | | | filename| varchar(60) | NO | | | | | filenameqz | varchar(28) | NO | | | | | fh | tinyint(1) | NO | | 0 | | | groupid | smallint(6) | NO | | 0 | | | newstempid | smallint(6) | NO | | 0 | | | plnum | int(11) | NO | | 0 | | | firsttitle | tinyint(1) | NO | | 0 | | | isqf| tinyint(1) | NO | | 0 | | | totaldown | int(11) | NO | | 0 | | | title | varchar(200) | NO | | | | | newstime| datetime | NO | MUL | -00-00 00:00:00 | | | titlepic| varchar(200) | NO | | | | | closepl | tinyint(1) | NO | | 0 | | | havehtml| tinyint(1) | NO | | 0 | | | lastdotime | int(11) | NO | | 0 | | | haveaddfen | tinyint(1) | NO | | 0
RE: Performance problem
New queries, tuning the insert (DELAYED) we make on the database. The clients have not to wait to the io response of the thread of the database that inserts the data and the repl data. -Mensaje original- De: Tim McDaniel [mailto:[EMAIL PROTECTED] Enviado el: viernes 18 de abril de 2008 17:34 CC: mysql@lists.mysql.com Asunto: RE: Performance problem On Fri, 18 Apr 2008, Francisco Rodrigo Cortinas Maseda [EMAIL PROTECTED] wrote: im new on the performance tuning of this database (MySQL 5.0.45, rpm-based installation), and i have one performance problem on our new installation: ... We are experiencing problems about the performance of the database, in the way that we are seeing that the radius clients are seeing the radius servers gone away for the acct service. I`ve resolved my problems without hardware manipulation. Me, I'd not like to see much technical detail, but I'm curious now: what sorts of things did you do? Restructuring, different queries, what? -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Antes de imprimir este e-mail piense bien si es necesario hacerlo. Antes de imprimir este e-mail piense bien si es necesario hacerlo. * Este mensaje es privado y CONFIDENCIAL y se dirige exclusivamente a su destinatario. Si usted ha recibido este mensaje por error, no debe revelar, copiar, distribuir o usarlo en ningun sentido. Le rogamos lo comunique al remitente y borre dicho mensaje y cualquier documento adjunto que pudiera contener. El correo electronico via Internet no permite asegurar la confidencialidad de los mensajes que se transmiten ni su integridad o correcta recepcion. JAZZTEL no asume responsabilidad por estas circunstancias. Si el destinatario de este mensaje no consintiera la utilizacion del correo electronico via Internet y la grabacion de los mensajes, rogamos lo ponga en nuestro conocimiento de forma inmediata.Cualquier opinion expresada en este mensaje pertenece unicamente al autor remitente, y no representa necesariamente la opinion de JAZZTEL, a no ser que expresamente se diga y el remitente este autorizado para hacerlo. * This message is private and CONFIDENTIAL and it is intended exclusively for its addressee. If you receive this message in error, you should not disclose, copy, distribute this e-mail or use it in any other way. Please inform the sender and delete the message and attachments from your system.Internet e-mail neither guarantees the confidentiality nor the integrity or proper receipt of the messages sent. JAZZTEL does not assume any liability for those circumstances. If the addressee of this message does not consent to the use of Internet e-mail and message recording, please notify us immediately.Any views or opinions contained in this message are solely those of the author, and do not necessarily represent those of JAZZTEL, unless otherwise specifically stated and the sender is authorised to do so. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance problem
I`ve resolved my problems without hardware manipulation. Thanks to all. -Mensaje original- De: Francisco Rodrigo Cortinas Maseda Enviado el: miércoles 16 de abril de 2008 18:57 Para: mysql@lists.mysql.com Asunto: RV: Performance problem Hi all, im new on the performance tuning of this database (MySQL 5.0.45, rpm-based installation), and i have one performance problem on our new installation: - The radius servers (that are written on perl) we have are writing the auth and acct log to one mysql database. The conn we have is an TCPIP conn. - We have two databases, one for auth data and another for acct data. - We have one table for each day on each database, on which we insert the auth and acct data. We also have three indexes on each table, that occupy almost 300M per day. - The volume of traffic is nearly 10 million rows per day. - The partition of the database is mounted on a LVM partition of a RAID1 disk. We are experiencing problems about the performance of the database, in the way that we are seeing that the radius clients are seeing the radius servers gone away for the acct service. The server of the database is a Dell Poweredge 1855 with 6 GB of RAM and RHEL4. We have modified the variables of the database with: SET GLOBAL thread_cache_size=8; SET GLOBAL table_cache=256; set GLOBAL max_connections=200; set GLOBAL key_buffer_size=1610416128; set GLOBAL read_buffer_size=524288; set GLOBAL read_rnd_buffer_size=1048576; SET GLOBAL delayed_insert_limit=400; SET GLOBAL delayed_queue_size=12000; SET GLOBAL net_buffer_length=32768; The queries that we are doing are: INSERT DELAYED IGNORE () VALUES (); Originally, the server has 2GB of RAM, but seeing this problems, we have installed another 4 GB of RAM. From the statistics of vmstat we see that we are suffering som IO bottleneck (i think): procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 0 4 0 4280956 40144 139245600 014 1853 1180 1 0 48 50 0 3 0 4279932 40152 139348800 010 1882 1258 2 0 42 56 0 3 0 4279908 40172 139450800 0 2052 1861 1202 2 1 45 52 0 4 0 4276452 40192 139552800 0 9179 1850 1164 2 1 66 31 1 3 0 4274748 40200 139630000 0 7 1957 1337 2 1 64 34 0 4 0 4272956 40212 139732800 024 1926 1283 2 1 41 56 0 3 0 4271484 40224 139861600 026 1906 1250 2 1 32 66 0 3 0 4270204 40228 139965200 0 9 1855 1154 2 0 24 74 0 3 0 4268924 40236 140016400 010 1852 1144 2 0 24 74 1 4 0 4267516 40248 140145200 013 2063 1480 2 1 27 71 0 3 0 4264476 40280 140272000 0 11134 1965 1363 2 1 49 48 0 4 0 4262772 40300 140374000 013 1971 1382 2 0 60 37 0 4 0 4261372 40316 140476400 015 1875 1213 2 1 46 52 0 3 0 4260028 40328 140553200 014 1831 1152 2 0 48 50 The wa column shows a quite large number, so we think that it is an IO bottleneck. The question is: ¿ has anybody have seesomething similar? ¿has anybody an idea about how to resolve this problem? Thanks. Antes de imprimir este e-mail piense bien si es necesario hacerlo. * Este mensaje es privado y CONFIDENCIAL y se dirige exclusivamente a su destinatario. Si usted ha recibido este mensaje por error, no debe revelar, copiar, distribuir o usarlo en ningún sentido. Le rogamos lo comunique al remitente y borre dicho mensaje y cualquier documento adjunto que pudiera contener. El correo electrónico via Internet no permite asegurar la confidencialidad de los mensajes que se transmiten ni su integridad o correcta recepción. JAZZTEL no asume responsabilidad por estas circunstancias. Si el destinatario de este mensaje no consintiera la utilización del correo electrónico via Internet y la grabación de los mensajes, rogamos lo ponga en nuestro conocimiento de forma inmediata.Cualquier opinión expresada en este mensaje pertenece únicamente al autor remitente, y no representa necesariamente la opinión de JAZZTEL, a no ser que expresamente se diga y el remitente esté autorizado para hacerlo. * This message is private and CONFIDENTIAL and it is intended exclusively for its addressee. If you receive this message in error, you should not disclose, copy, distribute this e-mail or use it in any other way. Please inform the sender and delete the message and attachments from your system.Internet e-mail neither guarantees the confidentiality nor the integrity or proper receipt of the messages sent. JAZZTEL does not assume any liability for those circumstances. If the addressee of this message does not consent to the use of Internet e-mail and message recording, please notify us
RE: Performance problem
On Fri, 18 Apr 2008, Francisco Rodrigo Cortinas Maseda [EMAIL PROTECTED] wrote: im new on the performance tuning of this database (MySQL 5.0.45, rpm-based installation), and i have one performance problem on our new installation: ... We are experiencing problems about the performance of the database, in the way that we are seeing that the radius clients are seeing the radius servers gone away for the acct service. I`ve resolved my problems without hardware manipulation. Me, I'd not like to see much technical detail, but I'm curious now: what sorts of things did you do? Restructuring, different queries, what? -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query problem
Hi all, Iam having the one table name called AccessDetails and data inside that tables is following, DateTime UserName SiteName ScanType Status Virus_Category | 2008-04-16 | 13:05:31 | 172.16.1.22 | - | www.veer.com |C | A| unclassified | | 2008-04-16 | 13:05:31 | 172.16.1.52 | - | blogactiv.eu |C | O | unclassified | | 2008-04-16 | 13:05:32 | 172.16.1.22 | - | www.veer.com |V | A| Internet | | 2008-04-16 | 13:05:32 | 172.16.1.52 | - | www.verylowsodium.com |C | D| unclassified | | 2008-04-16 | 13:05:32 | 172.16.1.52 | - | blogactiv.eu |V | A | unclassified | In that , I need to calculate the number of total sites , number of total Accessed Sites,number of total Denied Sites and number of total Overriden Sites based on the particular Virus_Category,UserName,Date How can form the query to achieve that?? I have used the following query but the total site is not correctly displayed.. select count(a.UserName),sum(b.totalsites),sum(a.Allow),sum(a.Denied),sum(a.Over),sum(b.totalconn) from (select a.UserName,sum(a.Allow) as Allow,sum(a.Denied) as Denied,sum(a.Over) as Over from (select UserName,case Status when 'A' then count(distinct SiteName) else 0 END as Allow ,case Status when 'D' then count(distinct SiteName) else 0 END as Denied,case Status when 'O' then count(distinct SiteName) else 0 END as Over from AccessDetails where Virus_category = 'unclassified ' and Date='2008-04-16' and Date='2008-04-16' and ScanType='C' group by UserName, Status) a group by a.UserName) a left join (select UserName,count(distinct SiteName)as totalsites, count(Time)as totalconn from AccessDetails where Virus_category = 'unclassified ' and Date='2008-04-16' and Date='2008-04-16' and ScanType='C' and Virus_category '-' and UserName '-' group by UserName)b on a.UserName=b.UserName where b.totalsites is not null Thanks In Advance ,
[SOLVED] RE: Strange performance problem
It's possibly a DNS problem (reverse DNS exactly). You know, I'm feeling a bit stupid here... That was indeed the problem, as the new server hadn't been moved on DNS yet. I put the IP address into the windows hosts file on the DB server, and the problem cleared up immediately. Thanks! -Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
On Wed, Apr 16, 2008 at 4:35 AM, sivasakthi [EMAIL PROTECTED] wrote: Hi all, Iam having the one table name called AccessDetails and data inside that tables is following, [snip=schema] In that , I need to calculate the number of total sites , number of total Accessed Sites,number of total Denied Sites and number of total Overriden Sites based on the particular Virus_Category,UserName,Date How can form the query to achieve that?? I have used the following query but the total site is not correctly displayed.. You may want to look into the ROLLUP modifier. Here's the manual entry: http://dev.mysql.com/doc/refman/5.0/en/group-by-modifiers.html -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RV: Performance problem
Hi all, im new on the performance tuning of this database (MySQL 5.0.45, rpm-based installation), and i have one performance problem on our new installation: - The radius servers (that are written on perl) we have are writing the auth and acct log to one mysql database. The conn we have is an TCPIP conn. - We have two databases, one for auth data and another for acct data. - We have one table for each day on each database, on which we insert the auth and acct data. We also have three indexes on each table, that occupy almost 300M per day. - The volume of traffic is nearly 10 million rows per day. - The partition of the database is mounted on a LVM partition of a RAID1 disk. We are experiencing problems about the performance of the database, in the way that we are seeing that the radius clients are seeing the radius servers gone away for the acct service. The server of the database is a Dell Poweredge 1855 with 6 GB of RAM and RHEL4. We have modified the variables of the database with: SET GLOBAL thread_cache_size=8; SET GLOBAL table_cache=256; set GLOBAL max_connections=200; set GLOBAL key_buffer_size=1610416128; set GLOBAL read_buffer_size=524288; set GLOBAL read_rnd_buffer_size=1048576; SET GLOBAL delayed_insert_limit=400; SET GLOBAL delayed_queue_size=12000; SET GLOBAL net_buffer_length=32768; The queries that we are doing are: INSERT DELAYED IGNORE () VALUES (); Originally, the server has 2GB of RAM, but seeing this problems, we have installed another 4 GB of RAM. From the statistics of vmstat we see that we are suffering som IO bottleneck (i think): procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 0 4 0 4280956 40144 139245600 014 1853 1180 1 0 48 50 0 3 0 4279932 40152 139348800 010 1882 1258 2 0 42 56 0 3 0 4279908 40172 139450800 0 2052 1861 1202 2 1 45 52 0 4 0 4276452 40192 139552800 0 9179 1850 1164 2 1 66 31 1 3 0 4274748 40200 139630000 0 7 1957 1337 2 1 64 34 0 4 0 4272956 40212 139732800 024 1926 1283 2 1 41 56 0 3 0 4271484 40224 139861600 026 1906 1250 2 1 32 66 0 3 0 4270204 40228 139965200 0 9 1855 1154 2 0 24 74 0 3 0 4268924 40236 140016400 010 1852 1144 2 0 24 74 1 4 0 4267516 40248 140145200 013 2063 1480 2 1 27 71 0 3 0 4264476 40280 140272000 0 11134 1965 1363 2 1 49 48 0 4 0 4262772 40300 140374000 013 1971 1382 2 0 60 37 0 4 0 4261372 40316 140476400 015 1875 1213 2 1 46 52 0 3 0 4260028 40328 140553200 014 1831 1152 2 0 48 50 The wa column shows a quite large number, so we think that it is an IO bottleneck. The question is: ¿ has anybody have seesomething similar? ¿has anybody an idea about how to resolve this problem? Thanks. Antes de imprimir este e-mail piense bien si es necesario hacerlo. * Este mensaje es privado y CONFIDENCIAL y se dirige exclusivamente a su destinatario. Si usted ha recibido este mensaje por error, no debe revelar, copiar, distribuir o usarlo en ningún sentido. Le rogamos lo comunique al remitente y borre dicho mensaje y cualquier documento adjunto que pudiera contener. El correo electrónico via Internet no permite asegurar la confidencialidad de los mensajes que se transmiten ni su integridad o correcta recepción. JAZZTEL no asume responsabilidad por estas circunstancias. Si el destinatario de este mensaje no consintiera la utilización del correo electrónico via Internet y la grabación de los mensajes, rogamos lo ponga en nuestro conocimiento de forma inmediata.Cualquier opinión expresada en este mensaje pertenece únicamente al autor remitente, y no representa necesariamente la opinión de JAZZTEL, a no ser que expresamente se diga y el remitente esté autorizado para hacerlo. * This message is private and CONFIDENTIAL and it is intended exclusively for its addressee. If you receive this message in error, you should not disclose, copy, distribute this e-mail or use it in any other way. Please inform the sender and delete the message and attachments from your system.Internet e-mail neither guarantees the confidentiality nor the integrity or proper receipt of the messages sent. JAZZTEL does not assume any liability for those circumstances. If the addressee of this message does not consent to the use of Internet e-mail and message recording, please notify us immediately.Any views or opinions contained in this message are solely those of the author, and do not necessarily represent those of JAZZTEL, unless otherwise specifically stated and the sender is authorised to do so. *
Re: Strange performance problem
It's possibly a DNS problem (reverse DNS exactly). Best regards.
LEFT JOIN problem
I have a table, eo_name_table, that has exactly 860 unique titles in it. Each record also has a date field, eo_pub_date: +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | eo_name | varchar(255) | | PRI | | | | eo_pub_date | date | YES | | NULL| | +-+--+--+-+-+---+ I have another table, prod, that has many fields in it but the fields of interest are prod_num, prod_title, prod_discont, and prod_published. The other fields are irrelevant Here is the structure of the prod table: +-+---+--+-+-+-- -+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+-- -+ | prod_id | varchar(15) | | PRI | | | | prod_num| mediumint(6) unsigned | YES | MUL | NULL| | | prod_title | varchar(255) | YES | MUL | NULL| | | prod_type | varchar(2)| YES | | NULL| | | prod_vat_pct| decimal(5,2) | YES | | NULL| | | prod_discont| tinyint(1)| YES | | NULL| | | prod_ready | tinyint(1)| YES | | NULL| | | pub_id | varchar(15) | YES | MUL | NULL| | | prod_published | date | YES | | NULL| | | prod_pub_prod_id| varchar(255) | YES | MUL | NULL| | | prod_pub_acct_id| varchar(2)| YES | | NULL| | | prod_pub_doi| date | YES | | NULL| | | prod_pub_resp | date | YES | | NULL| | | prod_pub_prod_url | varchar(255) | YES | | NULL| | | prod_rel_freq | smallint(3) | YES | | NULL| | | prod_content_info | varchar(255) | YES | | NULL| | | prod_info_type | varchar(5)| YES | | NULL| | | prod_language | varchar(50) | YES | | NULL| | | prod_broch_doc | varchar(255) | YES | | NULL| | | prod_samp_doc | varchar(255) | YES | | NULL| | | prod_samp_pgs | varchar(255) | YES | | NULL| | | prod_exec_summ | varchar(255) | YES | | NULL| | | prod_toc_doc| varchar(255) | YES | | NULL| | | prod_e_title_tag| varchar(255) | YES | | NULL| | | prod_meta_tags | varchar(255) | YES | | NULL| | | prod_keywords | varchar(255) | YES | | NULL| | | prod_comments | text | YES | | NULL| | | prod_if_sample_pdf | varchar(255) | YES | | NULL| | | prod_stop_date | date | YES | | NULL| | | prod_hide_web | tinyint(1)| YES | | NULL| | | prod_changed| tinyint(1)| YES | | NULL| | | prod_export | tinyint(1)| YES | | NULL| | | prod_export_pending | tinyint(1)| YES | | NULL| | | prod_scoop_changed | tinyint(1)| YES | | NULL| | | prod_on_scoop | tinyint(1)| YES | | NULL| | | prod_added | datetime | YES | | NULL| | | prod_updated| datetime | YES | | NULL| | +-+---+--+-+-+-- -+ I am trying to break eo_name_table into two sets, based upon matching eo_name_table.eo_pub_date against prod.prod_published. The first query is SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS pub_date, IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num, IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, , Discontinued) AS discont, IF(prod.prod_title IS NOT NULL, prod.prod_title, ) AS match_title FROM eo_name_table LEFT JOIN prod ON eo_name_table.eo_name = prod.prod_title AND eo_name_table.eo_pub_date = prod.prod_published WHERE (prod.prod_discont = 0 OR prod.prod_discont IS NULL) ORDER BY eo_name_table.eo_name; As expected, this gives me exactly 860 rows in the result because the left join should give me (at least) one result row for each row in eo_name_table. Some of these rows, of course, have values for every field. Now I want to find the inverse set: SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS pub_date, IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num, IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, , Discontinued) AS discont, IF(prod.prod_title IS NOT NULL, prod.prod_title, ) AS match_title FROM
Problem attempting to use load data into
Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/ elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message| +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 10:29 AM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message | +-+--++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 10:47 AM, Rob Wultsch [EMAIL PROTECTED] wrote: It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; Above should read into an int field, while the server is in strict mode. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN problem
Hi Jerry, I think the problem is that NULL is not less than or greater than your prod_published date. So you probably have eo_pub_date set to NULL in 56 of your rows. so for eo_name_table.eo_pub_date prod.prod_published or eo_name_table.eo_pub_date = prod.prod_published mysql will rerturn false if eo_name_table.eo_pub_date is NULL for either test. Jerry Schwartz wrote: I have a table, eo_name_table, that has exactly 860 unique titles in it. Each record also has a date field, eo_pub_date: +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | eo_name | varchar(255) | | PRI | | | | eo_pub_date | date | YES | | NULL| | +-+--+--+-+-+---+ I have another table, prod, that has many fields in it but the fields of interest are prod_num, prod_title, prod_discont, and prod_published. The other fields are irrelevant Here is the structure of the prod table: +-+---+--+-+-+-- -+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+-- -+ | prod_id | varchar(15) | | PRI | | | | prod_num| mediumint(6) unsigned | YES | MUL | NULL| | | prod_title | varchar(255) | YES | MUL | NULL| | | prod_type | varchar(2)| YES | | NULL| | | prod_vat_pct| decimal(5,2) | YES | | NULL| | | prod_discont| tinyint(1)| YES | | NULL| | | prod_ready | tinyint(1)| YES | | NULL| | | pub_id | varchar(15) | YES | MUL | NULL| | | prod_published | date | YES | | NULL| | | prod_pub_prod_id| varchar(255) | YES | MUL | NULL| | | prod_pub_acct_id| varchar(2)| YES | | NULL| | | prod_pub_doi| date | YES | | NULL| | | prod_pub_resp | date | YES | | NULL| | | prod_pub_prod_url | varchar(255) | YES | | NULL| | | prod_rel_freq | smallint(3) | YES | | NULL| | | prod_content_info | varchar(255) | YES | | NULL| | | prod_info_type | varchar(5)| YES | | NULL| | | prod_language | varchar(50) | YES | | NULL| | | prod_broch_doc | varchar(255) | YES | | NULL| | | prod_samp_doc | varchar(255) | YES | | NULL| | | prod_samp_pgs | varchar(255) | YES | | NULL| | | prod_exec_summ | varchar(255) | YES | | NULL| | | prod_toc_doc| varchar(255) | YES | | NULL| | | prod_e_title_tag| varchar(255) | YES | | NULL| | | prod_meta_tags | varchar(255) | YES | | NULL| | | prod_keywords | varchar(255) | YES | | NULL| | | prod_comments | text | YES | | NULL| | | prod_if_sample_pdf | varchar(255) | YES | | NULL| | | prod_stop_date | date | YES | | NULL| | | prod_hide_web | tinyint(1)| YES | | NULL| | | prod_changed| tinyint(1)| YES | | NULL| | | prod_export | tinyint(1)| YES | | NULL| | | prod_export_pending | tinyint(1)| YES | | NULL| | | prod_scoop_changed | tinyint(1)| YES | | NULL| | | prod_on_scoop | tinyint(1)| YES | | NULL| | | prod_added | datetime | YES | | NULL| | | prod_updated| datetime | YES | | NULL| | +-+---+--+-+-+-- -+ I am trying to break eo_name_table into two sets, based upon matching eo_name_table.eo_pub_date against prod.prod_published. The first query is SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS pub_date, IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num, IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, , Discontinued) AS discont, IF(prod.prod_title IS NOT NULL, prod.prod_title, ) AS match_title FROM eo_name_table LEFT JOIN prod ON eo_name_table.eo_name = prod.prod_title AND eo_name_table.eo_pub_date = prod.prod_published WHERE (prod.prod_discont = 0 OR prod.prod_discont IS NULL) ORDER BY eo_name_table.eo_name; As expected, this gives me exactly 860 rows in the result because the left join should give me (at least) one result row for each row in eo_name_table. Some of these rows, of course, have
RE: LEFT JOIN problem
From: Bill Newton [mailto:[EMAIL PROTECTED] Sent: Monday, April 14, 2008 2:09 PM To: Jerry Schwartz Cc: 'Mysql' Subject: Re: LEFT JOIN problem Hi Jerry, I think the problem is that NULL is not less than or greater than your prod_published date. So you probably have eo_pub_date set to NULL in 56 of your rows. [JS] I wish it were that simple. There are no rows in eo_name_table where eo_pub_date is NULL. so for eo_name_table.eo_pub_date prod.prod_published or eo_name_table.eo_pub_date = prod.prod_published mysql will rerturn false if eo_name_table.eo_pub_date is NULL for either test. [JS] But it evidently does not. The second one works perfectly, the one above does not. Jerry Schwartz wrote: I have a table, eo_name_table, that has exactly 860 unique titles in it. Each record also has a date field, eo_pub_date: +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | eo_name | varchar(255) | | PRI | | | | eo_pub_date | date | YES | | NULL| | +-+--+--+-+-+---+ I have another table, prod, that has many fields in it but the fields of interest are prod_num, prod_title, prod_discont, and prod_published. The other fields are irrelevant Here is the structure of the prod table: +-+---+--+-+- +-- -+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+- +-- -+ | prod_id | varchar(15) | | PRI | | | | prod_num| mediumint(6) unsigned | YES | MUL | NULL| | | prod_title | varchar(255) | YES | MUL | NULL| | | prod_type | varchar(2)| YES | | NULL| | | prod_vat_pct| decimal(5,2) | YES | | NULL| | | prod_discont| tinyint(1)| YES | | NULL| | | prod_ready | tinyint(1)| YES | | NULL| | | pub_id | varchar(15) | YES | MUL | NULL| | | prod_published | date | YES | | NULL| | | prod_pub_prod_id| varchar(255) | YES | MUL | NULL| | | prod_pub_acct_id| varchar(2)| YES | | NULL| | | prod_pub_doi| date | YES | | NULL| | | prod_pub_resp | date | YES | | NULL| | | prod_pub_prod_url | varchar(255) | YES | | NULL| | | prod_rel_freq | smallint(3) | YES | | NULL| | | prod_content_info | varchar(255) | YES | | NULL| | | prod_info_type | varchar(5)| YES | | NULL| | | prod_language | varchar(50) | YES | | NULL| | | prod_broch_doc | varchar(255) | YES | | NULL| | | prod_samp_doc | varchar(255) | YES | | NULL| | | prod_samp_pgs | varchar(255) | YES | | NULL| | | prod_exec_summ | varchar(255) | YES | | NULL| | | prod_toc_doc| varchar(255) | YES | | NULL| | | prod_e_title_tag| varchar(255) | YES | | NULL| | | prod_meta_tags | varchar(255) | YES | | NULL| | | prod_keywords | varchar(255) | YES | | NULL| | | prod_comments | text | YES | | NULL| | | prod_if_sample_pdf | varchar(255) | YES | | NULL| | | prod_stop_date | date | YES | | NULL| | | prod_hide_web | tinyint(1)| YES | | NULL| | | prod_changed| tinyint(1)| YES | | NULL| | | prod_export | tinyint(1)| YES | | NULL| | | prod_export_pending | tinyint(1)| YES | | NULL| | | prod_scoop_changed | tinyint(1)| YES | | NULL| | | prod_on_scoop | tinyint(1)| YES | | NULL| | | prod_added | datetime | YES | | NULL| | | prod_updated| datetime | YES | | NULL| | +-+---+--+-+- +-- -+ I am trying to break eo_name_table into two sets, based upon matching eo_name_table.eo_pub_date against prod.prod_published. The first query is SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS pub_date, IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num, IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, , Discontinued) AS discont, IF(prod.prod_title IS NOT NULL, prod.prod_title, ) AS match_title
RE: LEFT JOIN problem
I've found yet another oddity with this situation. If I leave the date test off of both JOINs they give the same number of rows, but they give me the wrong number! Neither one of them gives me 860 rows returned. I must not understand how a LEFT JOIN works. By the way, the EXPLAIN for both of my original queries is the same: *** 1. row *** id: 1 select_type: SIMPLE table: eo_name_table type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 860 Extra: Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: prod type: ref possible_keys: prod_title,prod_title_fulltext key: prod_title key_len: 766 ref: giiexpr_db.eo_name_table.eo_name rows: 1 Extra: Using where 2 rows in set (0.05 sec) I have a table, eo_name_table, that has exactly 860 unique titles in it. Each record also has a date field, eo_pub_date: +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | eo_name | varchar(255) | | PRI | | | | eo_pub_date | date | YES | | NULL| | +-+--+--+-+-+---+ I have another table, prod, that has many fields in it but the fields of interest are prod_num, prod_title, prod_discont, and prod_published. The other fields are irrelevant Here is the structure of the prod table: +-+---+--+-+-+-- -+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+-- -+ | prod_id | varchar(15) | | PRI | | | | prod_num| mediumint(6) unsigned | YES | MUL | NULL| | | prod_title | varchar(255) | YES | MUL | NULL| | | prod_type | varchar(2)| YES | | NULL| | | prod_vat_pct| decimal(5,2) | YES | | NULL| | | prod_discont| tinyint(1)| YES | | NULL| | | prod_ready | tinyint(1)| YES | | NULL| | | pub_id | varchar(15) | YES | MUL | NULL| | | prod_published | date | YES | | NULL| | | prod_pub_prod_id| varchar(255) | YES | MUL | NULL| | | prod_pub_acct_id| varchar(2)| YES | | NULL| | | prod_pub_doi| date | YES | | NULL| | | prod_pub_resp | date | YES | | NULL| | | prod_pub_prod_url | varchar(255) | YES | | NULL| | | prod_rel_freq | smallint(3) | YES | | NULL| | | prod_content_info | varchar(255) | YES | | NULL| | | prod_info_type | varchar(5)| YES | | NULL| | | prod_language | varchar(50) | YES | | NULL| | | prod_broch_doc | varchar(255) | YES | | NULL| | | prod_samp_doc | varchar(255) | YES | | NULL| | | prod_samp_pgs | varchar(255) | YES | | NULL| | | prod_exec_summ | varchar(255) | YES | | NULL| | | prod_toc_doc| varchar(255) | YES | | NULL| | | prod_e_title_tag| varchar(255) | YES | | NULL| | | prod_meta_tags | varchar(255) | YES | | NULL| | | prod_keywords | varchar(255) | YES | | NULL| | | prod_comments | text | YES | | NULL| | | prod_if_sample_pdf | varchar(255) | YES | | NULL| | | prod_stop_date | date | YES | | NULL| | | prod_hide_web | tinyint(1)| YES | | NULL| | | prod_changed| tinyint(1)| YES | | NULL| | | prod_export | tinyint(1)| YES | | NULL| | | prod_export_pending | tinyint(1)| YES | | NULL| | | prod_scoop_changed | tinyint(1)| YES | | NULL| | | prod_on_scoop | tinyint(1)| YES | | NULL| | | prod_added | datetime | YES | | NULL| | | prod_updated| datetime | YES | | NULL| | +-+---+--+-+-+-- -+ I am trying to break eo_name_table into two sets, based upon matching eo_name_table.eo_pub_date against prod.prod_published. The first query is SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS pub_date, IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num,
Re: Problem attempting to use load data into
On Apr 14, 2008, at 2:01 PM, Rob Wultsch wrote: On Mon, Apr 14, 2008 at 10:47 AM, Rob Wultsch [EMAIL PROTECTED] wrote: It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; Above should read into an int field, while the server is in strict mode. Hi Rob, Where would I set that? I tried to add it to the load data infile line and it didn't like that... Should I try it before I do the indata? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
Hi again everyone, After taking the advice of someone offlist I tried the IGNORE 1 LINES and that didn't help... Same result. I've tried a tab delimited file, and a comma separated file. Same result with both. Any other ideas? :) On Apr 14, 2008, at 1:29 PM, Jason Pruim wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/ raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message| +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 1:29 PM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; [snip!] The error that I'm getting is: | Level | Code | Message | +-+--++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 3:33 PM, Jason Pruim [EMAIL PROTECTED] wrote: On Apr 14, 2008, at 3:29 PM, Daniel Brown wrote: That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. Replaced field name with 0 and had the same end result... Just no error. But I get the first row included! Which is just field names and a 0 for good measure :) Any other ideas Master Brown? :) ***Before I get yelled at for not showing respect please note that I know Dan from another list and I am allowed to give him crap like this no matter what he says :P I don't know you from Adam, you insignificant little cur! ;-P Does your file actually have the characters \t \t \n at the end of each row like that? Send it to me as an attachment off-list and I'll help you figure it out and then post back here for the MySQL archives. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LEFT JOIN problem
As usual, the computer is right and I am wrong. The only reason that one query was coming out right is that it just happened the WHERE clause was never failing. It was just luck that my data was just so. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: Monday, April 14, 2008 11:26 AM To: 'Mysql' Subject: LEFT JOIN problem I have a table, eo_name_table, that has exactly 860 unique titles in it. Each record also has a date field, eo_pub_date: +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | eo_name | varchar(255) | | PRI | | | | eo_pub_date | date | YES | | NULL| | +-+--+--+-+-+---+ I have another table, prod, that has many fields in it but the fields of interest are prod_num, prod_title, prod_discont, and prod_published. The other fields are irrelevant Here is the structure of the prod table: +-+---+--+-+-+-- -+ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-+-- -+ | prod_id | varchar(15) | | PRI | | | | prod_num| mediumint(6) unsigned | YES | MUL | NULL| | | prod_title | varchar(255) | YES | MUL | NULL| | | prod_type | varchar(2)| YES | | NULL| | | prod_vat_pct| decimal(5,2) | YES | | NULL| | | prod_discont| tinyint(1)| YES | | NULL| | | prod_ready | tinyint(1)| YES | | NULL| | | pub_id | varchar(15) | YES | MUL | NULL| | | prod_published | date | YES | | NULL| | | prod_pub_prod_id| varchar(255) | YES | MUL | NULL| | | prod_pub_acct_id| varchar(2)| YES | | NULL| | | prod_pub_doi| date | YES | | NULL| | | prod_pub_resp | date | YES | | NULL| | | prod_pub_prod_url | varchar(255) | YES | | NULL| | | prod_rel_freq | smallint(3) | YES | | NULL| | | prod_content_info | varchar(255) | YES | | NULL| | | prod_info_type | varchar(5)| YES | | NULL| | | prod_language | varchar(50) | YES | | NULL| | | prod_broch_doc | varchar(255) | YES | | NULL| | | prod_samp_doc | varchar(255) | YES | | NULL| | | prod_samp_pgs | varchar(255) | YES | | NULL| | | prod_exec_summ | varchar(255) | YES | | NULL| | | prod_toc_doc| varchar(255) | YES | | NULL| | | prod_e_title_tag| varchar(255) | YES | | NULL| | | prod_meta_tags | varchar(255) | YES | | NULL| | | prod_keywords | varchar(255) | YES | | NULL| | | prod_comments | text | YES | | NULL| | | prod_if_sample_pdf | varchar(255) | YES | | NULL| | | prod_stop_date | date | YES | | NULL| | | prod_hide_web | tinyint(1)| YES | | NULL| | | prod_changed| tinyint(1)| YES | | NULL| | | prod_export | tinyint(1)| YES | | NULL| | | prod_export_pending | tinyint(1)| YES | | NULL| | | prod_scoop_changed | tinyint(1)| YES | | NULL| | | prod_on_scoop | tinyint(1)| YES | | NULL| | | prod_added | datetime | YES | | NULL| | | prod_updated| datetime | YES | | NULL| | +-+---+--+-+-+-- -+ I am trying to break eo_name_table into two sets, based upon matching eo_name_table.eo_pub_date against prod.prod_published. The first query is SELECT IF(prod.prod_published IS NOT NULL, prod.prod_published, ) AS pub_date, IF(prod.prod_num IS NOT NULL, prod.prod_num, ) AS prod_num, IF(prod.prod_discont = 0 OR prod.prod_discont IS NULL, , Discontinued) AS discont, IF(prod.prod_title IS NOT NULL, prod.prod_title, ) AS match_title FROM eo_name_table LEFT JOIN prod ON eo_name_table.eo_name = prod.prod_title AND eo_name_table.eo_pub_date = prod.prod_published WHERE (prod.prod_discont = 0 OR prod.prod_discont IS NULL) ORDER BY eo_name_table.eo_name; As expected
Re: Problem attempting to use load data into
On Apr 14, 2008, at 3:29 PM, Daniel Brown wrote: On Mon, Apr 14, 2008 at 1:29 PM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; [snip!] The error that I'm getting is: | Level | Code | Message | +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. Replaced field name with 0 and had the same end result... Just no error. But I get the first row included! Which is just field names and a 0 for good measure :) Any other ideas Master Brown? :) ***Before I get yelled at for not showing respect please note that I know Dan from another list and I am allowed to give him crap like this no matter what he says :P -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Mon, Apr 14, 2008 at 3:45 PM, Daniel Brown [EMAIL PROTECTED] wrote: Does your file actually have the characters \t \t \n at the end of each row like that? Send it to me as an attachment off-list and I'll help you figure it out and then post back here for the MySQL archives. Sorry, got sidetracked with the day job and the pre-wife nagging me. ;-P Anyway, as I suspected, you did have literal \t and \n characters. I wrote a script to fix it, and I'll link you to the updated CSV file. Run that with the IGNORE 1 ROWS command and you should be set. -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Apr 14, 2008, at 4:37 PM, Daniel Brown wrote: On Mon, Apr 14, 2008 at 3:45 PM, Daniel Brown [EMAIL PROTECTED] wrote: Does your file actually have the characters \t \t \n at the end of each row like that? Send it to me as an attachment off-list and I'll help you figure it out and then post back here for the MySQL archives. Sorry, got sidetracked with the day job and the pre-wife nagging me. ;-P Anyway, as I suspected, you did have literal \t and \n characters. I wrote a script to fix it, and I'll link you to the updated CSV file. Run that with the IGNORE 1 ROWS command and you should be set. Just to complete the archives, This did fix it. Make sure you don't try and put literal tab values \t and new line values \n into your data and it should work just fine! So thank you Dan for your help! And everyone else as well! -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange performance problem
OK folks, I'm kind of stumped; looking into things a bit more, but thought I'd hit the list and see if anyone had any suggestions for a rock to look under, in case I'm missing it... DB Server: Windows 2003, 8-way CPU, lots of RAM, MySQL 4.1.22-nt binary from MySQL Current Production web server: Linux, RedHat 7.2, MySQL 5.0.51a client from MySQL Binary RPMs New Web Server: Linux, RedHat EL 4, MySQL 5.0.51a client from MySQL Binary RPMs The current production web server has a fairly high traffic load, but DB usage is extremely efficient, so there's not a significant load on the DB server. Connection times from the production web server are almost instantaneous (under 1 second), using the press enter and observe method, using the command-line interface. Connection times from the new web server box take between 4-6 seconds between the time you press enter and the time that you get the MySQL prompt. YSlow in Firefox confirms a consistent average of 4-6 seconds difference between the servers reflected in page load times. At this point, we're trying to see what it is going on between the client and the server; datacenter folks have assured us that it's not a networking issue (although I'm not sure that I'm convinced). We've ruled out any apache or PHP issues causing a problem, as the configurations and build options are identical. Any thoughts of a performance number or configuration option that would make any differences? Thanks much in advance - I'm scratching my head on this one... -Doug -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQl 5.0 optimization problem
Hi, I am trying to optimize our DB server. We have one table which has 1.3M entries, and the keys are GUIDs (so the key space is large). However, I have it all indexed. The performance was iffy, though, so I increased memory allocation, and the searches on the indexed fields seem to be OK now. Still, a simple count(field) on this table still takes, like, 2 minutes! I am guessing i am missing something obvious, but I read through a few MySQL optimization guides, and it seems like i am covering my bases. Here is the relevant slice of my my.cnf: # # * Fine Tuning # key_buffer = 256M max_allowed_packet = 64M thread_stack= 512K thread_cache_size = 32 # # * Query Cache Configuration # query_cache_limit = 32M query_cache_size= 256M query_cache_type= 1 table_cache = 512 sort_buffer_size= 32M I am running Ubuntu 7.04, with 1GB of RAM. The relevant fields are all indexed, but I can't imagine why a simple count() would take so long, when the actual query by value on the same field is effectively instant (after my cache setting expansion). Does anyone have an idea of what I am missing? Also, if you think any of the above settings seem wrong for a server with 1GB of RAM, please let me know. -- Victor Danilchenko Senior Software Engineer, AskOnline.net [EMAIL PROTECTED] - 617-273-0119 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl 5.0 optimization problem
Are you using MyIsam or InnoDB? Or something else? In either case the speed to get a COUNT() is largely down to the speed if your disks and size of disk caching. A COUNT() forces the system to read every row in order to count them, and any large table is probably larger than your caches. In some ways this is not so important, since it is unusual for a query to want to read every row of a table. (I have 250GB tables which have excellent performance but would take minutes to count every row :) It might be better to consider the type of queries you will be using, and the type of table, and optimise for that... Ben Victor Danilchenko wrote: Hi, I am trying to optimize our DB server. We have one table which has 1.3M entries, and the keys are GUIDs (so the key space is large). However, I have it all indexed. The performance was iffy, though, so I increased memory allocation, and the searches on the indexed fields seem to be OK now. Still, a simple count(field) on this table still takes, like, 2 minutes! I am guessing i am missing something obvious, but I read through a few MySQL optimization guides, and it seems like i am covering my bases. Here is the relevant slice of my my.cnf: # # * Fine Tuning # key_buffer = 256M max_allowed_packet = 64M thread_stack= 512K thread_cache_size = 32 # # * Query Cache Configuration # query_cache_limit = 32M query_cache_size= 256M query_cache_type= 1 table_cache = 512 sort_buffer_size= 32M I am running Ubuntu 7.04, with 1GB of RAM. The relevant fields are all indexed, but I can't imagine why a simple count() would take so long, when the actual query by value on the same field is effectively instant (after my cache setting expansion). Does anyone have an idea of what I am missing? Also, if you think any of the above settings seem wrong for a server with 1GB of RAM, please let me know. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl 5.0 optimization problem
On Fri, April 11, 2008 06:47, Ben Clewett wrote: Are you using MyIsam or InnoDB? Or something else? In either case the speed to get a COUNT() is largely down to the speed if your disks and size of disk caching. A COUNT() forces the system to read every row in order to count them, and any large table is probably larger than your caches. In some ways this is not so important, since it is unusual for a query to want to read every row of a table. (I have 250GB tables which have excellent performance but would take minutes to count every row :) It might be better to consider the type of queries you will be using, and the type of table, and optimise for that... Ben Victor Danilchenko wrote: Hi, I am trying to optimize our DB server. We have one table which has 1.3M entries, and the keys are GUIDs (so the key space is large). However, I have it all indexed. The performance was iffy, though, so I increased memory allocation, and the searches on the indexed fields seem to be OK now. Still, a simple count(field) on this table still takes, like, 2 minutes! I am guessing i am missing something obvious, but I read through a few MySQL optimization guides, and it seems like i am covering my bases. Here is the relevant slice of my my.cnf: # # * Fine Tuning # key_buffer = 256M max_allowed_packet = 64M thread_stack= 512K thread_cache_size = 32 # # * Query Cache Configuration # query_cache_limit = 32M query_cache_size= 256M query_cache_type= 1 table_cache = 512 sort_buffer_size= 32M I am running Ubuntu 7.04, with 1GB of RAM. The relevant fields are all indexed, but I can't imagine why a simple count() would take so long, when the actual query by value on the same field is effectively instant (after my cache setting expansion). Does anyone have an idea of what I am missing? Also, if you think any of the above settings seem wrong for a server with 1GB of RAM, please let me know. If the field you are counting is the first field in an index I would think it would go much faster (system will just use the index). If some of your counts are fast and some are slow (you said iffy) that might explain the difference. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl 5.0 optimization problem
Hi, Can you please post your query? I also need to know your table type as different settings effect different table types? You are right that a SELECT COUNT(*) WHERE field = 'value' should hit the index, but does depend on your query. You might also try EXPLAIN before your query, which will show the approximate number of rows you are hitting. Look at SHOW TABLE STATUS which will give an idea of the size of the rows and indexs. Marry one to the other and it will give an idea of the cache settings to get the query into cache. But still depends a lot on the table type! Ben Wm Mussatto wrote: On Fri, April 11, 2008 06:47, Ben Clewett wrote: Are you using MyIsam or InnoDB? Or something else? In either case the speed to get a COUNT() is largely down to the speed if your disks and size of disk caching. A COUNT() forces the system to read every row in order to count them, and any large table is probably larger than your caches. In some ways this is not so important, since it is unusual for a query to want to read every row of a table. (I have 250GB tables which have excellent performance but would take minutes to count every row :) It might be better to consider the type of queries you will be using, and the type of table, and optimise for that... Ben Victor Danilchenko wrote: Hi, I am trying to optimize our DB server. We have one table which has 1.3M entries, and the keys are GUIDs (so the key space is large). However, I have it all indexed. The performance was iffy, though, so I increased memory allocation, and the searches on the indexed fields seem to be OK now. Still, a simple count(field) on this table still takes, like, 2 minutes! I am guessing i am missing something obvious, but I read through a few MySQL optimization guides, and it seems like i am covering my bases. Here is the relevant slice of my my.cnf: # # * Fine Tuning # key_buffer = 256M max_allowed_packet = 64M thread_stack= 512K thread_cache_size = 32 # # * Query Cache Configuration # query_cache_limit = 32M query_cache_size= 256M query_cache_type= 1 table_cache = 512 sort_buffer_size= 32M I am running Ubuntu 7.04, with 1GB of RAM. The relevant fields are all indexed, but I can't imagine why a simple count() would take so long, when the actual query by value on the same field is effectively instant (after my cache setting expansion). Does anyone have an idea of what I am missing? Also, if you think any of the above settings seem wrong for a server with 1GB of RAM, please let me know. If the field you are counting is the first field in an index I would think it would go much faster (system will just use the index). If some of your counts are fast and some are slow (you said iffy) that might explain the difference. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl 5.0 optimization problem
On Fri, Apr 11, 2008 at 6:47 AM, Ben Clewett [EMAIL PROTECTED] wrote: A COUNT() forces the system to read every row in order to count them... That is not strictly the case. A count(field) can use an index scan rather than a sequential scan, which may or may not be faster. Also some count(field) can queries be optimized away if a field is NOT NULL and the type is myisam. Posting a Show status; might be useful. DDL is always helpful... -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl 5.0 optimization problem
First of all, my bad -- I forgot to mention that I use MyISAM. mysql show table status from example like 'leads'\G *** 1. row *** Name: leads Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 1267995 Avg_row_length: 224 Data_length: 284349972 Max_data_length: 281474976710655 Index_length: 201081856 Data_free: 0 Auto_increment: NULL Create_time: 2008-04-11 14:03:14 Update_time: 2008-04-11 14:04:26 Check_time: 2008-04-11 14:07:51 Collation: utf8_general_ci Checksum: NULL Create_options: Comment: Ben Clewett wrote: Hi, Can you please post your query? I also need to know your table type as different settings effect different table types? The query is simply: select count(email1) from leads; The table structure is as follows: mysql describe leads; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | id | varchar(36) | NO | PRI | | deleted| tinyint(1) | NO | | 0 | converted | tinyint(1) | NO | | 0 | date_entered | datetime | NO | | -00-00 00:00:00 | | | date_modified | datetime | NO | | -00-00 00:00:00 | | | modified_user_id | varchar(36) | YES | | NULL | assigned_user_id | varchar(36) | YES | MUL | NULL | created_by | varchar(36) | YES | | NULL| salutation | varchar(5) | YES | | NULL| first_name | varchar(25) | YES | | NULL| last_name | varchar(25) | YES | MUL | NULL| title | varchar(100) | YES | | NULL| refered_by | varchar(100) | YES | | NULL | lead_source| varchar(100) | YES | | NULL | lead_source_description| mediumtext | YES | | NULL| status | varchar(100) | YES | | NULL| status_description | mediumtext | YES | | NULL| department | varchar(100) | YES | | NULL| reports_to_id | varchar(36) | YES | | NULL| do_not_call | char(3) | YES | | 0 | phone_home | varchar(25) | YES | | NULL | phone_mobile | varchar(25) | YES | | NULL | phone_work | varchar(25) | YES | | NULL| phone_other| varchar(25) | YES | | NULL| phone_fax | varchar(25) | YES | | NULL| email1 | varchar(100) | YES | MUL | NULL| email2 | varchar(100) | YES | MUL | NULL| email_opt_out | char(3) | YES | | 0 | primary_address_street | varchar(150) | YES | | NULL | primary_address_city | varchar(100) | YES | | NULL | primary_address_state | varchar(100) | YES | | NULL | primary_address_postalcode | varchar(20) | YES | | NULL| primary_address_country| varchar(100) | YES | | NULL| alt_address_street | varchar(150) | YES | | NULL| alt_address_city | varchar(100) | YES | | NULL| alt_address_state | varchar(100) | YES | | NULL | alt_address_postalcode | varchar(20) | YES | | NULL | alt_address_country| varchar(100) | YES | | NULL| description| mediumtext | YES | | NULL| account_name | varchar(150) | YES | | NULL| account_description| mediumtext | YES | | NULL| contact_id | varchar(36) | YES | MUL | NULL| account_id | varchar(36) | YES | MUL | NULL| opportunity_id | varchar(36) | YES | MUL | NULL | opportunity_name | varchar(255) | YES | | NULL | opportunity_amount | varchar(50) | YES | | NULL| campaign_id| varchar(36) | YES | | NULL| portal_name| varchar(255) | YES | | NULL| portal_app | varchar(255) | YES | | NULL|
Re: Importing and exporting from MySQL, escape slash problem
Dave M G schrieb: PHP List, MySQL List In my PHP environment, I have Magic Quotes turned off, and I use the mysql_real_escape_string() function clean strings of SQL syntax before inserting them into my database. So the data stored in my database does not have escape characters in it. Particularly, double and single quotes don't have slashes in front of them. This seems to work fine so long as I'm reading data into and out of the database from within my scripts. However, when I backup and import databases - I use the phpMyAdmin interface - they have escape slashes in front of every double and single quote characters. I'm not sure if it's on the export or import where they get added in. what version of phpMyAdmin? I've looked through the phpMyAdmin online documentation, and I can't see any option to control the presence of escape slashes. It seems to me that if it adds them in when exporting, it should take them out when importing. Or vice versa, but in either case be consistent. I just want my database to be exactly as it is before any export or import options. I'm a little muddled as to where I'm making the mistake. Can anyone advice on the best practice for preserving my database as is when backing up and restoring? this 'bug' is unknown to me, did you tried to reproduce on phpMyAdmin demo servers? http://pma.cihar.com/ http://wiki.cihar.com/pma/Getting_Help -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP] Importing and exporting from MySQL, escape slash problem
Richard, Jed, Thank you for replying. Richard said: It's possible that there is an .htaccess file in phpMyAdmin that has Magic Quotes on that is messing you up... The .htaccess file for phpMyAdmin says php_flag magic_quotes_gpc Off, so I guess that means I'm okay there. Other than that, it's specific to phpMyAdmin, so maybe ask those guys what they did... I joined their list through Sourceforge, but I haven't seen any mail from it, and any mail I send gets bounced back to me. I'm not sure what the issue is. Jed said: If you're having trouble importing a mysql dump using phpMyAdmin, it might be simpler not to use it, and use mysqldump instead. I suppose I'll have to if I can't get phpMyAdmin to behave. It's too bad, though, as phpMyAdmin is so convenient otherwise. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP] Importing and exporting from MySQL, escape slash problem
It's possible that there is an .htaccess file in phpMyAdmin that has Magic Quotes on that is messing you up... Other than that, it's specific to phpMyAdmin, so maybe ask those guys what they did... On Sat, March 1, 2008 7:38 pm, Dave M G wrote: PHP List, MySQL List In my PHP environment, I have Magic Quotes turned off, and I use the mysql_real_escape_string() function clean strings of SQL syntax before inserting them into my database. So the data stored in my database does not have escape characters in it. Particularly, double and single quotes don't have slashes in front of them. This seems to work fine so long as I'm reading data into and out of the database from within my scripts. However, when I backup and import databases - I use the phpMyAdmin interface - they have escape slashes in front of every double and single quote characters. I'm not sure if it's on the export or import where they get added in. I've looked through the phpMyAdmin online documentation, and I can't see any option to control the presence of escape slashes. It seems to me that if it adds them in when exporting, it should take them out when importing. Or vice versa, but in either case be consistent. I just want my database to be exactly as it is before any export or import options. I'm a little muddled as to where I'm making the mistake. Can anyone advice on the best practice for preserving my database as is when backing up and restoring? Thanks for any advice. -- Dave M G -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- Some people have a gift link here. Know what I want? I want you to buy a CD from some indie artist. http://cdbaby.com/from/lynch Yeah, I get a buck. So? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing and exporting from MySQL, escape slash problem
Dave M G wrote: PHP List, MySQL List In my PHP environment, I have Magic Quotes turned off, and I use the mysql_real_escape_string() function clean strings of SQL syntax before inserting them into my database. So the data stored in my database does not have escape characters in it. Particularly, double and single quotes don't have slashes in front of them. This seems to work fine so long as I'm reading data into and out of the database from within my scripts. However, when I backup and import databases - I use the phpMyAdmin interface - they have escape slashes in front of every double and single quote characters. I'm not sure if it's on the export or import where they get added in. It probably depends on what you mean by exporting...if you do a select * into outfile it might remove them. If you're doing a mysql dump, it will add them because it's constructing sql statements, and they have to be escaped. I would take phpMyAdmin out of the equation and just use mysqldump. You should have no problem doing something like creating a scratch table, dumping it, and re-importing from the dump. mysql create table B like table A; mysql insert into table B select * from A where c like %'% limit 10; bash$ mysqldump --opt frumble B b.sql bash$ mysql -u -p frumble b.sql This should replace table B in database frumble without incident. If you're having trouble importing a mysql dump using phpMyAdmin, it might be simpler not to use it, and use mysqldump instead. HTH Jed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing and exporting from MySQL, escape slash problem
PHP List, MySQL List In my PHP environment, I have Magic Quotes turned off, and I use the mysql_real_escape_string() function clean strings of SQL syntax before inserting them into my database. So the data stored in my database does not have escape characters in it. Particularly, double and single quotes don't have slashes in front of them. This seems to work fine so long as I'm reading data into and out of the database from within my scripts. However, when I backup and import databases - I use the phpMyAdmin interface - they have escape slashes in front of every double and single quote characters. I'm not sure if it's on the export or import where they get added in. I've looked through the phpMyAdmin online documentation, and I can't see any option to control the presence of escape slashes. It seems to me that if it adds them in when exporting, it should take them out when importing. Or vice versa, but in either case be consistent. I just want my database to be exactly as it is before any export or import options. I'm a little muddled as to where I'm making the mistake. Can anyone advice on the best practice for preserving my database as is when backing up and restoring? Thanks for any advice. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LIKE problem with characters 'å' (norwe gian) and 'a' (mysql bug?)
I get incorrect result when searching for the norwegian character 'å' using LIKE. I get rows with 'a' in it, and visa versa if I search for 'a', I get results which has 'å' in it in addition to the ones with 'a'. Make sure that your table has: charset=utf8 collation=utf8_norwegian_ci And that every column ALSO has: charset=utf8 collation=utf8_norwegian_ci Notice that I am making 'utf8_norwegian_ci' up. I looked for it using my MySQL Query Browser but couldn't find it. As I'm from Sweden I've had similar problems (åäöÅÄÖ matched åaäÅÄAÖO) and setting as above but using (the existing) 'utf8_swedish_ci' worked in my case. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LIKE problem with characters 'å' (norwe gian) and 'a' (mysql bug?)
Dear List,
I get incorrect result when searching for the norwegian character 'å'
using LIKE. I get rows with 'a' in it, and visa versa if I search for
'a', I get results which has 'å' in it in addition to the ones with 'a'.
Example:
CREATE TABLE names (
name VARCHAR(255)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO names VALUES
('Foo'), ('Bar'), ('Båt'), ('Bør'), ('Ære');
Now, searching gives me the following results:
mysql SELECT * FROM names WHERE name LIKE '%å%';
+--+
| name |
+--+
| Bar |
| Båt |
+--+
mysql SELECT * FROM names WHERE name LIKE '%a%';
+--+
| name |
+--+
| Bar |
| Båt |
+--+
Searching for strings with other norwegian characters seams to work:
mysql SELECT * FROM names WHERE name LIKE '%ø%';
+--+
| name |
+--+
| Bør |
+--+
I found that I may use
mysql SELECT * FROM names WHERE LOWER(name) LIKE BINARY LOWER('%å%');
which returns correct results, but this disables me from letting the
user do case sensitive searches.
Am I doing something wrong or stupid? Could this be a MySQL bug?
How do I know this isn't a problem with other utf-8 characters in other
languages?
I've searched in bug reports, but cannot find this exact problem.
Some additional information that might be useful:
mysql SELECT VERSION();
+--+
| VERSION()|
+--+
| 5.0.45-Debian_1ubuntu3.1-log |
+--+
mysql SHOW VARIABLES LIKE '%character%';
+--++
| Variable_name| Value |
+--++
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results| utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--++
Thanks,
Magne Westlie
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
jdbc connection pool problem, help, thanks!
Hi experts, I am looking for a library for managing connection pool as I can't use Tomcat, I found a couple of third party connection pool but with high coupling - using those require me to change quite a lot existing code, I am looking for something which can return a standard connection to me, any recommendations are much appericated! PS: I posted this question on Java section but no replys :( Ray -- View this message in context: http://www.nabble.com/jdbc-connection-pool-problem%2C-help%2C-thanks%21-tp15673813p15673813.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizer problem?
I have the following table:
--
-- Table structure for table 'media'
--
CREATE TABLE media (
id int(10) unsigned NOT NULL auto_increment,
user_id int(10) unsigned default NULL,
title varchar(255) NOT NULL,
description text NOT NULL,
`hash` varchar(255) NOT NULL,
length float(9,2) NOT NULL,
created timestamp NOT NULL default CURRENT_TIMESTAMP,
`type` enum('video','image') default NULL,
`status`
enum('new','processing','suspended','active','deleted','failed','pending')
NOT NULL default 'new',
flags int(20) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY `hash` (`hash`),
KEY `type` (`type`),
KEY user_id (user_id),
KEY created (created),
KEY `status` (`status`),
KEY flags (flags)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
the table has about 200,000 rows.
the following query takes about .56 seconds on a completely empty system:
SELECT *
FROM
media
WHERE
media.status = 'active' AND
user_id = '190' AND
id != '13660' AND
media.flags 3 = 0
and media.type = 'video'
ORDER BY
media.id DESC LIMIT 0, 6
When I do explain, I can see it shows PRIMARY as a viable index to use, but
instead its using and index merge with user_id,status,type.
when I add use index (PRIMARY), the query drops to 0.02.
Any ideas why the optimizer isn't using the primary? since i'm ordering by
that, it seems it would make sense to use that.
Re: Optimizer problem?
Hi,
On Wed, Feb 20, 2008 at 7:23 PM, Tanner Postert
[EMAIL PROTECTED] wrote:
I have the following table:
--
-- Table structure for table 'media'
--
CREATE TABLE media (
id int(10) unsigned NOT NULL auto_increment,
user_id int(10) unsigned default NULL,
title varchar(255) NOT NULL,
description text NOT NULL,
`hash` varchar(255) NOT NULL,
length float(9,2) NOT NULL,
created timestamp NOT NULL default CURRENT_TIMESTAMP,
`type` enum('video','image') default NULL,
`status`
enum('new','processing','suspended','active','deleted','failed','pending')
NOT NULL default 'new',
flags int(20) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY `hash` (`hash`),
KEY `type` (`type`),
KEY user_id (user_id),
KEY created (created),
KEY `status` (`status`),
KEY flags (flags)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
the table has about 200,000 rows.
the following query takes about .56 seconds on a completely empty system:
SELECT *
FROM
media
WHERE
media.status = 'active' AND
user_id = '190' AND
id != '13660' AND
media.flags 3 = 0
and media.type = 'video'
ORDER BY
media.id DESC LIMIT 0, 6
When I do explain, I can see it shows PRIMARY as a viable index to use, but
instead its using and index merge with user_id,status,type.
when I add use index (PRIMARY), the query drops to 0.02.
Any ideas why the optimizer isn't using the primary? since i'm ordering by
that, it seems it would make sense to use that.
As far as I know, the optimizer's cost metric doesn't account for the
extra work caused by merging the index scans, so it probably thinks
it'll be cheaper to do so. I think this is one of the cases where the
human is smarter than the optimizer.
I always try to avoid manually adding hints, but sometimes you have
to. It may be a good idea to check when you upgrade MySQL and
determine if it has gotten smart enough to execute the query faster
than your forced execution plan. Otherwise you might be locking it
into a worse plan than it might be able to use in future versions.
Baron
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedure problem
Hello everyone! Hopefully somone can enlight me, i am hanging on this vor a few hours now :/ i have this stored procedure: CREATE PROCEDURE additems () BEGIN DECLARE grammatures VARCHAR(128) DEFAULT '7,12,1,13,2,5,14,21'; DECLARE newids VARCHAR(128); SELECT GROUP_CONCAT(a_id,newids) INTO newids FROM articles WHERE article_garmmature IN (grammatures); SELECT @newids; END; What i wan't is to store the id's as a comma seperated list into the newids VAR. I get the error that there is more than one result set. I also tried group_concat, but that didn't worked :( Anyone has an idea how to store multiple ids into one VAR ? Thanks for reading! Best wishes Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored Procedure problem
Dynamic SQL would work for Imbedding String for the IN clause.
That would be too messy for such a little query.
Here is a crazy suggestion
CREATE PROCEDURE additems ()
BEGIN
DECLARE grammatures VARCHAR(128) DEFAULT '7,12,1,13,2,5,14,21';
DECLARE newids VARCHAR(128);
SELECT GROUP_CONCAT(a_id,newids) INTO newids FROM articles WHERE
INSTR(CONCAT(',',grammatures,','),CONCAT(',',article_grammatures,',')) 0
SELECT @newids;
END;
By the way, is it ' article_grammature ' or ' article_garmmature ' ?
Give it a try !!!
-Original Message-
From: Barry [mailto:[EMAIL PROTECTED]
Sent: Friday, February 15, 2008 5:28 AM
To: mysql@lists.mysql.com
Subject: Stored Procedure problem
Hello everyone!
Hopefully somone can enlight me, i am hanging on this vor a few hours now :/
i have this stored procedure:
CREATE PROCEDURE additems ()
BEGIN
DECLARE grammatures VARCHAR(128) DEFAULT '7,12,1,13,2,5,14,21';
DECLARE newids VARCHAR(128);
SELECT GROUP_CONCAT(a_id,newids) INTO newids
FROM articles
WHERE article_garmmature IN (grammatures);
SELECT @newids;
END;
What i wan't is to store the id's as a comma seperated list into the
newids VAR.
I get the error that there is more than one result set.
I also tried group_concat, but that didn't worked :(
Anyone has an idea how to store multiple ids into one VAR ?
Thanks for reading!
Best wishes
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
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]
Date sorting problem with Date_Format?
Hello mysql, I have a PHP script that is running the following query: SELECT `Messages`.`ID`, `Messages`.`Sender`, `Messages`.`Subject`, DATE_FORMAT( `Messages`.`Date`, '%D %M %Y' ) AS Date, LEFT(`Messages`.`Text`, 200 ) AS Preview, `Messages`.`New`, `Profiles`.`NickName`, `Profiles`.`RealName`, `Profiles`.`Status`, `Profiles`.`ID` as pID FROM `Messages` LEFT JOIN Profiles ON Messages.Sender = Profiles.ID WHERE Recipient = 5 ORDER BY `Date` DESC; The problem is that the ORDER BY `Date` DESC is generating random results? If I run this same script interactively in an sql window it works correctly. If I remove DATE_FORMAT function and just call `Date`, the script sorts and displays correctly. Is this a bug? -- Best regards, mikesz mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date sorting problem with Date_Format?
Hello mysql list, As is very often the case, five minutes after I posted this, I found the problem or solution, not sure it was the problem as I am not convinced that mysql ought to get confused so easily. I changed DATE_FORMAT( `Messages`.`Date`, '%D %M %Y' ) AS Date, DATE_FORMAT( `Messages`.`Date`, '%D %M %Y' ) AS FmtDate, and used the FmtDate reference to display the query results and it worked fine. -- Best regards, mikeszmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Crash Recovery Problem
I've got MySQL 5.0.55a running on a Windows XP system. All tables in the active database on the system are innodb. The startup log says that innodb recovery completed and that connections are available. Meanwhile, mysql is writing to the hdd at around 2MB/sec according to process explorer. I can connect to the server fine until I run a query against the database. Then no other connections can issue any command whatsoever. The mysql client just freezes up, and I can't make any new connections to the server either. After a period of time, mysqld-nt.exe crashed and one thread is left running, still writing to the drive at approx 2MB/sec. Does anyone out there have any idea what's going wrong here? I'm stumped. -- Grant Limberg [EMAIL PROTECTED]
Re: Error 1136 problem
Hi,
On Feb 6, 2008 4:47 PM, Olaf Stein [EMAIL PROTECTED] wrote:
Hi All
I am trying to run this query:
update minpheno set TMP_ados_version='0' where ident=898;
On below table:
And get error 1136
ERROR 1136 (21S01): Column count doesn't match value count at row 1
How can that be (obviously ident=898 exists)?
It sounds like the table is corrupt. Run CHECK TABLE.
The other possibility is that there is a trigger that is doing a blind
insert (one that doesn't specify column names) into another table.
I am going nuts
Thanks
Olaf
+---+---+--+
-+-++
| Field | Type | Null | Key
| Default | Extra |
+---+---+--+
-+-++
| minpheno_id | mediumint(8) unsigned | NO | PRI
| NULL| auto_increment |
| ident | mediumint(8) unsigned | NO | MUL
| ||
| irb | tinyint(3) unsigned | YES |
| NULL||
| ethnicity | enum('0','1','2') | NO |
| 0 ||
| race | enum('0','1','2','3','4','5') | NO |
| 0 ||
| dob | date | YES |
| NULL||
| current_age | smallint(4) unsigned | YES |
| NULL||
| epilepsy_hx | enum('0','1','2','7','9') | YES |
| NULL||
| parental_lang_delay | enum('0','1','2','9') | YES |
| NULL||
| best_est_dx | enum('1','2','3','4','5','6') | YES |
| NULL||
| best_est_iq | enum('0','1','2','3','4','5') | YES |
| NULL||
| adi_age | smallint(4) unsigned | YES |
| NULL||
| adi_version | enum('1','2') | YES |
| NULL||
| adi_social| tinyint(3) unsigned | YES |
| NULL||
| adi_comm | tinyint(3) unsigned | YES |
| NULL||
| adi_stereo| tinyint(3) unsigned | YES |
| NULL||
| adi_onset | enum('0','1') | YES |
| NULL||
| adi_age_first_words | smallint(4) unsigned | YES |
| NULL||
| adi_age_first_phrases | smallint(4) unsigned | YES |
| NULL||
| adi_verbal| enum('0','1','2') | YES |
| NULL||
| adi_dx| enum('0','1') | YES |
| NULL||
| TMP_adi_dx| enum('0','1') | YES |
| NULL||
| ados_age | smallint(4) unsigned | YES |
| NULL||
| ados_version | enum('1','2') | YES |
| NULL||
| TMP_ados_version | enum('0','1','2','3','4','5','6','7') | YES |
| NULL||
| ados_module | enum('0','1','2','3','4') | YES |
| NULL||
| ados_social | tinyint(3) unsigned | YES |
| NULL||
| ados_comm | tinyint(3) unsigned | YES |
| NULL||
| ados_stereo | tinyint(3) unsigned | YES |
| NULL||
| ados_total| tinyint(3) unsigned | YES |
| NULL||
| ados_dx | enum('0','1','2') | YES |
| NULL||
| TMP_ados_dx | enum('0','1','2') | YES |
| NULL||
| vine_age | smallint(4) unsigned | YES |
| NULL||
| vine_comm | smallint(3) unsigned | YES |
| NULL||
| vine_living | smallint(3) unsigned | YES |
| NULL||
| vine_social | smallint(3) unsigned | YES |
| NULL||
| vine_comp | smallint(3) unsigned | YES |
| NULL||
| comment | text | YES |
| NULL||
+---+---+--+
-+-++
-
Error 1136 problem
Hi All
I am trying to run this query:
update minpheno set TMP_ados_version='0' where ident=898;
On below table:
And get error 1136
ERROR 1136 (21S01): Column count doesn't match value count at row 1
How can that be (obviously ident=898 exists)?
I am going nuts
Thanks
Olaf
+---+---+--+
-+-++
| Field | Type | Null | Key
| Default | Extra |
+---+---+--+
-+-++
| minpheno_id | mediumint(8) unsigned | NO | PRI
| NULL| auto_increment |
| ident | mediumint(8) unsigned | NO | MUL
| ||
| irb | tinyint(3) unsigned | YES |
| NULL||
| ethnicity | enum('0','1','2') | NO |
| 0 ||
| race | enum('0','1','2','3','4','5') | NO |
| 0 ||
| dob | date | YES |
| NULL||
| current_age | smallint(4) unsigned | YES |
| NULL||
| epilepsy_hx | enum('0','1','2','7','9') | YES |
| NULL||
| parental_lang_delay | enum('0','1','2','9') | YES |
| NULL||
| best_est_dx | enum('1','2','3','4','5','6') | YES |
| NULL||
| best_est_iq | enum('0','1','2','3','4','5') | YES |
| NULL||
| adi_age | smallint(4) unsigned | YES |
| NULL||
| adi_version | enum('1','2') | YES |
| NULL||
| adi_social| tinyint(3) unsigned | YES |
| NULL||
| adi_comm | tinyint(3) unsigned | YES |
| NULL||
| adi_stereo| tinyint(3) unsigned | YES |
| NULL||
| adi_onset | enum('0','1') | YES |
| NULL||
| adi_age_first_words | smallint(4) unsigned | YES |
| NULL||
| adi_age_first_phrases | smallint(4) unsigned | YES |
| NULL||
| adi_verbal| enum('0','1','2') | YES |
| NULL||
| adi_dx| enum('0','1') | YES |
| NULL||
| TMP_adi_dx| enum('0','1') | YES |
| NULL||
| ados_age | smallint(4) unsigned | YES |
| NULL||
| ados_version | enum('1','2') | YES |
| NULL||
| TMP_ados_version | enum('0','1','2','3','4','5','6','7') | YES |
| NULL||
| ados_module | enum('0','1','2','3','4') | YES |
| NULL||
| ados_social | tinyint(3) unsigned | YES |
| NULL||
| ados_comm | tinyint(3) unsigned | YES |
| NULL||
| ados_stereo | tinyint(3) unsigned | YES |
| NULL||
| ados_total| tinyint(3) unsigned | YES |
| NULL||
| ados_dx | enum('0','1','2') | YES |
| NULL||
| TMP_ados_dx | enum('0','1','2') | YES |
| NULL||
| vine_age | smallint(4) unsigned | YES |
| NULL||
| vine_comm | smallint(3) unsigned | YES |
| NULL||
| vine_living | smallint(3) unsigned | YES |
| NULL||
| vine_social | smallint(3) unsigned | YES |
| NULL||
| vine_comp | smallint(3) unsigned | YES |
| NULL||
| comment | text | YES |
| NULL||
+---+---+--+
-+-++
- Confidentiality Notice:
The following mail message, including any attachments, is for the
sole use of the intended recipient(s) and may contain confidential
and privileged information. The recipient is responsible to
maintain the confidentiality of this information and to use the
information only for authorized purposes. If you are not the
intended recipient (or
Re: Data folder copying problem
Hi Martin, On 1/31/08, Martijn Tonies [EMAIL PROTECTED] wrote: Hi, By default, InnoDB tables aren't stored in the database folder, but rather in it's own table space files. In fact when I try to drop the database, the server recognizes innodb tables. For example, T1 and T2 are INNODB tables in database DB1. Then when I try to drop DB1, it issues error Unknown tables (T1, T2). If InnoDb own its table space... where is it reside ? Thanks Regards, Feris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data folder copying problem
Hi, I have 2 database folder that being copied directly from a remote server and sent to me. That databases contains both MYISAM and INNODB tables. After I received the data, I try to restored it by copying that folders to my server. The problem is, only MYISAM tables are being recognized. How come ? I check mysql server storage engine status and INNODB is being enabled and active. By default, InnoDB tables aren't stored in the database folder, but rather in it's own table space files. Then I try to drop those databases, but every drop attempt will cause unknown tables ... error. Is my restoration problem has to do with some user permission or it is a bug ? For your information, both the database server use mysql 5.0.45-community-nt version. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data folder copying problem
Hi all, I have 2 database folder that being copied directly from a remote server and sent to me. That databases contains both MYISAM and INNODB tables. After I received the data, I try to restored it by copying that folders to my server. The problem is, only MYISAM tables are being recognized. How come ? I check mysql server storage engine status and INNODB is being enabled and active. Then I try to drop those databases, but every drop attempt will cause unknown tables ... error. Is my restoration problem has to do with some user permission or it is a bug ? For your information, both the database server use mysql 5.0.45-community-nt version. Regards, Feris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data folder copying problem
Hi, By default, InnoDB tables aren't stored in the database folder, but rather in it's own table space files. In fact when I try to drop the database, the server recognizes innodb tables. For example, T1 and T2 are INNODB tables in database DB1. Then when I try to drop DB1, it issues error Unknown tables (T1, T2). Why do you think it recognizes them? I have a database with only 1 table, but if I type this: drop table foo (foo doesn't exist) I get: #42S02Unknown table 'foo' Same error :-) If InnoDb own its table space... where is it reside ? I believe directly under the \data folder. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data folder copying problem
Hi Rick, Thanks... I think I found the answer from your direction. This article seems the solution to my problem : http://dev.mysql.com/doc/refman/5.1/en/innodb-backup.html Thanks ! Feris On 1/31/08, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: moving an innodb table is trickier than moving a myisam one. you might want to do a search like: moving innodb tables site:mysql.com in google. i think that the first couple of entries will give you hints on what you need to do to accomplish this. - Rick Original Message Date: Thursday, January 31, 2008 09:00:38 PM +0700 From: Feris Thia [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Data folder copying problem Hi all, I have 2 database folder that being copied directly from a remote server and sent to me. That databases contains both MYISAM and INNODB tables. After I received the data, I try to restored it by copying that folders to my server. The problem is, only MYISAM tables are being recognized. How come ? I check mysql server storage engine status and INNODB is being enabled and active. Then I try to drop those databases, but every drop attempt will cause unknown tables ... error. Is my restoration problem has to do with some user permission or it is a bug ? For your information, both the database server use mysql 5.0.45-community-nt version. -- End Original Message -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data folder copying problem
Hi Martin, You are correct. That's the same error that I got. Looks like this article is the solution = http://dev.mysql.com/doc/refman/5.1/en/innodb-backup.html Thanks ! Feris On 1/31/08, Martijn Tonies [EMAIL PROTECTED] wrote: Hi, By default, InnoDB tables aren't stored in the database folder, but rather in it's own table space files. In fact when I try to drop the database, the server recognizes innodb tables. For example, T1 and T2 are INNODB tables in database DB1. Then when I try to drop DB1, it issues error Unknown tables (T1, T2). Why do you think it recognizes them? I have a database with only 1 table, but if I type this: drop table foo (foo doesn't exist) I get: #42S02Unknown table 'foo' Same error :-) If InnoDb own its table space... where is it reside ? I believe directly under the \data folder. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Who know this strange problem
This is correct.Thank you very much. But I also why I wrote is wrong on linux and right on windows. You can tell me answer if you have time.thanks. On Jan 19, 2008 12:47 AM, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, On Jan 18, 2008 3:50 AM, Moon's Father [EMAIL PROTECTED] wrote: My test is as follows: mysql select g_id from t group by g_id order by g_id desc limit 0,1; +--+ | g_id | +--+ |6 | +--+ 1 row in set (0.00 sec) mysql select g_id from t group by g_id order by g_id desc limit 0,1 into @tmp_id; Query OK, 1 row affected (0.00 sec) mysql select @tmp_id; +-+ | @tmp_id | +-+ | 5 | +-+ Try it this way: mysql select @tmp_id := g_id from t group by g_id order by g_id desc limit 0,1; +-+ | @tmp_id := g_id | +-+ | 6 | +-+ 1 row in set (0.00 sec) mysql select @tmp_id; +-+ | @tmp_id | +-+ | 6 | +-+ This seems like a bug to me. You should file a bug report at bugs.mysql.com. 1 row in set (0.00 sec) mysql select version(); +-+ | version() | +-+ | 5.1.21-beta-log | +-+ 1 row in set (0.00 sec) mysql But the variabe @tmp_id 's value is 5; Any help is appreciated. Here is my test data. DROP TABLE IF EXISTS `t`; CREATE TABLE `t` ( `id` int(11) NOT NULL auto_increment, `g_id` int(11) NOT NULL, `t_str` varchar(255) NOT NULL, PRIMARY KEY (`id`), key (`g_id`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8; /*Data for the table `t` */ insert into `t`(`id`,`g_id`,`t_str`) values (1,2,'wo'), (2,2,'ni'), (3,2,'ta'), (4,3,'wo '), (5,4,'ni'), (6,3,'ni'), (7,4,'ta'), (8,3,'wang'), (9,4,'li'), (10,3,'hai'), (11,4,'ri'), (12,2,'ren'), (13,5,'ta'), (14,6,'ri'), (15,6,'ren'), (16,6,'fuck'), (17,6,'shit'), (18,5,'ls'), (19,5,'chmod'), (20,5,'chgrp'), (21,5,'chown'), (22,3,'rm'), (23,3,'desc'), (24,4,'pwd'), (25,5,'cd'); -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Who know this strange problem
My test is as follows: mysql select g_id from t group by g_id order by g_id desc limit 0,1; +--+ | g_id | +--+ |6 | +--+ 1 row in set (0.00 sec) mysql select g_id from t group by g_id order by g_id desc limit 0,1 into @tmp_id; Query OK, 1 row affected (0.00 sec) mysql select @tmp_id; +-+ | @tmp_id | +-+ | 5 | +-+ 1 row in set (0.00 sec) mysql select version(); +-+ | version() | +-+ | 5.1.21-beta-log | +-+ 1 row in set (0.00 sec) mysql But the variabe @tmp_id 's value is 5; Any help is appreciated. Here is my test data. DROP TABLE IF EXISTS `t`; CREATE TABLE `t` ( `id` int(11) NOT NULL auto_increment, `g_id` int(11) NOT NULL, `t_str` varchar(255) NOT NULL, PRIMARY KEY (`id`), key (`g_id`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8; /*Data for the table `t` */ insert into `t`(`id`,`g_id`,`t_str`) values (1,2,'wo'), (2,2,'ni'), (3,2,'ta'), (4,3,'wo '), (5,4,'ni'), (6,3,'ni'), (7,4,'ta'), (8,3,'wang'), (9,4,'li'), (10,3,'hai'), (11,4,'ri'), (12,2,'ren'), (13,5,'ta'), (14,6,'ri'), (15,6,'ren'), (16,6,'fuck'), (17,6,'shit'), (18,5,'ls'), (19,5,'chmod'), (20,5,'chgrp'), (21,5,'chown'), (22,3,'rm'), (23,3,'desc'), (24,4,'pwd'), (25,5,'cd'); -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Who know this strange problem
Hi, On Jan 18, 2008 3:50 AM, Moon's Father [EMAIL PROTECTED] wrote: My test is as follows: mysql select g_id from t group by g_id order by g_id desc limit 0,1; +--+ | g_id | +--+ |6 | +--+ 1 row in set (0.00 sec) mysql select g_id from t group by g_id order by g_id desc limit 0,1 into @tmp_id; Query OK, 1 row affected (0.00 sec) mysql select @tmp_id; +-+ | @tmp_id | +-+ | 5 | +-+ Try it this way: mysql select @tmp_id := g_id from t group by g_id order by g_id desc limit 0,1; +-+ | @tmp_id := g_id | +-+ | 6 | +-+ 1 row in set (0.00 sec) mysql select @tmp_id; +-+ | @tmp_id | +-+ | 6 | +-+ This seems like a bug to me. You should file a bug report at bugs.mysql.com. 1 row in set (0.00 sec) mysql select version(); +-+ | version() | +-+ | 5.1.21-beta-log | +-+ 1 row in set (0.00 sec) mysql But the variabe @tmp_id 's value is 5; Any help is appreciated. Here is my test data. DROP TABLE IF EXISTS `t`; CREATE TABLE `t` ( `id` int(11) NOT NULL auto_increment, `g_id` int(11) NOT NULL, `t_str` varchar(255) NOT NULL, PRIMARY KEY (`id`), key (`g_id`) ) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8; /*Data for the table `t` */ insert into `t`(`id`,`g_id`,`t_str`) values (1,2,'wo'), (2,2,'ni'), (3,2,'ta'), (4,3,'wo '), (5,4,'ni'), (6,3,'ni'), (7,4,'ta'), (8,3,'wang'), (9,4,'li'), (10,3,'hai'), (11,4,'ri'), (12,2,'ren'), (13,5,'ta'), (14,6,'ri'), (15,6,'ren'), (16,6,'fuck'), (17,6,'shit'), (18,5,'ls'), (19,5,'chmod'), (20,5,'chgrp'), (21,5,'chown'), (22,3,'rm'), (23,3,'desc'), (24,4,'pwd'), (25,5,'cd'); -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problem - MySQL at 99.9% CPU
acceptable load should be #processors (so in your case 2 is okay - machine is performing well - 4 somethings being over utilized somewhere) Also in top 100% = 100% of one processor, so in a dual processor (or core) setup, you can actually go to 200% Your userland apps are taking up 61.3 + 57.0 (118.3% of 200% or 59% overall) of system resources. Your system processes are taking up 66.2% (of 200% or 33% overall) and it's leaving about 14% (of 200% - so 7% overall) of the system idle. The remainders are I/O waits etc (your numbers look pretty good there, but IO wait can spike and so may be misleading without using other tools. You may be encountering a thrashing problem with the amount of memory left or any number of things, but I would look at memory use on this box, because your load is pretty high and your performance is suffering if it's staying there. Your memory is at about 92% utilized too... while 91Mb seems like a lot of memory - it's easily consumed by a couple of large queries, sorts and so on which then goes right to disk swapping for virtual memory - never good for performance. It might also be impacted by IO and you just can't see it in the one slice of top we have here. If that number spikes up to 5% and then falls back down - it might be time spent going to disk with temp tables etc. Also turn on slow query logging (yes, I know it's another performance hit) and see if there is one query that's particularly problematic, perhaps optimizing the indexes etc on the table might help with the performance. Also, make sure your HD's aren't full... that will kill performance very quickly if the needed disk space isn't there. Erik On Jan 3, 2008, at 3:44 PM, Gunnar R. wrote: Hello, Thanks. I read the document, but unfortunately it didn't tell me anything new.. One of the things I am a bit confused about is: top - 22:08:12 up 6 days, 7:23, 1 user, load average: 4.36, 3.30, 2.84 Tasks: 134 total, 1 running, 133 sleeping, 0 stopped, 0 zombie Cpu0 : 61.3% us, 29.1% sy, 0.0% ni, 7.9% id, 0.7% wa, 0.3% hi, 0.7% si Cpu1 : 57.0% us, 37.1% sy, 0.0% ni, 6.0% id, 0.0% wa, 0.0% hi, 0.0% si Mem: 1034280k total, 942780k used,91500k free,34252k buffers Swap: 2031608k total, 104k used, 2031504k free, 278788k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 2410 mysql 15 0 470m 310m 4464 S 99.9 30.8 4200:25 mysqld How come the CPUs can have idle time even though mysqld is running at 99.9%, AND there's a processor queue (4.36)? Cheers, Gunnar R. On ons, januar 2, 2008, 13:07, Andrew Braithwaite wrote: Hi, If you can follow this document: http://www.ufsdump.org/papers/uuasc-june-2006.pdf You should be able to figure out what's happening. Cheers, Andrew -Original Message- From: Gunnar R. [mailto:[EMAIL PROTECTED] Sent: Tue, 01 January 2008 23:31 To: mysql@lists.mysql.com Subject: Performance problem - MySQL at 99.9% CPU Hello, I am running a community site mainly based on phpBB. It has about 9.300 registered users, 650.000 posts and about 200.000 visitors/month (12 mill hits). The SQL database is about 700MB. It's all running on a couple of years old Dell box with two P4 Xeon 1.7Ghz CPUs, 1GB of RAMBUS memory and SCSI disks, with Linux and Apache. The last year the server has been having huge performance problems, and MySQL (5.0.45) seems to be the problem. It's almost constantly running at 99.9% CPU (measured using 'top'). I know the hardware isn't too hot, but either way I am a bit confused by the fact that I can't seem to get MySQL to run smoothly. Is this just too big a database for this kind of box, or could this be a configuration issue? I am thinking about buying a new dual core box (with IDE disks?), but I have to make sure this really is a hardware issue before I spend thousands of bucks. Any help will be hugely appreciated! Cheers, Gunnar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] LOVEFiLM International Limited is a company registered in England and Wales. Registered Number: 04392195. Registered Office: No.9, 6 Portal Way, London W3 6RU, United Kingdom. This e-mail is confidential to the ordinary user of the e-mail address to which it was addressed. If you have received it in error, please delete it from your system and notify the sender immediately. This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com -- 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/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL
Re: left join problem
Yes, that is the correct behavior of a LEFT JOIN. A left join keeps all the record from the original/left table and will link up any related data from the right table, but if there is no related data, it sets it to NULL. If you want the join to act as a filter, the just use regular JOIN. Brent On Jan 8, 2008, at 12:24 PM, Wes Hegge wrote: I am attempting to left join several tables. This was working at one time but has seemed to stop just recently. I have checked and installed the lastest version of mysql via Debian Etch apt-get. I am running version 5.0.32. I have simplified the example down to just 3 tables but the problem exists at this point. Table 'contacts' - Does not matter what the fields are, still the same problem. I am not using TEXT fields though. Most are int's or varchar's account_num first_name last_name Table 'address' account_num address_1 address_2 city state zip Table 'phone' account_num phone_1 phone_1_type phone_2 phone_2_type What I want to do is search all three tables for something, return anything that matches. So here is the select statement I have been using: SELECT contacts.account_num, first_name, last_name, address_1, city_1, phone_1 FROM contacts LEFT JOIN (address, phone) ON (contacts.account_num = address.account_num AND contacts.account_num = phone.account_num) WHERE contacts.account_num LIKE '%something%' OR contacts.first_name LIKE '%something%' OR address.address_1 LIKE '%something%' OR address.address_2LIKE '%something%' OR address.city LIKE '%somehting%' OR phone.phone_1 LIKE '%something%' OR phone.phone_2 LIKE '%something%' ORDER BY last_name; When I run this query I only get data back from the 'contacts' table. What I have been able to track down is that if I am missing data from any of the tables that I LEFT JOIN'd then all the data from all the LEFT JOIN'd tables will be NULL. In other words if I have account data in tables 'contacts' and 'address' but nothing in 'phone' then no data from tables 'address' or 'phone' will be returned. If I add data to 'phone' then data is returned properly. Is this correct behavior? If so, any suggestions on how to solve this problem would be great. Realize this is a smaller example of what I am really trying to do. There are at least 4 tables in the select statement at any one time and could be as many as 6. Thanks! -- Wes Hegge - If the phone rings. Its not me. -- Jimmy Buffet -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problem - MySQL at 99.9% CPU
Thank you Erik! HDs are OK, a couple of GB free. Not that it's a lot, but I can't imagine it being too low for MySQL.. I'm aware memory is a bit low, but RAMBUS chips are hard to come by. They don't have them in stock anywhere anymore. Also they are quite expensive. It's almost like you could've bought 1/3rd of a new cheap Dell server for 2x512MB RAMBUS. But if a new box for a reasonable price wouldn't be any faster it's no use anyway... Concerning slow queries, it seems there's a couple of different queries that's being logged. This is one, taking 66 seconds: # Query_time: 66 Lock_time: 0 Rows_sent: 0 Rows_examined: 15857680 SELECT word_id FROM phpbb_search_wordmatch GROUP BY word_id HAVING COUNT(word_id) 263916; Usual time for this seems to be from 12 to 66 seconds. And then there's this, usually taking 10-20 seconds: # Query_time: 12 Lock_time: 0 Rows_sent: 10 Rows_examined: 395960 SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time, f.forum_name FROM phpbb_topics t, phpbb_users u, phpbb_posts p, phpbb_posts p2, phpbb_users u2, phpbb_forums f WHERE t.topic_poster = u.user_id AND t.forum_id NOT IN (16, 17) AND p.post_id = t.topic_first_post_id AND p2.post_id = t.topic_last_post_id AND t.forum_id = f.forum_id AND u2.user_id = p2.poster_id AND t.topic_status 1 AND t.topic_status 2 ORDER BY t.topic_last_post_id DESC LIMIT 10; In the evenings there seems to be 10-20 slow queries every hour, time between them varying from seconds to usually 5-10 minutes. Cheers, Gunnar On fre, januar 4, 2008, 05:55, Erik Giberti wrote: Gunnar, us = user (things like MySQL/PHP/Apache) sy = system (memory management / swap space / threading / kernel processes and so on) ni = nice (apps running only when nothing else needs the resource) id = idle (extra cpu cycles being wasted) wa = wait state (io wait for disk/network/memory) hi si - interrupts Generally acceptable load should be #processors (so in your case 2 is okay - machine is performing well - 4 somethings being over utilized somewhere) Also in top 100% = 100% of one processor, so in a dual processor (or core) setup, you can actually go to 200% Your userland apps are taking up 61.3 + 57.0 (118.3% of 200% or 59% overall) of system resources. Your system processes are taking up 66.2% (of 200% or 33% overall) and it's leaving about 14% (of 200% - so 7% overall) of the system idle. The remainders are I/O waits etc (your numbers look pretty good there, but IO wait can spike and so may be misleading without using other tools. You may be encountering a thrashing problem with the amount of memory left or any number of things, but I would look at memory use on this box, because your load is pretty high and your performance is suffering if it's staying there. Your memory is at about 92% utilized too... while 91Mb seems like a lot of memory - it's easily consumed by a couple of large queries, sorts and so on which then goes right to disk swapping for virtual memory - never good for performance. It might also be impacted by IO and you just can't see it in the one slice of top we have here. If that number spikes up to 5% and then falls back down - it might be time spent going to disk with temp tables etc. Also turn on slow query logging (yes, I know it's another performance hit) and see if there is one query that's particularly problematic, perhaps optimizing the indexes etc on the table might help with the performance. Also, make sure your HD's aren't full... that will kill performance very quickly if the needed disk space isn't there. Erik On Jan 3, 2008, at 3:44 PM, Gunnar R. wrote: Hello, Thanks. I read the document, but unfortunately it didn't tell me anything new.. One of the things I am a bit confused about is: top - 22:08:12 up 6 days, 7:23, 1 user, load average: 4.36, 3.30, 2.84 Tasks: 134 total, 1 running, 133 sleeping, 0 stopped, 0 zombie Cpu0 : 61.3% us, 29.1% sy, 0.0% ni, 7.9% id, 0.7% wa, 0.3% hi, 0.7% si Cpu1 : 57.0% us, 37.1% sy, 0.0% ni, 6.0% id, 0.0% wa, 0.0% hi, 0.0% si Mem: 1034280k total, 942780k used,91500k free,34252k buffers Swap: 2031608k total, 104k used, 2031504k free, 278788k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 2410 mysql 15 0 470m 310m 4464 S 99.9 30.8 4200:25 mysqld How come the CPUs can have idle time even though mysqld is running at 99.9
Re: Performance problem - MySQL at 99.9% CPU
Thank you Erik! HDs are OK, a couple of GB free. Not that it's a lot, but I can't imagine it being too low for MySQL.. I'm aware memory is a bit low, but RAMBUS chips are hard to come by. They don't have them in stock anywhere anymore. Also they are quite expensive. It's almost like you could've bought 1/3rd of a new cheap Dell server for 2x512MB RAMBUS. But if a new box for a reasonable price wouldn't be any faster it's no use anyway... Concerning slow queries, it seems there's a couple of different queries that's being logged. This is one, taking 66 seconds: # Query_time: 66 Lock_time: 0 Rows_sent: 0 Rows_examined: 15857680 SELECT word_id FROM phpbb_search_wordmatch GROUP BY word_id HAVING COUNT(word_id) 263916; Usual time for this seems to be from 12 to 66 seconds. And then there's this, usually taking 10-20 seconds: # Query_time: 12 Lock_time: 0 Rows_sent: 10 Rows_examined: 395960 SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time, f.forum_name FROM phpbb_topics t, phpbb_users u, phpbb_posts p, phpbb_posts p2, phpbb_users u2, phpbb_forums f WHERE t.topic_poster = u.user_id AND t.forum_id NOT IN (16, 17) AND p.post_id = t.topic_first_post_id AND p2.post_id = t.topic_last_post_id AND t.forum_id = f.forum_id AND u2.user_id = p2.poster_id AND t.topic_status 1 AND t.topic_status 2 ORDER BY t.topic_last_post_id DESC LIMIT 10; In the evenings there seems to be 10-20 slow queries every hour, time between them varying from seconds to usually 5-10 minutes. Cheers, Gunnar On fre, januar 4, 2008, 05:55, Erik Giberti wrote: Gunnar, us = user (things like MySQL/PHP/Apache) sy = system (memory management / swap space / threading / kernel processes and so on) ni = nice (apps running only when nothing else needs the resource) id = idle (extra cpu cycles being wasted) wa = wait state (io wait for disk/network/memory) hi si - interrupts Generally acceptable load should be #processors (so in your case 2 is okay - machine is performing well - 4 somethings being over utilized somewhere) Also in top 100% = 100% of one processor, so in a dual processor (or core) setup, you can actually go to 200% Your userland apps are taking up 61.3 + 57.0 (118.3% of 200% or 59% overall) of system resources. Your system processes are taking up 66.2% (of 200% or 33% overall) and it's leaving about 14% (of 200% - so 7% overall) of the system idle. The remainders are I/O waits etc (your numbers look pretty good there, but IO wait can spike and so may be misleading without using other tools. You may be encountering a thrashing problem with the amount of memory left or any number of things, but I would look at memory use on this box, because your load is pretty high and your performance is suffering if it's staying there. Your memory is at about 92% utilized too... while 91Mb seems like a lot of memory - it's easily consumed by a couple of large queries, sorts and so on which then goes right to disk swapping for virtual memory - never good for performance. It might also be impacted by IO and you just can't see it in the one slice of top we have here. If that number spikes up to 5% and then falls back down - it might be time spent going to disk with temp tables etc. Also turn on slow query logging (yes, I know it's another performance hit) and see if there is one query that's particularly problematic, perhaps optimizing the indexes etc on the table might help with the performance. Also, make sure your HD's aren't full... that will kill performance very quickly if the needed disk space isn't there. Erik On Jan 3, 2008, at 3:44 PM, Gunnar R. wrote: Hello, Thanks. I read the document, but unfortunately it didn't tell me anything new.. One of the things I am a bit confused about is: top - 22:08:12 up 6 days, 7:23, 1 user, load average: 4.36, 3.30, 2.84 Tasks: 134 total, 1 running, 133 sleeping, 0 stopped, 0 zombie Cpu0 : 61.3% us, 29.1% sy, 0.0% ni, 7.9% id, 0.7% wa, 0.3% hi, 0.7% si Cpu1 : 57.0% us, 37.1% sy, 0.0% ni, 6.0% id, 0.0% wa, 0.0% hi, 0.0% si Mem: 1034280k total, 942780k used,91500k free,34252k buffers Swap: 2031608k total, 104k used, 2031504k free, 278788k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 2410 mysql 15 0 470m 310m 4464 S 99.9 30.8 4200:25 mysqld How come the CPUs can have idle time even though mysqld is running at 99.9
left join problem
I am attempting to left join several tables. This was working at one time but has seemed to stop just recently. I have checked and installed the lastest version of mysql via Debian Etch apt-get. I am running version 5.0.32. I have simplified the example down to just 3 tables but the problem exists at this point. Table 'contacts' - Does not matter what the fields are, still the same problem. I am not using TEXT fields though. Most are int's or varchar's account_num first_name last_name Table 'address' account_num address_1 address_2 city state zip Table 'phone' account_num phone_1 phone_1_type phone_2 phone_2_type What I want to do is search all three tables for something, return anything that matches. So here is the select statement I have been using: SELECT contacts.account_num, first_name, last_name, address_1, city_1, phone_1 FROM contacts LEFT JOIN (address, phone) ON (contacts.account_num = address.account_num AND contacts.account_num = phone.account_num) WHERE contacts.account_num LIKE '%something%' OR contacts.first_name LIKE '%something%' OR address.address_1 LIKE '%something%' OR address.address_2LIKE '%something%' OR address.city LIKE '%somehting%' OR phone.phone_1 LIKE '%something%' OR phone.phone_2 LIKE '%something%' ORDER BY last_name; When I run this query I only get data back from the 'contacts' table. What I have been able to track down is that if I am missing data from any of the tables that I LEFT JOIN'd then all the data from all the LEFT JOIN'd tables will be NULL. In other words if I have account data in tables 'contacts' and 'address' but nothing in 'phone' then no data from tables 'address' or 'phone' will be returned. If I add data to 'phone' then data is returned properly. Is this correct behavior? If so, any suggestions on how to solve this problem would be great. Realize this is a smaller example of what I am really trying to do. There are at least 4 tables in the select statement at any one time and could be as many as 6. Thanks! -- Wes Hegge - If the phone rings. Its not me. -- Jimmy Buffet
SQL help/problem with timestamped data differences
Hi All I have the following data example UID Timestamp 123456 20071201 12:00:01 123456 20071201 12:00:06 987654 20071201 12:00:01 987654 20071201 12:00:09 etc I need : UID Timestamp secs 123456 20071201 12:00:01 123456 20071201 12:00:06 0005 987654 20071201 12:00:01 987654 20071201 12:00:09 0008 or similar solution. I am using version 5.0 and willing to use interim tables or any SQL based technique. Thanks in advance Mark -- Mark Carson Managing Integrated Product Intelligence CC EMail : [EMAIL PROTECTED]/[EMAIL PROTECTED] snailmail : P.O. Box 36095 Menlo Park 0102, South Africa Cell : +27 83 260 8515 This e-mail may contain PRIVILEGED AND/OR CONFIDENTIAL INFORMATION intended only for use of the addressee. If you are not the addressee, or the person responsible for delivering it to the person addressed, you may not copy or deliver this to anyone else. If you received this e-mail by mistake, please do not make use of it, nor disclose it's contents to anyone. Thank you for notifying us immediately by return e-mail or telephone. INFORMATION PROVIDED IN THIS ELECTRONIC MAIL IS PROVIDED AS IS WITHOUT WARRANTY REPRESENTATION OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY AND THE USE OF THIS DOCUMENT. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problem - MySQL at 99.9% CPU
At 6:47a -0500 on 08 Jan 2008, Gunnar R. wrote: Concerning slow queries, it seems there's a couple of different queries that's being logged. I haven't tried it yet, but this recently went by on debaday.debian.net: mytop: a top clone for MySQL http://debaday.debian.net/2007/12/26/mytop-a-top-clone-for-mysql/ Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL help/problem with timestamped data differences
Mark, is the 'secs' column the offset from the minimum value of the timestamp column? If so, you might try something like this: SELECT UNIX_TIMESTAMP(MIN(timestamp)) INTO @min_timestamp FROM my_table; SELECT uid, timestamp, UNIX_TIMESTAMP(timestamp) - @min_timestamp AS secs FROM my_table ORDER BY 1, 2, 3; HTH, Dan On Jan 8, 2008 7:17 PM, mark carson [EMAIL PROTECTED] wrote: Hi All I have the following data example UID Timestamp 123456 20071201 12:00:01 123456 20071201 12:00:06 987654 20071201 12:00:01 987654 20071201 12:00:09 etc I need : UID Timestamp secs 123456 20071201 12:00:01 123456 20071201 12:00:06 0005 987654 20071201 12:00:01 987654 20071201 12:00:09 0008 or similar solution. I am using version 5.0 and willing to use interim tables or any SQL based technique. Thanks in advance Mark -- Mark Carson Managing Integrated Product Intelligence CC EMail : [EMAIL PROTECTED]/[EMAIL PROTECTED] snailmail : P.O. Box 36095 Menlo Park 0102, South Africa Cell : +27 83 260 8515 This e-mail may contain PRIVILEGED AND/OR CONFIDENTIAL INFORMATION intended only for use of the addressee. If you are not the addressee, or the person responsible for delivering it to the person addressed, you may not copy or deliver this to anyone else. If you received this e-mail by mistake, please do not make use of it, nor disclose it's contents to anyone. Thank you for notifying us immediately by return e-mail or telephone. INFORMATION PROVIDED IN THIS ELECTRONIC MAIL IS PROVIDED AS IS WITHOUT WARRANTY REPRESENTATION OR CONDITION OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO CONDITIONS OR OTHER TERMS OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. THE USER ASSUMES THE ENTIRE RISK AS TO THE ACCURACY AND THE USE OF THIS DOCUMENT. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problem - MySQL at 99.9% CPU
At 3:51p -0500 onGunnar R. wrote, On 01/08/2008 03:51 PM: That tool tells me 100% of the data is read from memory, not a byte from disk... would there still be any point in getting more memory? Any suggestions to where to go from here? I dunno. My hunch is that could do some query optimization. Have you checked the index usage on your queries? Although MySQL's internal planner is pretty weak for anything more complex than simple statements, there are some excellent tools available to help you tune your queries. http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html A quick google with these keywords mysql index hints query profiler seemed to provide a good jumping off point. HTH, Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem forcing indexes
I have the following 2 tables:
CREATE TABLE media (
id int(10) unsigned NOT NULL auto_increment,
user_id int(10) unsigned default NULL,
title varchar(255) NOT NULL,
description text NOT NULL,
`hash` varchar(255) NOT NULL,
length float(9,2) NOT NULL,
created timestamp NOT NULL default CURRENT_TIMESTAMP,
`type` enum('video','image') default NULL,
`status`
enum('new','processing','suspended','active','deleted','failed','pending')
NOT NULL default 'new',
flags int(20) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY `hash` (`hash`),
KEY `type` (`type`),
KEY user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
and
CREATE TABLE media_views (
media_id int(20) unsigned NOT NULL,
user_id int(12) unsigned NOT NULL,
views int(20) unsigned NOT NULL,
30d int(20) unsigned NOT NULL,
7d int(20) unsigned NOT NULL,
24h int(20) unsigned NOT NULL,
site30d int(11) unsigned NOT NULL default '0',
site7d int(11) unsigned NOT NULL default '0',
site24h int(11) unsigned NOT NULL default '0',
click int(20) NOT NULL,
last_dt timestamp NOT NULL default '-00-00 00:00:00' on update
CURRENT_TIMESTAMP,
PRIMARY KEY (media_id),
KEY last_dt (last_dt),
KEY user_id (user_id),
KEY 7d (7d,24h)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
and the following query using them:
select
SQL_BIG_RESULT media.*, media_views.*, media.created as dt,
media_views.views + media_views.embeds as alltime_views
FROM media
JOIN media_views ON ( media.id = media_views.media_id )
where
media.status = 'active'
and media.type = 'whatever'
order by
24h DESC, media.created desc LIMIT 0, 20
each table has about 125,000 records, and the query takes about 4 seconds to
run.
When I run explain on the query, it says:
++-+-++---+-+-+---+---+--+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows |
Extra|
++-+-++---+-+-+---+---+--+
| 1 | SIMPLE | media | ref| PRIMARY,type | type|
2 | const | 56518 | Using where; Using temporary; Using
filesort |
| 1 | SIMPLE | media_views | eq_ref | PRIMARY | PRIMARY |
4 | awv_free.media.id | 1
| |
++-+-++---+-+-+---+---+--+
When I try to add a force index (PRIMARY) after the media table to try and
make is use PRIMARY, rather than TYPE, the optimizer switches and uses no
key at all. I've tried to change the order in which the tables are selected,
but it seems to have no effect. In some scenarios it will switch and use the
media_views table, but the rows is still 125,000+ using temporary and
filesort.
how can I get this query time down?
Re: Performance problem - MySQL at 99.9% CPU
, with Linux and Apache. The last year the server has been having huge performance problems, and MySQL (5.0.45) seems to be the problem. It's almost constantly running at 99.9% CPU (measured using 'top'). I know the hardware isn't too hot, but either way I am a bit confused by the fact that I can't seem to get MySQL to run smoothly. Is this just too big a database for this kind of box, or could this be a configuration issue? I am thinking about buying a new dual core box (with IDE disks?), but I have to make sure this really is a hardware issue before I spend thousands of bucks. Any help will be hugely appreciated! Cheers, Gunnar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Regards, Ady Wicaksono Email: ady.wicaksono at gmail.com http://adywicaksono.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance problem - MySQL at 99.9% CPU
Hello, Thanks. I read the document, but unfortunately it didn't tell me anything new.. One of the things I am a bit confused about is: top - 22:08:12 up 6 days, 7:23, 1 user, load average: 4.36, 3.30, 2.84 Tasks: 134 total, 1 running, 133 sleeping, 0 stopped, 0 zombie Cpu0 : 61.3% us, 29.1% sy, 0.0% ni, 7.9% id, 0.7% wa, 0.3% hi, 0.7% si Cpu1 : 57.0% us, 37.1% sy, 0.0% ni, 6.0% id, 0.0% wa, 0.0% hi, 0.0% si Mem: 1034280k total, 942780k used,91500k free,34252k buffers Swap: 2031608k total, 104k used, 2031504k free, 278788k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 2410 mysql 15 0 470m 310m 4464 S 99.9 30.8 4200:25 mysqld How come the CPUs can have idle time even though mysqld is running at 99.9%, AND there's a processor queue (4.36)? Cheers, Gunnar R. On ons, januar 2, 2008, 13:07, Andrew Braithwaite wrote: Hi, If you can follow this document: http://www.ufsdump.org/papers/uuasc-june-2006.pdf You should be able to figure out what's happening. Cheers, Andrew -Original Message- From: Gunnar R. [mailto:[EMAIL PROTECTED] Sent: Tue, 01 January 2008 23:31 To: mysql@lists.mysql.com Subject: Performance problem - MySQL at 99.9% CPU Hello, I am running a community site mainly based on phpBB. It has about 9.300 registered users, 650.000 posts and about 200.000 visitors/month (12 mill hits). The SQL database is about 700MB. It's all running on a couple of years old Dell box with two P4 Xeon 1.7Ghz CPUs, 1GB of RAMBUS memory and SCSI disks, with Linux and Apache. The last year the server has been having huge performance problems, and MySQL (5.0.45) seems to be the problem. It's almost constantly running at 99.9% CPU (measured using 'top'). I know the hardware isn't too hot, but either way I am a bit confused by the fact that I can't seem to get MySQL to run smoothly. Is this just too big a database for this kind of box, or could this be a configuration issue? I am thinking about buying a new dual core box (with IDE disks?), but I have to make sure this really is a hardware issue before I spend thousands of bucks. Any help will be hugely appreciated! Cheers, Gunnar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] LOVEFiLM International Limited is a company registered in England and Wales. Registered Number: 04392195. Registered Office: No.9, 6 Portal Way, London W3 6RU, United Kingdom. This e-mail is confidential to the ordinary user of the e-mail address to which it was addressed. If you have received it in error, please delete it from your system and notify the sender immediately. This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com -- 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: problem forcing indexes
On Jan 3, 2008 4:23 PM, Tanner Postert [EMAIL PROTECTED] wrote: When I try to add a force index (PRIMARY) after the media table to try and make is use PRIMARY, rather than TYPE, the optimizer switches and uses no key at all. It usually knows better than you do about indexes. I've tried to change the order in which the tables are selected, but it seems to have no effect. It should be able to choose the best order most of the time. You can force it, but that's nearly always a mistake. In some scenarios it will switch and use the media_views table, but the rows is still 125,000+ using temporary and filesort. For this relatively small result set, temporary and filesort may not be a big deal. They are probably being used to handle your ORDER BY. how can I get this query time down? You can try some combined indexes, like one on media (id, status, type, created) and one on media_views (media_id, 24h). I don't think you can eliminate the temp table with that ORDER BY though. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problem - MySQL at 99.9% CPU
Gunnar, us = user (things like MySQL/PHP/Apache) sy = system (memory management / swap space / threading / kernel processes and so on) ni = nice (apps running only when nothing else needs the resource) id = idle (extra cpu cycles being wasted) wa = wait state (io wait for disk/network/memory) hi si - interrupts Generally acceptable load should be #processors (so in your case 2 is okay - machine is performing well - 4 somethings being over utilized somewhere) Also in top 100% = 100% of one processor, so in a dual processor (or core) setup, you can actually go to 200% Your userland apps are taking up 61.3 + 57.0 (118.3% of 200% or 59% overall) of system resources. Your system processes are taking up 66.2% (of 200% or 33% overall) and it's leaving about 14% (of 200% - so 7% overall) of the system idle. The remainders are I/O waits etc (your numbers look pretty good there, but IO wait can spike and so may be misleading without using other tools. You may be encountering a thrashing problem with the amount of memory left or any number of things, but I would look at memory use on this box, because your load is pretty high and your performance is suffering if it's staying there. Your memory is at about 92% utilized too... while 91Mb seems like a lot of memory - it's easily consumed by a couple of large queries, sorts and so on which then goes right to disk swapping for virtual memory - never good for performance. It might also be impacted by IO and you just can't see it in the one slice of top we have here. If that number spikes up to 5% and then falls back down - it might be time spent going to disk with temp tables etc. Also turn on slow query logging (yes, I know it's another performance hit) and see if there is one query that's particularly problematic, perhaps optimizing the indexes etc on the table might help with the performance. Also, make sure your HD's aren't full... that will kill performance very quickly if the needed disk space isn't there. Erik On Jan 3, 2008, at 3:44 PM, Gunnar R. wrote: Hello, Thanks. I read the document, but unfortunately it didn't tell me anything new.. One of the things I am a bit confused about is: top - 22:08:12 up 6 days, 7:23, 1 user, load average: 4.36, 3.30, 2.84 Tasks: 134 total, 1 running, 133 sleeping, 0 stopped, 0 zombie Cpu0 : 61.3% us, 29.1% sy, 0.0% ni, 7.9% id, 0.7% wa, 0.3% hi, 0.7% si Cpu1 : 57.0% us, 37.1% sy, 0.0% ni, 6.0% id, 0.0% wa, 0.0% hi, 0.0% si Mem: 1034280k total, 942780k used,91500k free,34252k buffers Swap: 2031608k total, 104k used, 2031504k free, 278788k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 2410 mysql 15 0 470m 310m 4464 S 99.9 30.8 4200:25 mysqld How come the CPUs can have idle time even though mysqld is running at 99.9%, AND there's a processor queue (4.36)? Cheers, Gunnar R. On ons, januar 2, 2008, 13:07, Andrew Braithwaite wrote: Hi, If you can follow this document: http://www.ufsdump.org/papers/uuasc-june-2006.pdf You should be able to figure out what's happening. Cheers, Andrew -Original Message- From: Gunnar R. [mailto:[EMAIL PROTECTED] Sent: Tue, 01 January 2008 23:31 To: mysql@lists.mysql.com Subject: Performance problem - MySQL at 99.9% CPU Hello, I am running a community site mainly based on phpBB. It has about 9.300 registered users, 650.000 posts and about 200.000 visitors/month (12 mill hits). The SQL database is about 700MB. It's all running on a couple of years old Dell box with two P4 Xeon 1.7Ghz CPUs, 1GB of RAMBUS memory and SCSI disks, with Linux and Apache. The last year the server has been having huge performance problems, and MySQL (5.0.45) seems to be the problem. It's almost constantly running at 99.9% CPU (measured using 'top'). I know the hardware isn't too hot, but either way I am a bit confused by the fact that I can't seem to get MySQL to run smoothly. Is this just too big a database for this kind of box, or could this be a configuration issue? I am thinking about buying a new dual core box (with IDE disks?), but I have to make sure this really is a hardware issue before I spend thousands of bucks. Any help will be hugely appreciated! Cheers, Gunnar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] LOVEFiLM International Limited is a company registered in England and Wales. Registered Number: 04392195. Registered Office: No.9, 6 Portal Way, London W3 6RU, United Kingdom. This e-mail is confidential to the ordinary user of the e-mail address to which it was addressed. If you have received it in error, please delete it from your system and notify the sender immediately. This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com -- MySQL General Mailing List For list
Re: Performance problem - MySQL at 99.9% CPU
Gunnar R. wrote: I am thinking about buying a new dual core box (with IDE disks?), but I have to make sure this really is a hardware issue before I spend thousands of bucks. I think you've got an application problem somewhere which you should look into first. Hardware-wise I think you're doing fine, except you could probably increase overall performance with more memory. MySQL is pretty good at query-caching. /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
