Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!

I had perfectly working complex queries both with LEFT JOIN and without and they were returning results in under a second. After upgrade to 5.0.x, the same queries would return results in 20-30 second range. Through trial and error, I discovered that in case of SELECT ... FROM table1, table2 ...

Re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!

Gmail User schrieb: I had perfectly working complex queries both with LEFT JOIN and without and they were returning results in under a second. After upgrade to 5.0.x, the same queries would return results in 20-30 second range. possible you had set up some query cache in 4, but not currently

Re: string to timestamp conversion

Like: str_to_date('Thu May 17 09:15:47 2007','%a %b %e %T %Y') On Mon, May 21, 2007 21:10, [EMAIL PROTECTED] wrote: Have you considered using the string to time function? Sent via BlackBerry from T-Mobile -Original Message- From: Bryan Cantwell [EMAIL PROTECTED] Date: Mon, 21

design choice - quite many tables

Hi All. I'm working on quite big database. It consists of about 200 tables. Additionaly about 50 tables are per year (because of annual data). It means every year new 50 tables will have to appear in application. And now I have a question. Should I use separate databases for annual data

5.1 release date

Hi all, Are there any projections as to when mysql 5.1 will be released? Thanks Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Problem with compex query

Friends, im having troubles with the following query: --- INSERT drp_volumen_venta_diaria_deposito (drp_id_sku, drp_id_deposito, drp_volumen, drp_es_cct, drp_dia, drp_mes, drp_anno, drp_fecha_dia_cargado ) SELECT DISTINCT s.drp_id_sku,

re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!

Here's a question that begs to be asked -- When you upgraded from 4.1 to 5.0, did you do an in-place upgrade, or mysqldump your data and then re-import? MySQL 5.x's query cache and table optimizers work very differently than in 4.1, so the results you are getting are probably from 2 issues:

Re: design choice - quite many tables

It seems to me that you are asking about Merge tables. A merge table allows you to combine 1 or more tables to appear as a single virtual table. What tables make up the merge table can modified quickly and easily, regardless of size. Then your code only needs to reference 1 table name. There

Re: Problem with GRANT ... 'user'@'%'

On my servers i'm using the 'user'@'localhost' for PHP apps. running on the local web-server. Those users allocated for web-apps can only connect to their specific DB from localhost. On Tue, May 22, 2007 03:19, Miguel Cardenas wrote: Localhost is indeed a special value that isn't include in '%'.

a function to convert a uk date to and from mysql date

Hi, My UK dates are this format DD/MM/ I want it reversed and then the seperator changed so it becomes -MM-DD I use this PHP at the moment $available_from = implode('/', array_reverse(explode('-', $available_from))); Ta, R.

Re: a function to convert a uk date to and from mysql date

My UK dates are this format DD/MM/ I want it reversed and then the seperator changed so it becomes -MM-DD I use this PHP at the moment $available_from = implode('/', array_reverse(explode('-', $available_from))); An even better solution would be: $UKDate = '22/05/2007' $USDate = date(

RE: a function to convert a uk date to and from mysql date

-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 22 May 2007 15:47 To: mysql@lists.mysql.com Subject: a function to convert a uk date to and from mysql date Hi, My UK dates are this format DD/MM/ I want it reversed and then the seperator changed

Stored function problem

Hi All, I have a problem that I do not quite understand. I have a table with individuals: CREATE TABLE `individual` ( `ident` mediumint(8) unsigned NOT NULL auto_increment, `fid` mediumint(8) unsigned NOT NULL, `iid` mediumint(8) unsigned NOT NULL, PRIMARY KEY (`ident`), KEY `fidiid`

Re: Problem with compex query

Hi Patricio, Some options are to prevent programmers/users make a bad queries into the database' SQL_BIG_SELECTS = 0 | 1 The documentation say : If set to 0, MySQL will abort if a SELECT is attempted that probably will take a very long time. This is useful when an inadvisable WHERE statement

Mysql and Textarea

I'm just a php beginner. So please be patient with my stupid questions. What am I missing in this code that causing the function can’t update the textarea? Update button erase the old information from mysql database and replace it with nothing. Basically it can’t read what is

Re: Problem with compex query

Juan Eduardo, Great to hear about you :) One thing you must know is that i can run this query from a mysql client, without the insert part. The problem only happens when is run from the J2EE (Websphere - drp) application. I run the query as you asked, here are some results:

Re: Mysql and Textarea

Hi Sam, sam rumaizan wrote: I'm just a php beginner. So please be patient with my stupid questions. It's OK :-) What am I missing in this code that causing the function can’t update the textarea? Update button erase the old information from mysql database and replace it with

Bash script array from MySQL query - HELP Please!!!

I'm having problems getting a monitoring script to run. I've put the troublesome bit in a separate script just to test, and it goes like this: declare -a HNdeclares the array HN HN=(`echo SELECT url FROM hosts | mysql --user=netmon --password=n3tm0n --skip-column-names

Re: Problem with compex query

Hi Patricio, Your explain is scary.No use of index...Please, ask to programers in order to create some index in the schema. Development Server has the same version of MySQL production environment?. 4.0.18? Resume: 1) Please check the version of mysql ( prod and development) 2) Please use

Re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!

possible you had set up some query cache in 4, but not currently in 5? may not be optimized, but yes, query cache is enabled, all 25 MB of it. :-) how did you 'upgraded' your data? regrettably, in-place. interestingly, I was recovering after server crash that chopped of a table. after

Re: Mysql and Textarea

On 5/22/07, sam rumaizan [EMAIL PROTECTED] wrote: I'm just a php beginner. So please be patient with my stupid questions. What am I missing in this code that causing the function can't update the textarea? Update button erase the old information from mysql database and replace it with

Re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!

When you upgraded from 4.1 to 5.0, did you do an in-place upgrade, or mysqldump your data and then re-import? As replied to Sebastian's post, in-place. Try using either mysqldump or mysql-administrator to dump out your data to an .sql file. Then re-import all of your data into 5.x. You will

Re: Problem with compex query

Juan Eduardo, 1) The versions are the same. 2) What you meant with indexes for the query? 3) Im goint to try that. 4) Good Idea. Thanks. - Mensaje Original - De: Juan Eduardo Moreno [EMAIL PROTECTED] Para: Patricio A. Bruna [EMAIL PROTECTED] Cc: mysql@lists.mysql.com

Re: design choice - quite many tables

Hi, I'm working on quite big database. It consists of about 200 tables. Additionaly about 50 tables are per year (because of annual data). It means every year new 50 tables will have to appear in application. And now I have a question. Should I use separate databases for annual data (i.e.

Re: Problem with compex query

Patricio, 2) What you meant with indexes for the query? A: Create some indexes for a query in order to improve the performance. Regards, Juan Eduardo On 5/22/07, Patricio A. Bruna [EMAIL PROTECTED] wrote: Juan Eduardo, 1) The versions are the same. 2) What you meant with indexes for

Re: Upgraded to 5.0.x from 4.1.x and ORDER BY sucks!

Gmail User wrote: I had perfectly working complex queries both with LEFT JOIN and without and they were returning results in under a second. After upgrade to 5.0.x, the same queries would return results in 20-30 second range. I had a similar problem once (not related to 4.x-5.x though), it

corruption in db. myisam bad? innodb good?

hi. database is myisam, 5.8Gb, 7mil records. recently had some corruption i think due to mysqld service failure. 10 tabes were crashed. question -- how can i prevent or minimize this? Will switching to innodb help? Will converting all my queries to transactions help? thanks. -- Tim H

dump db without locking up the server

is there a safe way to dump/backup a live database without disrupting service? when i run mysqldump the whole server comes to a crawl and queries start taking 60+ seconds to complete. is there a way to make mysqldump run at low priority? worst case scenario is i miss the queries that took place

Re: Bash script array from MySQL query - HELP Please!!!

I would look at the 15th URL to see if there are specials in there that are breaking the hash somehow. On 5/22/07, Ben Benson [EMAIL PROTECTED] wrote: I'm having problems getting a monitoring script to run. I've put the troublesome bit in a separate script just to test, and it goes like

Re: dump db without locking up the server

Tim, it's a gnarly problem that most DBAs struggle with in some form or another, whether using MySQL or another database package. If you're using only MyISAM tables, MySQL's free, included 'mysqlhotcopy' script might work for you, as it's generally a bit faster than mysqldump in my experience.

InnoDB and raw devices and DRBD question

Hi ALL, I would like to use INNODB with raw devices for tablespace in the innodb_data_file_path both on MySQL 5.0.x and MySQL 5.1.x. Is it possible to use DRBD (Distributed Raw Block Device) with InnoDB and raw devices. If yes, which are the constraints ? Thanks in advance for your

Help on selecting a View with 3 Billions rows !

Hi all, I am having a big performance performance problem here, and I was wondering if you could give me some advise here. I have 2 big static tables, one with 65K rows, the other with 54K rows. I need to do a calculation for each combination of these two table rows. So what I did was to

Re: mysqldump running out out of memory

Fredrik Carlsson 写道: Hi, Yes I'm using the -q option with mysqldump, but the result is the same. This is a replicated environment and the master is running FreeBSD and the slave NetBSD and on the master which only has InnoDB tables there is no problems to run a dump but the machine is to