RE: Performance Spamassin PostgreSQL vs MySQL

[Miles Thompson]

We had an awkward setup, which forced us to use PGSQL for SpamAssassin. 
Unfortunately the SA queries are not processed well by PGSQL.

Back in January we switched SA processing to MySQL. Bingo! Instant improvement 
in overall performance, and no PGSQL maintenance required. This is not 
sophisticated box - about 3 yr old, 2Mbytes RAM.

- Miles

> Date: Wed, 18 Mar 2009 12:53:45 -0700
> Subject: Performance Spamassin PostgreSQL vs MySQL
> From: mussa...@csz.com
> To: mysql@lists.mysql.com
> 
> We are using the PostgreSQL currently to store the Bayes information.  It
> seems to periodically spend a lot of time 'vacumming' which of course
> drives up disk load.  The system admin has adjusted it so it only does
> this at low load.  I'm curious if anyone has actually tested the
> PostgreSQL vs MySQL versions.  We are currently running a uniprocessor
> system (Linux version 2.6.18-6-vserver-amd64 (Debian 2.6.18.dfsg.1-24).
> 
> System appears disk limited, we have the files on hardware raid 0 and have
> moved nearly everything else off that set (they are the fastest drives).
> 
> Just curious.  Thanks.
> 
> Bill Mussatto
> CyberStrategies, Inc.
> www.csz.com
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=one.point@hotmail.com
> 

_
Share photos with friends on Windows Live Messenger
http://go.microsoft.com/?linkid=9650734

Re: CVS-Like System For Database Changes

[Miles Thompson]

Michael,

We're about to head down the same road, using Subversion, and my thought was to
initially capture a series of CREATE TABLE statements and store them all in 
one file.


Then as schema was modified on the development server, update those 
statements using SVN.


Your idea looks a lot better, may I presume to outline how I think you use it?
I'm assuming you capture, for each table, an initial CREATE TABLE,  and 
save it in a file. Then as the schema changes you update the file with the 
ALTER TABLE statements, commiting the changes.


If you have to recreate the database, you execute the file up to the last 
change point.


I suppose you could do the same thing to maintain the data stored in lookup 
tables.


We're using Joomla! and have extended it quite a bit, and are now running 
three databases - dev, beta and since last week, live.


Later this week I'll be moving myself and one other developer to an SVN 
environment; we will see how it goes.



Cheers - Miles Thompson


At 07:04 PM 3/30/2007, Michael Dykman wrote:


We keep all of the schema (one file per table) in SVN (subversion)
with a directory to represent each database.  As the schema evolves,
we have had no trouble tracking changes through resource history and
are able to extract diffs on every commited change.  It works like a
charm and would proably work equally as well with CVS.

- michael


On 3/30/07, Tim Gustafson <[EMAIL PROTECTED]> wrote:

Hello!

I'm just getting in to using CVS to track changes to my source code for PHP
projects that I'm working on, and it's exactly what my organization needed.

However, there does not appear to be a way to track changes to mySQL
databases in the same way.  Basically, as the structure of tables are
changed to meet the requirements of new features, I'd like a way to be able
to record those changes (both structural table changes and also "default
table data" such as table of states or zip codes or whatever) in a CVS-type
system (preferably integrated with CVS directly) so that when a customer
uses CVS to get the newest version of the code for their project, they can
also get (and automatically apply) all changes to their database for the new
version.

Does such a system exist?  How do other people cope with these types of
updates?

Thanks for any guidance!

Tim Gustafson
(831) 425-4522 x 100
(831) 621-6299 Fax


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




--
- michael dykman
- [EMAIL PROTECTED]



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 268.18.24/741 - Release Date: 3/31/2007 8:54 
PM



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

Re: INSERT ... SELECT Challenge

[Miles Thompson]

So with a unique index on ItemI + AttributeID + Attribute_Value, this could 
be the

statement:

INSERT INTO bm_ItemsAttributes (ItemID, AttributeID, Attribute_Value) IGNORE
 SELECT DISTINCT(bmIA1.ItemID) FROM bm__ItemsAttributes bmIA1, '31', 
'default text';


which should result in a new row containg '31' and 'default text' for every 
ItemID.


Never thought of this approach - innovative.

Regards - Miles


From: "Brent Baisley" <[EMAIL PROTECTED]>

Skip the whol SELECT part an create a unique index on the fields you want 
unique (AttributeID, Attribute_Value). Then just do an INSERT IGNORE. The 
index will prevent a new non-unique from being entered and the IGNORE will 
prevent an error.


- Original Message - From: "Miles Thompson" 
<[EMAIL PROTECTED]>

To: 
Sent: Monday, March 12, 2007 3:02 PM
Subject: INSERT ... SELECT Challenge


I want to add records to an attributes table for every item which does not 
have an attribute of a given value. The problem is that some records have 
already had these values added.


Please have a look at the following query, an INSERT ... SELECT construct 
which I believe will do the job:


INSERT INTO bm_ItemsAttributes (ItemID, AttributeID, Attribute_Value)
  SELECT DISTINCT(bmIA1.ItemID) FROM bm__ItemsAttributes bmIA1
  WHERE bmIA1.ItemID NOT IN
( SELECT DISTINCT bmIA2.ItemID from bm_ItemsAttributes bmIA2
   WHERE bmIA2.AttributeID BETWEEN '31' AND '33' ), '31', 'test val';

The fields in the bm_ItemsAttributes table are ItemID, AttributeID and 
Attribute_Value and there is no primary key or autoincrement.


The SELECT query and its sub-query return a list of unique ItemID's which 
do not have any AttributeID's between 31 and 33.


I plan to run the query again to insert '32' and then '33'. I suppose one 
could build this into a stored procedure and 
thehttp://by117fd.bay117.hotmail.msn.com/cgi-bin/compose?&curmbox=FB54776F%2dC57C%2d4DCB%2dA84B%2d7F6C01495042&a=d4ad23bf331d111b37a1ca3291dc8b63f9d0a513e78101aa6abf0a85ce8c3745#

Sendn loop  through each,

Any observations will be welcomed. (Jay will probably figure out a much 
more elegant way, but I'm pretty proud to have gotten this far.)


Cheers - Miles

_
RealLiveMoms: Share your experience with Real Live Moms just like you 
http://www.reallivemoms.ca/



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





_
Have Some Fun Out Of The Sun This March Break 
http://local.live.com/?mkt=en-ca/?v=2&cid=A6D6BDB4586E357F!142



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

Re: INSERT ... SELECT Challenge

[Miles Thompson]

So with a unique index on ItemI + AttributeID + Attribute_Value, this could 
be the

statement:

INSERT INTO bm_ItemsAttributes (ItemID, AttributeID, Attribute_Value) IGNORE
 SELECT DISTINCT(bmIA1.ItemID) FROM bm__ItemsAttributes bmIA1, '31', 
'default text';


which should result in a new row containg '31' and 'default text' for every 
ItemID.


Never thought of this approach - innovative.

Regards - Miles


From: "Brent Baisley" <[EMAIL PROTECTED]>

Skip the whol SELECT part an create a unique index on the fields you want 
unique (AttributeID, Attribute_Value). Then just do an INSERT IGNORE. The 
index will prevent a new non-unique from being entered and the IGNORE will 
prevent an error.


- Original Message - From: "Miles Thompson" 
<[EMAIL PROTECTED]>

To: 
Sent: Monday, March 12, 2007 3:02 PM
Subject: INSERT ... SELECT Challenge


I want to add records to an attributes table for every item which does not 
have an attribute of a given value. The problem is that some records have 
already had these values added.


Please have a look at the following query, an INSERT ... SELECT construct 
which I believe will do the job:


INSERT INTO bm_ItemsAttributes (ItemID, AttributeID, Attribute_Value)
  SELECT DISTINCT(bmIA1.ItemID) FROM bm__ItemsAttributes bmIA1
  WHERE bmIA1.ItemID NOT IN
( SELECT DISTINCT bmIA2.ItemID from bm_ItemsAttributes bmIA2
   WHERE bmIA2.AttributeID BETWEEN '31' AND '33' ), '31', 'test val';

The fields in the bm_ItemsAttributes table are ItemID, AttributeID and 
Attribute_Value and there is no primary key or autoincrement.


The SELECT query and its sub-query return a list of unique ItemID's which 
do not have any AttributeID's between 31 and 33.


I plan to run the query again to insert '32' and then '33'. I suppose one 
could build this into a stored procedure and 
thehttp://by117fd.bay117.hotmail.msn.com/cgi-bin/compose?&curmbox=FB54776F%2dC57C%2d4DCB%2dA84B%2d7F6C01495042&a=d4ad23bf331d111b37a1ca3291dc8b63f9d0a513e78101aa6abf0a85ce8c3745#

Sendn loop  through each,

Any observations will be welcomed. (Jay will probably figure out a much 
more elegant way, but I'm pretty proud to have gotten this far.)


Cheers - Miles

_
RealLiveMoms: Share your experience with Real Live Moms just like you 
http://www.reallivemoms.ca/



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





_
Have Some Fresh Air Fun This March Break 
http://local.live.com/?mkt=en-ca/?v=2&cid=A6D6BDB4586E357F!147



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

INSERT ... SELECT Challenge

[Miles Thompson]

I want to add records to an attributes table for every item which does not 
have an attribute of a given value. The problem is that some records have 
already had these values added.


Please have a look at the following query, an INSERT ... SELECT construct 
which I believe will do the job:


INSERT INTO bm_ItemsAttributes (ItemID, AttributeID, Attribute_Value)
  SELECT DISTINCT(bmIA1.ItemID) FROM bm__ItemsAttributes bmIA1
  WHERE bmIA1.ItemID NOT IN
( SELECT DISTINCT bmIA2.ItemID from bm_ItemsAttributes bmIA2
   WHERE bmIA2.AttributeID BETWEEN '31' AND '33' ), '31', 'test val';

The fields in the bm_ItemsAttributes table are ItemID, AttributeID and 
Attribute_Value and there is no primary key or autoincrement.


The SELECT query and its sub-query return a list of unique ItemID's which do 
not have any AttributeID's between 31 and 33.


I plan to run the query again to insert '32' and then '33'. I suppose one 
could build this into a stored procedure and 
thehttp://by117fd.bay117.hotmail.msn.com/cgi-bin/compose?&curmbox=FB54776F%2dC57C%2d4DCB%2dA84B%2d7F6C01495042&a=d4ad23bf331d111b37a1ca3291dc8b63f9d0a513e78101aa6abf0a85ce8c3745#

Sendn loop  through each,

Any observations will be welcomed. (Jay will probably figure out a much more 
elegant way, but I'm pretty proud to have gotten this far.)


Cheers - Miles

_
RealLiveMoms: Share your experience with Real Live Moms just like you 
http://www.reallivemoms.ca/



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

Re: Elimination Query

[Miles Thompson]

Jay,

That looks a lot more professional - thanks.

Where can I get information on writing better SQL?

Regards - Miles Thompson


From: Jay Pipes <[EMAIL PROTECTED]>

DELETE bm_KW FROM bm_KW
INNER JOIN (
SELECT kw2.KeywordID
FROM bmjn_KW kw1
INNER JOIN bmjn_KW kw2
ON kw1.KeywordID = kw2.KeywordID
AND kw2.ItemID != '1016'
WHERE kw1.ItemID = '1016'
) AS keywords
ON bm_KW.KeywordID = keywords.KeywordID;

Miles Thompson wrote:
This query works but is there any way of making it more elegant or 
speeding it up?


DELETE from bm_KW USING bm_KW, bmjn_KW
WHERE
   bm_KW.KeywordID IN
(SELECT bmjn_KW.KeywordID FROM bmjn_KW
 WHERE bmjn_KW.ItemID = '1016' ) AND

   bm_KW.KeywordID NOT IN
(SELECT bmjn_KW.KeywordID FROM bmjn_KW
 WHERE bmjn_KW.ItemID != '1016');








_
Buy what you want when you want it on Sympatico / MSN Shopping  
http://shopping.sympatico.msn.ca/content/shp/?ctId=2,ptnrid=176,ptnrdata=081805



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

Elimination Query

[Miles Thompson]

This query works but is there any way of making it more elegant or speeding 
it up?


DELETE from bm_KW USING bm_KW, bmjn_KW
WHERE
   bm_KW.KeywordID IN
(SELECT bmjn_KW.KeywordID FROM bmjn_KW
 WHERE bmjn_KW.ItemID = '1016' ) AND

   bm_KW.KeywordID NOT IN
(SELECT bmjn_KW.KeywordID FROM bmjn_KW
 WHERE bmjn_KW.ItemID != '1016');

Its purpose is to delete only the keywords which are unique to the item 
being deleted, "1016" in this case.


The bm_KW table stores the keywords and consists of two columns: KeywordID 
and Keyword. KeywordID is an auto-incrementing primary key.


The bmjn_KW table stores only pointers to keywords and items and consists of 
two columns: ItemID - a foreign key pointing to the id of a given item - and 
KeywordID a foreign key pointing to the KeywordID in the bm_KW table.


When an item is added the bm_KW table is searched to determine if any of the 
keywords used to describe it have been used before. If so a record is added 
to bmjn_KW referencing the item and the  KeywordID in bm_KW.


If the keyword has not been used it is added to bm_KW and then referenced as 
described above.


Any thoughts or opinions?

Regards - Miles Thompson

_
Win a trip for four to a concert anywhere in the world! 
http://www.mobilelivetour.ca/



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

Re: Really worried about DELETE statement - THANKS

[Miles Thompson]

At 10:42 PM 2/25/2007, mos wrote:


At 06:13 PM 2/25/2007, Miles Thompson wrote:


Would someone please check this delete query?

This should delete all rows from the geodesic_user_data that have no 
match in the subscriber table, but another set of eyes would be appreciated.


DELETE geodesic_user_data
FROM geodesic_user_data
LEFT JOIN subscriber ON 
geodesic_classifieds_userdata.id=subscriber.GeoClassID

WHERE subscriber.GeoClassID IS NULL;

Our hosting company uses MySQL 4.0.17 so the subquery approach is not 
possible.


Thanks in advance - Miles


Miles,
It looks fine but you could have checked it yourself using:

select *
FROM geodesic_user_data
LEFT JOIN subscriber ON geodesic_classifieds_userdata.id=subscriber.GeoClassID
WHERE subscriber.GeoClassID IS NULL;
--


Thanks Mos, and to everyone else who responded.

I had tested it with a SELECT; it was just that other set of eyes that I 
needed for confirmation.


I did what I should have donein the first place - exported the appropriate 
tables with data, created them on my computer, and tested the deletes. They 
worked fine.


Tomorrow evening they will get run for real.

Regards - Miles  



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.412 / Virus Database: 268.18.4/702 - Release Date: 2/25/2007



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

Really worried about DELETE statement

[Miles Thompson]

Would someone please check this delete query?

This should delete all rows from the geodesic_user_data that have no match 
in the subscriber table, but another set of eyes would be appreciated.


DELETE geodesic_user_data
FROM geodesic_user_data
LEFT JOIN subscriber ON geodesic_classifieds_userdata.id=subscriber.GeoClassID
WHERE subscriber.GeoClassID IS NULL;

Our hosting company uses MySQL 4.0.17 so the subquery approach is not possible.

Thanks in advance - Miles


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.412 / Virus Database: 268.18.3/700 - Release Date: 2/24/2007



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

4.0.17 subquery

[Miles Thompson]

This works:
SELECT geodesic_classifieds_userdata.id FROM geodesic_classifieds_userdata
  LEFT JOIN subscriber ON 
geodesic_classifieds_userdata.id=subscriber.GeoClassID

  WHERE subscriber.GeoClassID IS NULL;


Please check this syntax for a DELETE statement

DELETE geodesic_user_data gud, subscriber s
FROM gud
WHERE gud.id != s.GeoClassID

or would this work:

DELETE geodesic_user_data gud, subscriber s
FROM gud
LEFT JOIN subscriber ON geodesic_classifieds_userdata.id=subscriber.GeoClassID
WHERE subscriber.GeoClassID IS NULL;

We're stuck with MySQL 4.0.17 so the subquery approach isn't possible.

Regards - Miles


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.412 / Virus Database: 268.18.3/696 - Release Date: 2/21/2007



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

Re: best way to query this table

[Miles Thompson]

At 03:31 PM 1/24/2007, Martijn Tonies wrote:


Hello Randy,

> I have a table that has a column that has a timestamp Column.
>
> This table is going to get very large ( 1 or 2 million rows) and will
> be queried alot.
>
> Alot of these queries will be against timestamp column.
>
> These queries will be like, getting entries for today, this week and this
month
>
> I have indexed the column, but i am wondering if i should create a
> couple of int columns that would be day of week, week of year and
> month and query against these instead.
>
> would love you opinions

Indices are all about "selectivity", the higher the selectivity, the better.

The maximum selectitivy is "1", that means that for each row, there's
a unique value.

If you created columns for days/months, the selectivity for each of
these will be lower than the selectivity for the timestamp column.

So an index on the timestamp column is better. Make sure that your
query uses the index and you should be doing well.

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


And all I would add to that, is make certain your queries cover date ranges 
so you maximize use of the index.


Sounds obvious, I know. We sometimes forget the obvious.

Cheers - Miles


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.17.8/649 - Release Date: 1/23/2007



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

Re: Query skips one set of records

[Miles Thompson]

Peter (and Felix),

Thank you, and I learned something today. Now I have a script that needs 
adjusting.

The amount certainly is "indeterminate".

The wonder is that we were able to run with this for so long.

Regards - Miles

At 03:57 PM 1/21/2007, Peter Brawley wrote:


Miles

Try it yourself with table tbl (id,cost) with rows (1,10),(1,50),(2,100):
SELECT
 id,
 IF( cost=10, SUM(cost*2 )+ 200, SUM(cost*12)) AS Amount
FROM tbl
GROUP BY id,cost;
+--++
| id   | Amount |
+--++
|1 |320 |
|2 |   1200 |
+--++
SELECT
 id,
 IF( cost=10, SUM(cost*2 )+ 200, SUM(cost*12)) AS Amount
FROM tbl
GROUP BY id,cost;
+--++
| id   | Amount |
+--++
|1 |220 |
|1 |600 |
|2 |   1200 |
+--++

Unless (i) there is exactly one unique value of cost per id, or (ii) the 
query groups by cost as well as id, retrieved cost values are indeterminate.


PB

-----

Miles Thompson wrote:

At 03:58 AM 1/19/2007, Felix Geerinckx wrote:


[EMAIL PROTECTED] (Miles Thompson) wrote in
news:[EMAIL PROTECTED]:


> This query:
>
> SELECT
>  member_id,
>  member_sub_id,
>  IF( ( monthly_cost = 10 ), ( SUM(( monthly_cost * 2.00 ) + 200 )
>  ), ( SUM(
> monthly_cost * 12.00 ) ) ) AS Amount
> FROM subinfo
> WHERE
>  MONTH(anniv_bill_date) = 12 AND
>  MONTH(fetch_date) = 12 AND
>  YEAR(fetch_date) = 2006 AND
>  pay_method = 'Invoice'
>  GROUP BY member_id
>
> Should return Amount as $280 : ( 10*2 ) + 200 for the first record
> plus 5 * 12 for the next one. Instead it is returning $180.


The monthly_cost column is indeterminate (read meaningless) outside of an
aggregate function, since you're not grouping on it.
See http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html

--
felix


Felix,

Sorry, but I do not understand what you are trying to explain. I have 
looked at that page in the manual, but I'm no clearer on its meaning.


What I understand my SQL statement to say, is for every member_id add up 
the monthly cost, subject to this condition: If the monthly cost = 10 
then calculate  monthly_cost *2 plus 200 and add it to the total for that 
member_id, otherwise calculate monthly_cost * 12 and add that to the 
total for the member_id.


How does this differ from the second example given in the previous 
section, http://dev.mysql.com/doc/refman/4.1/en/group-by-modifiers.html ?


What would the point be on grouping on monthly_cost? Apart from that, it 
works just fine for all other member_id's.


If you could bring a little more light I would appreciate it.

Regards - Miles



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007




--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007



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

Re: changing from mysql5 to mysql4

[Miles Thompson]

At 02:55 PM 1/21/2007, Thibaud Hulin wrote:


Hello !
I need of koha, a software for libraries. It works with mysql 4, but it's 
not compatible with mysql5.
How can do this changement with a Debian Etch ? Must I only remove the one 
and install the other ?

Thanks for help,
Thibaud.


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


I imagine there is a way to run both, but do ont know how. Check the mysql 
docs.


I would not trust a backward revision. Use mysqldump to get all your data 
out, then remove 5 and install 4.


Why or how is koha not compatible with version 5?

Regards - Miles Thompson 



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007



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

Re: Query skips one set of records

[Miles Thompson]

At 03:58 AM 1/19/2007, Felix Geerinckx wrote:


[EMAIL PROTECTED] (Miles Thompson) wrote in
news:[EMAIL PROTECTED]:


> This query:
>
> SELECT
>  member_id,
>  member_sub_id,
>  IF( ( monthly_cost = 10 ), ( SUM(( monthly_cost * 2.00 ) + 200 )
>  ), ( SUM(
> monthly_cost * 12.00 ) ) ) AS Amount
> FROM subinfo
> WHERE
>  MONTH(anniv_bill_date) = 12 AND
>  MONTH(fetch_date) = 12 AND
>  YEAR(fetch_date) = 2006 AND
>  pay_method = 'Invoice'
>  GROUP BY member_id
>
> Should return Amount as $280 : ( 10*2 ) + 200 for the first record
> plus 5 * 12 for the next one. Instead it is returning $180.


The monthly_cost column is indeterminate (read meaningless) outside of an
aggregate function, since you're not grouping on it.
See http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-fields.html

--
felix


Felix,

Sorry, but I do not understand what you are trying to explain. I have 
looked at that page in the manual, but I'm no clearer on its meaning.


What I understand my SQL statement to say, is for every member_id add up 
the monthly cost, subject to this condition: If the monthly cost = 10 then 
calculate  monthly_cost *2 plus 200 and add it to the total for that 
member_id, otherwise calculate monthly_cost * 12 and add that to the total 
for the member_id.


How does this differ from the second example given in the previous section, 
http://dev.mysql.com/doc/refman/4.1/en/group-by-modifiers.html ?


What would the point be on grouping on monthly_cost? Apart from that, it 
works just fine for all other member_id's.


If you could bring a little more light I would appreciate it.

Regards - Miles 



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007



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

Re: Query skips one set of records

[Miles Thompson]

At 12:14 AM 1/19/2007, Dan Nelson wrote:


In the last episode (Jan 18), Miles Thompson said:
> The query displayed below performs flawlessly, except for these two 
records:

>
> 
7364  M0174000250510  Invoice 2006-12-13 
2006-12-13 2006-12-31
> 7365  M01740  002506  5   Invoice 
2006-12-13  2006-12-132006-12-31

>
> Here's the table structure:
>
> member_idvarchar(6)
> member_sub_idvarchar(6)
> pay_method   varchar(8)
> monthly_cost decimal(11,0)
> anniv_bill_date  date
> dtCreateddate
> fetch_date   date
>
> This query:
>
> SELECT
>   member_id,
>   member_sub_id,
>   IF( ( monthly_cost = 10 ), ( SUM(( monthly_cost * 2.00 ) + 200 ) ),
>   ( SUM( monthly_cost * 12.00 ) ) ) AS Amount
> FROM subinfo
> WHERE
>   MONTH(anniv_bill_date) = 12 AND
>   MONTH(fetch_date) = 12 AND
>   YEAR(fetch_date) = 2006 AND
>   pay_method = 'Invoice'
>   GROUP BY member_id
>
> Should return Amount as $280 : ( 10*2 ) + 200 for the first record
> plus 5 * 12 for the next one. Instead it is returning $180.

Not for me:

mysql> create table subinfo ( member_id varchar(6), member_sub_id varchar(6),
 pay_method varchar(8),monthly_cost decimal(11,0), anniv_bill_date date,
 dtCreated date, fetch_date date);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into subinfo values
  ("7364","M01740","Invoice","10","2006-12-13","2006-12-13","2006-12-31"),
  ("7365","M01740","Invoice","5","2006-12-13","2006-12-13","2006-12-31");
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT member_id, member_sub_id, IF( ( monthly_cost = 10 ),
(SUM(( monthly_cost * 2.00 ) + 200 ) ), ( SUM( monthly_cost * 12.00 ) ))
AS Amount FROM subinfo WHERE MONTH(anniv_bill_date) = 12 AND
MONTH(fetch_date) = 12 AND YEAR(fetch_date) = 2006 AND pay_method =
'Invoice' GROUP BY member_id;
+---+---++
| member_id | member_sub_id | Amount |
+---+---++
| 7364  | M01740| 220.00 |
| 7365  | M01740|  60.00 |
+---+---++
2 rows in set (0.10 sec)

mysql> select version();
+---+
| version() |
+---+
| 3.23.58   |
+---+
1 row in set (0.00 sec)

I get the same result on 5.1.14, too.  Try selecting count(*) along
with the other columns in your query and verify that another record
isn't sneaking in and getting totalled up.

--
Dan Nelson
[EMAIL PROTECTED]


Dan,

I thought I had proofed the message carefully when I sent it - your results 
are correct, but I should have left off the column containing the 7364 and 
7365. Those are auto incrementing primary keys. The grouping, and summing, 
is to be done on the "MO1740" column.


I upgraded to MySQL 4.1 and results remained the same.

Thank you for looking at this.
Regards - Miles



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007



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

Re: Visual Basic 6 + MySQL

[Miles Thompson]

At 04:39 PM 1/19/2007, Nuno Vaz Oliveira wrote:



Hello!

I'm thinking to develop a database interface for our company and I thought 
"great" when I discovered that Visual Basic 6 was able to interact with 
MySQL. That's because VB6 is very easy and PHP (even being fast, but its 
hard for me) gets slower because the source code for HTML pages needs to 
go from the server to the client and big pages must carry the data and the 
tags, images, css, formating, etc...


However I'm not going to start a project without knowing that VB6+ODBC 
Driver is able to do all the needed operations  to 
connect/read/write/insert/update a database.


Additionaly I would like to know if I can manage users from VB and to 
backup the DB.


If anyone can tell me something about it I appreciate. I would also "like" 
(Not Mandatory) to use a secure connection (SSL) but I don't know if this 
is possible using VB6/ODBC Driver.


Note: The MySQL Database will be served from our company server.
(Any links to tutorials/tips/recommendations/forums about this subject are 
also welcome)


Thanks

- Nuno


Why VB6? This isn't an anti-Microsoft position, but that language has been 
left to die on the vine. So I'd think about .NET, or RealBASIC, or ... or 
 You will have to decide.


I trust you realize that in VB ( 6 or .NET) you are doing desktop 
development, not Internet development; so take a hard look at what can be 
done with a back end scripting language like PHP, JavaScript on the client 
side, and the use of AJAX. You might come close to what you need.


Having said that, from the second through fourth paragraphs of your email, 
you do not really seem know what you are about. Why don't you build a 
trivial little application in VB working from a MySQL database and see if 
it does what you want it to do. Write a routine to stuff it full of pseudo 
data, using tables / indexes which will approximate the size of your 
envisioned application. As for relative difficulty, both PHP and VB are 
very easy languages to use, but are used in completely different contexts.


If your thinking that VB is "easy" is based on Microsoft's demonstrations, 
believe me the real world does not work that way; there is much more back 
end plumbing involved.


Regards - Miles Thompson 



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.17.3/642 - Release Date: 1/20/2007



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

Query skips one set of records

[Miles Thompson]

The query displayed below performs flawlessly, except for these two records:

7364M0174000250510  Invoice 2006-12-13  
2006-12-13  2006-12-31
7365M01740  002506  5   Invoice 2006-12-13  2006-12-13  
2006-12-31

Here's the table structure:

member_idvarchar(6)
member_sub_idvarchar(6)
pay_method   varchar(8)
monthly_cost decimal(11,0)
anniv_bill_date  date
dtCreateddate
fetch_date   date


This query:

SELECT
member_id,
member_sub_id,
	IF( ( monthly_cost = 10 ), ( SUM(( monthly_cost * 2.00 ) + 200 ) ), ( SUM( 
monthly_cost * 12.00 ) ) ) AS Amount

FROM subinfo
WHERE
MONTH(anniv_bill_date) = 12 AND
MONTH(fetch_date) = 12 AND
YEAR(fetch_date) = 2006 AND
pay_method = 'Invoice'
GROUP BY member_id

Should return Amount as $280 : ( 10*2 ) + 200 for the first record plus 5 * 
12 for the next one.

Instead it is returning $180.

Other records which have similar conditions are processed with no 
difficulty. Is this kind of intermittency a bug in MySQL 3.23?


If anyone has any suggestions, then I would love to know it. If I execute a 
test query without the SUM() function and GROUP BY, but using all of the 
other WHERE conditions, these two records are flawlessly selected from the 
data set. With SUM() and GROUP BY - wrong results, for only these two.


Suggestions or hints will be welcome.

I can now upgrade to MySQL 4.x, so I will do that.

Regards - Miles Thompson


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.410 / Virus Database: 268.16.14/636 - Release Date: 1/18/2007



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

Re: database design help

[Miles Thompson]

At 12:54 PM 12/16/2006, you wrote:



Hiya,

Real easy quick question.
I need to design a database which holds users with email, name and some
other details.
I also want each user to be able to create one or more groups of users,
owned by themselves.
What would be the best design approach?

So far i have a table for the users which stores their personal details, but
i dont know where to go from here to create the groups? Create a new table
for every group? The group would just contain a list of the users emails in
that group.
Or would i create a new table for the groups and attach a password field on
it so only the user that created it could access it?

A very newbie question i know, but i am one, i'll admit it.

Any help would be apprectiated.

Thanks

John
--
View this message in context: 
http://www.nabble.com/database-design-help-tf2832533.html#a7908028

Sent from the MySQL - General mailing list archive at Nabble.com.


Presumably users is something like this:
Members table
ID - numeric, auto-generated
BelongsTo - group id
Firstname
Surname
email
phone
etc.

But that's not right - A User can belong to only one group, most probably 
want to belong to many - so remove the BelongsTo field and let a refernce 
to the ID field of the membertable do the work


GroupMembers table

ID - numeric, autogenerated
Group_ID - numeric, foreign key
Member_ID - numeric, foreign key - refers to ID field in Members table

and of course a Groups table,

ID - numeric, autogenerated
Managed_By - foreign key, refers to ID field in Members table
Name
other pertinent stuff


There you go - three tables able to hold unlimited combinations of groups 
and members and you will never have a many to many problem.


Cheers - Miles

other info .






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.15.21/589 - Release Date: 12/15/2006



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

Re: MyISAM vs InnoDB

[Miles Thompson]

At 07:56 AM 11/1/2006, Leandro Guimarães Faria Corcete DUTRA wrote:


 .. further notices; and SolidDB, which
is still β.

Choose your evil.

--
Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel)
Administrador de (Bases de) Dados +55 (11) 2122 0302 (com)
http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res)
BRASILmailto:[EMAIL PROTECTED]



Leandro,

Help this poor English-speaker - what's the symbol you use to describe SolidDB?

Cheers - Miles Thompson



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.13.21/509 - Release Date: 10/31/2006




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

Re: multiple primary keys on one table?

[Miles Thompson]

At 06:26 PM 10/5/2006, Ferindo Middleton wrote:


I have a primary key set on a table which consists of the combination of the
values: firstname, lastname, and a schedule_id (BIGINT(20))... I have this
so the records in this table do not have duplicates, being that no one
record should have the exact same name and schedule_id identifier.

However, I want to keep this same restriction while also ensuring that no
two records have the same email_address and schedule_id identifier...

You can't have the db enforce two different primary keys on one table, so
how would I implement having this kind of restriction, which, in itself,
seems to require that I have a second primary key to enforce another
constraint to dissalow records to be added that carry the same combination
of: email_address and schedule_id?

just ferindo


unique index

M. 



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.407 / Virus Database: 268.12.13/463 - Release Date: 10/4/2006



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

Re: SELECT counting chars with LEFT()

[Miles Thompson]

At 10:55 AM 9/6/2006, spacemarc wrote:


Hi,
I want to get all records from Tab1 and the first 20 words for the fieldA

SELECT *, LEFT(fieldA, 20) AS fieldA FROM Tab1

But this query does not work: why?

thanks!

--
http://www.spacemarc.it


First of all - please reply to the list ..

I thought you wanted all fields, that's the way your SELECT statement is 
constructed. If just the first 20 char from fieldA, then ...

SELECT LEFT(fieldA, 20) AS fieldA FROM Tab1
should do what you want. Again, check the syntax for LEFT() in the MySQL docs.

Miles 



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.11.7/438 - Release Date: 9/5/2006



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

Re: Problems Updating Database

[Miles Thompson]

At 08:22 AM 9/6/2006, Nicholas Vettese wrote:


I have a registration script that inserts data into the database, and
then emails you an activation code to activate your account.  The
problem is that when I click on the link in the email, the account does
not get updated.  The portion of the script that has a problem is this:

$query = "UPDATE users SET active=NULL WHERE user_id=$x AND active='" .
escape_data($y) . "' LIMIT 1";
$result = mysql_query ($query) or trigger_error("Query: $query\nMySQL Error: " . mysql_error());

I do not believe this is a PHP problem because the error states that it
is most likely a MySQL error.  If anyone can see the problem, can you
please let me know what it is.  I have been looking at this for about
three days now, and I can't figure it out.

The registration page does update the DB (MySQL 3.24.49), but the
activation page does not.

Thank you,
Nick


Echo $query; test for existence of $x and $y.

Are you sure you need trigger_error()? Why not just use the built-in error 
handling?

In any case, what error message do you get?

HTH - Miles 



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.405 / Virus Database: 268.11.7/438 - Release Date: 9/5/2006



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

Re: Query problem

[Miles Thompson]

At 03:08 PM 8/3/2006, André Hänsel wrote:


Hi,

I have a table logging downloads (time, username, download).

Now I'd like to have the last 5 downloads per user.

Can someone tell me a solution (or what to search for)?

Regards,
André


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


Correlated subquery, which can be the devil to debug, and don't run really 
quickly.

I have no idea if this will work:

SELECT t1.time, t1.username, t1.download FROM downloads AS t1
WHERE t1.username = ANY
(SELECT t2.username FROM downloads AS t2 WHERE t2.username = 
t1.username)

ORDER BY t1.time DESC
LIMIT 5

Hmmm, that's just going to return 5 records; you need 5 or fewer for each 
username.


That's almost like creating a view of users, then stepping through the 
view, selecting * limit 5 where username = view.username. See where that's 
headed? You may need a temporary table.


Sorry I've not been more help.

Regards - Miles Thompson


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.10.5/406 - Release Date: 8/2/2006



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

Re: Alter Problem

[Miles Thompson]

At 11:12 AM 8/3/2006, Nicholas Vettese wrote:

I am working on a script that looks at the MySQL Table, checks to see if 
there are any matches, and if there are, alter the table to add the 
requested information.  Here is the code I am using:



</pre><tt>$query = "ALTER profile (acct_type, username, firstname, lastname, email, 
</tt><tt>addr1, city, state, zip, nude, artistic, model_look, about_you, why_model, 
</tt><tt>genre) VALUES ('$at', '$un', '$fn', '$ln', '$e', '$ad1', '$c', '$st', 
</tt><tt>'$zc', '$nd', '$art', '$ml', '$ay', '$wm', '$g')";
</tt><pre style="margin: 0em;">


What am I doing wrong?  The error comes back saying that this is where the 
problem is, and I have changed it from UPDATE to ALTER because I was 
trying to get the information to be added to the table of an already 
registered user.


Nick


Nick,

Just to add to what Peter said, and so you do not inadvertently shoot 
yourself in the foot, have a look at some tutorials, like this one:

http://www.php-mysql-tutorial.com/
which appears to be divided into sections appropriate to operations on the 
database.


Then check the MySQL docs for specifics on SELECT, INSERT and UPDATE.

Fortunately your ALTER TABLE statement was incorrect, otherwise you would 
have destroyed the table and lost all of your data.


Regards - Miles Thompson 



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.10.5/406 - Release Date: 8/2/2006



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

Re: How many columns??

[Miles Thompson]

At 06:32 AM 8/3/2006, Ratheesh K J wrote:


Hello all,

Just wanted to know how many columns are preferable in table. At present 
we are having nearly 50 - 60 columns in some of the tables. Is this ok or 
should we be splitting the tables for normalization.


If we really need to split then how better would it be in terms of 
performance.?


1) Splitting into related tables would then require Joins for data retrieval
2) Inserts/ updates will have to be done on more than one table now

It would be great to also know the best practices on number of rows that a 
table must hold.



Ratheesh Bhat K J



Read up on database normalization - there are benefits other than speed.

Retrieval is very fast when tables are appropriately indexed, so that's a 
non-issue. Similarly once a query is defined it's usually the conditions in 
the WHERE clause that change, so that is a non-issue as well.


With data normalized to third normal form, is you have to revise your 
structure or change the data you are storing, you will not break your 
application or your existing queries.


You may find, after normalizing your data, that you want to de-normalize 
part of it, at least you are doing so with full awareness of what it looks 
like in normal form.


As for number of rows - check the archives. There are tables with millions 
of rows, their number is generally not an issue. Remember that if a 
database grows and grows and grows, then suddenly performance hits the 
wall, you've likely reached the limits of the hardware it's running on, not 
the database.


Cheers - Miles


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.10.5/406 - Release Date: 8/2/2006



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

Re: MySQL performing too badly under heavy load - urgent hlp needed

[Miles Thompson]

At 09:38 AM 7/27/2006, Ratheesh K J wrote:


Hello all,

Stuck up with a major problem. Urgent hlp required

MySQL seems to be performing too bad during heavy load on the server. 
Queries which normally take around 5 secs to complete are taking more than 
1000 secs to complete during load.


What could be the reason. Show processlist shows many process in sending 
data state. All tables are of INNODB type. But we are not running any 
transactions as yet.


The server is clogged due to many httpd requests (150 Max). All the httpd 
requests are in W state ( means sending response ). What could be causing 
this. Is it MySQL or is it Apache...


Any suggestions would help...


Thanks,

Ratheesh K J


So, what have you tried? Give us some information -- knowing which version 
you are running would be a good start.


Are the queries slow if run from the command line?
Are there enough threads in Apache?

Have you rebuilt your indexes? Dropped them and replaced.
Run optimize database?
Done a dump and restore?


Miles Thompson


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.10.4/401 - Release Date: 7/26/2006



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

Re: MySQL version 4.0.23 uses 99% of processor on FreeBSD 4.11

[Miles Thompson]

At 09:15 AM 7/14/2006, Jeremiah Foster wrote:


Hello,

Nearly once or twice a day the mysql daemon on our FreeBSD server has to
be restarted since it is taking up most of the processor.

One of the entries in our slow_queries log looks like this;

# Query_time: 14074  Lock_time: 0  Rows_sent: 11  Rows_examined:
1020472928

SELECT pp.art_num, pp.showprice as price, pc.custom_price,
pc.custom_name, pc.custom_delivery, pd.instock, Stdnsv.Description FROM
product_tags pt, products_prices pp, products_dists pd,
filter_categories fc, product_linking pl  LEFT JOIN cds_Stdnsv Stdnsv ON
(Stdnsv.ProdID = pp.art_num) LEFT JOIN products_custom pc ON (pc.art_num
= pp.art_num) WHERE pp.art_num = pl.art_num AND pp.art_num = pd.art_num
AND fc.filter_type_id = 1 AND fc.filter_id = pl.filter_id AND
(Stdnsv.Description != 'None' OR Stdnsv.Description != '') AND
(Stdnsv.Description LIKE '%Media%Center%' OR pl.art_num LIKE '%Media%
Center%') GROUP BY pp.art_num ORDER BY pp.showprice DESC LIMIT 100;

That is about 3 hours and a billion rows examined. Is it likely that
this is the source of my problems?

Thanks very much,

Jeremiah


Jeremiah,

I'd say YES. But first, how much time does it take if you remove the join 
conditions for the LIKEs?


There are two difficulties there:
1. Use of LIKE with wildcard search forces a table scan, although 
see [1] below.

2. The two LIKEs are joined by an OR so
Stdnsv will be scanned until the first condition is met, 
and if it is fulfilled, then
pl is scanned until the second condition is met. Full scan 
on each table.


Alternately ...

If the first condition for Stdnsv is not met, then OR has 
failed, but you will have a full table scan on the left hand side of the OR 
regardless.


Second, have you indexes on the columns which are being compared?

Your memory usage is probably pretty high as well, as MySQL tries to do as 
much work as it can in memory.


Have you tried this:

SELECT pp.art_num, pp.showprice as price, pc.custom_price,
pc.custom_name, pc.custom_delivery, pd.instock, Stdnsv.Description
FROM product_tags pt, products_prices pp, products_dists pd,
filter_categories fc, product_linking pl, cds_Stdnsv Stdnsv
WHERE
Stdnsv.ProdID = pp.art_num AND
pc.art_num = pp.art_num AND
pp.art_num = pl.art_num AND
pp.art_num = pd.art_num AND
fc.filter_type_id = 1 AND
fc.filter_id = pl.filter_id AND
(Stdnsv.Description != 'None' OR Stdnsv.Description != '')
GROUP BY pp.art_num
ORDER BY pp.showprice DESC LIMIT 100;

If that gives weird or unexpected results, try whacking off AND conditions 
until it's fast, then think about why the query slows when they are 
added.  Note the simple WHERE is an implicit LEFT JOIN.


Also formulate time query, and time it, with  " (Stdnsv.Description LIKE 
'%Media%Center%' OR pl.art_num LIKE '%Media%

Center%') " as the WHERE.

Apologies if this has been too pedantic.

Hope this is helpful - Miles Thompson

[1] MySQL 4.0 does another optimisation on LIKE. If you use ... LIKE 
"%string%" and string is longer than 3 characters, MySQL will use the Turbo 
Boyer-Moore algorithm to initialise the pattern for the string and then use 
this pattern to perform the search quicker.
This is taken from http://mysqld.active-venture.com/MySQL_indexes.html 



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.10.0/388 - Release Date: 7/13/2006



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

Re: Field name DESC

[Miles Thompson]

At 12:42 PM 7/13/2006, Anthony wrote:


Hello,

i want to know how i can create a table with the feild name "desc" ?

when i do:


CREATE TABLE bank (
 name varchar(50) NOT NULL default '',
 desc varchar(50) NOT NULL default '',
)

the desc is badely interpreted...



In a word - DON'T.  "DESC" is a reserved word - if it is an abbreviation 
for description, use "descr", if for descending, try "dscnd".


Use of reserved words for field names is bad practice and is guaranteed to 
cause grief in the future.


Cheers - Miles Thompson 



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.9.10/387 - Release Date: 7/12/2006



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

Re: which is better long rows in table or two short row tables

[Miles Thompson]

At 08:54 AM 7/13/2006, abhishek jain wrote:


Dear Friends,
I was to create a site with quite some heavy mySQL database.
I wanted to know which is better longer rows in a table or two short rows
tables.
When compared in terms of speed etc.

Pl. help me , with this question and any other tip you may find can be
useful to me.

Thanks,
Abhishek jain


Properly denormalized data with appropriate use of indexes. Google for 
"relational data denormalise".


Without knowing your data, whether the application is heavy on INSERTs and 
UPDATEs, or if it is mostly SELECTs, it is impossible to answer your question.


Regards - Miles Thompson 



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.9.10/387 - Release Date: 7/12/2006



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

Re: Mysql-workbench 1.0.6beta not working on reverse-engineer

[Miles Thompson]

At 07:16 PM 7/12/2006, Yvan wrote:


rturnbull wrote:

Yvan,
   I used three different packages for Linux.  1) Was the source tar.gz 
2) was the rpm which I converted to a tgz file (slackware) 3) was the 
compiled binary version of the workbench.  Here are the filenames


mysql-workbench-1.0.6beta-1.i386.rpm
mysql-workbench-1.0.6beta-1.i386.tgz
mysql-workbench-1.0.6beta.tar.gz

So the version is 1.0.6.

As for the gtkmm2.4 package, I as well used that packages and still 
errors occur.  I have done some further looking into it and it appears to 
be the build that the linux packages /tar file are.  I was able to get 
everything working with the Windows GUI-TOOLS package on my windows 
system. The version of the tool is different under windows than it is 
under linux, so I think there are modifications with reverse-engineering 
that might be in the Linux packages but in the Windows version. Its a big 
disappointment that the tool doesn't work properly under *NIX systems 
(Really just reverse-engineering is broken, but I haven't tried any 
further "POWER FEATURES").



Thanks


Thanks Ryan,

I also ended up by using workbench on xp and then opening the .mwb file in 
the other systems. Not very elegant...


Thanks for your answer.

yvan


I used the Workbench under XP - once I started adding relations and foreign 
keys, everything fell apart.


So I took the generated SQL, edited it by hand and generated the tables. 
It's still pretty rough.


Regards - Miles Thompson 



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.9.10/385 - Release Date: 7/11/2006



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

Re: How to look for balanced parenthesis?

[Miles Thompson]

At 12:36 AM 7/11/2006, you wrote:


I used to use UltraEdit, but then switched to EditPlus because it can edit
remote files almost transparently. (Opening a file FTP's it down, you edit
local copy, Saving FTP's it back.)




FYI - UltraEdit can do this - It uses SFTP over SSH to edit remote files.
Works like a charm.

-Hank
mysql, query


It's funny how an editor-switch comes about. My old version of UE, could 
not do this, and I really didn't care, except that the "editor question" 
reared its head again on the PHP list. Keep in mind this was probably close 
to four years ago.


The capability of EditPlus to edit remote files was promoted as one of the 
things it could do - at the time I was working on a project for a client 
for which there was no SSH access - so I downloaded it and began using it 
occasionally ... and the use became more and more frequent.


UE still lives on my machine, but it's no longer my "daily editor" but it 
is invaluable if I have to look at a binary file. Ian Meade has a fine 
editor there. Maybe I should download the latest version and give it a try. 
When I last checked, its  FTPdown/Edit/FTPup cycle was not transparent, but 
that may have been v. 9 or v.10 - my copy is a pretty old v.7.


What I would really like would be EditPlus / UltraEdit simplicity and 
speed, with the capability to interactively provide function names and 
their parameters. Similar to VB or Zend Studio, BUT while working on the 
server. Yeah, an oxyMORONIC request. 


Cheers - Miles 



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.9.10/384 - Release Date: 7/10/2006



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

Re: How to look for balanced parenthesis?

[Miles Thompson]

At 03:48 PM 7/9/2006, Rhino wrote:



- Original Message - From: "mos" <[EMAIL PROTECTED]>
To: 
Sent: Sunday, July 09, 2006 1:35 PM
Subject: How to look for balanced parenthesis?


I have a complicated SQL statement with around a dozen 
"if(this,val1,val2)" embedded in it and there are even nested If clauses. 
I'm getting syntax errors because I'm not balancing the "( )" properly. 
Is there any free software out there for Windows that I can copy and 
paste the SQL statement into that will show me where the parenthesis are 
unbalanced? It doesn't have to know about SQL, I just to either highlight 
the parenthesis range or find out when the parenthesis become out of sync.


I'm going to give you one answer that you almost certainly won't like: 
Eclipse. Eclipse is an IDE for developing programs, especially Java, and 
it has a parenthesis matcher which also handles braces and square 
brackets. Installing Eclipse solely for the bracket matcher is a bit like 
using atomic weapons to kill mosquitos but if you were going to develop 
applications anyway and wanted a great IDE, it might be the answer to your 
problem. It's free by the way. You can get it at http://eclipse.org.


Another editor that can also match brackets is PFE, Programmer's File 
Editor. It's also free and is a good editor. You can find it many places, 
including http://www.lancs.ac.uk/staff/steveb/cpaap/pfe/pfefiles.htm. It 
only runs on Windows though.


Another decent little editor that has the feature is TextPad. It's also 
free and can be found at http://www.textpad.com/.


There are probably more basic editors out there that have bracket matchers 
but I can't name any for you. I'm not even sure what the feature you want 
is supposed to be called: "bracket matcher", "parenthesis balancer", or 
whatever. It's getting to be a pretty standard feature in editors in 
recent years, although it doesn't seem to be in NotePad or WordPad.


--
Rhino


Rhino,

Eclipse can't edit files on the server, can it?

I used to use UltraEdit, but then switched to EditPlus because it can edit 
remote files almost transparently. (Opening a file FTP's it down, you edit 
local copy, Saving FTP's it back.)


Cheers - Miles Thompson



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.9.10/383 - Release Date: 7/7/2006



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

Re: How to look for balanced parenthesis?

[Miles Thompson]

At 02:35 PM 7/9/2006, mos wrote:

I have a complicated SQL statement with around a dozen 
"if(this,val1,val2)" embedded in it and there are even nested If clauses. 
I'm getting syntax errors because I'm not balancing the "( )" properly. Is 
there any free software out there for Windows that I can copy and paste 
the SQL statement into that will show me where the parenthesis are 
unbalanced? It doesn't have to know about SQL, I just to either highlight 
the parenthesis range or find out when the parenthesis become out of sync.


TIA
(Mike))



Mike,


EditPlus will match parentheses;  I think the latest version of UltraEdit 
does as well.

It is a real help at times.

Cheers - Miles Thompson



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.9.10/383 - Release Date: 7/7/2006



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

Re: The Practical SQL Handbook

[Miles Thompson]

At 07:15 PM 6/21/2006, Karl Larsen wrote:

   I have the subject book and I want to learn SQL from this book. Alas 
the bookbiz.sql file on the cd-rom was last updated in 1996 :-)   10 
years ago. I tried to load it on my version 4 mysql and it would not get 
far at all. It appears that the software has changed since 1996.


   I did the testing and found first dates were now -mo-day and in 
1996 it was mo/day/yy and so I had to change all those. Under all the 
INSERT INTO titles VALUE entries the use of '' was not complete. And the 
sentenses were too long.


   It took about 6 hours to correct the file but it's done. It loads with 
just a couple of warnings on my version 4.1.10.



73 Karl


Karl,

That's a good book, and converting those scripts was quite a job. You would 
do a lot of people a favour if you could post the MySQL compliant version.


Please keep in mind the provided SQL was for Sybase SQLAnywhere (Ver 6?) 
and data conventions are still not standard, and in this case I believe 
it's MySQL, with its European roots, that goes against the grain.


Another great book is SQL for Smarties by Joe Celko. A number of his 
articles and exercises can be found on the web -- v. instructional.


Cheers and have fun - Miles


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.9.1/369 - Release Date: 6/19/2006



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

Re: Error 1045 - Access denied

[Miles Thompson]

Dan,

I believe Dreamhost has it MySQL locked down so that only they have primary 
access, and yes phpMyAdmin is installed on their system.


It would be .v. difficult for them to give me rights. 

This is sort of off-topic, but hub.org, which operates out of Wolfville, 
NS, has shut down the central MySQL database for all their clients and each 
client now has a private instance running in the virtual host. V. nice.


Thanks for having a look.

Miles




At 12:32 PM 6/16/2006, Dan Buettner wrote:

Miles, can you access the remote database from any other tools on your 
local machine?  mysql command line, etc.


You state you can use phpmyadmin, but is that installed at your hosting 
provider?


It's not unusual for hosting providers to lock down their database hosts 
to only allow access from other hosts on the provider's network.  Added 
security, added control.  My provider, pair Networks, by default locks the 
dbs down to their network only, but I have the option to open them up.


Dan


Miles Thompson wrote:

I have searched the MySQL Administrator forum for help on this, but no joy.
When I try to conect to a database on Dreamhost, using MySQL 
Administrator, I get this error, with a red "X" in a Windows dialog box:

(The name of the database and and user are changed)
--- 


Could not connect to the specified instance.
MySQL Error Number 1045
Access denied for user'xxxtest'@'pc-24-222-199-71.cpe.rushcomm.ca'(using
password: YES)
If you want to check the network connection, please click the PING button
[OK] [PING]
-- 

It pings OK, and I can use phpMyAdmin, I just wanted to try a more direct 
tool.
Dreamhost has not been a lot of help. I suspect it's because MySQL 
Administrator is too powerful.

Regards - Miles Thompson


--
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 outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.9.0/366 - Release Date: 6/15/2006



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

Error 1045 - Access denied

[Miles Thompson]

I have searched the MySQL Administrator forum for help on this, but no joy.

When I try to conect to a database on Dreamhost, using MySQL Administrator, 
I get this error, with a red "X" in a Windows dialog box:

(The name of the database and and user are changed)
---
Could not connect to the specified instance.
MySQL Error Number 1045
Access denied for user'xxxtest'@'pc-24-222-199-71.cpe.rushcomm.ca'(using
password: YES)

If you want to check the network connection, please click the PING button

[OK] [PING]
--

It pings OK, and I can use phpMyAdmin, I just wanted to try a more direct tool.
Dreamhost has not been a lot of help. I suspect it's because MySQL 
Administrator is too powerful.


Regards - Miles Thompson


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.9.0/366 - Release Date: 6/15/2006



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

Re: Automatically add +1 every 30mins

[Miles Thompson]

Dan,

Did not know about events in MySQL. That's a terrific feature.

Miles

At 09:44 AM 6/2/2006, Dan Buettner wrote:

Alex, as Miles noted, this could easily be accomplished with an external 
cron event.  Doesn't have to be written in an external language like PHP 
or perl, even - could be a self-contained crontab entry a la:


0,30 * * * * /path/to/mysql -u user -psecret database_name -e "update 
table_name set gold = gold + 1" > /dev/null



To accomplish this within MySQL, one option might be 5.1's events:

http://dev.mysql.com/doc/refman/5.1/en/events.html
http://dev.mysql.com/doc/refman/5.1/en/create-event.html


Hope this helps,
Dan



Alex Major wrote:
Hi there. I've posted this up on both this list, and the php list as I'm 
not sure

whether this is something that I'd need to do with the php or mysql.
Basically, I am making an add-on to my small website which is a mini online
game. Every user will have gold, and every 30mins I'd like their amount of
gold to go up by 1 (or say a variable say $goldupdateamount).
I'd like to know which would be the best way of doing this, and if there is
a command in mysql which would achieve this.
Regards, Alex.



--
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 outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.8.1/354 - Release Date: 6/1/2006



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

Re: Automatically add +1 every 30mins

[Miles Thompson]

At 07:58 AM 6/2/2006, Alex Major wrote:


Hi there.
I've posted this up on both this list, and the php list as I'm not sure
whether this is something that I'd need to do with the php or mysql.

Basically, I am making an add-on to my small website which is a mini online
game. Every user will have gold, and every 30mins I'd like their amount of
gold to go up by 1 (or say a variable say $goldupdateamount).

I'd like to know which would be the best way of doing this, and if there is
a command in mysql which would achieve this.

Regards,
Alex.


I don't know about MySQL, but for PHP you would have to run it as a cron 
(or similar OS service) to trigger a script to do the update.
If you have access to the server, this could be any language which can work 
with MySQL: Perl, Java, VB, C  ? Or run JavaScript on the player's page?


 As for MySQL, I do not believe it has a built-in timer, except for 
synchronization or replication.


Miles Thompson 



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.8.1/354 - Release Date: 6/1/2006



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

MySQL documentor - not MySQL Workbench

[Miles Thompson]

Is there a tool, preferably open source, that can read database schema 
scripts (for lack of a better  term) or connect to the database, and 
generate a diagram? This for a MySQL 5.x database.


I've been working with MySQL Workbench, and if I used it as a dumb device 
it was OK. As soon as I started adding foreign keys - BLOOM! BLOOM! - lines 
and labels everywhere. The schema code it generated need a lot of editing 
as well.


This is expecting rather a lot, but thought I would ask.

Regards - Miles Thompson


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.394 / Virus Database: 268.8.0/353 - Release Date: 5/31/2006



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

Re: Unknown option --install

[Miles Thompson]

Sheeri,

So even if I am executing mysqld-nt.exe from the directory where it is 
installed, preface it with the full path?


Never thought of that - worth a try.

Thanks - Miles

At 05:23 PM 5/12/2006, sheeri kritzer wrote:


I'm going to guess that the path variable is only looking at the MySQL
3.23 mysqld binary.  Try using a full path to the MySQL 5.0.20 binary
and see if you still get errors.

-Sheeri

On 5/10/06, Miles Thompson <[EMAIL PROTECTED]> wrote:


I am trying to install two MySQL servers to run as Windows XP services.
One for work with php-gtk+  as mysqld1,  MySQL 3.23.55 on port 3306
and another as mysqld2, MySQL 5.0.20 on port 3308 for PHP5 development and
testing.

I'm following the manual's instructions in section 5.13.1.2. Starting
Multiple Windows Servers as Services
found at 
http://dev.mysql.com/doc/refman/5.0/en/multiple-windows-services.html


The installation of mysqd1 went fine, but I consistently get an error when
trying to install mysqld2, like so:

C:\PROGRA~1\xampp\mysql\bin>mysqld-nt --install mysqld2
--defaults-file=C:/Program Files/xampp/mysql/bin/my_opts5.cnf

Which returns this error:
060510 12:18:34 [ERROR] mysqld-nt: unknown option '--install'

Huh? It's listed as one of the parameters after issuing mysqld-nt --help
--verbose.

More background:
The previous instances of MySQL services have been removed.
Have tried both forward "/" and back "\" slashes in the 
defaults-file path

Console window has been closed and reopened.
mysql.ini in the \Windows directory has been renamed to 
mysql.ini.old


Does anyone have any suggestions? They will be most welcome.

Regards - Miles Thompson


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 5/9/2006



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





--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.392 / Virus Database: 268.5.6/339 - Release Date: 5/14/2006



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

Unknown option --install

[Miles Thompson]

I am trying to install two MySQL servers to run as Windows XP services.
One for work with php-gtk+  as mysqld1,  MySQL 3.23.55 on port 3306
and another as mysqld2, MySQL 5.0.20 on port 3308 for PHP5 development and 
testing.


I'm following the manual's instructions in section 5.13.1.2. Starting 
Multiple Windows Servers as Services

found at http://dev.mysql.com/doc/refman/5.0/en/multiple-windows-services.html

The installation of mysqd1 went fine, but I consistently get an error when 
trying to install mysqld2, like so:


C:\PROGRA~1\xampp\mysql\bin>mysqld-nt --install mysqld2 
--defaults-file=C:/Program Files/xampp/mysql/bin/my_opts5.cnf


Which returns this error:
060510 12:18:34 [ERROR] mysqld-nt: unknown option '--install'

Huh? It's listed as one of the parameters after issuing mysqld-nt --help 
--verbose.


More background:
The previous instances of MySQL services have been removed.
Have tried both forward "/" and back "\" slashes in the defaults-file 
path
Console window has been closed and reopened.
mysql.ini in the \Windows directory has been renamed to mysql.ini.old

Does anyone have any suggestions? They will be most welcome.

Regards - Miles Thompson


--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 5/9/2006



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

Re: [PHP-DB] MYSQL query help

[Miles Thompson]

It's not the IN, it's the sub-query you cannot use. MySQL doesn't support 
them and it's just about the main reason I don't like it.

Alternatives? You could execute your subquery and return the results to an 
array. Loop through the array, using the index and the indexed element to 
drive a series of queries, accumulating your results as you go.

If the number of items return by the subquery is not large, you could build 
an "in set", for lack of a better term, so you would end up with " ... and 
IN ("first", "second", ..."nth") ". (Check syntax!!) But that's likely to 
be slower than cold molasses and run like a pig.

If you're not too far into the project, and you will have a lot of 
subqueries, switch to PostgreSQL or a database that supports them.

I don't really have an answer. I hope someone comes up with a more elegant 
solution.

Regards - Miles Thompson
At 09:34 AM 12/14/2001 -0500, Harpreet wrote:
>I dont think we can use 'IN' and 'NOT IN' in mysql. Is there an alternative
>that would work?
>
>select * from lib_asset_tbl where material_id <>'' and asset_id in (select
>asset_id from lib_copy_tbl)
>
>Help is appreciated.
>
>Regards,
>Harpreet Kaur
>Software Developer
>Crispin Corporations Inc.
>
>
>
>--
>PHP Database Mailing List (http://www.php.net/)
>To unsubscribe, e-mail: [EMAIL PROTECTED]
>For additional commands, e-mail: [EMAIL PROTECTED]
>To contact the list administrators, e-mail: [EMAIL PROTECTED]


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php