from:"Fan, Wellington"

RE: Implement a logging table; avoiding conflicting inserts

2007-09-11 Thread Fan, Wellington

   Given: MySQL 4.0.12, I need to implement a pageview log with a 
  resolution of 1 day.
..
 
 Would the REPLACE method work? 
 
 David


Hmmm...as I read the docs, the LOCK IN SHARED MODE seemed to be the
real key to this.


I created a test script and ran:

$ ab -n100 -c100 localhost/hits.php

Where hits.php looks like:

?php
/**
 * Import db connection parameters
 */

require $_SERVER['DOCUMENT_ROOT'] . '/generic/app_global.inc.php';

$err = null;
($date = $_GET['date']) or ($date = date('Y-m-d'));
($url  = $_GET['url'])  or ($url  = $_SERVER['HTTP_REFERER']);

/**
 * For testing, get a random date and URL
 */
$dates = array(
  '2007-09-11',
  '2007-09-12',
  '2007-09-13',
  );
$urls = array(
  'URL A',
  'URL B',
  'URL C',
  );

shuffle($dates);
shuffle($urls);

$date = pos($dates);
$url  = pos($urls);

/**
 * Connect
 */
$dblink =
mysql_connect($page_options['host_main'],$page_options['host_main_user']
,$page_options['host_main_pass']);
mysql_select_db('articles',$dblink);


/**
 * BEGIN TRANSACTION
 */
$rs = mysql_query('START TRANSACTION',$dblink);
$debug = 'Begin Transaction said:'.mysql_error($dblink).'';
error_log($debug.\n, 3, '/tmp/errors.log');

// see:
http://dev.mysql.com/doc/refman/4.1/en/innodb-next-key-locking.html
$sql = SELECT views FROM pageviews WHERE
date='.mysql_escape_string($date).' AND
url='.mysql_escape_string($url).' LOCK IN SHARE MODE;

/**
 * If NO records are returned, we need to INSERT with our first pageview
 */
$rs = mysql_query($sql,$dblink);
if( mysql_num_rows($rs) == 0 ) {
  $sql = INSERT INTO pageviews SET views=1,
date='.mysql_escape_string($date).',
url='.mysql_escape_string($url).';
} else {
  $sql = REPLACE INTO pageviews SET
views=.(intval(mysql_result($rs,0,'views'))+1).,
date='.mysql_escape_string($date).',
url='.mysql_escape_string($url).';
  //$sql = UPDATE pageviews SET views=views+1 WHERE
date='.mysql_escape_string($date).' AND
url='.mysql_escape_string($url).';
}
echo $sql;
$rs = mysql_query($sql,$dblink);

/**
 * Barely error-checking...
 */
if ( mysql_affected_rows($dblink) != 1 ) {
  $err = mysql_error($dblink);
  error_log ($err.\n, 3, '/tmp/errors.log');
}
mysql_query('COMMIT',$dblink);

?


As you can see, I tried *both* the REPLACE INTO and UPDATE queries
and received *very* strange results. I sum(views) and get roughly 115
views!! I expected 100 or less, but maybe I do NOT understand 'ab'.

So, I added this:
error_log('foo'.\n, 3, '/tmp/errors.log');
exit;

At the top of my script, and ran:
$ ab -n100 -c100 localhost/hits.php

Again, expecting 100 'foo's -- I get roughly 160!

What the hell? I guess I really *don't* understand ab...

Thoughts?

--
Wellington

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

Implement a logging table; avoiding conflicting inserts

2007-09-10 Thread Fan, Wellington

Hello Listies,

Given: MySQL 4.0.12, I need to implement a pageview log with a
resolution of 1 day.

I propose this table:

CREATE TABLE `pageviews` (
  `id` int(11) NOT NULL auto_increment,
  `date` date NOT NULL default '-00-00',
  `url` char(120) NOT NULL default '',
  `views` mediumint(9) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `date` (`date`,`url`),
  KEY `url` (`url`)
) TYPE=InnoDB;


So that an update will look like:
  UPDATE pageviews SET views=views+1 WHERE date='DATE' AND
url='ARTIST'

Of course I need to INSERT the record if one does not match my WHERE.
This would be easy if I had 4.1 -- INSERT ... ON DUPLICATE KEY UPDATE,
I think -- but I do not. 

So, how should I write my queries so that when a new day dawns, I don't
have 2 connections racing to INSERT?

I suspect I could do something like this (in PHP, line numbers added:

01  $link = connect2Db();
02  $sql = SELECT * FROM pageviews WHERE date='DATE' AND
url='ARTIST' LOCK IN SHARE MODE;
03  if ( mysql_num_rows(mysql_query($sql,$link)) ) {
04  // UPDATE
05  } else {
06  // INSERT
07  }


Also, should I explicitly mysql_query('COMMIT',$link) on line 8?

Thanks all!

--
wellington

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

RE: make mysqldump to sort columns alphabetically

2006-09-08 Thread Fan, Wellington

Nikita,

Try somehting like this:

mysql -hHOST -uUSER -pPASS -s -e'show tables' DATABASE | \
tail +1 | \
while read TABLE
do
echo == $TABLE ==
mysql -s -pwmihp -edescribe $TABLE articles | sort
Done

I used the tail +1 to trim out the header row -- tho I think there is
an option to make output less verbose.


 

 -Original Message-
 From: Nikita Tovstoles [mailto:[EMAIL PROTECTED] 
 Sent: Friday, September 08, 2006 2:00 PM
 To: mysql@lists.mysql.com
 Subject: Re: make mysqldump to sort columns alphabetically
 
 Thanks, Douglas!
 
 That seems OK, but I'd prefer to avoid altering the schemas 
 in any way. 
 In particular altering order of constraints seems 
 error-prone, given that one is essentially re-defining these, 
 not simply rearranging the order. Am I asking for impossible? ;-
 
 -nikita
 
 Douglas Sims wrote:
  One way you could solve this is to conform the column 
 orders between 
  the two versions.
 
  For example, if one table, t6, has columns id, name, and 
 address and 
  the same table in the second database is id, address, name, 
 you could 
  just ALTER the second database t6 table to be id, name, address:
 
  mysql describe t6;
  +-+-+--+-+-+---+
  | Field   | Type| Null | Key | Default | Extra |
  +-+-+--+-+-+---+
  | id  | int(9)  | NO   | PRI | 0   |   |
  | address | varchar(32) | YES  | | |   |
  | name| varchar(32) | YES  | MUL | |   |
  +-+-+--+-+-+---+
  3 rows in set (0.00 sec)
 
  mysql alter table t6 change column address address 
 varchar(32) after
  name;
  Query OK, 0 rows affected (0.02 sec)
  Records: 0  Duplicates: 0  Warnings: 0
 
  mysql describe t6;
  +-+-+--+-+-+---+
  | Field   | Type| Null | Key | Default | Extra |
  +-+-+--+-+-+---+
  | id  | int(9)  | NO   | PRI | 0   |   |
  | name| varchar(32) | YES  | MUL | |   |
  | address | varchar(32) | YES  | | |   |
  +-+-+--+-+-+---+
  3 rows in set (0.00 sec)
 
  Here is the mysql documentation on ALTER TABLE: 
  http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
 
 
 
  Douglas Sims
  [EMAIL PROTECTED]
 
 
 
  On Sep 8, 2006, at 12:27 PM, Nikita Tovstoles wrote:
 
  Hi,
 
  I'm diffing two versions of a schema produced using mysqldump and 
  would like to know if there's a way to make mysqldump sort entries 
  inside CREATE statements (say alphabetically or in some other way)?
  Currently some of the column declarations are juxtaposed 
 between the 
  versions and thus produce false diffs.
 
  Mysql 5.0, InnoDB
 
  thanks a lot
  -nikita
 
 
  --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]
 
 
 
 --
 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: #1191 - Can't find FULLTEXT index matching the column list

2006-05-11 Thread Fan, Wellington

Hello all,

I just inherited an application that has 2 tables under consideration, events 
and attribute_master. They are linked on (events.eventID = 
attribute_master.id AND attribute_master.tableis = 'events'). In other words, 
attribute_master.id is kinda like a foreign key to events.eventID, but only 
where attribute_master.tableis = 'events'.

I have ommited some columns from 'events' that I am fairly certain are NOT 
germane to my question:

CREATE TABLE attribute_master (
  tableis varchar(128) NOT NULL default '',
  id int(12) NOT NULL default '0',
  attributeID int(8) NOT NULL default '0',
  PRIMARY KEY  (tableis,id,attributeID)
) TYPE=MyISAM;

CREATE TABLE events (
  eventID mediumint(9) NOT NULL auto_increment,
  eventReview text NOT NULL,
  status tinyint(1) NOT NULL default '0',
  modlast int(8) NOT NULL default '0',
  PRIMARY KEY  (eventID)
) TYPE=MyISAM


 Now, an intersting query:
SELECT
events.eventID AS id,
attribute_master.attributeID AS attrib_id
FROM
events,
attribute_master
WHERE
events.status='8' AND
events.eventReview!='' AND
events.modlast  1146470445 AND
events.eventID = attribute_master.id AND
attribute_master.tableis = 'events'
GROUP BY
attribute_master.id

So here's my question(s): Given this query, how can on GROUP BY operate on a 
column that doesn't appear in the SELECT list? Secondly, I thought that every 
column listed in the SELECT clause would have to be in an aggregate function or 
mentioned in the GROUP BY...how am I wrong?

--
Wellington

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

RE: #1191 - Can't find FULLTEXT index matching the column list

2006-05-11 Thread Fan, Wellington

Damn, I really didn't mean to use that subject line; Sorry all!

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

GROUP BY question

2006-05-11 Thread Fan, Wellington

Sorry about the re-post, all!

Hello all,

I just inherited an application that has 2 tables under consideration, events 
and attribute_master. They are linked on (events.eventID = 
attribute_master.id AND attribute_master.tableis = 'events'). In other words, 
attribute_master.id is kinda like a foreign key to events.eventID, but only 
where attribute_master.tableis = 'events'.

I have ommited some columns from 'events' that I am fairly certain are NOT 
germane to my question:

CREATE TABLE attribute_master (
  tableis varchar(128) NOT NULL default '',
  id int(12) NOT NULL default '0',
  attributeID int(8) NOT NULL default '0',
  PRIMARY KEY  (tableis,id,attributeID)
) TYPE=MyISAM;

CREATE TABLE events (
  eventID mediumint(9) NOT NULL auto_increment,
  eventReview text NOT NULL,
  status tinyint(1) NOT NULL default '0',
  modlast int(8) NOT NULL default '0',
  PRIMARY KEY  (eventID)
) TYPE=MyISAM


 Now, an intersting query:
SELECT
events.eventID AS id,
attribute_master.attributeID AS attrib_id
FROM
events,
attribute_master
WHERE
events.status='8' AND
events.eventReview!='' AND
events.modlast  1146470445 AND
events.eventID = attribute_master.id AND
attribute_master.tableis = 'events'
GROUP BY
attribute_master.id

So here's my question(s): Given this query, how can on GROUP BY operate on a 
column that doesn't appear in the SELECT list? Secondly, I thought that every 
column listed in the SELECT clause would have to be in an aggregate function or 
mentioned in the GROUP BY...how am I wrong?

--
Wellington

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

GROUP BY column when column is NOT in SELECT list?

2006-05-11 Thread Fan, Wellington

Hello all,

I have inherited this query:

SELECT
events.eventID AS id,
attribute_master.attributeID AS attrib_id
FROM
events,
attribute_master
WHERE
events.status='8' AND
events.eventReview!='' AND
events.modlast  1146470445 AND
events.eventID = attribute_master.id AND
attribute_master.tableis = 'events'
GROUP BY
attribute_master.id


I thought that every column listed in the SELECT clause would have to be in an 
aggregate function or mentioned in the GROUP BY...how am I wrong?

--
Wellington

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

Table design; 2-column index

2006-04-24 Thread Fan, Wellington

Hello List,

If I have a table:

CREATE TABLE t (
  id int(11) NOT NULL auto_increment,
  fk1 mediumint(9) NOT NULL default '0',
  fk2 smallint(6) NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY idxfk1 (fk1,fk2),
  UNIQUE KEY idxfk2 (fk2,fk1)
) TYPE=MyISAM;

I will about half the time have a query WHERE fk1 IN () and about the 
other half the time have WHERE fk2 IN ()

Does it make sense to define the UNIQUE KEYS the way I have?

Thanks!

--
Wellington

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

RE: Table design; 2-column index

2006-04-24 Thread Fan, Wellington

Hey Dan,

Thanks; I was really trying to ask about the potential performance gain, 
however. I don't care so much about the UNIQUEness, but the INDEXness.

See, I am wondering if I create an 2-column index wiht fk1 as the first 
component, will that index help me if I am refering fk2 in my query?



 -Original Message-
 From: Dan Nelson [mailto:[EMAIL PROTECTED]
 Sent: Monday, April 24, 2006 1:40 PM
 To: Fan, Wellington
 Cc: mysql@lists.mysql.com
 Subject: Re: Table design; 2-column index
 
 
 In the last episode (Apr 24), Fan, Wellington said:
  If I have a table:
  
  CREATE TABLE t (
id int(11) NOT NULL auto_increment,
fk1 mediumint(9) NOT NULL default '0',
fk2 smallint(6) NOT NULL default '0',
PRIMARY KEY  (id),
UNIQUE KEY idxfk1 (fk1,fk2),
UNIQUE KEY idxfk2 (fk2,fk1)
  ) TYPE=MyISAM;
  
  I will about half the time have a query WHERE fk1 IN () and
  about the other half the time have WHERE fk2 IN ()
  
  Does it make sense to define the UNIQUE KEYS the way I have?
 
 You only need one unique index to enforce uniqueness, so you 
 can safely
 convert your idxfk2 to a single-column regular index and save a little
 bit of space.  
 
 ALTER TABLE t drop key idxfk2, add key idxfk2 (fk2);
 
 -- 
   Dan Nelson
   [EMAIL PROTECTED]
 

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

RE: Remove all non-alpha characters?

2005-11-16 Thread Fan, Wellington

Gleb,

Thanks; using REPLACE(), as I understand it, would require me to list ALL
non-alpha characters, and assuming just ASCII characters, approx (127 - 52)
nested calls to REPLACE()...


select
  REPLACE(

   ...REPLACE(
REPLACE(
  REPLACE(
text,'~',''),
  '!',''),
'@','')
 ...)



 -Original Message-
 From: Gleb Paharenko [mailto:[EMAIL PROTECTED]
...
 You should think about using REPLACE function. See:
   http://dev.mysql.com/doc/refman/5.0/en/string-functions.html



 Fan, Wellington wrote:
  Hello List-people ,
  
  I am looking to remove all non-alphanumeric characters from 
 a column:

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

Remove all non-alpha characters?

2005-11-15 Thread Fan, Wellington

Hello List-people ,

I am looking to remove all non-alphanumeric characters from a column:


UPDATE
mytable
SET
mycolumn = REMOVE_NON_ALPHAS(mycolumn)
WHERE
mycolumn REGEXP '[^[:alpha:]]'



where REMOVE_NON_ALPHAS() is a fictional function.

How can I achieve this result? MySQL 3.23, BTW.

--
Wellington

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

Optimization; index on combined PK

2005-04-15 Thread Fan, Wellington

Hello all,

I have a link table that relates 'Profiles' with 'Categories':

CREATE TABLE lnk_profile_category (
  profile_fk int(11) NOT NULL default '0',
  category_fk int(11) NOT NULL default '0',
  PRIMARY KEY  (category_fk,profile_fk)
) TYPE=MyISAM;

If I expect that my most frequent queries are going to be those that select
profiles in a given category -- i.e.: (select * from profile
Yadda-Yadda-JOIN where category.label = 'widgets' ) -- is it better to
define my PK as:

1. PRIMARY KEY  (category_fk,profile_fk)
 --Rather than--
2. PRIMARY KEY  (profile_fk,category_fk)

??

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

Selecting a 'Constant' result set?

2005-03-11 Thread Fan, Wellington

Hello MySQL'ers,

Is there a way to return a simple table of one-column, with *fixed* output?
For example, I would like to return a one-column, 3-record table:

-
| value |
-
|  'a'  |
-
|  'b'  |
-
|  'c'  |
-


Where I have specified 'a','b' and 'c' somewhere. Some *imaginary* SQL like:
SELECT ['a','b','c'] as value

It seems simple, no?


--
Wellington




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

Perfomance: UNION vs. # of SELECT

2004-10-27 Thread Fan, Wellington

Hello Listpeople,

I need to output headline and link information from an 'articles' table.
Each article belongs to one and only one 'section'. I will probably need a
different number of articles from each section. I'm wondering about
performance, in very general terms, of a few different strategies outlined
below.


I imagined using a single SELECT like:
1.) SELECT * FROM articles WHERE section IN ('music','art','books'...) LIMIT
??
but couldn't figure out how to get 4 records from 'music' and 6 records from
'art'.


I imagined using 6 different queries, then patching the results together in
my application:
2.)
  a.) SELECT * FROM articles WHERE section='music' LIMIT 4
  b.) SELECT * FROM articles WHERE section='art' LIMIT 6
etc.
but thought this might be a lot of queries to throw at the database for each
page request.


Then I thought that I could send in one big old query using a UNION:
3.)
  SELECT * FROM articles WHERE section='music' LIMIT 4
  UNION
  SELECT * FROM articles WHERE section='art' LIMIT 6
  UNION
  SELECT * FROM articles WHERE section='books' LIMIT 6
  etc.

Could anyone comment on these 3 strategies and any 'gotchas', limitations,
other considerations that I haven't, uhh, considered?

--
Wellington





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

RE: Group By Question

2004-10-13 Thread Fan, Wellington

Perfect! Thank you.

 -Original Message-
 From: Harald Fuchs [mailto:[EMAIL PROTECTED]
 Subject: Re: Group By Question

 SELECT category_fk,
sum(case status when 1 then 1 else 0 end) AS 'status=1',
sum(case status when 2 then 1 else 0 end) AS 'status=2',
sum(case status when 3 then 1 else 0 end) AS 'status=3'
 FROM myTable
 GROUP BY category_fk;
 


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

Group By Question

2004-10-12 Thread Fan, Wellington

Hello Listfolk,

I have a table with a 'category_fk' column and a 'status' column. 'Status'
has but a tiny handful of  known values, kinda like an enum.

I'd like to form a query that would give me results like:

category_fk  |  status=1 | status=2 | status=3 |

  toys   | 23|45|0 |

  games  | 12|0 |0 |

  books  | 5 |1 |3 |


Where the non-fk columns represent the counts of records with that
category_fk with a certain 'status'

I've got something close:

SELECT
category_fk,
count(*) as n,
status
FROM
myTable
GROUP BY
category_fk,
status

But this gives me a record for each category_fk/status.


TIA!

--
WF


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

RE: Implement a logging table; avoiding conflicting inserts

Implement a logging table; avoiding conflicting inserts

RE: make mysqldump to sort columns alphabetically

RE: #1191 - Can't find FULLTEXT index matching the column list

RE: #1191 - Can't find FULLTEXT index matching the column list

GROUP BY question

GROUP BY column when column is NOT in SELECT list?

Table design; 2-column index

RE: Table design; 2-column index

RE: Remove all non-alpha characters?

Remove all non-alpha characters?

Optimization; index on combined PK

Selecting a 'Constant' result set?

Perfomance: UNION vs. # of SELECT

RE: Group By Question

Group By Question

16 matches

Site Navigation

Mail list logo

Footer information