Memory tables and INNODB have different query plans for GROUP BY with btree?

[Kevin Burton]

I was benchmarking a few of my queries tonight and I noticed that two  
queries had different query plans based on table type.


Here's the "broken" query:

mysql> EXPLAIN SELECT * FROM FOO_LINK_MEMORY_TEST GROUP BY  
TARGET_NODE_ID\G

*** 1. row ***
   id: 1
  select_type: SIMPLE
table: FOO_LINK_MEMORY_TEST
 type: index
possible_keys: NULL
  key: TEST
  key_len: 18
  ref: NULL
 rows: 1000
Extra:
1 row in set (0.00 sec)

Note no index is used.


mysql> EXPLAIN SELECT * FROM FOO_LINK_INNODB_TEST GROUP BY  
TARGET_NODE_ID\G

*** 1. row ***
   id: 1
  select_type: SIMPLE
table: FOO_LINK_INNODB_TEST
 type: index
possible_keys: NULL
  key: TARGET_NODE_ID
  key_len: 9
  ref: NULL
 rows: 1011
Extra:
1 row in set (0.00 sec)

...

and here it uses TARGET_NODE_ID. The only difference is that I  
created an INNODB table and inserted the columns in the memory table  
into the INNODB table.


I'm trying to follow the instructions here:

http://dev.mysql.com/doc/mysql/en/loose-index-scan.html

To get decent GROUP BY performance.  Is this a bug?  Is there a  
workaround?


Kevin A. Burton, Location - San Francisco, CA
  AIM/YIM - sfburtonator,  Web - http://www.feedblog.org/
GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04

encryption syntax

[Jeff Pflueger]

Anybody have an idea why I might be getting the following message:

ERROR 1064 (0): You have an error in your SQL syntax near 
'('hello','password')' at line 1



When I type this in at the command line:

SELECT AES_ENCRYPT('hello','password');



Version info: mysql  Ver 14.7 Distrib 4.1.12, for redhat-linux-gnu 
(i686) using readline 4.3


Thanks for any help

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

Lock wait timeout exceeded

[Jonathan Stockley]

Hi,

We're having a problem with "lock wait timeout exceeded" errors. We are
exclusively using innodb tables apart from the mysql database. The
problem seems to be with the way we are simulating sequences.

 

There is a table called SEQUENCES defined as follows:

 

CREATE TABLE IF NOT EXISTS Sequences

(

tableName VARCHAR(64) NOT NULL PRIMARY KEY,

id INTEGER UNSIGNED NOT NULL

)

 

We then generate the next number for a given table as follows:

 

UPDATE Sequences SET id=LAST_INSERT_ID(id+1) WHERE tableName =
'THE_TABLE_NAME'

 

There are several hundred rows in the Sequences table.

 

The general flow is that for each row or set of rows to be inserted we
do the following:

(AUTOCOMMIT is turned OFF).

 

1.  begin transaction
2.  get next sequence number for given target table using above
UPDATE statement.
3.  insert row into target table
4.  if more rows to insert go to step 2
5.  commit transaction

 

We are not using LOCK TABLE anywhere and we are using the default
transaction isolation level which I believe is READ-COMMITED.

 

Every so often we get the 1205 error "lock wait timeout exceeded".

 

Any ideas where to go with this? How can I find out which session is
holding the lock and what lock it is?

 

Thanks,

Jo

 

 

 

 

Re: Table names with periods

[Patrick]

Shawn,
Your correct about my algorithm for IPv4, I was not paying attention (too 
many things at once). It should have read:


AAA.BBB.CCC.DDD
  \\   \\
\\  \   DDD
  \   \  CCC x 256
\  BBB x 256 x 256
  AAA x 256 x 256 x 256

However, the 'SELECT' statement was conceptual, not literal and did in fact 
state that it would need the appropriate 'CREATE TABLE' options as well as 
an 'INTO OUTFILE' clause.  This was left as an exercise for the user to 
construct. It is not intended as Dynamic SQL.  It was intended to create a 
file of SQL statements that could be executed 'ad hoc'.  It is also just one 
of many ways to accomplish the goal. The literal and now complete 'SELECT' 
with the same stated assumptions would look like this:


SELECT  "CREATE TABLE  ",REPLACE(ip_address,'.','_'), " ( ip CHAR (16), 
last_access TIMESTAMP " INTO OUTFILE "/tmp/createtable.sql" FROM 
IP_Addresses WHERE status ="ACTIVE"


This creates a file of SQL statements that can then be executed on the 
command line or in a cron with the appropriate redirect.


I did, however, miss the INET_ATON() and INET_ATOA() functions added in 
v3.23.30. Eliminating any need for a UDF.


Now that I have embarrased myself with a bad algorithm, defended my 'SELECT' 
construction, and missed a very important pre-built function, I must say, I 
do agree that superficially Chance's concept of creating a table for each IP 
is not one I would personally embrace, but then again, I do not know what he 
is trying to accomplish and he elected not to make the list privy to his 
design.  He did state early on in the thread, that he wasn't looking for 
help in db design, just a solution to the punctuation issue.


Pat...

- Original Message - 
From: [EMAIL PROTECTED]

To: Patrick
Cc: Chance Ellis ; mysql@lists.mysql.com
Sent: Monday, October 03, 2005 4:30 PM
Subject: Re: Table names with periods

Replies embedded:

"Patrick" <[EMAIL PROTECTED]> wrote on 10/03/2005 03:43:20 PM:


There are many ways to approach this.  How are you receiving the IP
data?  Are you reading a file or other stream or are you trying to
process the table creation by reading a column from a previously
populated table through a select statement?

The functions, inet_ntoa() and inet_addr(), are part of most
networking libraries. These are the common functions to convert
dotted quad notation. If you wanted to write you own function, an
IPv4 address is broken down as follows:

AAA.BBB.CCC.DDD
  \   \   \   \
\   \   \   DDD
  \   \   CCC x CCC
\   BBB x BBB x BBB
  AAA x AAA x AAA x AAA


I am not sure of your algorithm. Perhaps I am just not understanding your 
notation.





If you are not able to pre-process (scrub) the incoming data
programmatically, you would need to create a UDF in MySQL to perform
the conversion, or, alternatively, if you want to use MySQL SELECT
statement as-is could replace the 'period' with an 'underscore'
using MySQL's built-in string functions like so:

Assumptions: Reading IP address from an existing table named
IP_Addresses with a column named ip_address and a column named status.

SELECT  "CREATE TABLE ",REPLACE(ip_address,'.','_'), " [insert
create options here]" FROM IP_Addresses WHERE status ="ACTIVE"


Dynamic SQL? Not with that statement. He is going to need to create his SQL 
statement client-side and send it pre-formatted to the server. MySQL 5.0 has 
the beginnings of dynamic SQL and I am not 100% sure it would accept what 
you typed.




You would obviously add your "CREATE TABLE" options and "INTO
OUTFILE" options as needed.
This would be an alternative to converting IPv4 to 32bit Integer.

I hope this helps...

If at all possible, it is probably best to continue in the MySQL
list, there are some pretty clever people out there

Pat...

[EMAIL PROTECTED]
CocoNet Corporation
SW Florida's First ISP
825 SE 47th Terrace
Cape Coral, FL 33904

  - Original Message - 
  From: Chance Ellis

  To: Patrick
  Sent: Monday, October 03, 2005 2:22 PM
  Subject: Re: Table names with periods


  Patrick,

  I have been trying to figure out how I can convert an IP address
to a 32bit integer within a SQL create statement.


You are mixing purposes. MySQL has a CREATE TABLE statement but it requires 
a string literal. You cannot build a CREATE TABLE statement on the 
fly -inside- MySQL. You have to build your statement client-side and send it 
(as a complete statemnt) to MySQL to process.


MySQL has a function that converts IP addresses into numbers (see above) but 
you cannot combine that with a CREATE TABLE statement.




  Is this possible or am I thinking about this all wrong? The input


Yes, I think you are all wrong. You are being too "literal" in your design 
choices. Generally if your data storage design requires you to add tables 
whenever you add a new "whatever", that is a bad design. The better thing to 
do is to create one table that can ho

alter table

[s. keeling]

I'd like to add a bit of history data to a table (who changed a record
last, and when it was last changed).  Is this the way to do it?

   alter table MEMBERS
   add CHG_BY varchar(3)

   alter table MEMBERS
   alter CHG_BY set default "sbk"

   alter table MEMBERS
   add CHG_DATE date

   alter table MEMBERS
   alter CHG_DATE set default CURRENT_DATE

Whoever next ends up with this can set CHG_BY's default to their
initials and carry on from there.

Will CURRENT_DATE work in this context?


-- 
Any technology distinguishable from magic is insufficiently advanced.
(*)http://www.spots.ab.ca/~keeling  Please don't Cc: me.
- -
For the ChiComms: democracy human rights Taiwan Independence

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

Re: Documenting and visualizing a database

[Graham Reeds]

Raz wrote:

Dan,

Forgot to say, re. MySQL Workbench - this may be a useful source of info:

http://forums.mysql.com/list.php?113

Raz



Sent off the link to the forum before I moved on to this message. D'OH!

G.


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

Re: Table names with periods

[Bastian Balthazar Bux]

Chance Ellis wrote:
[snip]
> of CREATE TABLE entries. I then import those files with a cron script into
> mysql. This is where I am looking to convert IP address into something
[snip]

There is a world of possibilities at this point, one is this:

sed \
--expression='s/@@@NEEDTOREPLACETHIS\([0123456789]\).\([0123456789]\)\.\([0123456789]\)\.\([0123456789]\)*NEEDTOREPLACETHIS@@@/\1_\2_\3_\4/'
\
SAVEDFILENAME \
| mysql -ublabla -psecret dbname



You can do quite everyting from a cron script, also create sql query
from normal syslog logs.


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

Re: Table names with periods

[Jerl Simpson]

You should be able to do the conversion when you run the cron script. Or at
least just before. Preprocess the output from syslog-ng to translate all .
to _. in the table names.


Jerl



On 10/3/05, Chance Ellis <[EMAIL PROTECTED]> wrote:
>
> So more information about the project...
> I am working on a syslog-ng project to input syslog messages into mysql.
> The syslog-ng.conf file is the only way to tell syslog-ng where to put
> data.
> You can perform filtering based on predefined syslog-ng macros such as
> $HOST, $DATE $TIME etc... however within this .conf file I am not able to
> create functions and to a replace($host,".","_").
> The reason I am creating a table for each individual host or IP address is
> because of the amount of data and trying to minimize search time. These
> tables will hold millions of records each and to limit the search time,
> the
> application picks the table for the device and performs the search.
> For those who are ready to respond with their own input on how syslog-ng
> should be configured, I will add that syslog-ng is using file
> destinations.
> It is using file destinations over program or pipe destinations for
> performance reasons... Thus, syslog-ng creates a file that contains a
> bunch
> of CREATE TABLE entries. I then import those files with a cron script into
> mysql. This is where I am looking to convert IP address into something
> without periods. I cannot perform logic within the .conf file and it
> appears
> MySQL will not accept periods.
> Please let me know if any more info is needed.
> Thanks!
>
> On 10/3/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> >
> >
> > Replies embedded:
> >
> > "Patrick" <[EMAIL PROTECTED]> wrote on 10/03/2005 03:43:20 PM:
> >
> > > There are many ways to approach this. How are you receiving the IP
> > > data? Are you reading a file or other stream or are you trying to
> > > process the table creation by reading a column from a previously
> > > populated table through a select statement?
> > >
> > > The functions, inet_ntoa() and inet_addr(), are part of most
> > > networking libraries. These are the common functions to convert
> > > dotted quad notation. If you wanted to write you own function, an
> > > IPv4 address is broken down as follows:
> > >
> > > AAA.BBB.CCC.DDD
> > > \ \ \ \
> > > \ \ \ DDD
> > > \ \ CCC x CCC
> > > \ BBB x BBB x BBB
> > > AAA x AAA x AAA x AAA
> >
> > I am not sure of your algorithm. Perhaps I am just not understanding
> your
> > notation.
> >
> > >
> > > Add the results and you have your unique 32bit number.
> > >
> > > eg. 10.10.10.1   = 10,000 +
> 1,000 + 100 + 1
> > > = 11,101
> > >
> > Let me try a different number(one a little less friendly to base10):
> >
> > the address 4.4.4.4  :
> > 4^4 + 4^3 + 4^2 + 4 = 256 + 64 + 16 + 4 = 340
> >
> > Which would be the same as: 2.4.16.4  
> = 340
> > your method does not seem to generate unique numbers for each IP
> > address
> >
> > Maybe I just don't get your description.
> >
> >
> > Mathematically speaking: The base10 representation of the 32-bit number
> > that 2.30.40.10   corresponds to
> would be:
> > (2 * 256*256*256) + (30 * 256*256) + (40 * 256) + 10 = 35530762
> >
> > Which is the same thing we get from
> >
> > mysql>select inet_aton('2.30.40.10  <
> http://2.30.40.10/>');
> > +-+
> > | inet_aton('2.30.40.10  ') |
> > +-+
> > | 35530762 |
> > +-+
> > 1 row in set (0.04 sec)
> >
> > as was mentioned earlier...
> >
> > >
> > > If you are not able to pre-process (scrub) the incoming data
> > > programmatically, you would need to create a UDF in MySQL to perform
> > > the conversion, or, alternatively, if you want to use MySQL SELECT
> > > statement as-is could replace the 'period' with an 'underscore'
> > > using MySQL's built-in string functions like so:
> > >
> > > Assumptions: Reading IP address from an existing table named
> > > IP_Addresses with a column named ip_address and a column named status.
> > >
> > > SELECT "CREATE TABLE ",REPLACE(ip_address,'.','_'), " [insert
> > > create options here]" FROM IP_Addresses WHERE status ="ACTIVE"
> >
> > Dynamic SQL? Not with that statement. He is going to need to create his
> > SQL statement client-side and send it pre-formatted to the server. MySQL
> > 5.0 has the beginnings of dynamic SQL and I am not 100% sure it would
> > accept what you typed.
> >
> > >
> > > You would obviously add your "CREATE TABLE" options and "INTO
> > > OUTFILE" options as needed.
> > > This would be an alternative to converting IPv4 to 32bit Integer.
> > >
> > > I hope this helps...
> > >
> > > If at all possible, it is probably best to continue in the MySQL
> > > list, there are some pretty clever people out there
> > >
> > > Pat...
> > >
> > > 

Re: Table names with periods

[Jasper Bryant-Greene]

Chance Ellis wrote:

[snip]

Thus, syslog-ng creates a file that contains a bunch of CREATE TABLE
entries. I then import those files with a cron script into mysql.
This is where I am looking to convert IP address into something
without periods. I cannot perform logic within the .conf file and it
appears MySQL will not accept periods.


Have your cron script replace the periods in the IP addresses with
underscores before importing the SQL into MySQL.

--
Jasper Bryant-Greene
Freelance web developer
http://jasper.bryant-greene.name/

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

Re: Table names with periods

[Chance Ellis]

So more information about the project...
 I am working on a syslog-ng project to input syslog messages into mysql.
The syslog-ng.conf file is the only way to tell syslog-ng where to put data.
You can perform filtering based on predefined syslog-ng macros such as
$HOST, $DATE $TIME etc... however within this .conf file I am not able to
create functions and to a replace($host,".","_").
 The reason I am creating a table for each individual host or IP address is
because of the amount of data and trying to minimize search time. These
tables will hold millions of records each and to limit the search time, the
application picks the table for the device and performs the search.
 For those who are ready to respond with their own input on how syslog-ng
should be configured, I will add that syslog-ng is using file destinations.
It is using file destinations over program or pipe destinations for
performance reasons... Thus, syslog-ng creates a file that contains a bunch
of CREATE TABLE entries. I then import those files with a cron script into
mysql. This is where I am looking to convert IP address into something
without periods. I cannot perform logic within the .conf file and it appears
MySQL will not accept periods.
 Please let me know if any more info is needed.
 Thanks!

 On 10/3/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
>
> Replies embedded:
>
> "Patrick" <[EMAIL PROTECTED]> wrote on 10/03/2005 03:43:20 PM:
>
> > There are many ways to approach this. How are you receiving the IP
> > data? Are you reading a file or other stream or are you trying to
> > process the table creation by reading a column from a previously
> > populated table through a select statement?
> >
> > The functions, inet_ntoa() and inet_addr(), are part of most
> > networking libraries. These are the common functions to convert
> > dotted quad notation. If you wanted to write you own function, an
> > IPv4 address is broken down as follows:
> >
> > AAA.BBB.CCC.DDD
> > \ \ \ \
> > \ \ \ DDD
> > \ \ CCC x CCC
> > \ BBB x BBB x BBB
> > AAA x AAA x AAA x AAA
>
> I am not sure of your algorithm. Perhaps I am just not understanding your
> notation.
>
> >
> > Add the results and you have your unique 32bit number.
> >
> > eg. 10.10.10.1  = 10,000 + 1,000 + 100 + 1
> > = 11,101
> >
> Let me try a different number(one a little less friendly to base10):
>
> the address 4.4.4.4 :
> 4^4 + 4^3 + 4^2 + 4 = 256 + 64 + 16 + 4 = 340
>
> Which would be the same as: 2.4.16.4  = 340
> your method does not seem to generate unique numbers for each IP
> address
>
> Maybe I just don't get your description.
>
>
> Mathematically speaking: The base10 representation of the 32-bit number
> that 2.30.40.10  corresponds to would be:
> (2 * 256*256*256) + (30 * 256*256) + (40 * 256) + 10 = 35530762
>
> Which is the same thing we get from
>
> mysql>select inet_aton('2.30.40.10 ');
> +-+
> | inet_aton('2.30.40.10 ') |
> +-+
> | 35530762 |
> +-+
> 1 row in set (0.04 sec)
>
> as was mentioned earlier...
>
> >
> > If you are not able to pre-process (scrub) the incoming data
> > programmatically, you would need to create a UDF in MySQL to perform
> > the conversion, or, alternatively, if you want to use MySQL SELECT
> > statement as-is could replace the 'period' with an 'underscore'
> > using MySQL's built-in string functions like so:
> >
> > Assumptions: Reading IP address from an existing table named
> > IP_Addresses with a column named ip_address and a column named status.
> >
> > SELECT "CREATE TABLE ",REPLACE(ip_address,'.','_'), " [insert
> > create options here]" FROM IP_Addresses WHERE status ="ACTIVE"
>
> Dynamic SQL? Not with that statement. He is going to need to create his
> SQL statement client-side and send it pre-formatted to the server. MySQL
> 5.0 has the beginnings of dynamic SQL and I am not 100% sure it would
> accept what you typed.
>
> >
> > You would obviously add your "CREATE TABLE" options and "INTO
> > OUTFILE" options as needed.
> > This would be an alternative to converting IPv4 to 32bit Integer.
> >
> > I hope this helps...
> >
> > If at all possible, it is probably best to continue in the MySQL
> > list, there are some pretty clever people out there
> >
> > Pat...
> >
> > [EMAIL PROTECTED]
> > CocoNet Corporation
> > SW Florida's First ISP
> > 825 SE 47th Terrace
> > Cape Coral, FL 33904
> >
> > - Original Message -
> > From: Chance Ellis
> > To: Patrick
> > Sent: Monday, October 03, 2005 2:22 PM
> > Subject: Re: Table names with periods
> >
> >
> > Patrick,
> >
> > I have been trying to figure out how I can convert an IP address
> > to a 32bit integer within a SQL create statement.
>
> You are mixing purposes. MySQL has a CREATE TABLE statement but it
> requires a string literal. You cannot build a CREATE TABLE statement on the
> fly -inside- MySQL

Re: How to match a binary null in a varchar column???

[Richard F. Rebel]

Hi Keith,

Thanks, it did work.

I was trying to use a regular expression but this is just fine for my
needs.

On Mon, 2005-10-03 at 13:18 -0400, Keith Ivey wrote:
> Richard F. Rebel wrote:
> 
> > do I say REGEXP BINARY ""
> > 
> > I have tried \000 \0 as they are common representations for binary null.
> 
> Have you tried "WHERE your_column LIKE '%\0%'"?  That works for me.
> 
> -- 
> Keith Ivey <[EMAIL PROTECTED]>
> Smokefree DC
> http://www.smokefreedc.org
> Washington, DC
> 
-- 
Richard F. Rebel

cat /dev/null > `tty`


signature.asc
Description: This is a digitally signed message part

Re: Query Trouble!

[Barry]

  - Original Message - 
  From: [EMAIL PROTECTED] 
  To: Barry 
  Cc: mysql@lists.mysql.com 
  Sent: Monday, October 03, 2005 9:49 PM
  Subject: Re: Query Trouble!




  "Barry" <[EMAIL PROTECTED]> wrote on 10/03/2005 04:38:48 PM:

  > I'm new to MySQL and am using version 4-1-12a on my development
  > machine locally this query works just fine:
  > 
  > SELECT * FROM actor
  > WHERE id IN
  > ( SELECT id FROM episode_cast
  > WHERE episode_number =001)
  > 
  > but when I try to run it from the web server which is running version
  > 4.0.24-standard I get an error:
  > 
  > #1064 - You have an error in your SQL syntax.  Check the manual that
  > corresponds to your MySQL server version for the right syntax to use
  > near 'SELECT id from episode_cast WHERE episode_number = 001
  > 
  > I put this together from a book that's at least 2 years old so I
  > wouldn't have thought that it was because of different versions, but I
  > could well be wrong!
  > 
  > Any help would be much appreciated.
  > 

  Well, Barry, the book was written towards 4.1 as that is the first version 
with subqueries. You will need to convert your SQL into the JOIN-form like 
this: 

  SELECT a.* 
  FROM actor a 
  INNER JOIN episode_cast e 
  on e.id = a.id 
  WHERE e.episode_number = 001; 

  That will work on all versions :-) 

  Shawn Green
  Database Administrator
  Unimin Corporation - Spruce Pine 


  Thanks for that Shawn it works a treat.

   I have just spent the last three hours reading about joins and subquerys in 
the manual and I'm still no clearer on how they work..

  Barry

Re: Query Trouble!

[SGreen]

"Barry" <[EMAIL PROTECTED]> wrote on 10/03/2005 04:38:48 PM:

> I'm new to MySQL and am using version 4-1-12a on my development
> machine locally this query works just fine:
> 
> SELECT * FROM actor
> WHERE id IN
> ( SELECT id FROM episode_cast
> WHERE episode_number =001)
> 
> but when I try to run it from the web server which is running version
> 4.0.24-standard I get an error:
> 
> #1064 - You have an error in your SQL syntax.  Check the manual that
> corresponds to your MySQL server version for the right syntax to use
> near 'SELECT id from episode_cast WHERE episode_number = 001
> 
> I put this together from a book that's at least 2 years old so I
> wouldn't have thought that it was because of different versions, but I
> could well be wrong!
> 
> Any help would be much appreciated.
> 

Well, Barry, the book was written towards 4.1 as that is the first version 
with subqueries. You will need to convert your SQL into the JOIN-form like 
this:

SELECT a.*
FROM actor a
INNER JOIN episode_cast e
on e.id = a.id
WHERE e.episode_number = 001;

That will work on all versions :-)

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Query Trouble!

[Barry]

I'm new to MySQL and am using version 4-1-12a on my development
machine locally this query works just fine:

SELECT * FROM actor
WHERE id IN
( SELECT id FROM episode_cast
WHERE episode_number =001)

but when I try to run it from the web server which is running version
4.0.24-standard I get an error:

#1064 - You have an error in your SQL syntax.  Check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'SELECT id from episode_cast WHERE episode_number = 001

I put this together from a book that's at least 2 years old so I
wouldn't have thought that it was because of different versions, but I
could well be wrong!

Any help would be much appreciated.

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

Re: Encryption for mySQL 3.23

[SGreen]

Jeff Pflueger <[EMAIL PROTECTED]> wrote on 10/03/2005 03:46:09 PM:

> Hi,
> I need to encrypt data as I insert it into a mySQL database.
> The data will then be sent as a text file to another institution to be
> decrypted.
> 
> I am using mySQL version 11.18 Distrib 3.23.58 for RedHat Linux.
> 
> I cannot find a single encryption function in the documentation that
> seems to not create a syntax error for the version of mySQL I am using.
> 
> I am also concerned because whatever encryption I am using needs to be
> decrypted outside of mySQL.
> 
> Any suggestions?
> 
> Thanks!
> 

I don't expect a de-encryptor to be able to disentagle encrypted data from 
the other file and record markers from outside of MySQL if you only send 
them the data file. If you do send them the datafile (as-is), then they 
will need a MySQL server to read the data. They will need to decrypt the 
data on the client-side.

Did you mean to say that an EXTRACT of the data will be written to a text 
file and encrypted before being sent to your other location? That may be a 
better solution for transport security. 

As another point of design why are you attempting to use the old, 
decrepit 3.23 branch?  At least upgrade to the 4.1 branch. You gain 
encryption options and stability and lots of other improvements with the 
move to the newer version.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

5.0 backward application supp

[Barbara Deaton]

I currently have an application that is written on the 4.0 C API, but supports 
both 4.1 and 4.0 client/server.  My question is if I move my application to 
support 5.0 and some of the new API's will the 5.0 Application/client be able 
to communicate with a 4.1 or 4.0 server?

I support windows, HP 64-bit, AIX 64-bit, Solaris 64-bit and Linux 32&64 bit.

Thanks.
Barbara

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

Re: Table names with periods

[SGreen]

Replies embedded:

"Patrick" <[EMAIL PROTECTED]> wrote on 10/03/2005 03:43:20 PM:

> There are many ways to approach this.  How are you receiving the IP 
> data?  Are you reading a file or other stream or are you trying to 
> process the table creation by reading a column from a previously 
> populated table through a select statement?
> 
> The functions, inet_ntoa() and inet_addr(), are part of most 
> networking libraries. These are the common functions to convert 
> dotted quad notation. If you wanted to write you own function, an 
> IPv4 address is broken down as follows:
> 
> AAA.BBB.CCC.DDD
>   \   \   \   \
> \   \   \   DDD
>   \   \   CCC x CCC
> \   BBB x BBB x BBB
>   AAA x AAA x AAA x AAA

I am not sure of your algorithm. Perhaps I am just not understanding your 
notation.

> 
> Add the results and you have your unique 32bit number. 
> 
> eg. 10.10.10.1 = 10,000 + 1,000 + 100 + 1
>= 11,101
> 
Let me try a different number(one a little less friendly to base10):

the address 4.4.4.4:
4^4 + 4^3 + 4^2 + 4 = 256 + 64 + 16 + 4 = 340

Which would be the same as: 2.4.16.4 = 340
your method does not seem to generate unique numbers for each IP 
address

Maybe I just don't get your description. 


Mathematically speaking: The base10 representation of the 32-bit number 
that 2.30.40.10 corresponds to would be:
(2 * 256*256*256) + (30 * 256*256) + (40 * 256) + 10 = 35530762

Which is the same thing we get from 

mysql>select inet_aton('2.30.40.10');
+-+
| inet_aton('2.30.40.10') |
+-+
|35530762 |
+-+
1 row in set (0.04 sec)

as was mentioned earlier...

> 
> If you are not able to pre-process (scrub) the incoming data 
> programmatically, you would need to create a UDF in MySQL to perform
> the conversion, or, alternatively, if you want to use MySQL SELECT 
> statement as-is could replace the 'period' with an 'underscore' 
> using MySQL's built-in string functions like so:
> 
> Assumptions: Reading IP address from an existing table named 
> IP_Addresses with a column named ip_address and a column named status.
> 
> SELECT  "CREATE TABLE ",REPLACE(ip_address,'.','_'), " [insert 
> create options here]" FROM IP_Addresses WHERE status ="ACTIVE"

Dynamic SQL? Not with that statement. He is going to need to create his 
SQL statement client-side and send it pre-formatted to the server. MySQL 
5.0 has the beginnings of dynamic SQL and I am not 100% sure it would 
accept what you typed.

> 
> You would obviously add your "CREATE TABLE" options and "INTO 
> OUTFILE" options as needed.
> This would be an alternative to converting IPv4 to 32bit Integer.
> 
> I hope this helps...
> 
> If at all possible, it is probably best to continue in the MySQL 
> list, there are some pretty clever people out there
> 
> Pat...
> 
> [EMAIL PROTECTED]
> CocoNet Corporation
> SW Florida's First ISP
> 825 SE 47th Terrace
> Cape Coral, FL 33904
> 
>   - Original Message - 
>   From: Chance Ellis 
>   To: Patrick 
>   Sent: Monday, October 03, 2005 2:22 PM
>   Subject: Re: Table names with periods
> 
> 
>   Patrick,
> 
>   I have been trying to figure out how I can convert an IP address 
> to a 32bit integer within a SQL create statement.

You are mixing purposes. MySQL has a CREATE TABLE statement but it 
requires a string literal. You cannot build a CREATE TABLE statement on 
the fly -inside- MySQL. You have to build your statement client-side and 
send it (as a complete statemnt) to MySQL to process. 

MySQL has a function that converts IP addresses into numbers (see above) 
but you cannot combine that with a CREATE TABLE statement.

> 
>   Is this possible or am I thinking about this all wrong? The input 

Yes, I think you are all wrong. You are being too "literal" in your design 
choices. Generally if your data storage design requires you to add tables 
whenever you add a new "whatever", that is a bad design. The better thing 
to do is to create one table that can hold the entire class of "whatevers" 
and differentiate between them with data markers. I assume that each of 
these IP-named tables would look identical to every other (same column 
names, same column types, ...)?  The preferred method of modelling this is 
to create one table (that looks just like each IP table was going to look) 
and adding a column to it for the IP address. I know I am not the first 
person to recommend this design (I can think of at least two others that 
have also tried). 

Just so that we aren't all telling you to possibly do the wrong thing: Why 
do you feel that individual IP tables is a correct DATABASE design? It may 
be an acceptable PROGRAMMING design (one IP list object per address) but 
this is probably one of those points where good DB design and good OO 
design diverge.

> I am given is a straight IP address. I have no way of modifying it 
> other than some option in the SQL create statement is pos

Re: How to call C API functions from MS Access ?

[C.R. Vegelin]

Thanks Shawn,
Your reply was clear. To get MySQLd results I will use ADODB.
I need these results for error trapping and included in my VB code:
  Dim adbError As ADODB.Error
and forced an error with:
  adbConn.Execute ""SELECT a MOD b;"
and finally the trapping code to get the mySQL error messages:
  For Each adbError In conn.Errors
 MsgBox adbError.Description, vbCritical
  Next
It's working !
Thanks again, Cor


- Original Message - 
From: <[EMAIL PROTECTED]>

To: "C.R. Vegelin" <[EMAIL PROTECTED]>
Cc: 
Sent: Monday, October 03, 2005 5:25 PM
Subject: Re: How to call C API functions from MS Access ?



"C.R. Vegelin" <[EMAIL PROTECTED]> wrote on 10/01/2005 03:51:41 AM:


Hi List,

I want to call the C API functions from Visual Basic in MS Access 2003.
I assume that I have to use: "c:\Program Files\MySQL\MySQL Server 4.
1\Bin\LibMySQL.dll".
In my code at module level I have included various code lines to
declare entry-points, such as:
Declare Function mysql_info Lib "c:\Program Files\MySQL\MySQL Server
4.1\Bin\LibMySQL.dll" ()
Declare Function mysql_stat Lib "c:\Program Files\MySQL\MySQL Server
4.1\Bin\LibMySQL.dll" ()

After making an ADODB.Connection and running a query succesfully, I
want to call mysql_info().
The syntax in C language for this function is: char *mysql_info(MYSQL

*mysql)


My question: how to call mysql_info() from Visual Basic ?
When I use: myString = mysql_info() it returns an empty string.

My system includes: Windows XP, MySQL 4.1.13 and MyODBC 3.51.11.



To answer the question in your subject: you are already doing that
correctly. You have selected which library you want to use and are
declaring the entry points you want to use from that library. Good job.

To see the status of an ADODB connection, you have to use ADODB functions.
You cannot use the C API functions to check on the status of an ADODB
process (even if they are sharing the same DLL). That is because the
client library is thread-safe. Whatever you do in one instance of the
library is not apparent in any other instance. Any bleed-over from one
instance to another should be checked against the documentation and if the
two are not in agreement, report it as a bug.

If you want to use the C API, all of your database calls (including all of
your SQL execution) need to happen through the C API. If you want to use
the ADODB objects, you need to connect through ADODB through ODBC and stay
within that paradigm. You cannot mix or intermingle the two and expect it
to work. Sorry!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine 




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

Encryption for mySQL 3.23

[Jeff Pflueger]

Hi,
I need to encrypt data as I insert it into a mySQL database.
The data will then be sent as a text file to another institution to be
decrypted.

I am using mySQL version 11.18 Distrib 3.23.58 for RedHat Linux.

I cannot find a single encryption function in the documentation that
seems to not create a syntax error for the version of mySQL I am using.

I am also concerned because whatever encryption I am using needs to be
decrypted outside of mySQL.

Any suggestions?

Thanks!


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

Re: LVM-snapshot + mysqldump -- is this a reasonable backup

[George Herson]

> > Jim,
> >
> > I didn't word my question quite right because I was only guessing at
> > what a LVM snapshot was. Moreover, what you're saying is all correct.
> > However, I was not suggesting that the snapshot be kept around once the
> > backup is made.
> >
> > Let's go to article "What is a Logical Volume Manager (LVM) snapshot and
> >  how do I use it?" in the RedHat k'base
> > .  It says "After
> > performing the backup of the snapshot partition we release the
> > snapshot".  This implies, at least to me, that one doesn't need a 2nd
> > database server or to do a mysqldump (your steps 5-7).  Instead, we just
> >  tar cv /mnt/ops/dbbackup (to use the article's example name for the
> > mounted snapshot), save the tape, and dispense with the snapshot.
> > Wouldn't that work?  MySQL keeps its data in files already, so why is it
> >  necessary to mysqldump it?  Are you only trying to avoid having to also
> >  backup the mysqld version that wrote the data files (to ensure that
> > these can be read later)?
> >
> > George
> The LVM snapshot will hold the state of the database as it is written to
> the  disk.  However, the database engine may have to write several things
> to the disk to ensure consistency (e.g, main table and index). If you take
> the snapshot between these two operations the database will be in an
> inconsistent state.  Mysqldump locks the tables before performing a dump
> to prevent this.
> 
> ---
> 
> William R. Mussatto, Senior Systems Engineer
> http://www.csz.com
> Ph. 909-920-9154 ext. 27
> FAX. 909-608-7061

William,

Doesn't "TABLES WITH READ LOCK" avoid the kind of db inconsistency problems you describe?

Jim's original post, http://lists.mysql.com/mysql/188871, specified step one as

 1. effectively quiesce and stabilize the database via "flush tables
with read lock"

which, according to http://dev.mysql.com/doc/mysql/en/flush.html, 

  Closes all open tables and locks all tables for all
  databases with a read lock until you execute
  UNLOCK TABLES. This is very convenient
  way to get backups if you have a filesystem such as
  Veritas that can take snapshots in time.

George




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

Re: Table names with periods

[Patrick]

There are many ways to approach this.  How are you receiving the IP data?  Are 
you reading a file or other stream or are you trying to process the table 
creation by reading a column from a previously populated table through a select 
statement?

The functions, inet_ntoa() and inet_addr(), are part of most networking 
libraries. These are the common functions to convert dotted quad notation. If 
you wanted to write you own function, an IPv4 address is broken down as follows:

AAA.BBB.CCC.DDD
  \   \   \   \
\   \   \   DDD
  \   \   CCC x CCC
\   BBB x BBB x BBB
  AAA x AAA x AAA x AAA

Add the results and you have your unique 32bit number. 

eg. 10.10.10.1 = 10,000 + 1,000 + 100 + 1
   = 11,101


If you are not able to pre-process (scrub) the incoming data programmatically, 
you would need to create a UDF in MySQL to perform the conversion, or, 
alternatively, if you want to use MySQL SELECT statement as-is could replace 
the 'period' with an 'underscore' using MySQL's built-in string functions like 
so:

Assumptions: Reading IP address from an existing table named IP_Addresses with 
a column named ip_address and a column named status.

SELECT  "CREATE TABLE ",REPLACE(ip_address,'.','_'), " [insert create 
options here]" FROM IP_Addresses WHERE status ="ACTIVE"

You would obviously add your "CREATE TABLE" options and "INTO OUTFILE" options 
as needed.
This would be an alternative to converting IPv4 to 32bit Integer.

I hope this helps...

If at all possible, it is probably best to continue in the MySQL list, there 
are some pretty clever people out there

Pat...

[EMAIL PROTECTED]
CocoNet Corporation
SW Florida's First ISP
825 SE 47th Terrace
Cape Coral, FL 33904

  - Original Message - 
  From: Chance Ellis 
  To: Patrick 
  Sent: Monday, October 03, 2005 2:22 PM
  Subject: Re: Table names with periods


  Patrick,

  I have been trying to figure out how I can convert an IP address to a 32bit 
integer within a SQL create statement.

  Is this possible or am I thinking about this all wrong? The input I am given 
is a straight IP address. I have no way of modifying it other than some option 
in the SQL create statement is possible. I want to create a new table for each 
IP address. Without getting too much into the details, these are my 
requirements and I have been wasting alot of time trying to figure out how to 
change this string in the create statement. 

  Any help you can provide is greatly appreciated.

  Thanks!

  Chance


   
  On 9/28/05, Patrick <[EMAIL PROTECTED]> wrote: 
Historically any form of punctuation, parameter delimiter, or filepath
delimiter in either a database name, field or column name, file or table 
name would not be recommended; even if the RDBMS or File Handler allows it.

If you are able to stick to alphanumeric characters using underscores
characters if needed for clarity, you go a long way for portability across 
various operating systems. Also, IPv4 addresses are readily converted to
single 32bit integers that minimize the need for dotted quartets.

Early versions of MySQL allowed periods.  This caused OS incompatibility 
issues.  To my knowledge this was fixed prior to version 3 and you are no
longer allowed periods in database or table names.  This, in my thinking, is
a good thing by assuring greater portability and easier migration. 

I hope this helps.

Pat...

[EMAIL PROTECTED]
CocoNet Corporation
SW Florida's First ISP
825 SE 47th Terrace
Cape Coral, FL 33904

database design

[Matthew Lenz]

anyone using openoffice:base to design mysql db's?  back when I tried it
earlier this year it wasn't able to define relationships which made it
pretty much useless as a time saving tool.

-Matt


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

LASSO TIPS for MYSQL: 3.0 THE LASSO APP

[m i l e s]

   - Hi and Welcome to -


   LASSO TIPS FOR MYSQL: 3.0


I'm your host, M i l e s.

First and foremost, a good place for you to start with Lasso is the  
following 5 things:


The FIRST LASSO TIPS FOR NEWBIES
-> http://www.listsearch.com/lassotalk.lasso?id=143312
The LAST LASSO TIPS FOR NEWBIES
-> http://www.listsearch.com/lassotalk.lasso?id=153613
10 LASSO RESOURCES
-> http://www.listsearch.com/lassotalk.lasso?id=143018
THE LASSO RESOURCES ADDENDUM
-> http://www.listsearch.com/lassotalk.lasso?id=143417
OMNIPILOT RESOURCE LIST
-> http://www.omnipilot.com/Resources+for+Beginners.2225.lasso
   http://www.omnipilot.com/Tip+of+the+Week.1768.lasso
   http://www.omnipilot.com/Hosting+Providers.1744.lasso
   http://www.omnipilot.com/Frequently+Asked+Questions.1791.lasso


   TODAYS TIP:
   T h e   L a s s o   A p p


So there you are, code all done, functions working perfectly,  
and all done several days ahead of schedule.  When the phone rings  
and its another developer friend who tells you that he just got  
ripped off.  His client just up and disappeared with the code he'd  
spent weeks working on, weeks.  Some of his best work, and he's got  
nothing to show for it, absolutely NOTHING!  He's worried about  
paying the rent, and you being the kind soul that you are, offer to  
help him out a bit till things get better.  You hang up the phone and  
begin to wonder...while the app that you just finished is not your  
best work, it is very clean, very stable and will make your client  
very happy.  You wonder will your client pay you or skip town with  
the goods never to be heard from again ?  This is a new client and  
you've only done one small project with this client before...a few  
pages, after another developer just 'mysteriously' disappeared on the  
client - so the client says.  He was a lil late in paying you the  
last time and this time around while he dropped a good amount  
upfront, he's been rather silent of late.  You start to worry, "Is  
this guy for real ?".  You have a solid development contract, but as  
a favorite character of yours points out in response to a similar  
question, "INK on the PAGE!".  Which is to say that your friend  
pointed this out to you as well and look where THAT got him.  You  
being the resourceful lil Lasso Developer, you ask the all important  
question, "Does Lasso have anything that could protect my code ?".


In the all too real scenario above of the digital age where  
clients come and go like the wind, which Ive had happen a few times  
over my career, there was up until Lasso 5 no way to protect your  
code from at the very least being modified.  Enter the LassoApp.


 ++
   TO SEE THE REST OF THIS LASSO TIP
 ++

 THE CURRENT LASSO TIP for MYSQL:

 http://www.listsearch.com/lassotalk.lasso?id=153881

 THE LAST 5 LASSO TIPS for MYSQL:

 LTƒM 2.9: The Login Routine ->> http://www.listsearch.com/ 
lassotalk.lasso?id=153613
 LTƒM 2.8: SQL and Lasso (pt2) ->>  http://www.listsearch.com/ 
lassotalk.lasso?id=149158
 LTƒM 2.7: SQL and Lasso (pt1) ->>  http://www.listsearch.com/ 
lassotalk.lasso?id=148892
 LTƒM 2.6: LASSO STUDIO for ECLIPSE PT 2. ->>  http:// 
www.listsearch.com/lassotalk.lasso?id=148569
 LTƒM 2.5: LASSO STUDIO for ECLIPSE PT 1. ->>  http:// 
www.listsearch.com/lassotalk.lasso?id=148197


M i l e s.

  BRINGING THE LASSO LIGHT TO THE UNWASHED MASSES 
  -don't know what lasso is ? - http://www.omnipilot.com/

M i l e s  [EMAIL PROTECTED]
MagicMiles Software (415) 686 - 6164
http://www.lassoevangelist.com/   AIM/Yahoo/MSN:  magikmiles

"The strangeness of this life can not be measured, in trying
to produce my own death, I was elevated to the status of a
living hero." - Lt. John J. Dunbar. - 'Dances With Wolves'




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

Re: Indexing and no values

[Gleb Paharenko]

Hello.



> What's the reason behind this?



If you're asking about why I've written that columns defined as NOT NULL

are faster - I've read it in one of the articles from dev.mysql.com.

I'm not sure about the true reason, but the way MySQL stores NULLs

is strongly dependent on the storage engine. Dig in documentation which

is available in MySQL development tree about the physical structure of

the index for different storage engines.





Jigal van Hemert wrote:

> Gleb Paharenko wrote:

> 

>> I'm not giving an exact answer on your question, however, it might be

>> interesting for you. Usually queries are faster if you define the

>> column as NOT NULL.

> 

> 

> What's the reason behind this? NULL 'values' are a bit of strange

> phenomenon. In the EXPLAIN output a query with WHERE  NOT NULL; is

> of type 'range', which implies that NULL has a position in the range of

> values of the column. On the other hand UNIQUE indexes allow multiple

> NULL 'values' (except for BDB tables) and the storage space for various

> data types does not leave room for an extra 'value' in the range.

> 

> It almost seems as if NULL is stored as a kind of prefix in an index?

> 

> Regards, Jigal.

> 



-- 
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: Security Question

[Armando]

If it's a DoS attack then perhaps you should be speaking to your ISP and 
getting that resolved rather than trying to work around the problem on 
your side of things!


Having said that, you could possibly impose host level restrictions in 
MySQL, but that could be a lot of work to modify your existing user 
base, especially since you'd need to gather all your remote host 
information first, and then do all the updates. Cheers.


Armando

J.R. Bullington wrote:

Hi All --

I have been a member of this list for a while but I actually have a 
question that I can't answer.


MySQL v4.1.14-nt on Win2k3 Server

I've got someone who is trying to get in, but I have locked it down. 
Methods used include, but are not limited to:


No Outside Root Access
System DSNs for Web connectivity
Strong Passwords for each user
User Permissions different for each purpose


Here's the question -- It's a DoS attack and it's locking up the system 
for other users (max_connections_allowed).


Anything I can do extra via MySQL that will keep this person away, or 
perhaps free up the server? I would rather not increase the 
max_conn_allowed var as it's already at 800 (more than I need).


Do not have access to the Router (I wish I did, ACLs are such a great 
thing), but have full Admin rights to the server.


Thanks everyone!

J.R.



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

Re: Making ORDER BY RAND() more random [resend]

[Graham Anderson]

In the below query, the results first row is usually the same 3-4 
tracks out of 30.


so should I use PHP to generate a random seed, $r, and pass that to the 
query ?

ORDER BY RAND($r)
or, can it be done just with mysql

many thanks
g
On Oct 3, 2005, at 10:15 AM, Michael Stassen wrote:


Graham Anderson wrote:

is there a way to make  ORDER BY RAND() a bit more spontaneous ?
I am using it in a php statement:
$sql = "SELECT media.id,
artist.name as artist,
artist.spanish as bio,
artist.purchaseLink,
artist.picture,
media.spanish as trackName,
media.path,
media.quality,
mediaType.id as mediaType
FROM artist, media, playlistItems, mediaType
WHERE playlistItems.playlist_id = $myID
AND playlistItems.media_id = media.id
AND media.artist_id = artist.id
AND media.mediaType_id = mediaType.id
ORDER BY RAND() LIMIT 0, 30";
the result seems to be pretty predictable 
is there a way to improve RAND() or is there something better ?
Should I be using php to randomize the found set instead ?
many thanks
g


I don't think anyone can answer this, as is.  RAND() is meant to 
produce a pseudo-random sequence that is not truly random in the 
mathematical sense, but which is usually good enough for what you 
appear to be doing.  The manual 
 puts 
it this way, "RAND() is not meant to be a perfect random generator, 
but instead a fast way to generate ad hoc random numbers that is 
portable between platforms for the same MySQL version."


What is your standard for randomness?  What do you mean by "the result 
seems to be pretty predictable"?  Put another way, what are you 
expecting, and what are you getting?


Michael




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

Re: Add 15% to column?

[Subscriptions]

D'OH!  You're right.  heh  So much for the degree in computer science.  lol

Jenifer




- Original Message - 


Subscriptions wrote:

Er... do you mean this instead?

>
> UPDATE your_table
> SET decimal_field = decimal_field + (0.15 * decimal_field);

No, but they do the same thing:

decimal_field + (0.15 * decimal_field)
 = (1 * decimal_field) + (0.15 * decimal_field)
 = (1 + 0.15) * decimal_field
 =  1.15 * decimal_field


Would this work?


Yes.


Jenifer


- Original Message - From: "Michael Stassen" 
<[EMAIL PROTECTED]>

To: "Subscriptions" <[EMAIL PROTECTED]>
Cc: 
Sent: Monday, October 03, 2005 12:25 PM
Subject: Re: Add 15% to column?



Subscriptions wrote:

I have a decimal field in my table... what would be the query to add 
15%
to that number in every row? I need to mark the column up by 15% and 
want to

make sure I get the query right the first time. heh

Jenifer



  UPDATE your_table
  SET decimal_field = 1.15 * decimal_field;

Michael


--
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: Add 15% to column?

[Michael Stassen]

Subscriptions wrote:

Er... do you mean this instead?

>
> UPDATE your_table
> SET decimal_field = decimal_field + (0.15 * decimal_field);

No, but they do the same thing:

decimal_field + (0.15 * decimal_field)
 = (1 * decimal_field) + (0.15 * decimal_field)
 = (1 + 0.15) * decimal_field
 =  1.15 * decimal_field


Would this work?


Yes.


Jenifer


- Original Message - From: "Michael Stassen" 
<[EMAIL PROTECTED]>

To: "Subscriptions" <[EMAIL PROTECTED]>
Cc: 
Sent: Monday, October 03, 2005 12:25 PM
Subject: Re: Add 15% to column?



Subscriptions wrote:


I have a decimal field in my table... what would be the query to add 15%
to that number in every row? I need to mark the column up by 15% and 
want to

make sure I get the query right the first time. heh

Jenifer



  UPDATE your_table
  SET decimal_field = 1.15 * decimal_field;

Michael 


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

Re: Add 15% to column?

[Subscriptions]

Er... do you mean this instead?

UPDATE your_table
SET decimal_field = decimal_field + (0.15 * decimal_field);

Would this work?

Jenifer


- Original Message - 
From: "Michael Stassen" <[EMAIL PROTECTED]>

To: "Subscriptions" <[EMAIL PROTECTED]>
Cc: 
Sent: Monday, October 03, 2005 12:25 PM
Subject: Re: Add 15% to column?



Subscriptions wrote:

I have a decimal field in my table... what would be the query to add 15%
to that number in every row? I need to mark the column up by 15% and want 
to

make sure I get the query right the first time. heh

Jenifer


  UPDATE your_table
  SET decimal_field = 1.15 * decimal_field;

Michael 



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

Re: Add 15% to column?

[Michael Stassen]

Subscriptions wrote:

I have a decimal field in my table... what would be the query to add 15%
to that number in every row? I need to mark the column up by 15% and want to
make sure I get the query right the first time. heh

Jenifer


  UPDATE your_table
  SET decimal_field = 1.15 * decimal_field;

Michael

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

Re: How to match a binary null in a varchar column???

[Keith Ivey]

Richard F. Rebel wrote:


do I say REGEXP BINARY ""

I have tried \000 \0 as they are common representations for binary null.


Have you tried "WHERE your_column LIKE '%\0%'"?  That works for me.

--
Keith Ivey <[EMAIL PROTECTED]>
Smokefree DC
http://www.smokefreedc.org
Washington, DC

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

Re: Making ORDER BY RAND() more random [resend]

[Michael Stassen]

Graham Anderson wrote:

is there a way to make  ORDER BY RAND() a bit more spontaneous ?

I am using it in a php statement:
$sql = "SELECT media.id,
artist.name as artist,
artist.spanish as bio,
artist.purchaseLink,
artist.picture,
media.spanish as trackName,
media.path,
media.quality,
mediaType.id as mediaType
FROM artist, media, playlistItems, mediaType
WHERE playlistItems.playlist_id = $myID
AND playlistItems.media_id = media.id
AND media.artist_id = artist.id
AND media.mediaType_id = mediaType.id
ORDER BY RAND() LIMIT 0, 30";

the result seems to be pretty predictable 
is there a way to improve RAND() or is there something better ?
Should I be using php to randomize the found set instead ?

many thanks
g


I don't think anyone can answer this, as is.  RAND() is meant to produce a 
pseudo-random sequence that is not truly random in the mathematical sense, 
but which is usually good enough for what you appear to be doing.  The 
manual  puts 
it this way, "RAND() is not meant to be a perfect random generator, but 
instead a fast way to generate ad hoc random numbers that is portable 
between platforms for the same MySQL version."


What is your standard for randomness?  What do you mean by "the result seems 
to be pretty predictable"?  Put another way, what are you expecting, and 
what are you getting?


Michael


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

Re: Making ORDER BY RAND() more random [resend]

[Henry Wong]

change the seed on the random function.

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

On Mon, 2005-10-03 at 08:33 -0700, Graham Anderson wrote:
> is there a way to make  ORDER BY RAND()  at bit more spontaneous ?
> 
> I am using it in a php statement:
> $sql = "SELECT media.id,
>   artist.name as artist,
>   artist.spanish as bio,
>   artist.purchaseLink,
>   artist.picture,
>   media.spanish as trackName,
>   media.path,
>   media.quality,
>   mediaType.id as mediaType
>   FROM artist, media, playlistItems, mediaType
>   WHERE playlistItems.playlist_id = $myID
>   AND playlistItems.media_id = media.id
>   AND media.artist_id = artist.id
>   AND media.mediaType_id = mediaType.id
>  ORDER BY RAND() LIMIT 0, 30";
> 
> 
> the result seems to be pretty predictable 
> is there a way to improve RAND() or is there something better ?
> Should I be using php to randomize the found set instead ?
> 
> 
> many thanks
> g
> 
> 
> -- 
> 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: How to match a binary null in a varchar column???

[Richard F. Rebel]

Hi,

I have tried several formulations using BINARY, but cannot see how to
apply it.

do I say REGEXP BINARY ""

I have tried \000 \0 as they are common representations for binary null.



On Mon, 2005-10-03 at 17:52 +0100, Andrew Braithwaite wrote:
> Hi,
> 
> You could try the binary operator:
> 
> http://dev.mysql.com/doc/mysql/en/charset-binary-op.html
> 
> Cheers,
> 
> Andrew
> 
> -Original Message-
> From: Richard F. Rebel [mailto:[EMAIL PROTECTED] 
> Sent: Mon, 03 Oct 2005 17:48
> To: Untitled
> Subject: How to match a binary null in a varchar column???
> 
> 
> Hello,
> 
> How do you match all rows with a binary null (octal 000) in a given
> column.
> 
> I have tried all sorts of strange combinations of REGEXP and LIKE with
> no results.  I have dug in the manual, but can't seem to find anything.
> 
> Any help would be appreciated.
> 
-- 
Richard F. Rebel

cat /dev/null > `tty`


signature.asc
Description: This is a digitally signed message part

Security Question

[J.R. Bullington]

Title: Security Question






Hi All --


I have been a member of this list for a while but I actually have a question that I can't answer.


MySQL v4.1.14-nt on Win2k3 Server


I've got someone who is trying to get in, but I have locked it down. Methods used include, but are not limited to:


No Outside Root Access

System DSNs for Web connectivity

Strong Passwords for each user

User Permissions different for each purpose



Here's the question -- It's a DoS attack and it's locking up the system for other users (max_connections_allowed). 


Anything I can do extra via MySQL that will keep this person away, or perhaps free up the server? I would rather not increase the max_conn_allowed var as it's already at 800 (more than I need).

Do not have access to the Router (I wish I did, ACLs are such a great thing), but have full Admin rights to the server.


Thanks everyone!


J.R.





smime.p7s
Description: S/MIME cryptographic signature

RE: How to match a binary null in a varchar column???

[Andrew Braithwaite]

Hi,

You could try the binary operator:

http://dev.mysql.com/doc/mysql/en/charset-binary-op.html

Cheers,

Andrew

-Original Message-
From: Richard F. Rebel [mailto:[EMAIL PROTECTED] 
Sent: Mon, 03 Oct 2005 17:48
To: Untitled
Subject: How to match a binary null in a varchar column???


Hello,

How do you match all rows with a binary null (octal 000) in a given
column.

I have tried all sorts of strange combinations of REGEXP and LIKE with
no results.  I have dug in the manual, but can't seem to find anything.

Any help would be appreciated.

-- 
Richard F. Rebel

cat /dev/null > `tty`


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

How to match a binary null in a varchar column???

[Richard F. Rebel]

Hello,

How do you match all rows with a binary null (octal 000) in a given
column.

I have tried all sorts of strange combinations of REGEXP and LIKE with
no results.  I have dug in the manual, but can't seem to find anything.

Any help would be appreciated.

-- 
Richard F. Rebel

cat /dev/null > `tty`


signature.asc
Description: This is a digitally signed message part

Re: Documenting and visualizing a database

[Kevin Liu]

This looks great! Is there anything like this for Mac OS X?

Kevin

On 10/2/05 10:39 PM, "Ligaya Turmelle" <[EMAIL PROTECTED]> wrote:

> +1 - it is wonderful.
> 
> olinux wrote:
> 
>> You will love this.
>> http://www.fabforce.net/dbdesigner4/
>> 
>> Josh
>> 
>> 
>> --- Jeffrey Goldberg <[EMAIL PROTECTED]> wrote:
>> 
>>  
>> 
>>> This is probably a FAQ, but I haven't been able to
>>> find the answer.
>>> 
>>> Briefly, I am looking for tools that will help me
>>> document a database.  Visualization would be nice
>>> too, so that I could quickly see the relations
>>>
>>> 
>> between tables.
>> 
>> 
>> 
>> __
>> Yahoo! Mail - PC Magazine Editors' Choice 2005
>> http://mail.yahoo.com
>> 
>>  
>> 





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

Add 15% to column?

[Subscriptions]

I have a decimal field in my table... what would be the query to add 15% to 
that number in every row?  I need to mark the column up by 15% and want to make 
sure I get the query right the first time.  heh

Jenifer

Re: Does MySQL open the .frm file when opening a table?

[Martijn van den Burg]

Hi,

Thanks for your detailed reply.

Kind regards,

Martijn

On Sunday 02 October 2005 21:18, Gleb Paharenko wrote:
> Hello.
>
>  > For InnoDB I'm not sure, so I don't make guessings.
>
> I'm continuing the previous message. Here is what I've found out:
>
>
>   InnoDB has its own open file statistics, but currently they are not
> printed even by SHOW INNODB STATUS.
>By default, InnoDB keeps at most 300 files open. It always keeps the
> ibdata files open. Please refer to my.cnf options. If there are more
> than 300 open files, then files are closed on the LRU basis.


-- 


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

Re: modifying duplicate unique keys with LOAD DATA INFILE

[Michael Stassen]

Gerhard Prilmeier wrote:

Hello,

I use tables with one primary key (which is the only unique key). I'd 
like to export data from such a table to a file, and then import it on 
another machine.
If a duplicate unique key is found when importing with LOAD DATA INFILE, 


How does that happen?  I take it you are adding the imported data into an 
already populated table.



MySQL gives me the choice of whether to
1. stop execution with an error
2. not import rows with duplicate unique keys (using IGNORE)


It can also replace the existing rows, but that's not what you want.

What I'd like to do is to alter the unique key (either the imported or 
the existing one) to a value that does not already exist, and then 
import the row.


Don't alter the keys for the existing data!  That path leads to trouble. 
Usually, other tables will refer to rows in this table by key.  Changing 
keys breaks relationships.


Is it the case that the imported data is simply a set of new rows with no 
references to it?  If so, there's no reason to preserve the old key for any 
of the imported rows.  Instead, we just assign new keys to all the imported 
rows.  This should be relatively easy if the primary key on the destination 
table is AUTO_INCREMENT.  In that case, the simplest solution would be to 
not export the keys in the first place.  Then new keys will be assigned 
automatically when you leave out the key column during the import into the 
destination table.  Somethng like


  SELECT col1, col2, ...
  INTO OUTFILE '/tmp/export.txt'
  FROM export_table;

  LOAD DATA INFILE '/tmp/export.txt'
  INTO TABLE dest_table
  (col1, col2, ...);

where "col1, col2, ..." is all the columns except the key, or

  SELECT NULL, col1, col2, ...
  INTO OUTFILE '/tmp/export.txt'
  FROM export_table;

  LOAD DATA INFILE '/tmp/export.txt'
  INTO TABLE dest_table;

where "NULL" is in the position of the key column.

Do I have to fall back on a bunch of INSERT statements to accomplish 
this, or do you see a way to get there with LOAD DATA INFILE?


If you already have the exported data and don't want to start over, you can 
probably accomplish the same thing with a temporary table.  Something like


  # make a temporary table to match dest_table
  CREATE TEMPORARY TABLE expdata SELECT * FROM dest_table WHERE 0;

  # change the temp table to allow NULLs in the key column
  ALTER TABLE expdata CHANGE id id INT;

  # import the data int the temp table
  LOAD DATA INFILE '/tmp/export.txt'
  INTO TABLE expdata;

  # change the key column to all NULLs
  UPDATE expdata SET id = NULL;

  # copy the temp table rows into dest_table, where new auto_inc
  # keys will replace the NULLs in the imported key column
  INSERT INTO dest_table SELECT * FROM expdata;

  # clean up
  DROP TABLE expdata;


Thank you very much!
Gerhard Prilmeier


If this isn't what you need, I think we'll need more details about your 
tables and what you are trying to accomplish.


Michael

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

Making ORDER BY RAND() more random [resend]

[Graham Anderson]

is there a way to make  ORDER BY RAND()  at bit more spontaneous ?

I am using it in a php statement:
$sql = "SELECT media.id,
artist.name as artist,
artist.spanish as bio,
artist.purchaseLink,
artist.picture,
media.spanish as trackName,
media.path,
media.quality,
mediaType.id as mediaType
FROM artist, media, playlistItems, mediaType
WHERE playlistItems.playlist_id = $myID
AND playlistItems.media_id = media.id
AND media.artist_id = artist.id
AND media.mediaType_id = mediaType.id
ORDER BY RAND() LIMIT 0, 30";


the result seems to be pretty predictable 
is there a way to improve RAND() or is there something better ?
Should I be using php to randomize the found set instead ?


many thanks
g


--
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: How to call C API functions from MS Access ?

[SGreen]

"C.R. Vegelin" <[EMAIL PROTECTED]> wrote on 10/01/2005 03:51:41 AM:

> Hi List,
> 
> I want to call the C API functions from Visual Basic in MS Access 2003.
> I assume that I have to use: "c:\Program Files\MySQL\MySQL Server 4.
> 1\Bin\LibMySQL.dll".
> In my code at module level I have included various code lines to 
> declare entry-points, such as:
> Declare Function mysql_info Lib "c:\Program Files\MySQL\MySQL Server
> 4.1\Bin\LibMySQL.dll" ()
> Declare Function mysql_stat Lib "c:\Program Files\MySQL\MySQL Server
> 4.1\Bin\LibMySQL.dll" ()
> 
> After making an ADODB.Connection and running a query succesfully, I 
> want to call mysql_info().
> The syntax in C language for this function is: char *mysql_info(MYSQL 
*mysql)
> 
> My question: how to call mysql_info() from Visual Basic ?
> When I use: myString = mysql_info() it returns an empty string.
> 
> My system includes: Windows XP, MySQL 4.1.13 and MyODBC 3.51.11.
> 

To answer the question in your subject: you are already doing that 
correctly. You have selected which library you want to use and are 
declaring the entry points you want to use from that library. Good job. 

To see the status of an ADODB connection, you have to use ADODB functions. 
You cannot use the C API functions to check on the status of an ADODB 
process (even if they are sharing the same DLL). That is because the 
client library is thread-safe. Whatever you do in one instance of the 
library is not apparent in any other instance. Any bleed-over from one 
instance to another should be checked against the documentation and if the 
two are not in agreement, report it as a bug.

If you want to use the C API, all of your database calls (including all of 
your SQL execution) need to happen through the C API. If you want to use 
the ADODB objects, you need to connect through ADODB through ODBC and stay 
within that paradigm. You cannot mix or intermingle the two and expect it 
to work. Sorry!

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: Double indexes on one field

[Yannick Warnier]

Le lundi 03 octobre 2005 à 16:24 +0200, Bastian Balthazar Bux a écrit :
> Yannick Warnier wrote:
> > Le lundi 03 octobre 2005 à 12:29 +0200, Martijn Tonies a écrit :
> > 
> >>>Using PhpMyAdmin, I seldom get the warning message:
> >>>PRIMARY and INDEX keys should not both be set for column `ID`
> >>>
> >>>I understand its meaning, but I was wondering to what extent having a
> >>>field indexed AND being a primary key might slow down/speed up my
> >>>queries.
> >>>
> >>>Is that gonna take twice the time if I am searching on the ID field,
> >>>just because there are two indexes?
> >>>
> >>>I'd like to have a rough idea of how serioulsy I need to avoid these.
> >>
> >>Creating a PRIMARY KEY will automatically create an index.
> >>
> >>Why would you create a second index for that field?
> > 
> > 
> > I'm asking myself the same question. I am working on someone else's
> > database.
> > 
> > Thanks both,
> > 
> > Yannick
> > 
> > 
> 
> 
> Is it a multi-field index ? some versions of phpmyadmin show that
> messages also if only one field is duplicated.
> 
> example:
> 
> 
> CREATE TABLE `tab_sint` (
>   `id_cns` tinyint(3) unsigned NOT NULL default '0',
>   `anno_dep` smallint(4) unsigned zerofill NOT NULL default '',
>   `data_dep` smallint(4) unsigned zerofill NOT NULL default '',
>   `particolare` mediumint(8) unsigned NOT NULL default '0',
>   `generale` mediumint(8) unsigned NOT NULL default '0',
>   UNIQUE KEY `idx_cns_gen_anno`
> (`anno_dep`,`id_cns`,`generale`,`particolare`),
>   UNIQUE KEY `idx_cns_par_anno`
> (`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 AUTO_INCREMENT=87189031 ;
> 
> 
> This schema issue the warning on 'id_cns' but really make sense having 2
> indices here since they serves different kind of querys and constraints.

No, no, it's just a dumb double index :-)

I am pretty sure it is completely useless. Actually I have already
removed it.

Yannick


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

4.1 replication logs growing at a much greater rate than with 4.0

[Andrew Braithwaite]

Hi all,

 

I have just upgraded a master slave database system from 4.0 to 4.1.
the replication binlogs are now growing at a vastly greater rate.  The
queries going through are the same.  Did 4.0 use some kind of
compression by default or something?

 

Does anyone have any idea what's going on with this?  Any other pointers
will be greatly appreciated.

 

Cheers,

 

Andrew

 

Sql, query

Re: Double indexes on one field

[Bastian Balthazar Bux]

Yannick Warnier wrote:
> Le lundi 03 octobre 2005 à 12:29 +0200, Martijn Tonies a écrit :
> 
>>>Using PhpMyAdmin, I seldom get the warning message:
>>>PRIMARY and INDEX keys should not both be set for column `ID`
>>>
>>>I understand its meaning, but I was wondering to what extent having a
>>>field indexed AND being a primary key might slow down/speed up my
>>>queries.
>>>
>>>Is that gonna take twice the time if I am searching on the ID field,
>>>just because there are two indexes?
>>>
>>>I'd like to have a rough idea of how serioulsy I need to avoid these.
>>
>>Creating a PRIMARY KEY will automatically create an index.
>>
>>Why would you create a second index for that field?
> 
> 
> I'm asking myself the same question. I am working on someone else's
> database.
> 
> Thanks both,
> 
> Yannick
> 
> 


Is it a multi-field index ? some versions of phpmyadmin show that
messages also if only one field is duplicated.

example:


CREATE TABLE `tab_sint` (
  `id_cns` tinyint(3) unsigned NOT NULL default '0',
  `anno_dep` smallint(4) unsigned zerofill NOT NULL default '',
  `data_dep` smallint(4) unsigned zerofill NOT NULL default '',
  `particolare` mediumint(8) unsigned NOT NULL default '0',
  `generale` mediumint(8) unsigned NOT NULL default '0',
  UNIQUE KEY `idx_cns_gen_anno`
(`anno_dep`,`id_cns`,`generale`,`particolare`),
  UNIQUE KEY `idx_cns_par_anno`
(`id_cns`,`anno_dep`,`tipo`,`particolare`,`bis`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 PACK_KEYS=1 AUTO_INCREMENT=87189031 ;


This schema issue the warning on 'id_cns' but really make sense having 2
indices here since they serves different kind of querys and constraints.

Regards,
Francesco

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

modifying duplicate unique keys with LOAD DATA INFILE

[Gerhard Prilmeier]

Hello,

I use tables with one primary key (which is the only unique key). I'd like 
to export data from such a table to a file, and then import it on another 
machine.
If a duplicate unique key is found when importing with LOAD DATA INFILE, 
MySQL gives me the choice of whether to

1. stop execution with an error
2. not import rows with duplicate unique keys (using IGNORE)

What I'd like to do is to alter the unique key (either the imported or the 
existing one) to a value that does not already exist, and then import the 
row.


Do I have to fall back on a bunch of INSERT statements to accomplish this, 
or do you see a way to get there with LOAD DATA INFILE?


Thank you very much!
Gerhard Prilmeier 



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

Re: Double indexes on one field

[Yannick Warnier]

Le lundi 03 octobre 2005 à 12:29 +0200, Martijn Tonies a écrit :
> > Using PhpMyAdmin, I seldom get the warning message:
> > PRIMARY and INDEX keys should not both be set for column `ID`
> >
> > I understand its meaning, but I was wondering to what extent having a
> > field indexed AND being a primary key might slow down/speed up my
> > queries.
> >
> > Is that gonna take twice the time if I am searching on the ID field,
> > just because there are two indexes?
> >
> > I'd like to have a rough idea of how serioulsy I need to avoid these.
> 
> Creating a PRIMARY KEY will automatically create an index.
> 
> Why would you create a second index for that field?

I'm asking myself the same question. I am working on someone else's
database.

Thanks both,

Yannick


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

Re: Double indexes on one field

[Alec . Cawley]

Yannick Warnier <[EMAIL PROTECTED]> wrote on 03/10/2005 11:18:05:

> Hi all,
> 
> Using PhpMyAdmin, I seldom get the warning message:
> PRIMARY and INDEX keys should not both be set for column `ID`
> 
> I understand its meaning, but I was wondering to what extent having a
> field indexed AND being a primary key might slow down/speed up my
> queries.
> 
> Is that gonna take twice the time if I am searching on the ID field,
> just because there are two indexes?
> 
> I'd like to have a rough idea of how serioulsy I need to avoid these.

It will not slow down your searches at all, but it will slow down your 
inserts. Since a PRIMARY KEY is a UNIQUE index that happens to have been 
declared to be primary, you are simply storing the same information twice. 
I cannot think of any possible benefit in having two identical indexes on 
a table, and there is a cost to maintaining two index trees.

Alec
 


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

Re: Double indexes on one field

[Martijn Tonies]

> Using PhpMyAdmin, I seldom get the warning message:
> PRIMARY and INDEX keys should not both be set for column `ID`
>
> I understand its meaning, but I was wondering to what extent having a
> field indexed AND being a primary key might slow down/speed up my
> queries.
>
> Is that gonna take twice the time if I am searching on the ID field,
> just because there are two indexes?
>
> I'd like to have a rough idea of how serioulsy I need to avoid these.

Creating a PRIMARY KEY will automatically create an index.

Why would you create a second index for that field?

With regards,

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


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

Double indexes on one field

[Yannick Warnier]

Hi all,

Using PhpMyAdmin, I seldom get the warning message:
PRIMARY and INDEX keys should not both be set for column `ID`

I understand its meaning, but I was wondering to what extent having a
field indexed AND being a primary key might slow down/speed up my
queries.

Is that gonna take twice the time if I am searching on the ID field,
just because there are two indexes?

I'd like to have a rough idea of how serioulsy I need to avoid these.

Thank you,

Yannick


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

Re: Indexing and no values

[Jigal van Hemert]

Gleb Paharenko wrote:

I'm not giving an exact answer on your question, however, it might be
interesting for you. Usually queries are faster if you define the column as NOT 
NULL.


What's the reason behind this? NULL 'values' are a bit of strange 
phenomenon. In the EXPLAIN output a query with WHERE  NOT NULL; is 
of type 'range', which implies that NULL has a position in the range of 
values of the column. On the other hand UNIQUE indexes allow multiple 
NULL 'values' (except for BDB tables) and the storage space for various 
data types does not leave room for an extra 'value' in the range.


It almost seems as if NULL is stored as a kind of prefix in an index?

Regards, Jigal.

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

Re: Indexing and no values

[Gleb Paharenko]

Hello.



I'm not giving an exact answer on your question, however, it might be

interesting for you. Usually queries are faster if you define the column as NOT 
NULL.



Lefteris Tsintjelis wrote:

> Hi,

> 

> What is better/faster to insert as a value for indexed fields, in case of

> 0 or Null values, to leave it blank ('') or to insert a '0'/'NULL'?

> 

> Thnx,

> 

> Lefteris

> 



-- 
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]

Indexing and no values

[Lefteris Tsintjelis]

Hi,

What is better/faster to insert as a value for indexed fields, in case of
0 or Null values, to leave it blank ('') or to insert a '0'/'NULL'?

Thnx,

Lefteris

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