run out of memory

Hello, My server box has 8G memory and 8CPU (DELL Standard Server),when I configure Mysql server (5.0.45) using large memory,mysqld would say it run out of memory. For example,when this line appear in my.cnf, key_buffer = 4000M mysqld can't startup.When I change it to, key_buffer = 2560M

HOW QUERIES WORK

Hi all, i have to optimize complex join queries. i am not able to understand how the complex queries gets executed. Whether it follow top-down or bottom-up approach. There is millions of data. The queries is: select ucp.user_id as RESULTANT_ID from user_course_payment ucp,crm_sales_contact sc,

Re: HOW QUERIES WORK

Hi Krishna, Its the bottom-up approach in all db's which follow SQL standards. Can u please show us the explain plan of the below query. regards anandkl On 8/14/07, krishna chandra prajapati [EMAIL PROTECTED] wrote: Hi all, i have to optimize complex join queries. i am not able to

Enterprise Wide Deployment

Hey guys, for an enterprise wide deployment, what will you suggest and why among - Red Hat Linux, Suse Linux and Ubuntu Linux, also, do you think, we can negotiate the support pricing down? -- View this message in context:

Re: index, unique index question

A composite index on both columns may be used by queries involving either both columns, or the first column in the index. http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html So, an index on (receiver_id, sender_id) may be used by predicates on both columns or receiver_id

Re: HOW QUERIES WORK

Hi, The details are as follows mysql explain select ucp.user_id as RESULTANT_ID from user_course_payment ucp,crm_sales_contact sc, crm_contacts c where c.contact_id=sc.contact_idand c.product_id = sc.product_id and sc.product_id=1 and sc.resultant_id = ucp.user_id and sc.resultant_id !='' and

Re: MySQL Ends Enterprise Server Source Tarballs

Eric Bergen wrote: It's nothing to be concerned about because the source tar balls and binaries are being mirrored at http://mirror.provenscaling.com/mysql/ -Eric On 8/10/07, Daevid Vincent [EMAIL PROTECTED] wrote: Is this anything to be concerned about? We are Enterprise customers. We

Re: HOW QUERIES WORK

Hi, MySQL is seeking into the operator_id index on crm_sales_contact and finding one or more matching rows where c.operator_id = 1. For each row it finds, it uses the value of contact_id to do a unique index lookup into crm_sales_contact's primary key. It then does a unique index lookup

date query

I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the scale reading on the next

Re: date query

Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the

Re: MySQL Ends Enterprise Server Source Tarballs

Hi Daevid, all! Daevid Vincent wrote: Is this anything to be concerned about? We are Enterprise customers. We distribute mySQL on our appliance that we sell. It doesn't seem like we should worry, now. But I'm a little nervous about the future?

Re: HOW QUERIES WORK

Hi Krishna, The index combination ur using is not correct, please check the cardinality of the joining columns in the where condition and create the index also, you can use an BETWEEN clause instead of date_format(ucp.payment_date,'%Y-%m-%d') = '2001-12-12' and

Re: date query

On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop

Re: date query

On Aug 14, 2007, at 8:38 AM, Christian High wrote: On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or

Re: date query

Hi, Christian High wrote: On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I

Re: date query

On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150

RE: index, unique index question

Of course, since James said he will never search for a record matching receiver_ID AND sender_ID, it would be more efficient to simply create one index for each of the columns. -Noah -Original Message- From: Kristian Myllymäki [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 14, 2007

Re: Enterprise Wide Deployment

What exactly will you be doing with this deployment? Is it for all workstations, servers, just servers -- what? What will be the daily functions of the PCs (what applications/daemons will be used)? Some of the support pricing can be negotiated down if bundled or if if purchased in a large enough

Re: index, unique index question

Of course, since James said he will never search for a record matching receiver_ID AND sender_ID, it would be more efficient to simply create one index for each of the columns. Then again, his question isn't really about indices (to speed up searches and what not), but about constraints,

Re: index, unique index question

very true, to have unique constraint on both columns, he needs to create a composite index using both columns. regards anandkl On 8/14/07, Martijn Tonies [EMAIL PROTECTED] wrote: Of course, since James said he will never search for a record matching receiver_ID AND sender_ID, it would be

Re: date query

Hi Christian, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one

RE: Slow query involving ORDER BY

I've added both indexes but don't see an improvement in speed. Below are the the descriptions of each table and the explain plan: CREATE TABLE `hosts` ( `hostid` bigint(20) unsigned NOT NULL default '0', `host` varchar(64) NOT NULL default '', `dns` varchar(64) NOT NULL default '',

RE: Slow query involving ORDER BY

Sorry, That was really hard to read. Here it is again: mysql explain select fsys.facility, fsys.severity, h.host, fsys.src_ip, fsys.src_time, fsys.content, fsys.tag - from fs_syslog fsys, hosts h - where fsys.src_ip=h.ip - and h.status 4 - and h.hostid in

Problem with a complex query

Hi, I have a message system wich work in this way: - Each message can be sent to one or more recipients - Each message received have a lot of settings, like date and hour received, date and hour of read, mark as read and so on - When the user select view message, the system will get the history

RE: Problem with a complex query

Struggling with the Portuguese here but... What kind of indexes do you have in place? Are the y appropriate? I have had some success with removed or clauses from queries, creating a new query and join them with a UNION ALL. -Original Message- From: Hugo Ferreira da Silva [mailto:[EMAIL

Re: Problem with a complex query

First of all, thanks for your attention guys, Rhys, I've tried UNION and UNION ALL statements too, but I found some problems when ordering the results. I've tried order in each query and at end - (SELECT ...) UNION (SELECT ...) ORDER BY ... -, and result was poorly than this query. Martin, that

MySQLへログインできない

お世話になります吉田と申します。 早速ですがMySQL(5.0)へログインできず困っております。 Access denied for user 'root'@'localhost' (using password: YES) 再セットアップもかなら色々とやり直してみましたが駄目でした。 起動オプションで mysqld_safe --user=mysql --skip-grant-tables 等も試してみましたがどうしても接続できませんでした。 FreeBSDのVPSサーバ（NTT.COM）で、root権限付きのサーバなのですが

MySQLへログインできない

お世話になります吉田と申します。 早速ですがMySQL(5.0)へログインできず困っております。 Access denied for user 'root'@'localhost' (using password: YES) 再セットアップもかなら色々とやり直してみましたが駄目でした。 起動オプションで mysqld_safe --user=mysql --skip-grant-tables 等も試してみましたがどうしても接続できませんでした。 FreeBSDのVPSサーバ（NTT.COM）で、root権限付きのサーバなのですが

Re: Problem with a complex query

hum... I get it. But in my query, I look in 3 tables FROM mensagens m, mensagenspara mp, usuarios u, usuarios up WHERE m.codmensagem = mp.codmensagem AND u.codusaurio = m.codusuario AND up.codusuario = mp.codusuario m.codmensagem, u.codusaurio and up.codusuario are primary keys mp.codusuario,

Re: Problem with a complex query

what does explain tell you about the query? I also think you should try a combined index with all three columns that are used in one index. As far as I know only one index can be used per query... B On Aug 14, 2007, at 1:26 PM, Hugo Ferreira da Silva wrote: hum... I get it. But in my

Fwd: Problem with a complex query

The indexes are the primary way of tuning your query speed but bear in mind that mysql can only use 1 index-per-table-per-query.. your single column indexes do help some. try an EXPLAIN to see what I mean: EXPLAIN SELECT ... This will show you how mysql is approaching the query. From your

Re: Problem with a complex query

I've created an index with the statement create index `usuario_pasta_situacao` on `mensagens` (codusuario, codpasta, situacao); And for mensagenspara table this index create index `mensagem_usuario_pasta_situacao_idx` on `mensagenspara` (codmensagem, codusuario, codpasta, situacao); This is

Re: Problem with a complex query

From explain you can see that your problem lies within the mensagens table (the first entry from your explain query) where it says type: ALL and rows 68337. This basically means that it is not using any index for this table. MySQL doesn't seem to be very smart about queries involving OR

Re: Problem with a complex query

MySQL doesn't seem to be very smart about queries involving OR and things like . For me creating temporary tables or writing perl scripts to do the job solved my particular problems. But I am working with tables that don't change but have some 100,000,000 rows... I guess I suggest,

Re: run out of memory

What operating system are you running and is it 32 or 64 bit? Keith - Original Message - From: Jen mlists [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 14, 2007 2:35:34 AM (GMT-0500) America/New_York Subject: run out of memory Hello, My server box has 8G

Re: Enterprise Wide Deployment

Really probably not the best list for this. But if it were up to me..I would use CentOS/RedHat Enterprise Linux or Debian. Keith - Original Message - From: john_sm [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, August 14, 2007 3:20:36 AM (GMT-0500) America/New_York

Scheduled events

Hi, I have been trying for days to find a simple command in MySQL where I can automatically delete records based on some criteria after a certain timeframe. I found this in the MySQL manual, but I guess it only works with version 5.1+. Unfortunately the version I need this to work on is 4.1, and

Re: Scheduled events

Hi, Beauford wrote: Hi, I have been trying for days to find a simple command in MySQL where I can automatically delete records based on some criteria after a certain timeframe. I found this in the MySQL manual, but I guess it only works with version 5.1+. Unfortunately the version I need this

RE: Scheduled events

I have been trying for days to find a simple command in MySQL where I can automatically delete records based on some criteria after a certain timeframe. I found this in the MySQL manual, but I guess it only works with version 5.1+. Unfortunately the version I need this to work

Re: Scheduled events

It's a database, not a scripting language ... :) You can run a simple cron entry like this: 0 4 * * * /path/to/mysql -u USER -pPASS -D DATABASE -e delete from contacts where TO_DAYS(CURDATE()) - TO_DAYS(today) = 30 and status != 'Y'; so at 4 AM each day your SQL would be executed. For long

Re: Scheduled events

Beauford wrote: delete from contacts where TO_DAYS(CURDATE()) - TO_DAYS(today) = 30 and status != Y; Off-topic: you're defeating indexes with TO_DAYS(). If there's an index on the 'today' column, this will be able to use it: ... where today = date_sub(current_date, interval 30 day)...

Re: run out of memory

2007/8/15, B. Keith Murphy [EMAIL PROTECTED]: What operating system are you running and is it 32 or 64 bit? Thanks for the reply. Here is my OS info: $ uname -r 2.6.9-42.ELsmp $ cat /etc/redhat-release Red Hat Enterprise Linux AS release 4 (Nahant Update 4) Yes I think it's 64bit OS. The CPU

re: run out of memory

try: uname -a to see if your OS is 64bit or not. Gu Lei -原始邮件- 发件人: Jen mlists [mailto:[EMAIL PROTECTED] 发送时间: 2007年8月15日 9:46 收件人: mysql@lists.mysql.com 主题: Re: run out of memory 2007/8/15, B. Keith Murphy [EMAIL PROTECTED]: What operating system are you running and is it 32 or 64