RE: [PHP-DB] Mysql completing Query fast but mysql->query() takes long time to return even single selects/updates
I see this occurring randomly on different quries with different indexes/tables. It appears like php is taking a lot longer than mysql's transmission time to cache the result be it bool TRUE/FALSE ora select of 1 - 50 records. While it does not happen all the time, since from the MySQL side I can see how long each step was and how long it took to even transmit the results to the php server. I am thinking it a small memory hole we do not normaly see for some reason but there are no sqlng parameters even that seem to be able to help in this situation. Also since this is is not really repeatable as is occurs only sometimes and not with a predictable frequency it would be very hard to do the roll back. Also since its in the mysql class of functions I cant even debug into it to try to see much of anything (since its c++ code not userland functions). David -Original Message- From: Chris [mailto:dmag...@gmail.com] Sent: Sunday, April 11, 2010 6:26 PM To: David Murphy Cc: php-db@lists.php.net Subject: Re: [PHP-DB] Mysql completing Query fast but mysql->query() takes long time to return even single selects/updates David Murphy wrote: > As you can see PHP claims it took 20 seconds for mysql->query() to return > but mysql think is took around 1.0s > > > This is from our application > I enabled profile in mysql to determine why an update took 20seconds. As > you can see MySQL reported no where near that amount of duration took > place. > Is there any way I can dig into php and determine why mysql client libs are > so slow (this is not using mysqlnd but mysql-client-libs on CentOS using > 5.3.2) Is this a one-off thing or is it happening all the time? If it's a one-off thing it could be a spurious result (maybe someone else was doing a mysqldump when your query ran, the dump blocks your query).. What sort of mysql table is it? if it's innodb you can try it in a transaction and roll it back: begin; update blah; rollback; see how long it takes. if it takes a short time in the mysql client, then try it in a php script from your other server. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Postgres - how to skip 'sequence' columns
Is it possible to skip column(s) in the target table when using
pg_copy_from? When I tried to bulk insert some data from a csv
file by way of pg_copy_from I received the error "Warning:
pg_copy_from() [function.pg-copy-from]: Copy command failed:
ERROR: permission denied for relation xyz", pretty much as I
expected since things look like:
CREATE SEQUENCE xyz;
CREATE TABLE some_table (
id int NOT NULL PRIMARY KEY DEFAULT nextval('xyz')
...
(i.e. the first column is automatically populated by the db itself).
The csv file contains only data for the second and subsequent
columns, so anyway needs to "hop right" one column.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Postgres - how to skip 'sequence' columns
Gary . wrote:
Is it possible to skip column(s) in the target table when using
pg_copy_from? When I tried to bulk insert some data from a csv
file by way of pg_copy_from I received the error "Warning:
pg_copy_from() [function.pg-copy-from]: Copy command failed:
ERROR: permission denied for relation xyz", pretty much as I
expected since things look like:
CREATE SEQUENCE xyz;
CREATE TABLE some_table (
id int NOT NULL PRIMARY KEY DEFAULT nextval('xyz')
...
(i.e. the first column is automatically populated by the db itself).
It seems not from looking at the php manual.
You could use http://www.php.net/manual/en/function.pg-put-line.php instead.
--
Postgresql & php tutorials
http://www.designmagick.com/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] mySQL date query
I am trying to write a mySQL query on my "stats" table. I am trying to determine the number of records (users) during a 7 day period ending yesterday. I always to keep it current ... Yesterday will keep changing. In other words I want to know the number of users who accessed the web site during seven full days. This is the beginning of the query. The date column is "date". SELECT count(`visits`) as users FROM `stats` WHERE `date` Thanks, Ron -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mySQL date query
what is the format of the date u are storing ? Chaitanya On Tue, Apr 13, 2010 at 11:50 AM, Ron Piggott < ron.pigg...@actsministries.org> wrote: > I am trying to write a mySQL query on my "stats" table. I am trying to > determine the number of records (users) during a 7 day period ending > yesterday. I always to keep it current ... Yesterday will keep changing. > In other words I want to know the number of users who accessed the web > site during seven full days. > > This is the beginning of the query. The date column is "date". > > SELECT count(`visits`) as users FROM `stats` WHERE `date` > > Thanks, Ron > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >
Re: [PHP-DB] mySQL date query
Ron Piggott wrote: I am trying to write a mySQL query on my "stats" table. I am trying to determine the number of records (users) during a 7 day period ending yesterday. I always to keep it current ... Yesterday will keep changing. In other words I want to know the number of users who accessed the web site during seven full days. This is the beginning of the query. The date column is "date". SELECT count(`visits`) as users FROM `stats` WHERE `date` The mysql manual is a good place to start. http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-sub -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mySQL date query
It is in a 'date' column type -MM-DD > what is the format of the date u are storing ? > > Chaitanya > > > > On Tue, Apr 13, 2010 at 11:50 AM, Ron Piggott < > ron.pigg...@actsministries.org> wrote: > >> I am trying to write a mySQL query on my "stats" table. I am trying to >> determine the number of records (users) during a 7 day period ending >> yesterday. I always to keep it current ... Yesterday will keep >> changing. >> In other words I want to know the number of users who accessed the web >> site during seven full days. >> >> This is the beginning of the query. The date column is "date". >> >> SELECT count(`visits`) as users FROM `stats` WHERE `date` >> >> Thanks, Ron >> >> >> -- >> PHP Database Mailing List (http://www.php.net/) >> To unsubscribe, visit: http://www.php.net/unsub.php >> >> > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] mySQL date query
try this
SELECT DATE_ADD('2008-01-02', INTERVAL -7 DAY);
Chaitanya
On Tue, Apr 13, 2010 at 12:01 PM, Ron Piggott <
ron.pigg...@actsministries.org> wrote:
> It is in a 'date' column type -MM-DD
>
> > what is the format of the date u are storing ?
> >
> > Chaitanya
> >
> >
> >
> > On Tue, Apr 13, 2010 at 11:50 AM, Ron Piggott <
> > ron.pigg...@actsministries.org> wrote:
> >
> >> I am trying to write a mySQL query on my "stats" table. I am trying to
> >> determine the number of records (users) during a 7 day period ending
> >> yesterday. I always to keep it current ... Yesterday will keep
> >> changing.
> >> In other words I want to know the number of users who accessed the web
> >> site during seven full days.
> >>
> >> This is the beginning of the query. The date column is "date".
> >>
> >> SELECT count(`visits`) as users FROM `stats` WHERE `date`
> >>
> >> Thanks, Ron
> >>
> >>
> >> --
> >> PHP Database Mailing List (http://www.php.net/)
> >> To unsubscribe, visit: http://www.php.net/unsub.php
> >>
> >>
> >
>
>
>
Re: [PHP-DB] Postgres - how to skip 'sequence' columns
On 4/13/10, Chris wrote: > Gary . wrote: >> Is it possible to skip column(s) in the target table when using >> pg_copy_from? ... > It seems not from looking at the php manual. > > You could use http://www.php.net/manual/en/function.pg-put-line.php instead. Yeah. I was hoping to use pg_copy_from to avoid looping over the array in php, but I can do that. Thanks. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
