Re: possible join

[Peter Brawley]

Scott, sorry, my mistake, 
  SELECT price 
  FROM fedex_zones z
  INNER JOIN fedex_rates r ON z.zone=r.zone AND z.zip=94947
  WHERE r.weight = 25;

PB




Scott Haneda wrote:

  on 5/10/05 8:29 PM, Peter Brawley at [EMAIL PROTECTED] wrote:

  
  
Scott,



  ...In part, my trouble is that I need to take the resuling zone from the
first
select and use that to determine the field name.
  


  I can easily do this in my code in 2 selects, but was hoping to be able to
get the price back in just one select, if possible...
  

If you have control over the data model, it would be good to change the
structure of fedex_rates to (id int PK, zone int, weight int, price
decimal(10,2)), getting rid of the denormalised z_* columns which are causing
you problems. Then a one-stage query would just be SELECT price FROM
fedex_rates WHERE zone=8 AND weight=12.

If you're stuck with the table structure you show, you're stuck with two
queries. If these lookup tables aren't large, there's probably not much
performance to be gained from hiding the two stages inside a stored procedure,
but if you want a one-step, IMO that's the way to go.

  
  
Ok, I changed the tables around a little, I can not really do this all in
one table, since the data gets made new often by fedex, at any rate, (no pun
intended :-))...

mysql> describe fedex_zones;
+---+-+--+-+-++
| Field | Type| Null | Key | Default | Extra  |
+---+-+--+-+-++
| id| int(11) |  | PRI | NULL| auto_increment |
| zip   | char(5) |  | UNI | ||
| zone  | char(2) |  | | ||
+---+-+--+-+-++

mysql> describe fedex_rates;
++---+--+-+-++
| Field  | Type  | Null | Key | Default | Extra  |
++---+--+-+-++
| id | int(11)   |  | PRI | NULL| auto_increment |
| weight | int(11)   |  | | 0   ||
| zone   | int(11)   |  | | 0   ||
| price  | decimal(10,2) |  | | 0.00||
++---+--+-+-++

so first, I need to get the zone I am in, which is a:
SELECT zone from fedex_zones where zip = 94947
  
  
8

  
  
If the result in that case is 8, then I can
select price from fedex_rates where zone = '8' and weight = '25'

For some reason, this join is still not screaming out at me, or maybe I have
it right, and my data is in duplication, any help is appreciated.
  



No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 5/10/2005

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query to lowercase database columns names

[zzapper]

On Mon, 09 May 2005 17:32:29 +0100,  wrote:

>Hi,
>Is there a query which will "alter" a table to rename all the columns to 
>lowercase
>
>eg Price to price,  Quantity to quantity

Funny how sometimes a UseNet query is answered in minutes, and or exites a 
flurry of responses, and
othertimes like here zilch!



I've got a converted from Excel spreadsheet to mysql database, which has mixed 
case column names and
also columns beginning with a digit eg 01_name, 02_address etc what upsets PHP 
considerably.

So I'd like to have a way to generically rename all columns beginning with a 
numeric 
form ^\d.* to someprefix_&

Is it not possible to write such a query, or is this best done by Perl or PHP 
script?

-- 
zzapper
vim -c ":%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg?"
http://www.rayninfo.co.uk/tips/ vim, zsh & success tips


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: query to lowercase database columns names

[mfatene]

Hi,
this is the idea,

mysql> create table t (01_t varchar(10));
ERROR 1046 (3D000): No database selected
mysql>
mysql> use world
Database changed
mysql> create table t (01_t varchar(10));
Query OK, 0 rows affected (0.20 sec)

mysql>
mysql>
mysql> desc t
-> ;
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| 01_t  | varchar(10) | YES  | | NULL|   |
+---+-+--+-+-+---+
1 row in set (0.01 sec)

mysql> alter table t change 01_t something_01_t varchar(15);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t
-> ;
++-+--+-+-+---+
| Field  | Type| Null | Key | Default | Extra |
++-+--+-+-+---+
| something_01_t | varchar(15) | YES  | | NULL|   |
++-+--+-+-+---+
1 row in set (0.00 sec)


Scripting this query on all tables, and all columns from a perl or php script
can help you

Mathias



Selon zzapper <[EMAIL PROTECTED]>:

> On Mon, 09 May 2005 17:32:29 +0100,  wrote:
>
> >Hi,
> >Is there a query which will "alter" a table to rename all the columns to
> lowercase
> >
> >eg Price to price,  Quantity to quantity
>
> Funny how sometimes a UseNet query is answered in minutes, and or exites a
> flurry of responses, and
> othertimes like here zilch!
>
>
>
> I've got a converted from Excel spreadsheet to mysql database, which has
> mixed case column names and
> also columns beginning with a digit eg 01_name, 02_address etc what upsets
> PHP considerably.
>
> So I'd like to have a way to generically rename all columns beginning with a
> numeric
> form ^\d.* to someprefix_&
>
> Is it not possible to write such a query, or is this best done by Perl or PHP
> script?
>
> --
> zzapper
> vim -c ":%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg?"
> http://www.rayninfo.co.uk/tips/ vim, zsh & success tips
>
>
> --
> 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: query to lowercase database columns names

[zzapper]

On Wed, 11 May 2005 12:22:21 +0200,  wrote:

>Hi,
>this is the idea,
>
>mysql> create table t (01_t varchar(10));
>ERROR 1046 (3D000): No database selected
>mysql>
>mysql> use world
>Database changed
>mysql> create table t (01_t varchar(10));
>Query OK, 0 rows affected (0.20 sec)
>
>mysql>
>mysql>
>mysql> desc t
>-> ;
>+---+-+--+-+-+---+
>| Field | Type| Null | Key | Default | Extra |
>+---+-+--+-+-+---+
>| 01_t  | varchar(10) | YES  | | NULL|   |
>+---+-+--+-+-+---+
>1 row in set (0.01 sec)
>
>mysql> alter table t change 01_t something_01_t varchar(15);
>Query OK, 0 rows affected (0.17 sec)
>Records: 0  Duplicates: 0  Warnings: 0
>
>mysql> desc t
>-> ;
>++-+--+-+-+---+
>| Field  | Type| Null | Key | Default | Extra |
>++-+--+-+-+---+
>| something_01_t | varchar(15) | YES  | | NULL|   |
>++-+--+-+-+---+
>1 row in set (0.00 sec)
>
>
>Scripting this query on all tables, and all columns from a perl or php script
>can help you
>
Mathias

Yes of course I can use Perl to generate a multiple MySQL Alter script rather 
than actually doing it
via say Perl:DBI::MySQL

-- 
zzapper
vim -c ":%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg?"
http://www.rayninfo.co.uk/tips/ vim, zsh & success tips


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Solution to slow queries

[Roger Baklund]

Paul Halliday wrote:
 srcaddr   VARCHAR(15),
 dstaddr   VARCHAR(15),
Are these ip-adresses? If they are, consider using UNSIGNED INT columns
and the INET_NTOA() and INET_ATON() funtions. It will save you a lot of
space, thus increase the amount of data your hw can handle.

They are indeed ip addresses. This infomation is gathered and input
into the db via a program called flow-export (export netflows). I
intially had the column as UNSIGNED INT but it would only pick up the
first octet, so I switched to VARCHAR.
This would happen if you did not use INET_ATON() to transform the IP to 
a single integer. When you select the data later, you use INET_NTOA() to 
transform the other way:

mysql> select inet_aton('127.0.0.1');
++
| inet_aton('127.0.0.1') |
++
| 2130706433 |
++
1 row in set (0.00 sec)
mysql> select inet_ntoa(2130706433);
+---+
| inet_ntoa(2130706433) |
+---+
| 127.0.0.1 |
+---+
1 row in set (0.00 sec)
http://dev.mysql.com/doc/mysql/en/miscellaneous-functions.html >
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 1 table or 2?

[Roger Baklund]

Dave Shariff Yadallee wrote:
I am trying to set up a chem structures table.
column 1 is the key, column 2 is the description, column 3 is the structure
which is a blob.
In PHP the first 2 columns comes out as text as expected, but the
3rd I am trying to tell PHP please ignore this initially since
this is a blob.
I suggest you don't discuss this 3rd column with PHP at all...? ;)
$res = mysql_query("select col1,col2 from table");
Should I be using 1 or 2 tables?
There is no reason to use two tables to solve this problem. There could 
be other reasons why you should use two tables.

--
Roger

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

just hi

[Ezequiel Rodriguez]

well im from argentina, and today i begin using mysql, i have a lot of 
questions, first of all, is there a mysql official manual at www.mysql.com? i 
have searched but don't found it :S
I want to read something before ask  thnx :D

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Remove 1st 3 Chars

[shaun thornburgh]

Hi,
is it possible to alter fields in a column by removing the first three 
characters?

i.e. change 100123456789 to 123456789
Thanks for your help

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: just hi

[Johan Höök]

Hi Ezequiel,
see:
http://dev.mysql.com/doc/
/Johan
Ezequiel Rodriguez wrote:
well im from argentina, and today i begin using mysql, i have a lot of 
questions, first of all, is there a mysql official manual at www.mysql.com? i 
have searched but don't found it :S
I want to read something before ask  thnx :D


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Remove 1st 3 Chars

[Reinhart Viane]

Look at the substring example here
http://dev.mysql.com/doc/mysql/en/string-functions.html

Regards,
Reinhart Viane

-Oorspronkelijk bericht-
Van: shaun thornburgh [mailto:[EMAIL PROTECTED] 
Verzonden: woensdag 11 mei 2005 13:30
Aan: mysql@lists.mysql.com
Onderwerp: Remove 1st 3 Chars

Hi,

is it possible to alter fields in a column by removing the first three 
characters?

i.e. change 100123456789 to 123456789

Thanks for your help



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]




-- 
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 10/05/2005



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: DATA TYPE QUESTIONS?

[Jay Blanchard]

[snip]
  Thanks for your reply but I think you are missing my point or I am 
missing yours either way the query does not work when I substitute the 
S for a 0 in the part number as I described previously in detail.  You 
focus on 10902 which does work whether I put single quotes around same 
or not.  The problem is when I use another legitimate part number that 
has a letter in IT,i.e. 109S2, IT does not work whether I put single 
quotes around IT or not! I have tried the 'AND' 'OR' qualifiers but it 
makes no difference!  Please reread my post carefully!!
> [snip]
> IF I use the following statement:
> SELECT  *  FROM  `items`  WHERE  `item` = 10902 HAVING  `venturi_type`
> = 'universal' OR `venturi_type` = 'special';
> I get a complete and full data dump.
>
> IF I change the statement by inserting a 'letter' in this case 'S'
> instead of a 'number' in this case '0' in the 'item', i.e. from 10902
> to 109S2 I get the following error:
> #1054 - Unknown column '109S2' in 'where clause
>
> IF I change the statement by putting single quotes around '109S2' then
> I get an SQL execution completed result from phpMAdmin, but NO data
> dump!
>
> The column 'venturi_type' is the same NO MATTER what part number
'item'
> is and should display, yet it does not.
>
> Why should it make a difference wether I have a letter or a numeral in
> a part number?
> [/snip]
>
> Because the part number may be a string, not a number. Oh sure, it 
> looks
> like a number, but it is really a string. Also, the venturi_type
should
> be a conditional other than a HAVINGtry this...
>
> SELECT  *
> FROM  `items`
> WHERE  `item` = '10902' <--(note single quotes around string)
> AND (`venturi_type` = 'universal' OR `venturi_type` = 'special');
>
[/snip]
 I did read and then re-read carefully your postand the single
quotes are required when the 'S' is there because MySQL thinks that you
have specified a column namenot data. That is why you get a
successful completion of the query when the quotes are there. Since you
cannot specify a column name as a number, when you use the number minus
the 'S' you are essentially asking if the `item` is TRUE (which it is),
so therefore you get all of the data back. Make sense?

Do you have an item 10902 with a universal or special venturi? Do you
have an item 109S2 with a universal or special venturi?

You can also try

SELECT  *
FROM  `items`
WHERE  `item` = '10902'
AND `venturi_type` IN('universal','special');

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Remove 1st 3 Chars

[Johan Höök]

Hi Shaun,
I guess you could try something like:
UPDATE table SET col = RIGHT(col, LENGTH(col) - 3 ) WHERE ...
/Johan
shaun thornburgh wrote:
Hi,
is it possible to alter fields in a column by removing the first three 
characters?

i.e. change 100123456789 to 123456789
Thanks for your help


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Remove 1st 3 Chars

[Andrew Braithwaite]

Hello,

Is there any way to get MySQL to return the results of this query with
the 'fieldname' in the order listed in the in() bit?

select fieldname from tablename where fieldname in
('B4079','B4076','B4069','B4041','A4710','58282','58220','56751','56728'
,'45003','09234','04200','04035','04026');

i.e. I want row 1 to have fieldname=' B4079', row 2 to have
fieldname='B4076', etc.

Or is there any other way to list the results by the order as defined in
a list specified in the query.

Please don't reply saying that I should do this in the application layer
as that isn't an option.

Thanks for any help at all on this...

Cheers,

Andrew

MySQL, Query



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Maximum number of user variables

[Neculai Macarie]

Hi!

What is the maximum number of user variables that I can have in MySQL ? (I
need to transfer a application from one server to another and I need to
rebuild the links between tables with new id's on the new server (I'm using
auto_increment extensively), and I have a solution that involves many user
variables, around 30 000 in a single connection).

-- 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Re: query to lowercase database columns names

[Jay Blanchard]

[snip]
I've got a converted from Excel spreadsheet to mysql database, which has
mixed case column names and
also columns beginning with a digit eg 01_name, 02_address etc what
upsets PHP considerably.

So I'd like to have a way to generically rename all columns beginning
with a numeric 
form ^\d.* to someprefix_&

Is it not possible to write such a query, or is this best done by Perl
or PHP script?
[/snip]

It is best done using a programming language of some sort. I tried this
test to confirm;

SET @new_prefix='foo_';
ALTER TABLE `table` CHANGE `01_col` CONCAT(@new_prefix, `01_col`)
VARCHAR(10);

The result, if this had worked, would have been a column named
`foo_01_col`. The query throws a syntax error at the CONCAT. This is
confirmed by substituting a string for the variable in the query
(CONCAT(foo_, 01_col)).I have not tested with a subquery as my test
platform is not running 4.1

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Remove 1st 3 Chars

[Philippe Poelvoorde]

Hi !
First, could do avoir hijacking somebody else thread ?
Andrew Braithwaite wrote:
Hello,
Is there any way to get MySQL to return the results of this query with
the 'fieldname' in the order listed in the in() bit?
select fieldname from tablename where fieldname in
('B4079','B4076','B4069','B4041','A4710','58282','58220','56751','56728'
,'45003','09234','04200','04035','04026');
what about :
select fieldname from tablename where fieldname in 
('B4079','B4076','B4069','B4041','A4710','58282','58220','56751','56728'
,'45003','09234','04200','04035','04026')
Order by
'B4079'!=fieldname,
'B4076'!=fieldname,
'B4069'!=fieldname,
'B4041'!=fieldname,
'A4710'!=fieldname,
'58282'!=fieldname,
'58220'!=fieldname,
'56751'!=fieldname,
'56728'!=fieldname,
'45003'!=fieldname,
'09234'!=fieldname,
'04200'!=fieldname,
'04035'!=fieldname,
'04026'!=fieldname

(from a yesterday proposal)

i.e. I want row 1 to have fieldname=' B4079', row 2 to have
fieldname='B4076', etc.
Or is there any other way to list the results by the order as defined in
a list specified in the query.
Please don't reply saying that I should do this in the application layer
as that isn't an option.
Thanks for any help at all on this...
Cheers,
Andrew
MySQL, Query


--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Remove 1st 3 Chars

[Andy Eastham]

Andrew,

I think you'll get what you want if you add "order by fieldname desc" on the
end of your query, but that's only because the order you have specified
happens to be in reverse ascii order.

Andy

> -Original Message-
> From: Andrew Braithwaite [mailto:[EMAIL PROTECTED]
> Sent: 11 May 2005 12:53
> To: mysql@lists.mysql.com
> Subject: RE: Remove 1st 3 Chars
> 
> Hello,
> 
> Is there any way to get MySQL to return the results of this query with
> the 'fieldname' in the order listed in the in() bit?
> 
> select fieldname from tablename where fieldname in
> ('B4079','B4076','B4069','B4041','A4710','58282','58220','56751','56728'
> ,'45003','09234','04200','04035','04026');
> 
> i.e. I want row 1 to have fieldname=' B4079', row 2 to have
> fieldname='B4076', etc.
> 
> Or is there any other way to list the results by the order as defined in
> a list specified in the query.
> 
> Please don't reply saying that I should do this in the application layer
> as that isn't an option.
> 
> Thanks for any help at all on this...
> 
> Cheers,
> 
> Andrew
> 
> MySQL, Query
> 
> 
> 
> --
> 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: Re: query to lowercase database columns names

[mfatene]

Selon Jay Blanchard <[EMAIL PROTECTED]>:

> [snip]
> I've got a converted from Excel spreadsheet to mysql database, which has
> mixed case column names and
> also columns beginning with a digit eg 01_name, 02_address etc what
> upsets PHP considerably.
>
> So I'd like to have a way to generically rename all columns beginning
> with a numeric
> form ^\d.* to someprefix_&
>
> Is it not possible to write such a query, or is this best done by Perl
> or PHP script?
> [/snip]
>
> It is best done using a programming language of some sort. I tried this
> test to confirm;
>
> SET @new_prefix='foo_';
> ALTER TABLE `table` CHANGE `01_col` CONCAT(@new_prefix, `01_col`)
> VARCHAR(10);
>
> The result, if this had worked, would have been a column named
> `foo_01_col`. The query throws a syntax error at the CONCAT. This is
> confirmed by substituting a string for the variable in the query
> (CONCAT(foo_, 01_col)).I have not tested with a subquery as my test
> platform is not running 4.1
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

Hi,
now as i said, combine show tables and desc  to fetch columns array
with corresponding table name, and play the query above (PERL/DBI or PHP).

Mathias



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: possible join

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
Peter Brawley <[EMAIL PROTECTED]> writes:

> Scott, sorry, my mistake,
>   SELECT price
>   FROM fedex_zones z
>   INNER JOIN fedex_rates r ON z.zone=r.zone AND z.zip=94947
>   WHERE r.weight = 25;
> PB

Although correct, many people consider this bad style - the ON clause
of the JOIN should contain only the join condition(s). So it would be
better to say

  SELECT price
  FROM fedex_zones z
  JOIN fedex_rates r ON z.zone = r.zone
  WHERE r.weight = 25
AND z.zip = 94947


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

learning to understand compress function

[Steve Buehler]

	I am trying to figure something out about the compress function.  If I 
wanted to compress a field in a column/row and write it back to the table, 
how can I do this?  Is it possible to do it in one command? I tried:
update `conflicts` set `fname`=(SELECT compress(`fname`) FROM `conflicts` 
WHERE `conflict_id`='2') where `conflict_id`='2'

But that didn't work.  This question is more a learning thing that anything 
else.  I am not even sure what I would use the "compress" function 
for.  Maybe somebody can also give me an example of the uses for 
it..why it would be used.  Also, If I wanted to compress the `fname` 
field for every row in the database, how would that be done?

Thanks
Steve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: possible join

[SGreen]

news <[EMAIL PROTECTED]> wrote on 05/11/2005 09:09:36 AM:

> In article <[EMAIL PROTECTED]>,
> Peter Brawley <[EMAIL PROTECTED]> writes:
> 
> > Scott, sorry, my mistake,
> >   SELECT price
> >   FROM fedex_zones z
> >   INNER JOIN fedex_rates r ON z.zone=r.zone AND z.zip=94947
> >   WHERE r.weight = 25;
> > PB
> 
> Although correct, many people consider this bad style - the ON clause
> of the JOIN should contain only the join condition(s). So it would be
> better to say
> 
>   SELECT price
>   FROM fedex_zones z
>   JOIN fedex_rates r ON z.zone = r.zone
>   WHERE r.weight = 25
> AND z.zip = 94947
> 
> 

I agree, in principle, with the comment about preferred style. However, 
certain combinations of ON conditions can trigger the use of different 
indexes by the optimizer based on the conditions being matched. The final 
query results may be the same but if you want the best possible 
performance out of your JOIN queries, sometimes you need to break the 
style rule.

I list two other "correct" if not properly styled ways of writing the same 
query below. Based on the optimizer and its choice of indexes used during 
the JOIN processing phase of their executions, these may have (slightly) 
different performance characteristics.

SELECT price
FROM fedex_zones z
JOIN fedex_rates r 
ON z.zone = r.zone
AND r.weight = 25
WHERE z.zip = 94947

SELECT price
FROM fedex_zones z
JOIN fedex_rates r 
ON z.zone = r.zone
AND r.weight = 25
AND z.zip = 94947

As I stress, these should all produce the same results. The one that will 
perform "best" depends on both the data composition and the indexes on the 
tables. The EXPLAIN command will expose any differences in the execution 
plans for each of these queries. If none of them show a difference in 
their execution plans, then each of them is interchangeable for the other. 
That means that you cannot make a decision of which form to use based on 
performance. The form with the constant conditions in the WHERE clause 
(the one from the last post) would then be the preferred way of writing 
this query when performance is not an issue.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Remove 1st 3 Chars

[Roger Baklund]

Andrew Braithwaite wrote:
Is there any way to get MySQL to return the results of this query with
the 'fieldname' in the order listed in the in() bit?
select fieldname from tablename where fieldname in
('B4079','B4076','B4069','B4041','A4710','58282','58220','56751','56728'
,'45003','09234','04200','04035','04026');
Yes, try the FIND_IN_SET() or the FIELD() function:
select fieldname
  from tablename
  where fieldname in
  ('B4079','B4076','B4069','B4041',
   'A4710','58282','58220','56751','56728',
   '45003','09234','04200','04035','04026')
  order by
  FIELD(fieldname,'B4079','B4076','B4069','B4041',
   'A4710','58282','58220','56751','56728',
   '45003','09234','04200','04035','04026');
http://dev.mysql.com/doc/mysql/en/string-functions.html >
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: learning to understand compress function

[SGreen]

Steve Buehler <[EMAIL PROTECTED]> wrote on 05/11/2005 09:36:44 AM:

>I am trying to figure something out about the compress function.  If 
I 
> wanted to compress a field in a column/row and write it back to the 
table, 
> how can I do this?  Is it possible to do it in one command? I tried:
> update `conflicts` set `fname`=(SELECT compress(`fname`) FROM 
`conflicts` 
> WHERE `conflict_id`='2') where `conflict_id`='2'
> 
> But that didn't work.  This question is more a learning thing that 
anything 
> else.  I am not even sure what I would use the "compress" function 
> for.  Maybe somebody can also give me an example of the uses for 
> it..why it would be used.  Also, If I wanted to compress the `fname` 

> field for every row in the database, how would that be done?
> 
> Thanks
> Steve
> 
> 

Depending on your version of MySQL, that UPDATE statement won't work at 
all ( 1:it uses a subquery AND 2:the subquery references the target table 
[separate issues]). Since you want to duplicate the value from one field 
to another within the same row(s), this is how you would write the UPDATE 
(it's not as complex as you were making it out to be):

UPDATE `conflicts`
SET `fname` = COMPRESS(`fname`)
WHERE `conflict_id`=2;

According to http://dev.mysql.com/doc/mysql/en/string-functions.html, 
COMPRESS() wasn't added until 4.1.1 and the server must have been compiled 
with a compression library. Are you sure you are meeting both of those 
conditions in your environment?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: learning to understand compress function

[Jigal van Hemert]

From: "Steve Buehler"

> I am trying to figure something out about the compress function.  If I
> wanted to compress a field in a column/row and write it back to the table,
> how can I do this?  Is it possible to do it in one command? I tried:
> update `conflicts` set `fname`=(SELECT compress(`fname`) FROM `conflicts`
> WHERE `conflict_id`='2') where `conflict_id`='2'
>

As you can read at http://dev.mysql.com/doc/mysql/en/string-functions.html
you need MySQL 4.1.1 or later that has been compiled with a compression
library such as zlib.

You can check this by
SELECT COMPRESS('an example string to test the compress function');

If this produces NULL than MySQL has not been compiled with a compression
library.

To 'convert' a whole table all you need to do is:
UPDATE `conflicts` SET `fname` = COMPRESS(`fname`);

If you do selects in the future you have to use the UNCOMPRESS() function:

SELECT UNCOMPRESS(`fname`) AS `fname`, `other_col`, ... FROM `conflicts`
WHERE `conflict_id` < 5;

As you can imagine it is not efficient to use compress() for very short
strings. It is also not advisable to search on a column that is compressed,
because that would require an uncompress() on each record before you can do
any compare or other operation.

It's best used for relatively large text fields which you only use to store
information that will never be searched.

Regards, Jigal.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

write query question

[Jerry Swanson]

How to select 5 records(including username, first_name, last_name,
contact) for each user in the database?

table user(
id,
username,
first_name,
last_name
)

contact(
id
id_user //id from user table 
name
)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: repetition-operator operand invalid

[Gleb Paharenko]

Hello.



Use 5.0.4. Please, could you send an example of query (with pattern)

for words with sensitive characters which worked in 5.0.2 and doesn't

work in 5.0.4?





Scott Klarenbach <[EMAIL PROTECTED]> wrote:

> I've upgraded from 5.0.2 to 5.0.3 Beta, and now there is a glitch in

> one of my regular expression queries.

> 

> The expression works like this: a query for 'search' returns true for

> a matching 'search' field, but, querying 's$$#e%ar^c)(h' must also

> return true for a 'search' field.  In other words, I need to pad every

> letter of the search string and tell it to allow any number of

> non-alphanumeric characters.

> 

> Here is the expression I'm using below, for the term SEARCH:

> 

> '[^a-zA-Z0-9]*S[^a-zA-Z0-9]*E[^a-zA-Z0-9]*A[^a-zA-Z0-9]*R[^a-zA-Z0-9]*C[^a-=

> zA-Z0-9]*H[^a-zA-Z0-9]*'

> 

> As I said, it worked fine until I upgraded.  Is it to do with the

> double parsing MySQL does with REGEXP's?

> 

> In some instances, I get emtpy result set where I used to get a match,

> and in other instances, I get a 'repetition-operator operand invalid'.

> Is there a simple way I can prevent errors from a search string that

> contains ^*$ or other sensitive expression characters?  addslashes()

> in php maybe?

> 

> Any help is appreciated.

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: inserting special characters

[Gleb Paharenko]

Hello.



I see nothing wrong in your settings. The problem could be that

special characters in windows have different codes than in latin1,

as was mentioned by Dusan Pavlica. When you'll debug this issue

try to keep the table's and column's encoding the same as in SET NAMES,

you could lose some information during conversions.







Javier Ballesteros Correa <[EMAIL PROTECTED]> wrote:

> Hi, Mr. Paharenko,

> 

> Here are the results:

> 

> character_set_client | latin1

> character_set_connection | latin1

> character_set_database   | latin1

> character_set_results| latin1

> character_set_server | latin1

> character_set_system | utf8

> 

> character_sets_dir   | C:\Archivos de

> programa\MySQL\MySQL Server 4.1\share\charsets/

> 

> collation_connection | latin1_swedish_ci

> collation_database   | latin1_swedish_ci

> collation_server | latin1_swedish_ci

> 

> 

> Create Table: CREATE TABLE `datos` (

> `id_user` tinyint(3) unsigned NOT NULL auto_increment,

> `nombre` varchar(55) NOT NULL,

> `dni` varchar(10) NOT NULL,

> `nacimiento` date NOT NULL,

> `direccion` varchar(255) NOT NULL,

> `telefono` varchar(9) NOT NULL,

> `desde` date NOT NULL,

> `hasta` date NOT NULL,

> `subcontrata` enum('No','Correos','Vanyera'),

> PRIMARY KEY  (`id_user`)

> ) ENGINE=InnoDB DEFAULT CHARSET=latin1

> 

> I have tried with the latin1_spanish_ci collation, but

> the problem remains.

> 

> 

> 

> 

>

> __ 

> Renovamos el Correo Yahoo!: $250 MB GRATIS! 

> Nuevos servicios, m$s seguridad 

> http://correo.yahoo.es

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: write query question

[mfatene]

Hi,
try this,

select username, first_name,last_name, name as contact
from user,contact
where user.id=contact.user_id
order by username;

Mathias


Selon Jerry Swanson <[EMAIL PROTECTED]>:

> How to select 5 records(including username, first_name, last_name,
> contact) for each user in the database?
>
> table user(
>   id,
>   username,
>   first_name,
>   last_name
> )
>
> contact(
>   id
>   id_user //id from user table
>   name
> )
>
> --
> 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: write query question

[Jerry Swanson]

I need 5 records per user.

On 5/11/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Hi,
> try this,
> 
> select username, first_name,last_name, name as contact
> from user,contact
> where user.id=contact.user_id
> order by username;
> 
> Mathias
> 
> Selon Jerry Swanson <[EMAIL PROTECTED]>:
> 
> > How to select 5 records(including username, first_name, last_name,
> > contact) for each user in the database?
> >
> > table user(
> >   id,
> >   username,
> >   first_name,
> >   last_name
> > )
> >
> > contact(
> >   id
> >   id_user //id from user table
> >   name
> > )
> >
> > --
> > 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: write query question

[Eric Jensen]

So you want 5 contacts for every user?  Try this:

SELECT COUNT(c.id) AS count, u.username,  u.first_name,  u.last_name, 
c.name
FROM user AS u, contact AS c
WHERE u.id = c.id_user
GROUP BY c.id_user
HAVING count <= 5

Jerry Swanson wrote:

>How to select 5 records(including username, first_name, last_name,
>contact) for each user in the database?
>
>table user(
>   id,
>   username,
>   first_name,
>   last_name
>)
>
>contact(
>   id
>   id_user //id from user table 
>   name
>)
>
>  
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Multi-Language setup

[Mike Blezien]

Hello,
we are working on a project that will be using multiple languages to be 
displayed and stored in a database, for producing various text strings in 
english and the language associted with it's english counter part.

What do we need to consider before creating the database and tables that will be 
used to store this type of data with various language being stored due to the 
various unique characters used in many languages.

TIA,
--
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com/
Tel: 1.712.395.0670
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Multi-Language setup

[Mike Blezien]

Hello,
we are working on a project that will be using multiple languages to be 
displayed and stored in a database, for producing various text strings in 
english and the language associted with it's english counter part.

What do we need to consider before creating the database and tables that will be 
used to store this type of data with various language being stored due to the 
various unique characters used in many languages.

TIA,
--
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://thunder-rain.com/
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: possible join

[Peter Brawley]

>Although correct, many people consider this bad style - the ON
clause
>of the JOIN should contain only the join condition(s). So it would
be
>better to say

Yes indeed but here the 'zip' condition is in the join for the
possibility that the constant zip condition could speed up the join.
See Shawn's excellent response.

PB

-

Harald Fuchs wrote:

  In article <[EMAIL PROTECTED]>,
Peter Brawley <[EMAIL PROTECTED]> writes:

  
  
Scott, sorry, my mistake,
  SELECT price
  FROM fedex_zones z
  INNER JOIN fedex_rates r ON z.zone=r.zone AND z.zip=94947
  WHERE r.weight = 25;
PB

  
  
Although correct, many people consider this bad style - the ON clause
of the JOIN should contain only the join condition(s). So it would be
better to say

  SELECT price
  FROM fedex_zones z
  JOIN fedex_rates r ON z.zone = r.zone
  WHERE r.weight = 25
AND z.zip = 94947


  



No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.11.8 - Release Date: 5/10/2005

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Dates after version upgrade

[Jeff Hinds]

After I upgraded from 4.0.16 to 4.1.11 I have found that the date fields are
being pre-formatted and are causing problems to back-ground programs. Is
there a configuration or session setting that will force the date formatting
to the standard in 4.0 and earlier to correct the program issues?

 

 

Thanks,

 





 

  Information Technology Division

 



Jeff Hinds, ocp
Database Specialist 

Middle Tennessee State University
001 Cope Administration Building
Murfreesboro, TN 37132 


  [EMAIL PROTECTED]


tel: 
fax: 

(615) 898-5719
(615) 898-5720 

 

 

RE: Field property question!

[Matt Babineau]

Yes I do enjoy every day in which the manual makes my life easier. I guess I
didn't work my question specifically enough however. I was actually looking
for a way to 'ALTER' the table so that my State field data is always upper.
In MSSQL you can apply a function to a field, which will then run that
function over the data each time something new gets added. For instance you
could put in Now() in the default, and the default date would appear if you
added a row That's the idea.

-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 10, 2005 10:58 AM
To: Matt Babineau; mysql@lists.mysql.com
Subject: RE: Field property question!

[snip]
Can I setup a table so that no matter how data is entered into it (web
form,
command line) The data in one of the columns ALWAYS gets converted to
uppercase? I remeber MSSQL had this feature of being able to apply a
function to a field in its configuration.
[/snip]

The manual, it is amazing no?

http://dev.mysql.com/doc/mysql/en/string-functions.html 

UPPER()

INSERT INTO `table` (`colFoo`)
VALUES (UPPER('myData'));

-- 
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: Field property question!

[Jay Blanchard]

[snip]
Yes I do enjoy every day in which the manual makes my life easier. I
guess I
didn't work my question specifically enough however. I was actually
looking
for a way to 'ALTER' the table so that my State field data is always
upper.
In MSSQL you can apply a function to a field, which will then run that
function over the data each time something new gets added. For instance
you
could put in Now() in the default, and the default date would appear if
you
added a row That's the idea.
[/snip]

As stated before you would need a trigger. IIRC triggers will be
available in 5.n

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: just hi

[Nestor Florez]

Ezequiel,

Tambien hay una lista de mysql en Espagnol.

:-)

-Original Message-
From: Johan H��k <[EMAIL PROTECTED]>
Sent: May 11, 2005 4:35 AM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: Re: just hi

Hi Ezequiel,
see:
http://dev.mysql.com/doc/

/Johan

Ezequiel Rodriguez wrote:
> well im from argentina, and today i begin using mysql, i have a lot of 
> questions, first of all, is there a mysql official manual at www.mysql.com? i 
> have searched but don't found it :S
> I want to read something before ask  thnx :D
> 





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Maximum number of user variables

[Eric Bergen]

Not that I'm aware of. What type of conversions are you doing that you 
need 30,000 use vars? An easy solution would be to try it and find out :)

-Eric
Neculai Macarie wrote:
Hi!
What is the maximum number of user variables that I can have in MySQL ? (I
need to transfer a application from one server to another and I need to
rebuild the links between tables with new id's on the new server (I'm using
auto_increment extensively), and I have a solution that involves many user
variables, around 30 000 in a single connection).
 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Dates after version upgrade

[Eric Bergen]

The default timestamp display format changed between mysql 4.0 and 4.1
The easy fix is to add + 0 to all timestamp fields in selects. Like
this: select my_ts + 0 from t;
You should always read the upgrade notes in the manual. This is very well 
documented:
http://dev.mysql.com/doc/mysql/en/upgrading-from-4-0.html
Jeff Hinds wrote:
After I upgraded from 4.0.16 to 4.1.11 I have found that the date fields are
being pre-formatted and are causing problems to back-ground programs. Is
there a configuration or session setting that will force the date formatting
to the standard in 4.0 and earlier to correct the program issues?


Thanks,




 Information Technology Division


Jeff Hinds, ocp
Database Specialist 

Middle Tennessee State University
001 Cope Administration Building
Murfreesboro, TN 37132 

 [EMAIL PROTECTED]
tel: 
fax: 

(615) 898-5719
(615) 898-5720 



 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Multi-Language setup

[Gleb Paharenko]

Hello.



Unicode is usually used in such cases.



Mike Blezien <[EMAIL PROTECTED]> wrote:

> Hello,

> 

> we are working on a project that will be using multiple languages to be 

> displayed and stored in a database, for producing various text strings in 

> english and the language associted with it's english counter part.

> 

> What do we need to consider before creating the database and tables that will 
> be 

> used to store this type of data with various language being stored due to the 

> various unique characters used in many languages.

> 

> TIA,

> 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   <___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Multi-Language setup

[Mike Blezien]

Gleb Paharenko wrote:
Hello.
Unicode is usually used in such cases.
Mike Blezien <[EMAIL PROTECTED]> wrote:
Hello,
we are working on a project that will be using multiple languages to be 
displayed and stored in a database, for producing various text strings in 
english and the language associted with it's english counter part.

What do we need to consider before creating the database and tables that will be 
used to store this type of data with various language being stored due to the 
various unique characters used in many languages.

Unfortunately, if I am reading the docs correctly, this is only support in MySQL 
4.1 and older version do not support changing or assigning character sets. or am 
I wrong on this ??

Thx's
--
Mike(mickalo)Blezien
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: learning to understand compress function

[Steve Buehler]

At 08:50 AM 5/11/2005, you wrote:
Steve Buehler <[EMAIL PROTECTED]> wrote on 05/11/2005 09:36:44 AM:
>I am trying to figure something out about the compress function.  If I
> wanted to compress a field in a column/row and write it back to the table,
> how can I do this?  Is it possible to do it in one command? I tried:
> update `conflicts` set `fname`=(SELECT compress(`fname`) FROM `conflicts`
> WHERE `conflict_id`='2') where `conflict_id`='2'
>
> But that didn't work.  This question is more a learning thing that 
anything
> else.  I am not even sure what I would use the "compress" function
> for.  Maybe somebody can also give me an example of the uses for
> it..why it would be used.  Also, If I wanted to compress the `fname`
> field for every row in the database, how would that be done?

Depending on your version of MySQL, that UPDATE statement won't work at 
all ( 1:it uses a subquery AND 2:the subquery references the target table 
[separate issues]). Since you want to duplicate the value from one field 
to another within the same row(s), this is how you would write the UPDATE 
(it's not as complex as you were making it out to be):

UPDATE `conflicts`
SET `fname` = COMPRESS(`fname`)
WHERE `conflict_id`=2;
According to http://dev.mysql.com/doc/mysql/en/string-functions.html, 
COMPRESS() wasn't added until 4.1.1 and the server must have been compiled 
with a compression library. Are you sure you are meeting both of those 
conditions in your environment?
hmmm.  Ok, that works.  I guess from your and Jigal's help, I take it that 
it is really only beneficial to use compress when you have a large text or 
blob field, need to save disk space and aren't going to be doing a search 
of that field.

Thanks Shawn & Jigal
Steve
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ERROR 1111 (HY000): Invalid use of group function

[shaun thornburgh]

Hi,
I keep getting thw following error when i include this line:
AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
mysql> SELECT PRACT_NUMBER,
   -> COUNT(SUBSTRING(PRACT_NUMBER, 1, 4))
   -> FROM CSV_Upload_Data
   -> WHERE CSV_File = 'ICS'
   -> AND CHAR_LENGTH(PRACT_NUMBER) > 4
   -> AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
   -> AND Booking_ID = 6030
   -> GROUP BY PRACT_NUMBER;
ERROR  (HY000): Invalid use of group function
mysql>
Can anyone tell me why this is happening?
Thanks for your advice

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ERROR 1111 (HY000): Invalid use of group function

[Paul DuBois]

At 18:36 + 5/11/05, shaun thornburgh wrote:
Hi,
I keep getting thw following error when i include this line:
AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
mysql> SELECT PRACT_NUMBER,
   -> COUNT(SUBSTRING(PRACT_NUMBER, 1, 4))
   -> FROM CSV_Upload_Data
   -> WHERE CSV_File = 'ICS'
   -> AND CHAR_LENGTH(PRACT_NUMBER) > 4
   -> AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
   -> AND Booking_ID = 6030
   -> GROUP BY PRACT_NUMBER;
ERROR  (HY000): Invalid use of group function
mysql>
Can anyone tell me why this is happening?
Because you cannot use aggregate functions in a WHERE clause.
WHERE determines which rows to select.
Aggregate values are calculated from the selected rows.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ERROR 1111 (HY000): Invalid use of group function

[Stefan Kuhn]

I would guess it's because you can't use an aggregate function in where, but 
only in having. So use select  group by PRACT_NUMBER having 
COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1.
The reason is that where is applied before the count is done, whereas having 
after that. And you can't select by something which you will know after you 
have done the select.
Stefan

Am Wednesday 11 May 2005 20:36 schrieb shaun thornburgh:
> Hi,
>
> I keep getting thw following error when i include this line:
>
> AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
>
> mysql> SELECT PRACT_NUMBER,
> -> COUNT(SUBSTRING(PRACT_NUMBER, 1, 4))
> -> FROM CSV_Upload_Data
> -> WHERE CSV_File = 'ICS'
> -> AND CHAR_LENGTH(PRACT_NUMBER) > 4
> -> AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
> -> AND Booking_ID = 6030
> -> GROUP BY PRACT_NUMBER;
> ERROR  (HY000): Invalid use of group function
> mysql>
>
> Can anyone tell me why this is happening?
>
> Thanks for your advice

-- 
Stefan Kuhn M. A.
Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de)
ZÃlpicher Str. 47, 50674 Cologne
Tel: +49(0)221-470-7428   Fax: +49 (0) 221-470-7786
My public PGP key is available at http://pgp.mit.edu

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Union Performance Question

[Dan Salzer]

Hi everyone,

I have a question regarding the performance of UNION queries:

I need to do a full-text search against a large number of rows. Is it
faster to have one table with 10,000,000 text rows and perform one
full-text search. Or, am I better off having 10 smaller more managable
tables and performing UNION'ed full-text searches against them.

A merge table seems like it would be the ideal solution, but the
limitation on full-text indexing prohibits it.

Thanks!

-Dan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ERROR 1111 (HY000): Invalid use of group function

[Michael Stassen]

Paul DuBois wrote:
At 18:36 + 5/11/05, shaun thornburgh wrote:
Hi,
I keep getting thw following error when i include this line:
AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
mysql> SELECT PRACT_NUMBER,
   -> COUNT(SUBSTRING(PRACT_NUMBER, 1, 4))
   -> FROM CSV_Upload_Data
   -> WHERE CSV_File = 'ICS'
   -> AND CHAR_LENGTH(PRACT_NUMBER) > 4
   -> AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
   -> AND Booking_ID = 6030
   -> GROUP BY PRACT_NUMBER;
ERROR  (HY000): Invalid use of group function
mysql>
Can anyone tell me why this is happening?
Because you cannot use aggregate functions in a WHERE clause.
WHERE determines which rows to select.
Aggregate values are calculated from the selected rows.
so you have to move this condition to the HAVING clause:
  SELECT PRACT_NUMBER,
  COUNT(SUBSTRING(PRACT_NUMBER, 1, 4))
  FROM CSV_Upload_Data
  WHERE CSV_File = 'ICS'
  AND CHAR_LENGTH(PRACT_NUMBER) > 4
  AND COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1
  AND Booking_ID = 6030
  GROUP BY PRACT_NUMBER
  HAVING COUNT(SUBSTRING(PRACT_NUMBER, 1, 4)) > 1;
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Union Performance Question

[Dathan Pattishall]

Use 10 smaller tables and perform a union. It's faster to look up in
smaller tables then larger ones-generally. Additionally more of the
key_buffer can be used for tables with the most hits over the tables
with less hits, making the lookup sustain speed over time.

 

> -Original Message-
> From: Dan Salzer [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, May 11, 2005 11:46 AM
> To: mysql@lists.mysql.com
> Subject: Union Performance Question
> 
> Hi everyone,
> 
> I have a question regarding the performance of UNION queries:
> 
> I need to do a full-text search against a large number of 
> rows. Is it faster to have one table with 10,000,000 text 
> rows and perform one full-text search. Or, am I better off 
> having 10 smaller more managable tables and performing 
> UNION'ed full-text searches against them.
> 
> A merge table seems like it would be the ideal solution, but 
> the limitation on full-text indexing prohibits it.
> 
> Thanks!
> 
> -Dan
> 
> --
> 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: Opteron HOWTO?!

[Kevin Burton]

Dathan Pattishall wrote:
Are you using NPTL?
   

No that sucks we use the other one. Can't make a static build with NPTL.
What type of performance boost are you getting from running a static build.
Kevin
--
Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
See irc.freenode.net #rojo if you want to chat.

Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
  Kevin A. Burton, Location - San Francisco, CA
 AIM/YIM - sfburtonator,  Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Opteron HOWTO?!

[Kevin Burton]

Dathan Pattishall wrote:
We do about 70K qps at peak for about 1 Billion Queries per day (only on
30 servers BOOYA). So, it's pretty stable.
 

Also... based on my math.. this yields ~ 2300 qps per MySQL box...  
which is pretty good.

Kevin
--
Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
See irc.freenode.net #rojo if you want to chat.

Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
  Kevin A. Burton, Location - San Francisco, CA
 AIM/YIM - sfburtonator,  Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SATA vs SCSI

[Kevin Burton]

Were kicking around using SATA drives in software RAID0 config.  

The price diff is significant.  You can also get SATA drives in 10k RPM 
form now.,

Kevin
--
Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
See irc.freenode.net #rojo if you want to chat.

Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
  Kevin A. Burton, Location - San Francisco, CA
 AIM/YIM - sfburtonator,  Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SATA vs SCSI

[Brent Baisley]

Is there a question in there or are you just making a statement?
I'll make a statement myself.
The big difference between ATA and SCSI is command queueing. That's 
really where the performance difference comes from. Basically, command 
queueing means the drive has some intelligence about handling 
instructions and data. The drive can optimize the reads and writes 
without help from the OS. Command queueing is now available on some 
SATA (and ATA I think) drives, which would be more important than RPM. 
Unless of course, you deal with huge files like video or graphics, then 
command queueing isn't so important.

Personally, it think a 4 drive SATA raid array would probably be as 
fast as a two drive scsi array and still be cheaper.

On May 11, 2005, at 3:29 PM, Kevin Burton wrote:
Were kicking around using SATA drives in software RAID0 config.
The price diff is significant.  You can also get SATA drives in 10k 
RPM form now.,

Kevin
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Opteron HOWTO?!

[Dathan Pattishall]

DVP

Dathan Vance Pattishall http://www.friendster.com

 

> -Original Message-
> From: Kevin Burton [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, May 11, 2005 12:10 PM
> To: Dathan Pattishall
> Cc: [EMAIL PROTECTED]; Jochem van Dieten; mysql@lists.mysql.com
> Subject: Re: Opteron HOWTO?!
> 
> Dathan Pattishall wrote:
> 
> >>Are you using NPTL?
> >>
> >>
> >No that sucks we use the other one. Can't make a static 
> build with NPTL.
> >
> What type of performance boost are you getting from running a 
> static build.

5-7% with the security knowing that if another package updates a shared
lib mySQL will not crash.


> 
> Kevin
> 
> -- 
> 
> 
> Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
> See irc.freenode.net #rojo if you want to chat.
> 
> Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
> 
>Kevin A. Burton, Location - San Francisco, CA
>   AIM/YIM - sfburtonator,  Web - http://peerfear.org/ GPG 
> fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 
> 
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Opteron HOWTO?!

[Dathan Pattishall]

On some boxes we do more. Some we do less.

DVP

Dathan Vance Pattishall http://www.friendster.com

 

> -Original Message-
> From: Kevin Burton [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, May 11, 2005 12:24 PM
> To: Dathan Pattishall
> Cc: Jochem van Dieten; mysql@lists.mysql.com
> Subject: Re: Opteron HOWTO?!
> 
> Dathan Pattishall wrote:
> 
> >We do about 70K qps at peak for about 1 Billion Queries per 
> day (only 
> >on 30 servers BOOYA). So, it's pretty stable.
> >
> >  
> >
> Also... based on my math.. this yields ~ 2300 qps per MySQL box...  
> which is pretty good.
> 
> Kevin
> 
> -- 
> 
> 
> Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
> See irc.freenode.net #rojo if you want to chat.
> 
> Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
> 
>Kevin A. Burton, Location - San Francisco, CA
>   AIM/YIM - sfburtonator,  Web - http://peerfear.org/ GPG 
> fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 
> 
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SATA vs SCSI

[Dathan Pattishall]

Forget using drives all together for heavy hit applications.

Build data that can fit on a ram Drive (8GB) then your able to do 20K
qps. 

For instance, have a main master that holds a majority of tables call it
MASTER. Then a sub master that holds the tables which you desire to run
out of memory, call it SUBMASTER. Each slave connected to SUBMASTER then
does a LOAD DATA FROM MASTER on startup based on

init_file=

Defined in the my.cnf file

Loading 2GB of data across the network on a GigE is very fast. So now
you have the ability to handle 20K qps on a single box (assuming the box
is a 4GB X86 Opteron with 2 processors and the main lookup is on a
primary key) and also have redundancy with real time replication.




For instance 

I have 5 servers with 4 GB of ram - the table that displays user names
which is seen on friendster only takes 1.6 G of memory. All DB access is
real time producing 3000 qps during peak per server where one server can
handle the site, since that query type benchmarks for 2 qps. More
then enough head room to scale since I can LB the reads across the 5
boxes. The front end will fall over before the backend - Easy cheap
solution that can handle a crap load of load.


 

DVP

Dathan Vance Pattishall http://www.friendster.com

 

> -Original Message-
> From: Kevin Burton [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, May 11, 2005 12:30 PM
> To: mysql@lists.mysql.com
> Subject: SATA vs SCSI
> 
> Were kicking around using SATA drives in software RAID0 config.  
> 
> The price diff is significant.  You can also get SATA drives 
> in 10k RPM form now.,
> 
> Kevin
> 
> -- 
> 
> 
> Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
> See irc.freenode.net #rojo if you want to chat.
> 
> Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
> 
>Kevin A. Burton, Location - San Francisco, CA
>   AIM/YIM - sfburtonator,  Web - http://peerfear.org/ GPG 
> fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 
> 
> 
> --
> 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]

insert...select only inserts first record

[Andy Pieters]

Hi all

I want to 'copy' the contents of the table 'cart', where userid=... to the 
table pending_cart.

Here is some example data for the table 'cart'
++++-+
| id | userid | prodid | qty |
++++-+
| 25 |123 | 15 |   1 |
| 23 |124 | 14 |   3 |
| 22 |124 | 10 |   0 |
|  4 |121 | 14 |   5 |
| 21 |124 | 13 |   6 |
| 18 |123 | 13 |   5 |
| 12 |121 | 10 |   2 |
| 16 |121 | 13 |   7 |
++++-+


insert into `pending_cart`
(`pendingid`, 
 `prodid`, 
 `qty`)
select `cart`.`userid`,
 `cart`.`prodid`,
 `cart`.`qty`
from `cart` 
where `cart`.`userid`='123';

Query OK, 1 row affected (0.03 sec)
Records: 2  Duplicates: 1  Warnings: 0

When I look at pending_cart after that, 

++---++-+
| id | pendingid | prodid | qty |
++---++-+
|  0 |   123 | 15 |   1 |
++---++-+
1 row in set (0.00 sec)

I see only one record that has been copied altough more records with 
userid=123 exist in the table 'cart'


Anybody have any ideas where I went wrong?



Thank you for your help.


With kind regards



Andy
-- 
Registered Linux User Number 379093
-- --BEGIN GEEK CODE BLOCK-
Version: 3.1
GAT/O/>E$ d-(---)>+ s:(+)>: a--(-)>? C$(+++) UL>$ P-(+)>++
L+++>$ E---(-)@ W+++>+++$ !N@ o? !K? W--(---) !O !M- V-- PS++(+++)
PE--(-) Y+ PGP++(+++) t+(++) 5-- X++ R*(+)@ !tv b-() DI(+) D+(+++) G(+)
e>$@ h++(*) r-->++ y--()>
-- ---END GEEK CODE BLOCK--
--
Check out these few php utilities that I released
 under the GPL2 and that are meant for use with a 
 php cli binary:
 
 http://www.vlaamse-kern.com/sas/
--

--


pgpsJryAtBrzT.pgp
Description: PGP signature

Re: Union Performance Question

[Eric Bergen]

Use one big table. A merge table will run the same query over all 10 
tables. The key buffer is filled from the top down so if you have a key 
buffer that looks like this:
   a
  /  \
 /\
   b  c
  /  \/  \
de fg

Almost all queries for that index will be able to use the buffer for 'a' 
and 'b'. It's not until they get things very narrowed down that you have 
to actually hit the index files for specific leaves. Creating ten small 
tables creates ten duplicate 'a' and 'b' sections which polutes the key 
buffer.

-Eric
Dathan Pattishall wrote:
Use 10 smaller tables and perform a union. It's faster to look up in
smaller tables then larger ones-generally. Additionally more of the
key_buffer can be used for tables with the most hits over the tables
with less hits, making the lookup sustain speed over time.

 

-Original Message-
From: Dan Salzer [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, May 11, 2005 11:46 AM
To: mysql@lists.mysql.com
Subject: Union Performance Question

Hi everyone,
I have a question regarding the performance of UNION queries:
I need to do a full-text search against a large number of 
rows. Is it faster to have one table with 10,000,000 text 
rows and perform one full-text search. Or, am I better off 
having 10 smaller more managable tables and performing 
UNION'ed full-text searches against them.

A merge table seems like it would be the ideal solution, but 
the limitation on full-text indexing prohibits it.

Thanks!
-Dan
--
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: SATA vs SCSI

[Kevin Burton]

Dathan Pattishall wrote:
Forget using drives all together for heavy hit applications.
Build data that can fit on a ram Drive (8GB) then your able to do 20K
 

Not everyone can run in this config...  We have way more data than we 
can casually story in memory. It would just be cost prohibitive.

Memory tables in some situations can be a good thing though but mostly 
when they're overview tables.

Also.. if you have a high cache hit rate you can effectively have memory 
tables (in theory at least).  I just haven't seen anywhere near 20k qps.

Kevin
--
Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
See irc.freenode.net #rojo if you want to chat.

Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
  Kevin A. Burton, Location - San Francisco, CA
 AIM/YIM - sfburtonator,  Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: insert...select only inserts first record

[SGreen]

Andy Pieters <[EMAIL PROTECTED]> wrote on 05/11/2005 04:41:05 PM:

> Hi all
> 
> I want to 'copy' the contents of the table 'cart', where userid=... to 
the 
> table pending_cart.
> 
> Here is some example data for the table 'cart'
> ++++-+
> | id | userid | prodid | qty |
> ++++-+
> | 25 |123 | 15 |   1 |
> | 23 |124 | 14 |   3 |
> | 22 |124 | 10 |   0 |
> |  4 |121 | 14 |   5 |
> | 21 |124 | 13 |   6 |
> | 18 |123 | 13 |   5 |
> | 12 |121 | 10 |   2 |
> | 16 |121 | 13 |   7 |
> ++++-+
> 
> 
> insert into`pending_cart`
>   (`pendingid`, 
>`prodid`, 
>`qty`)
>   select `cart`.`userid`,
>   `cart`.`prodid`,
>   `cart`.`qty`
>from `cart` 
>where `cart`.`userid`='123';
> 
> Query OK, 1 row affected (0.03 sec)
> Records: 2  Duplicates: 1  Warnings: 0
> 
> When I look at pending_cart after that, 
> 
> ++---++-+
> | id | pendingid | prodid | qty |
> ++---++-+
> |  0 |   123 | 15 |   1 |
> ++---++-+
> 1 row in set (0.00 sec)
> 
> I see only one record that has been copied altough more records with 
> userid=123 exist in the table 'cart'
> 
> 
> Anybody have any ideas where I went wrong?
> 
> 
> 
> Thank you for your help.
> 
> 
> With kind regards
> 
> 
> 
> Andy
> -- 
> Registered Linux User Number 379093
> -- --BEGIN GEEK CODE BLOCK-
> Version: 3.1
> GAT/O/>E$ d-(---)>+ s:(+)>: a--(-)>? C$(+++) UL>$ P-(+)>++
> L+++>$ E---(-)@ W+++>+++$ !N@ o? !K? W--(---) !O !M- V-- PS++(+++)
> PE--(-) Y+ PGP++(+++) t+(++) 5-- X++ R*(+)@ !tv b-() DI(+) D+(+++) G(+)
> e>$@ h++(*) r-->++ y--()>
> -- ---END GEEK CODE BLOCK--
> --
> Check out these few php utilities that I released
>  under the GPL2 and that are meant for use with a 
>  php cli binary:
> 
>  http://www.vlaamse-kern.com/sas/
> --
> 
> --
> [attachment "attfzmu4.dat" deleted by Shawn Green/Unimin] 

This is what you posted as the results of your test INSERT...SELECT. Look 
at it again (carefully this time):

> Query OK, 1 row affected (0.03 sec)
> Records: 2  Duplicates: 1  Warnings: 0

MySQL found both of your '123' records but considered one of them a 
duplicate. Look at the Primary Key and any UNIQUE indexes for the 
pending_cart table. You will have to change at least one of them to allow 
the second record from cart to exist.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: insert...select only inserts first record [SOLVED]

[Andy Pieters]

Hi 

I got a reply offlist from Shawn Green telling me to check my indexes on table 
pending_cart.

As it turned out, the primary index (id) did not have its auto_increment bit 
set.

ALTER TABLE `pending_cart` 
CHANGE `id` 
`id` INT( 11 ) 
DEFAULT '0' 
NOT NULL 
AUTO_INCREMENT;

Took care of the situation.

Thanks again Shawn.


Kind regards

Andy

-- 
Registered Linux User Number 379093
-- --BEGIN GEEK CODE BLOCK-
Version: 3.1
GAT/O/>E$ d-(---)>+ s:(+)>: a--(-)>? C$(+++) UL>$ P-(+)>++
L+++>$ E---(-)@ W+++>+++$ !N@ o? !K? W--(---) !O !M- V-- PS++(+++)
PE--(-) Y+ PGP++(+++) t+(++) 5-- X++ R*(+)@ !tv b-() DI(+) D+(+++) G(+)
e>$@ h++(*) r-->++ y--()>
-- ---END GEEK CODE BLOCK--
--
Check out these few php utilities that I released
 under the GPL2 and that are meant for use with a 
 php cli binary:
 
 http://www.vlaamse-kern.com/sas/
--

--


pgpMW2glnjhUp.pgp
Description: PGP signature

RE: Union Performance Question

[Dathan Pattishall]

> 
> Use one big table. A merge table will run the same query over 
> all 10 tables. The key buffer is filled from the top down so 

He is using a fulltext index he can't use merge tables.

If he where to UNION across the tables being used assuming he uses the
tables that only have the data he would get a good boost in performance.
The performance comes from a smaller in time lock on the table and only
data that is being accessed the most would stay in the buffer.

He has to pick a good hash such as date or country code for splitting
tables on. 

To explain why let's look at the key buffer structure.

Having multiple tables do not pollute the buffer this is why:

mySQL pulls OS MYI file "blocks" then puts the frequently used blocks in
the buffer. The index file itself stores the data in a B-Tree+ (or
R-Tree) so a smaller index file means more of it can exist in the
keybuffer. Since an index file is associated with a table the most
frequently used tables' indexes will have more of the key buffer-that
assumed: if the application hashes the table correctly then a boost in
performance can be gained by using more memory for index blocks that
have more pertinent data then not.


If your using INNODB (no FULLTEXT INDEX option) use 1 big table.








  


> if you have a key buffer that looks like this:
> a
>/  \
>   /\
> b  c
>/  \/  \
>  de fg
> 



> 
> Almost all queries for that index will be able to use the 
> buffer for 'a' 
> and 'b'. It's not until they get things very narrowed down 
> that you have to actually hit the index files for specific 
> leaves. Creating ten small tables creates ten duplicate 'a' 
> and 'b' sections which polutes the key buffer.
> 
> -Eric
> 
> Dathan Pattishall wrote:
> 
> >Use 10 smaller tables and perform a union. It's faster to look up in 
> >smaller tables then larger ones-generally. Additionally more of the 
> >key_buffer can be used for tables with the most hits over the tables 
> >with less hits, making the lookup sustain speed over time.
> >
> > 
> >
> >  
> >
> >>-Original Message-
> >>From: Dan Salzer [mailto:[EMAIL PROTECTED]
> >>Sent: Wednesday, May 11, 2005 11:46 AM
> >>To: mysql@lists.mysql.com
> >>Subject: Union Performance Question
> >>
> >>Hi everyone,
> >>
> >>I have a question regarding the performance of UNION queries:
> >>
> >>I need to do a full-text search against a large number of 
> rows. Is it 
> >>faster to have one table with 10,000,000 text rows and perform one 
> >>full-text search. Or, am I better off having 10 smaller 
> more managable 
> >>tables and performing UNION'ed full-text searches against them.
> >>
> >>A merge table seems like it would be the ideal solution, but the 
> >>limitation on full-text indexing prohibits it.
> >>
> >>Thanks!
> >>
> >>-Dan
> >>
> >>--
> >>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: Union Performance Question

[Dan Salzer]

Most helpfull! Thanks!

This brings me to my next question I have a table that looks like this:

CREATE TABLE `Article_Search` (
> `ArticleID` int(11) NOT NULL default '0',
> `Content` text NOT NULL,
> PRIMARY KEY (`ArticleID`),
> FULLTEXT KEY `Content` (`Content`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1

 This table has several million rows, but I only want to search a subset of 
the table. IE:
 SELECT * FROM Article_Search WHERE MATCH(Content) AGAINST('"rubber 
duckies"' IN BOOLEAN MODE) AND ArticleID IN (100, 23, 223, 98, 4018, 1452, 
91)
 The reason I'm specifying a set of ArticleIDs is that I know any hits are 
going to be within those articles. So the presence of the IN() clause is 
purely there for performance. However, an explain on this Statement shows 
that it is using the Full-Text index. Is mysql text-searching the entire 
table under the hood, or does it use the PK to reduce the dataset before the 
text-search.
 Thanks again!
 -Dan

On 5/11/05, Dathan Pattishall <[EMAIL PROTECTED]> wrote:
> 
> >
> > Use one big table. A merge table will run the same query over
> > all 10 tables. The key buffer is filled from the top down so
> 
> He is using a fulltext index he can't use merge tables.
> 
> If he where to UNION across the tables being used assuming he uses the
> tables that only have the data he would get a good boost in performance.
> The performance comes from a smaller in time lock on the table and only
> data that is being accessed the most would stay in the buffer.
> 
> He has to pick a good hash such as date or country code for splitting
> tables on.
> 
> To explain why let's look at the key buffer structure.
> 
> Having multiple tables do not pollute the buffer this is why:
> 
> mySQL pulls OS MYI file "blocks" then puts the frequently used blocks in
> the buffer. The index file itself stores the data in a B-Tree+ (or
> R-Tree) so a smaller index file means more of it can exist in the
> keybuffer. Since an index file is associated with a table the most
> frequently used tables' indexes will have more of the key buffer-that
> assumed: if the application hashes the table correctly then a boost in
> performance can be gained by using more memory for index blocks that
> have more pertinent data then not.
> 
> If your using INNODB (no FULLTEXT INDEX option) use 1 big table.
> 
> 
> > if you have a key buffer that looks like this:
> > a
> > / \
> > / \
> > b c
> > / \ / \
> > d e f g
> >
> 
> >
> > Almost all queries for that index will be able to use the
> > buffer for 'a'
> > and 'b'. It's not until they get things very narrowed down
> > that you have to actually hit the index files for specific
> > leaves. Creating ten small tables creates ten duplicate 'a'
> > and 'b' sections which polutes the key buffer.
> >
> > -Eric
> >
> > Dathan Pattishall wrote:
> >
> > >Use 10 smaller tables and perform a union. It's faster to look up in
> > >smaller tables then larger ones-generally. Additionally more of the
> > >key_buffer can be used for tables with the most hits over the tables
> > >with less hits, making the lookup sustain speed over time.
> > >
> > >
> > >
> > >
> > >
> > >>-Original Message-
> > >>From: Dan Salzer [mailto:[EMAIL PROTECTED]
> > >>Sent: Wednesday, May 11, 2005 11:46 AM
> > >>To: mysql@lists.mysql.com
> > >>Subject: Union Performance Question
> > >>
> > >>Hi everyone,
> > >>
> > >>I have a question regarding the performance of UNION queries:
> > >>
> > >>I need to do a full-text search against a large number of
> > rows. Is it
> > >>faster to have one table with 10,000,000 text rows and perform one
> > >>full-text search. Or, am I better off having 10 smaller
> > more managable
> > >>tables and performing UNION'ed full-text searches against them.
> > >>
> > >>A merge table seems like it would be the ideal solution, but the
> > >>limitation on full-text indexing prohibits it.
> > >>
> > >>Thanks!
> > >>
> > >>-Dan
> > >>
> > >>--
> > >>MySQL General Mailing List
> > >>For list archives: http://lists.mysql.com/mysql
> > >>To unsubscribe:
> > >>http://lists.mysql.com/[EMAIL PROTECTED]
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> > >
> >
> >
>

OS 10.4 (Tiger) compilation errors on 4.1.11...

[Wells Oliver]

Has anyone else tried to compile 4.1.11 on OS 10.4? My compilation fails on 
mysqldd.cc:

mysqld.cc: In function `int bootstrap(FILE*)':
mysqld.cc:3350: warning: converting of negative value '-0x1' to 
'ulong'
mysqld.cc: In function `void* handle_connections_sockets(void*)':
mysqld.cc:3589: error: invalid conversion from 'size_socket*' to 'socklen_t*'
mysqld.cc:3589: error:   initializing argument 3 of 'int accept(int, sockaddr*, 
socklen_t*)'
mysqld.cc:3662: error: invalid conversion from 'size_socket*' to 'socklen_t*'
mysqld.cc:3662: error:   initializing argument 3 of 'int getsockname(int, 
sockaddr*, socklen_t*)'
sql_list.h: At global scope:
sql_list.h:401: warning: inline function `base_ilist::~base_ilist()' used but 
never defined
make[4]: *** [mysqld.o] Error 1
make[3]: *** [all-recursive] Error 1
make[2]: *** [all] Error 2
make[1]: *** [all-recursive] Error 1
make: *** [all] Error 2

I believe 10.4 has gcc 4.0. Anyway; any tips appreciated. Thanks!

--
Wells Oliver
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: write query question

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
Eric Jensen <[EMAIL PROTECTED]> writes:

> So you want 5 contacts for every user?  Try this:
> SELECT COUNT(c.id) AS count, u.username,  u.first_name,  u.last_name, 
> c.name
> FROM user AS u, contact AS c
> WHERE u.id = c.id_user
> GROUP BY c.id_user
> HAVING count <= 5

This won't work since "count" would be the total number of contacts
for this user.

Try something like that:

  SELECT u.username, c1.name
  FROM user AS u
  JOIN contact AS c1 ON u.id = c1.id_user
  LEFT JOIN contact AS c2 ON c2.id_user = c1.id_user
 AND c2.name < c1.name
  GROUP BY u.username, c1.name
  HAVING count(c2.id) < 5
  ORDER BY u.username, c1.name


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

same-table join

[Mike Johnson]

I think I'm just having a brain fart here, but if someone could help me
out I'd appreciate. Maybe it's just too late in the day for thinking...

Say I have a table `actions` with a structure and data as such:

++++
| id | userid | action |
++++
|  1 | a  | foo|
|  2 | a  | bar|
|  3 | a  | baz|
|  4 | b  | foo|
|  5 | c  | foo|
|  6 | c  | bar|
|  7 | d  | foo|
|  8 | d  | bar|
|  9 | d  | baz|
++++

What I want to do is return all values of `userid` that have a 'foo'
action but not a 'baz' action (those being b and c). I know I know the
answer to this, but I'm drawing a blank at the moment.

Thanks in advance if someone could give me a hand.


-- 
Mike Johnson Smarter Living, Inc.
Web Developerwww.smartertravel.com
[EMAIL PROTECTED]   (617) 886-5539

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: OS 10.4 (Tiger) compilation errors on 4.1.11...

[Jocelyn Fournier]

Hi,
I've already seen this problem with gcc 4.0, I have to change 
size_socket declaration to socklen_t.
Anyway, you won't be able to compile properly MySQL because of a bug in 
gcc 4.0 which will be fixed in 4.0.1.

Take a look at
http://gcc.gnu.org/bugzilla/show_bug.cgi?id=21173
for more details.
Regards,
  Jocelyn
Wells Oliver wrote:
Has anyone else tried to compile 4.1.11 on OS 10.4? My compilation fails on 
mysqldd.cc:
mysqld.cc: In function `int bootstrap(FILE*)':
mysqld.cc:3350: warning: converting of negative value '-0x1' to 
'ulong'
mysqld.cc: In function `void* handle_connections_sockets(void*)':
mysqld.cc:3589: error: invalid conversion from 'size_socket*' to 'socklen_t*'
mysqld.cc:3589: error:   initializing argument 3 of 'int accept(int, sockaddr*, 
socklen_t*)'
mysqld.cc:3662: error: invalid conversion from 'size_socket*' to 'socklen_t*'
mysqld.cc:3662: error:   initializing argument 3 of 'int getsockname(int, 
sockaddr*, socklen_t*)'
sql_list.h: At global scope:
sql_list.h:401: warning: inline function `base_ilist::~base_ilist()' used but 
never defined
make[4]: *** [mysqld.o] Error 1
make[3]: *** [all-recursive] Error 1
make[2]: *** [all] Error 2
make[1]: *** [all-recursive] Error 1
make: *** [all] Error 2
I believe 10.4 has gcc 4.0. Anyway; any tips appreciated. Thanks!
--
Wells Oliver
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SATA vs SCSI

[Donny Simonton]

Kevin,
I am in the same boat that you are, I can't store anything in memory, just
have too much data.  I've got 2tb on one box right now, I did get a quote
last week for that much memory, I think it was 4 million just for the
memory.

> Also.. if you have a high cache hit rate you can effectively have memory
> tables (in theory at least).  I just haven't seen anywhere near 20k qps.

20k qps is not that hard to do with all memory tables especially if you are
only using the primary key and nothing else.  We have one quad opteron that
was pushing over 13k queries per seconds without using any memory tables at
all.  All queries were simple perfect selects only using the primary key.
But we found that in many cases we were wasting so much time, doing so many
single selects, now we are only running about 1k qps on a box, but we are
doing thousands of IN queries now.  So we get lower qps, but faster overall
performance.

Donny



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

table optimisation

[Seena Blace]

Hi,
I have been noticing table performanace issue when # of rows grows more.How to 
tune that table?
thanks
-Seena


-
Do you Yahoo!?
 Read only the mail you want - Yahoo! Mail SpamGuard.

Report(query)

[Seena Blace]

Hi,
 
I want report like this
Table description is like as follows
 
Field   | Type | Null | Key | Default | 
Extra  |
+-+--+--+-+-++

| id  | int(10) unsigned |  | PRI | NULL
| auto_increment |

| host_id | int(10) unsigned |  | MUL | 0   
||

| time| datetime |  | MUL | -00-00 00:00:00 
||

| process   | int(10) unsigned |  | | 0   | 
   |

| pending  | int(10) unsigned |  | | 0   |  
  |

| wip | int(10) unsigned |  | | 0   |   
 |

 

 
date   process pending wip
 
5/10/051030  40
5/11/05 09   28  60

Summary 19   58 100
 
 
 
select date,sum(process),sum(pending),sum(wip) from tab
group by date;
What command I used to show heading and what command to show sub total and 
total ?
thanks
-Seena
 


-
Yahoo! Mail Mobile
 Take Yahoo! Mail with you! Check email on your mobile phone.

Preventing slaves from falling behind masters...

[Kevin Burton]

If you're running in a master/slave environment.. and you're application 
is using the slave too often... replication can fall behind which can 
then confuse your application.

This can happen if the IO performance of both the master and slaves is 
equivalent and you're performaning INSERT/UPDATE/DELETE with a load of 1 
on the master.  Then when the transactions move to the slave all the IO 
is used up and any additional SELECTS will just cause the slave to fall 
behind.

Has anyone else seen this?  One way I was thinking of solving this is to 
use RAID5 on our master and then RAID0 on the slaves so that the master 
is a hard bottleneck. Then the slaves have no problem running 
transactions via replication and have load available to run SELECTS.

Any other ideas?
--
Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com. 
See irc.freenode.net #rojo if you want to chat.

Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
  Kevin A. Burton, Location - San Francisco, CA
 AIM/YIM - sfburtonator,  Web - http://peerfear.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: write query question

[Jerry Swanson]

query runs  for 5 min... kill query id.



On 12 May 2005 00:29:56 +0200, Harald Fuchs <[EMAIL PROTECTED]> wrote:
> In article <[EMAIL PROTECTED]>,
> Eric Jensen <[EMAIL PROTECTED]> writes:
> 
> > So you want 5 contacts for every user?  Try this:
> > SELECT COUNT(c.id) AS count, u.username,  u.first_name,  u.last_name,
> > c.name
> > FROM user AS u, contact AS c
> > WHERE u.id = c.id_user
> > GROUP BY c.id_user
> > HAVING count <= 5
> 
> This won't work since "count" would be the total number of contacts
> for this user.
> 
> Try something like that:
> 
>   SELECT u.username, c1.name
>   FROM user AS u
>   JOIN contact AS c1 ON u.id = c1.id_user
>   LEFT JOIN contact AS c2 ON c2.id_user = c1.id_user
>  AND c2.name < c1.name
>   GROUP BY u.username, c1.name
>   HAVING count(c2.id) < 5
>   ORDER BY u.username, c1.name
> 
> 
> --
> 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]

missing file ( msyql.sock) solved

[ganesan malairaja]

i solved the problem by installing mysql-4.1.11-0 the rpm version the server 
and the client

then there is no problem of the missing mysql.socl file problem

http://graphics.hotmail.com/emarrow_right.gif"; 
width=16>Ganesan_Malairajahttp://graphics.hotmail.com/emarrow_left.gif"; 
width=16>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: same-table join

[Michael Stassen]

Mike Johnson wrote:
I think I'm just having a brain fart here, but if someone could help me
out I'd appreciate. Maybe it's just too late in the day for thinking...
Say I have a table `actions` with a structure and data as such:
++++
| id | userid | action |
++++
|  1 | a  | foo|
|  2 | a  | bar|
|  3 | a  | baz|
|  4 | b  | foo|
|  5 | c  | foo|
|  6 | c  | bar|
|  7 | d  | foo|
|  8 | d  | bar|
|  9 | d  | baz|
++++
What I want to do is return all values of `userid` that have a 'foo'
action but not a 'baz' action (those being b and c). I know I know the
answer to this, but I'm drawing a blank at the moment.
Thanks in advance if someone could give me a hand.
Join the table to itself on userid, requiring action foo on the left and 
action baz on the right.  Using a LEFT JOIN, the rows you want would be 
NULL on the right.

  SELECT a1.userid
  FROM actions a1
  LEFT JOIN actions a2
  ON a1.userid = a2.userid AND a2.action='baz'
  WHERE a1.action = 'foo'
  AND a2.id IS NULL;
++
| userid |
++
| b  |
| c  |
++
2 rows in set (0.01 sec)
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Preventing slaves from falling behind masters...

[Donny Simonton]

With Mysql you should ONLY use RAID10.  Everything else is not worth your
time.  

As long as you are using 15k SCSI drives, on both your master and your
slave, your slave should rarely ever fall behind.  Especially if you are
doing less than 1,000 inserts per second on the master.  Otherwise you
should be just fine.

The only time our slaves ever fall behind, is when you delete 40 records
from table A then insert 40 new ones.  But we do that for 600k records, so
we do a few million deletes and inserts in a short period of time.

Donny

> -Original Message-
> From: Kevin Burton [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, May 11, 2005 7:30 PM
> To: mysql@lists.mysql.com
> Subject: Preventing slaves from falling behind masters...
> 
> If you're running in a master/slave environment.. and you're application
> is using the slave too often... replication can fall behind which can
> then confuse your application.
> 
> This can happen if the IO performance of both the master and slaves is
> equivalent and you're performaning INSERT/UPDATE/DELETE with a load of 1
> on the master.  Then when the transactions move to the slave all the IO
> is used up and any additional SELECTS will just cause the slave to fall
> behind.
> 
> Has anyone else seen this?  One way I was thinking of solving this is to
> use RAID5 on our master and then RAID0 on the slaves so that the master
> is a hard bottleneck. Then the slaves have no problem running
> transactions via replication and have load available to run SELECTS.
> 
> Any other ideas?
> 
> --
> 
> 
> Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com.
> See irc.freenode.net #rojo if you want to chat.
> 
> Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html
> 
>Kevin A. Burton, Location - San Francisco, CA
>   AIM/YIM - sfburtonator,  Web - http://peerfear.org/
> GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412
> 
> 
> --
> 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: Maximum number of user variables

[Neculai Macarie]

> Not that I'm aware of. What type of conversions are you doing that you
> need 30,000 use vars? An easy solution would be to try it and find out :)

I need to move multiple linked entries (in around 12 tables) from one
running server to another. I'm using auto_increment's all over the place and
I have the following problem: I need to move (no replication possible) the
information in those 12 tables to an identical functioning system (same
software, same db, but other server) and I need to preserve the relations
between the tables. The problem that I have is that the systems evolve
independently and I can have the same id for 2 different entries on the
system (e.g. on the first system I can have the id 10 for User1, but on
second system id 10 would be for another user).

I want to make a script that dumps the info into an sql file that I can run
on the other server, something like this:
insert into customers () values ();
select @current_customer_id:=max(id) from customers;

insert into customer_categ (customer_id, name...) values
(@current_customer_id, "categ"...);

And I have around 20-30 000 records that are linked together (I cannot use a
single variable because the tables are "linked" 3 levels deep and I need to
keep the intermediate id's in variables).

-- 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: table optimisation

[Thomas Sundberg]

> -Original Message-
> From: Seena Blace [mailto:[EMAIL PROTECTED] 
> Sent: den 11 maj 2005 23:58
> To: mysql@lists.mysql.com
> Subject: table optimisation
> 
> Hi,
> I have been noticing table performanace issue when # of rows 
> grows more.How to tune that table?

Adding index on the column or columns that are often used in queries could
be a start. Performing full table scan on large, unindexed tables can take
quite longtime when the number of rows are increasing.

/Thomas


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Maximum number of user variables

[Dominicus Donny]

- Original Message - 
From: "Neculai Macarie" <[EMAIL PROTECTED]>
To: "Mysql" 
Sent: Thursday, May 12, 2005 1:20 PM
Subject: Re: Maximum number of user variables


Not that I'm aware of. What type of conversions are you doing that you
need 30,000 use vars? An easy solution would be to try it and find out :)
I need to move multiple linked entries (in around 12 tables) from one
running server to another. I'm using auto_increment's all over the place 
and
I have the following problem: I need to move (no replication possible) the
information in those 12 tables to an identical functioning system (same
software, same db, but other server) and I need to preserve the relations
between the tables. The problem that I have is that the systems evolve
independently and I can have the same id for 2 different entries on the
system (e.g. on the first system I can have the id 10 for User1, but on
second system id 10 would be for another user).

Perhaps after you dump the structure and data into sql files,
you could remove temporarily the extra attribut auto increment to those 
columns.
Then start inserting.
After that add the auto increment attribut again.
OR
use bulkcopy(?) if im not mistaken, or any other methods to copy as it is to 
new dbservers
(compress it first).

Donny. 


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: table optimisation

[Seena Blace]

How to check whae are tables are having how many indexes on which columns ?

Thomas Sundberg <[EMAIL PROTECTED]> wrote:
> -Original Message-
> From: Seena Blace [mailto:[EMAIL PROTECTED] 
> Sent: den 11 maj 2005 23:58
> To: mysql@lists.mysql.com
> Subject: table optimisation
> 
> Hi,
> I have been noticing table performanace issue when # of rows 
> grows more.How to tune that table?

Adding index on the column or columns that are often used in queries could
be a start. Performing full table scan on large, unindexed tables can take
quite longtime when the number of rows are increasing.

/Thomas




-
Yahoo! Mail
 Stay connected, organized, and protected. Take the tour