473,695 Members | 2,626 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem with executing MySQL queries through PHP.

Daz
Hi everyone.

I was faced with the choice of whether my problem is indeed a PHP
problem or a MySQL. I have decided it's a PHP problem as I don't
experience the same problem when I execute the same query at the CLI.

I am having trouble executing a large query through my PHP script. It
takes about 7-11 seconds on
average to execute, whereas the same query only takes 0.01 seconds to
execute through the CLI.

I thought that it could have been something to do with the table
collation, and I have changed that to alsorts of different things,
still to no avail. For some reason the problem appeared to have occured
from out of the blue. It all worked fine, and then all of a sudden
started going slow. The rest of the queries executed on the database
are very fast, it only seems to be when I use the tables that I have
recently created for my project that they run slow.

I am joining three tables together:

=============== =============== =============== ===============

CREATE TABLE IF NOT EXISTS `cms_users` (
`user_id` mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(60) default NULL,
`username` varchar(50) NOT NULL default '',
`user_email` varchar(255) NOT NULL default '',
`femail` varchar(255) default NULL,
`user_website` varchar(255) default NULL,
`user_avatar` varchar(255) NOT NULL default '',
`user_regdate` varchar(20) NOT NULL default '',
`user_icq` varchar(15) default NULL,
`user_occ` varchar(100) default NULL,
`user_from` varchar(100) default NULL,
`user_interests ` varchar(150) default NULL,
`user_sig` varchar(255) default NULL,
`user_viewemail ` tinyint(4) NOT NULL default '0',
`user_aim` varchar(35) default NULL,
`user_yim` varchar(40) default NULL,
`user_msnm` varchar(40) default NULL,
`user_password` varchar(40) NOT NULL default '',
`storynum` tinyint(4) NOT NULL default '10',
`umode` varchar(10) default NULL,
`uorder` tinyint(4) NOT NULL default '0',
`thold` tinyint(4) NOT NULL default '0',
`noscore` tinyint(4) NOT NULL default '0',
`bio` tinytext,
`ublockon` tinyint(4) NOT NULL default '0',
`ublock` tinytext,
`theme` varchar(255) NOT NULL default '',
`commentmax` int(11) NOT NULL default '4096',
`counter` int(11) NOT NULL default '0',
`newsletter` tinyint(4) NOT NULL default '0',
`user_posts` int(11) NOT NULL default '0',
`user_attachsig ` tinyint(4) NOT NULL default '1',
`user_rank` int(11) NOT NULL default '0',
`user_level` tinyint(4) NOT NULL default '1',
`user_active` tinyint(4) default '1',
`user_session_t ime` int(11) NOT NULL default '0',
`user_lastvisit ` int(11) NOT NULL default '0',
`user_timezone` varchar(6) NOT NULL default '0',
`user_dst` smallint(6) NOT NULL default '0',
`user_style` tinyint(4) default NULL,
`user_lang` varchar(255) NOT NULL default 'english',
`user_dateforma t` varchar(14) NOT NULL default 'D M d, Y g:i a',
`user_new_privm sg` smallint(5) unsigned NOT NULL default '0',
`user_unread_pr ivmsg` smallint(5) unsigned NOT NULL default '0',
`user_last_priv msg` int(11) NOT NULL default '0',
`user_emailtime ` int(11) default NULL,
`user_allowhtml ` tinyint(4) default '1',
`user_allowbbco de` tinyint(4) default '1',
`user_allowsmil e` tinyint(4) default '1',
`user_allowavat ar` tinyint(4) NOT NULL default '1',
`user_allow_pm` tinyint(4) NOT NULL default '1',
`user_allow_vie wonline` tinyint(4) NOT NULL default '1',
`user_notify` tinyint(4) NOT NULL default '0',
`user_notify_pm ` tinyint(4) NOT NULL default '0',
`user_popup_pm` tinyint(4) NOT NULL default '0',
`user_avatar_ty pe` tinyint(4) NOT NULL default '3',
`user_sig_bbcod e_uid` varchar(10) default NULL,
`user_actkey` varchar(32) default NULL,
`user_newpasswd ` varchar(32) default NULL,
`user_group_cp` int(11) NOT NULL default '2',
`user_group_lis t_cp` varchar(100) NOT NULL default '2',
`user_active_cp ` tinyint(4) NOT NULL default '1',
`susdel_reason` text,
PRIMARY KEY (`user_id`),
KEY `uname` (`username`),
KEY `user_session_t ime` (`user_session_ time`)
) ENGINE=MyISAM DEFAULT charset=utf8 AUTO_INCREMENT= 308 ;

DROP TABLE IF EXISTS `pp_books`;
CREATE TABLE IF NOT EXISTS `pp_books` (
`book_id` smallint(4) unsigned NOT NULL auto_increment,
`book_name` varchar(50) NOT NULL default '',
`is_retired` tinyint(1) unsigned default NULL,
PRIMARY KEY (`book_id`),
UNIQUE KEY `book_name` (`book_name`)
) ENGINE=MyISAM AUTO_INCREMENT= 3670 ;

DROP TABLE IF EXISTS `pp_ubooks`;
CREATE TABLE IF NOT EXISTS `pp_ubooks` (
`uid` int(10) unsigned NOT NULL default '0',
`book_id` int(10) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT charset=utf8;
=============== =============== =============== ===============

(The last table has a unique index which spans across both columns).

This is the query I am executing:

SELECT
t1.letter AS letter,
COUNT(*) AS total_books_in_ section,
SUM(IF(t1.retir ed='1',1,0 )) AS total_retired,
SUM(IF(t1.retir ed!='1',1,0 )) AS owned_regular,
SUM(IF(t1.retir ed='1' AND t2.bid IS NOT NULL,1,0 )) AS
owned_retired_b ooks,
SUM(IF(t1.retir ed='1' AND t2.bid IS NULL,1,0)) AS
unowned_retired _books,
SUM(IF(t1.retir ed!='1' AND t2.bid IS NOT NULL,1,0)) AS
owned_regular_b ooks,
SUM(IF(t1.retir ed!='1' AND t2.bid IS NULL,1,0)) AS
unowned_regular _books
FROM (
SELECT
book_id AS bid,
LEFT(book_name, 1) AS letter,
IF(is_retired=' 1',1,0) AS retired
FROM
pp_books
) AS t1
LEFT JOIN (
SELECT
book_id AS bid,
cms_users.usern ame AS username,
IF(book_id IS NULL,0,1) AS is_owned
FROM
pp_ubooks
RIGHT JOIN
cms_users
ON
cms_users.user_ id=pp_ubooks.ui d
WHERE
cms_users.usern ame='$username'
OR
cms_users.usern ame IS NULL
) AS t2
ON
t1.bid=t2.bid
GROUP BY
letter;

I am trying to comprehend what might have gone wrong to be causing this
problem. As I mentioned before, it takes only 0.01 seconds to execute
in both phpmyadmin and at the CLI. All other queries on my website
execute super-fast, just not any that use pp_ubooks or pp_books. Even
this query takes more than 5 seconds to execute on the website, but
only 0.00 seconds using the CLI.

Just for the record, my website and the CLI are using the same
database. I think it might have something to do with my table
collations, but I can't be sure if this is the case, and if it is, why
it has such a massive impact. I have tried setting my book tables to
latin-swedish-ci (which is the default), utf8-unicode-ci, and
utf8-general-ci (which all of my other tables are set to), and nothing
seems to work any better.

I have been utterly baffled by this mystery for days now, and if
haven't got any hair left on my head to pull out anymore. If anyone
could suggest anything they believe to be helpful I would really
appreciate it! I have even gone as far as to completely scrap apache2,
mysql-server-5.0 and php4 and to start the installation all over again.
This is what leads me to beleive it could be something to do with my
tables rather than the configuration of anything in particular, but I
am just guessing.

Best wishes.

Daz.

Oct 28 '06 #1
8 2007
Daz

Daz wrote:
Hi everyone.

I was faced with the choice of whether my problem is indeed a PHP
problem or a MySQL. I have decided it's a PHP problem as I don't
experience the same problem when I execute the same query at the CLI.

I am having trouble executing a large query through my PHP script. It
takes about 7-11 seconds on
average to execute, whereas the same query only takes 0.01 seconds to
execute through the CLI.

I thought that it could have been something to do with the table
collation, and I have changed that to alsorts of different things,
still to no avail. For some reason the problem appeared to have occured
from out of the blue. It all worked fine, and then all of a sudden
started going slow. The rest of the queries executed on the database
are very fast, it only seems to be when I use the tables that I have
recently created for my project that they run slow.

I am joining three tables together:

=============== =============== =============== ===============

CREATE TABLE IF NOT EXISTS `cms_users` (
`user_id` mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(60) default NULL,
`username` varchar(50) NOT NULL default '',
`user_email` varchar(255) NOT NULL default '',
`femail` varchar(255) default NULL,
`user_website` varchar(255) default NULL,
`user_avatar` varchar(255) NOT NULL default '',
`user_regdate` varchar(20) NOT NULL default '',
`user_icq` varchar(15) default NULL,
`user_occ` varchar(100) default NULL,
`user_from` varchar(100) default NULL,
`user_interests ` varchar(150) default NULL,
`user_sig` varchar(255) default NULL,
`user_viewemail ` tinyint(4) NOT NULL default '0',
`user_aim` varchar(35) default NULL,
`user_yim` varchar(40) default NULL,
`user_msnm` varchar(40) default NULL,
`user_password` varchar(40) NOT NULL default '',
`storynum` tinyint(4) NOT NULL default '10',
`umode` varchar(10) default NULL,
`uorder` tinyint(4) NOT NULL default '0',
`thold` tinyint(4) NOT NULL default '0',
`noscore` tinyint(4) NOT NULL default '0',
`bio` tinytext,
`ublockon` tinyint(4) NOT NULL default '0',
`ublock` tinytext,
`theme` varchar(255) NOT NULL default '',
`commentmax` int(11) NOT NULL default '4096',
`counter` int(11) NOT NULL default '0',
`newsletter` tinyint(4) NOT NULL default '0',
`user_posts` int(11) NOT NULL default '0',
`user_attachsig ` tinyint(4) NOT NULL default '1',
`user_rank` int(11) NOT NULL default '0',
`user_level` tinyint(4) NOT NULL default '1',
`user_active` tinyint(4) default '1',
`user_session_t ime` int(11) NOT NULL default '0',
`user_lastvisit ` int(11) NOT NULL default '0',
`user_timezone` varchar(6) NOT NULL default '0',
`user_dst` smallint(6) NOT NULL default '0',
`user_style` tinyint(4) default NULL,
`user_lang` varchar(255) NOT NULL default 'english',
`user_dateforma t` varchar(14) NOT NULL default 'D M d, Y g:i a',
`user_new_privm sg` smallint(5) unsigned NOT NULL default '0',
`user_unread_pr ivmsg` smallint(5) unsigned NOT NULL default '0',
`user_last_priv msg` int(11) NOT NULL default '0',
`user_emailtime ` int(11) default NULL,
`user_allowhtml ` tinyint(4) default '1',
`user_allowbbco de` tinyint(4) default '1',
`user_allowsmil e` tinyint(4) default '1',
`user_allowavat ar` tinyint(4) NOT NULL default '1',
`user_allow_pm` tinyint(4) NOT NULL default '1',
`user_allow_vie wonline` tinyint(4) NOT NULL default '1',
`user_notify` tinyint(4) NOT NULL default '0',
`user_notify_pm ` tinyint(4) NOT NULL default '0',
`user_popup_pm` tinyint(4) NOT NULL default '0',
`user_avatar_ty pe` tinyint(4) NOT NULL default '3',
`user_sig_bbcod e_uid` varchar(10) default NULL,
`user_actkey` varchar(32) default NULL,
`user_newpasswd ` varchar(32) default NULL,
`user_group_cp` int(11) NOT NULL default '2',
`user_group_lis t_cp` varchar(100) NOT NULL default '2',
`user_active_cp ` tinyint(4) NOT NULL default '1',
`susdel_reason` text,
PRIMARY KEY (`user_id`),
KEY `uname` (`username`),
KEY `user_session_t ime` (`user_session_ time`)
) ENGINE=MyISAM DEFAULT charset=utf8 AUTO_INCREMENT= 308 ;

DROP TABLE IF EXISTS `pp_books`;
CREATE TABLE IF NOT EXISTS `pp_books` (
`book_id` smallint(4) unsigned NOT NULL auto_increment,
`book_name` varchar(50) NOT NULL default '',
`is_retired` tinyint(1) unsigned default NULL,
PRIMARY KEY (`book_id`),
UNIQUE KEY `book_name` (`book_name`)
) ENGINE=MyISAM AUTO_INCREMENT= 3670 ;

DROP TABLE IF EXISTS `pp_ubooks`;
CREATE TABLE IF NOT EXISTS `pp_ubooks` (
`uid` int(10) unsigned NOT NULL default '0',
`book_id` int(10) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT charset=utf8;
=============== =============== =============== ===============

(The last table has a unique index which spans across both columns).

This is the query I am executing:

SELECT
t1.letter AS letter,
COUNT(*) AS total_books_in_ section,
SUM(IF(t1.retir ed='1',1,0 )) AS total_retired,
SUM(IF(t1.retir ed!='1',1,0 )) AS owned_regular,
SUM(IF(t1.retir ed='1' AND t2.bid IS NOT NULL,1,0 )) AS
owned_retired_b ooks,
SUM(IF(t1.retir ed='1' AND t2.bid IS NULL,1,0)) AS
unowned_retired _books,
SUM(IF(t1.retir ed!='1' AND t2.bid IS NOT NULL,1,0)) AS
owned_regular_b ooks,
SUM(IF(t1.retir ed!='1' AND t2.bid IS NULL,1,0)) AS
unowned_regular _books
FROM (
SELECT
book_id AS bid,
LEFT(book_name, 1) AS letter,
IF(is_retired=' 1',1,0) AS retired
FROM
pp_books
) AS t1
LEFT JOIN (
SELECT
book_id AS bid,
cms_users.usern ame AS username,
IF(book_id IS NULL,0,1) AS is_owned
FROM
pp_ubooks
RIGHT JOIN
cms_users
ON
cms_users.user_ id=pp_ubooks.ui d
WHERE
cms_users.usern ame='$username'
OR
cms_users.usern ame IS NULL
) AS t2
ON
t1.bid=t2.bid
GROUP BY
letter;

I am trying to comprehend what might have gone wrong to be causing this
problem. As I mentioned before, it takes only 0.01 seconds to execute
in both phpmyadmin and at the CLI. All other queries on my website
execute super-fast, just not any that use pp_ubooks or pp_books. Even
this query takes more than 5 seconds to execute on the website, but
only 0.00 seconds using the CLI.

Just for the record, my website and the CLI are using the same
database. I think it might have something to do with my table
collations, but I can't be sure if this is the case, and if it is, why
it has such a massive impact. I have tried setting my book tables to
latin-swedish-ci (which is the default), utf8-unicode-ci, and
utf8-general-ci (which all of my other tables are set to), and nothing
seems to work any better.

I have been utterly baffled by this mystery for days now, and if
haven't got any hair left on my head to pull out anymore. If anyone
could suggest anything they believe to be helpful I would really
appreciate it! I have even gone as far as to completely scrap apache2,
mysql-server-5.0 and php4 and to start the installation all over again.
This is what leads me to beleive it could be something to do with my
tables rather than the configuration of anything in particular, but I
am just guessing.

Best wishes.

Daz.
Oh, and this is th query that takes more than 5 seconds when executed
through PHP:
SELECT book_id AS bid, LEFT(book_name, 1) AS letter,
IF(is_retired=' 1',1,0) AS retired FROM pp_books;

Oct 28 '06 #2
How many results do you get back? If your recordset is huge, the render
time in your browser could be the problem rather than just the query.

What does php do to the records as it's outputting them? Any sort of
calculations?
Daz wrote:
Daz wrote:
Hi everyone.

I was faced with the choice of whether my problem is indeed a PHP
problem or a MySQL. I have decided it's a PHP problem as I don't
experience the same problem when I execute the same query at the CLI.

I am having trouble executing a large query through my PHP script. It
takes about 7-11 seconds on
average to execute, whereas the same query only takes 0.01 seconds to
execute through the CLI.

I thought that it could have been something to do with the table
collation, and I have changed that to alsorts of different things,
still to no avail. For some reason the problem appeared to have occured
from out of the blue. It all worked fine, and then all of a sudden
started going slow. The rest of the queries executed on the database
are very fast, it only seems to be when I use the tables that I have
recently created for my project that they run slow.

I am joining three tables together:

=============== =============== =============== ===============

CREATE TABLE IF NOT EXISTS `cms_users` (
`user_id` mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(60) default NULL,
`username` varchar(50) NOT NULL default '',
`user_email` varchar(255) NOT NULL default '',
`femail` varchar(255) default NULL,
`user_website` varchar(255) default NULL,
`user_avatar` varchar(255) NOT NULL default '',
`user_regdate` varchar(20) NOT NULL default '',
`user_icq` varchar(15) default NULL,
`user_occ` varchar(100) default NULL,
`user_from` varchar(100) default NULL,
`user_interests ` varchar(150) default NULL,
`user_sig` varchar(255) default NULL,
`user_viewemail ` tinyint(4) NOT NULL default '0',
`user_aim` varchar(35) default NULL,
`user_yim` varchar(40) default NULL,
`user_msnm` varchar(40) default NULL,
`user_password` varchar(40) NOT NULL default '',
`storynum` tinyint(4) NOT NULL default '10',
`umode` varchar(10) default NULL,
`uorder` tinyint(4) NOT NULL default '0',
`thold` tinyint(4) NOT NULL default '0',
`noscore` tinyint(4) NOT NULL default '0',
`bio` tinytext,
`ublockon` tinyint(4) NOT NULL default '0',
`ublock` tinytext,
`theme` varchar(255) NOT NULL default '',
`commentmax` int(11) NOT NULL default '4096',
`counter` int(11) NOT NULL default '0',
`newsletter` tinyint(4) NOT NULL default '0',
`user_posts` int(11) NOT NULL default '0',
`user_attachsig ` tinyint(4) NOT NULL default '1',
`user_rank` int(11) NOT NULL default '0',
`user_level` tinyint(4) NOT NULL default '1',
`user_active` tinyint(4) default '1',
`user_session_t ime` int(11) NOT NULL default '0',
`user_lastvisit ` int(11) NOT NULL default '0',
`user_timezone` varchar(6) NOT NULL default '0',
`user_dst` smallint(6) NOT NULL default '0',
`user_style` tinyint(4) default NULL,
`user_lang` varchar(255) NOT NULL default 'english',
`user_dateforma t` varchar(14) NOT NULL default 'D M d, Y g:i a',
`user_new_privm sg` smallint(5) unsigned NOT NULL default '0',
`user_unread_pr ivmsg` smallint(5) unsigned NOT NULL default '0',
`user_last_priv msg` int(11) NOT NULL default '0',
`user_emailtime ` int(11) default NULL,
`user_allowhtml ` tinyint(4) default '1',
`user_allowbbco de` tinyint(4) default '1',
`user_allowsmil e` tinyint(4) default '1',
`user_allowavat ar` tinyint(4) NOT NULL default '1',
`user_allow_pm` tinyint(4) NOT NULL default '1',
`user_allow_vie wonline` tinyint(4) NOT NULL default '1',
`user_notify` tinyint(4) NOT NULL default '0',
`user_notify_pm ` tinyint(4) NOT NULL default '0',
`user_popup_pm` tinyint(4) NOT NULL default '0',
`user_avatar_ty pe` tinyint(4) NOT NULL default '3',
`user_sig_bbcod e_uid` varchar(10) default NULL,
`user_actkey` varchar(32) default NULL,
`user_newpasswd ` varchar(32) default NULL,
`user_group_cp` int(11) NOT NULL default '2',
`user_group_lis t_cp` varchar(100) NOT NULL default '2',
`user_active_cp ` tinyint(4) NOT NULL default '1',
`susdel_reason` text,
PRIMARY KEY (`user_id`),
KEY `uname` (`username`),
KEY `user_session_t ime` (`user_session_ time`)
) ENGINE=MyISAM DEFAULT charset=utf8 AUTO_INCREMENT= 308 ;

DROP TABLE IF EXISTS `pp_books`;
CREATE TABLE IF NOT EXISTS `pp_books` (
`book_id` smallint(4) unsigned NOT NULL auto_increment,
`book_name` varchar(50) NOT NULL default '',
`is_retired` tinyint(1) unsigned default NULL,
PRIMARY KEY (`book_id`),
UNIQUE KEY `book_name` (`book_name`)
) ENGINE=MyISAM AUTO_INCREMENT= 3670 ;

DROP TABLE IF EXISTS `pp_ubooks`;
CREATE TABLE IF NOT EXISTS `pp_ubooks` (
`uid` int(10) unsigned NOT NULL default '0',
`book_id` int(10) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT charset=utf8;
=============== =============== =============== ===============

(The last table has a unique index which spans across both columns).

This is the query I am executing:

SELECT
t1.letter AS letter,
COUNT(*) AS total_books_in_ section,
SUM(IF(t1.retir ed='1',1,0 )) AS total_retired,
SUM(IF(t1.retir ed!='1',1,0 )) AS owned_regular,
SUM(IF(t1.retir ed='1' AND t2.bid IS NOT NULL,1,0 )) AS
owned_retired_b ooks,
SUM(IF(t1.retir ed='1' AND t2.bid IS NULL,1,0)) AS
unowned_retired _books,
SUM(IF(t1.retir ed!='1' AND t2.bid IS NOT NULL,1,0)) AS
owned_regular_b ooks,
SUM(IF(t1.retir ed!='1' AND t2.bid IS NULL,1,0)) AS
unowned_regular _books
FROM (
SELECT
book_id AS bid,
LEFT(book_name, 1) AS letter,
IF(is_retired=' 1',1,0) AS retired
FROM
pp_books
) AS t1
LEFT JOIN (
SELECT
book_id AS bid,
cms_users.usern ame AS username,
IF(book_id IS NULL,0,1) AS is_owned
FROM
pp_ubooks
RIGHT JOIN
cms_users
ON
cms_users.user_ id=pp_ubooks.ui d
WHERE
cms_users.usern ame='$username'
OR
cms_users.usern ame IS NULL
) AS t2
ON
t1.bid=t2.bid
GROUP BY
letter;

I am trying to comprehend what might have gone wrong to be causing this
problem. As I mentioned before, it takes only 0.01 seconds to execute
in both phpmyadmin and at the CLI. All other queries on my website
execute super-fast, just not any that use pp_ubooks or pp_books. Even
this query takes more than 5 seconds to execute on the website, but
only 0.00 seconds using the CLI.

Just for the record, my website and the CLI are using the same
database. I think it might have something to do with my table
collations, but I can't be sure if this is the case, and if it is, why
it has such a massive impact. I have tried setting my book tables to
latin-swedish-ci (which is the default), utf8-unicode-ci, and
utf8-general-ci (which all of my other tables are set to), and nothing
seems to work any better.

I have been utterly baffled by this mystery for days now, and if
haven't got any hair left on my head to pull out anymore. If anyone
could suggest anything they believe to be helpful I would really
appreciate it! I have even gone as far as to completely scrap apache2,
mysql-server-5.0 and php4 and to start the installation all over again.
This is what leads me to beleive it could be something to do with my
tables rather than the configuration of anything in particular, but I
am just guessing.

Best wishes.

Daz.

Oh, and this is th query that takes more than 5 seconds when executed
through PHP:
SELECT book_id AS bid, LEFT(book_name, 1) AS letter,
IF(is_retired=' 1',1,0) AS retired FROM pp_books;
Oct 28 '06 #3
Daz

pangea33 wrote:
How many results do you get back? If your recordset is huge, the render
time in your browser could be the problem rather than just the query.

What does php do to the records as it's outputting them? Any sort of
calculations?
The number of results varies. It can be 10, 20,30,50 or 100, and the
output is reformatted. However, I am using Dragonfly CMS, which tells
me how long the pages took to generate, and how long all of the queries
put together took to execute. I have also measured the time it takes to
execute the query myself using microtime(), and it's just taking far
too long. As I mentioned before, it never used to take as long, and
queries involving using other tables are still super-quick.

Oct 28 '06 #4
Daz wrote:
Oh, and this is th query that takes more than 5 seconds when executed
through PHP:
SELECT book_id AS bid, LEFT(book_name, 1) AS letter,
IF(is_retired=' 1',1,0) AS retired FROM pp_books;
Probably a wild-goose chase ... but try

explain
SELECT book_id AS bid, LEFT(book_name, 1) AS letter,
IF(is_retired=' 1',1,0) AS retired FROM pp_books;

both in PHP and in the CLI. See if there's any difference.

--
I (almost) never check the dodgeit address.
If you *really* need to mail me, use the address in the Reply-To
header with a message in *plain* *text* *without* *attachments*.
Oct 28 '06 #5
Daz

Pedro Graca wrote:
Probably a wild-goose chase ... but try

explain
SELECT book_id AS bid, LEFT(book_name, 1) AS letter,
IF(is_retired=' 1',1,0) AS retired FROM pp_books;

both in PHP and in the CLI. See if there's any difference.
Good thinking, Pedro!

Many thanks for your suggestion, I will give it a go. I can never quite
understand what the results of EXPLAIN means, but I guess I'd better
start learning. :)

Oct 29 '06 #6
Daz wrote:
Hi everyone.

I was faced with the choice of whether my problem is indeed a PHP
problem or a MySQL. I have decided it's a PHP problem as I don't
experience the same problem when I execute the same query at the CLI.

I am having trouble executing a large query through my PHP script. It
takes about 7-11 seconds on
average to execute, whereas the same query only takes 0.01 seconds to
execute through the CLI.

I thought that it could have been something to do with the table
collation, and I have changed that to alsorts of different things,
still to no avail. For some reason the problem appeared to have occured
from out of the blue. It all worked fine, and then all of a sudden
started going slow. The rest of the queries executed on the database
are very fast, it only seems to be when I use the tables that I have
recently created for my project that they run slow.

I am joining three tables together:

=============== =============== =============== ===============

CREATE TABLE IF NOT EXISTS `cms_users` (
`user_id` mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(60) default NULL,
`username` varchar(50) NOT NULL default '',
`user_email` varchar(255) NOT NULL default '',
`femail` varchar(255) default NULL,
`user_website` varchar(255) default NULL,
`user_avatar` varchar(255) NOT NULL default '',
`user_regdate` varchar(20) NOT NULL default '',
`user_icq` varchar(15) default NULL,
`user_occ` varchar(100) default NULL,
`user_from` varchar(100) default NULL,
`user_interests ` varchar(150) default NULL,
`user_sig` varchar(255) default NULL,
`user_viewemail ` tinyint(4) NOT NULL default '0',
`user_aim` varchar(35) default NULL,
`user_yim` varchar(40) default NULL,
`user_msnm` varchar(40) default NULL,
`user_password` varchar(40) NOT NULL default '',
`storynum` tinyint(4) NOT NULL default '10',
`umode` varchar(10) default NULL,
`uorder` tinyint(4) NOT NULL default '0',
`thold` tinyint(4) NOT NULL default '0',
`noscore` tinyint(4) NOT NULL default '0',
`bio` tinytext,
`ublockon` tinyint(4) NOT NULL default '0',
`ublock` tinytext,
`theme` varchar(255) NOT NULL default '',
`commentmax` int(11) NOT NULL default '4096',
`counter` int(11) NOT NULL default '0',
`newsletter` tinyint(4) NOT NULL default '0',
`user_posts` int(11) NOT NULL default '0',
`user_attachsig ` tinyint(4) NOT NULL default '1',
`user_rank` int(11) NOT NULL default '0',
`user_level` tinyint(4) NOT NULL default '1',
`user_active` tinyint(4) default '1',
`user_session_t ime` int(11) NOT NULL default '0',
`user_lastvisit ` int(11) NOT NULL default '0',
`user_timezone` varchar(6) NOT NULL default '0',
`user_dst` smallint(6) NOT NULL default '0',
`user_style` tinyint(4) default NULL,
`user_lang` varchar(255) NOT NULL default 'english',
`user_dateforma t` varchar(14) NOT NULL default 'D M d, Y g:i a',
`user_new_privm sg` smallint(5) unsigned NOT NULL default '0',
`user_unread_pr ivmsg` smallint(5) unsigned NOT NULL default '0',
`user_last_priv msg` int(11) NOT NULL default '0',
`user_emailtime ` int(11) default NULL,
`user_allowhtml ` tinyint(4) default '1',
`user_allowbbco de` tinyint(4) default '1',
`user_allowsmil e` tinyint(4) default '1',
`user_allowavat ar` tinyint(4) NOT NULL default '1',
`user_allow_pm` tinyint(4) NOT NULL default '1',
`user_allow_vie wonline` tinyint(4) NOT NULL default '1',
`user_notify` tinyint(4) NOT NULL default '0',
`user_notify_pm ` tinyint(4) NOT NULL default '0',
`user_popup_pm` tinyint(4) NOT NULL default '0',
`user_avatar_ty pe` tinyint(4) NOT NULL default '3',
`user_sig_bbcod e_uid` varchar(10) default NULL,
`user_actkey` varchar(32) default NULL,
`user_newpasswd ` varchar(32) default NULL,
`user_group_cp` int(11) NOT NULL default '2',
`user_group_lis t_cp` varchar(100) NOT NULL default '2',
`user_active_cp ` tinyint(4) NOT NULL default '1',
`susdel_reason` text,
PRIMARY KEY (`user_id`),
KEY `uname` (`username`),
KEY `user_session_t ime` (`user_session_ time`)
) ENGINE=MyISAM DEFAULT charset=utf8 AUTO_INCREMENT= 308 ;

DROP TABLE IF EXISTS `pp_books`;
CREATE TABLE IF NOT EXISTS `pp_books` (
`book_id` smallint(4) unsigned NOT NULL auto_increment,
`book_name` varchar(50) NOT NULL default '',
`is_retired` tinyint(1) unsigned default NULL,
PRIMARY KEY (`book_id`),
UNIQUE KEY `book_name` (`book_name`)
) ENGINE=MyISAM AUTO_INCREMENT= 3670 ;

DROP TABLE IF EXISTS `pp_ubooks`;
CREATE TABLE IF NOT EXISTS `pp_ubooks` (
`uid` int(10) unsigned NOT NULL default '0',
`book_id` int(10) unsigned NOT NULL default '0'
) ENGINE=MyISAM DEFAULT charset=utf8;
=============== =============== =============== ===============

(The last table has a unique index which spans across both columns).

This is the query I am executing:

SELECT
t1.letter AS letter,
COUNT(*) AS total_books_in_ section,
SUM(IF(t1.retir ed='1',1,0 )) AS total_retired,
SUM(IF(t1.retir ed!='1',1,0 )) AS owned_regular,
SUM(IF(t1.retir ed='1' AND t2.bid IS NOT NULL,1,0 )) AS
owned_retired_b ooks,
SUM(IF(t1.retir ed='1' AND t2.bid IS NULL,1,0)) AS
unowned_retired _books,
SUM(IF(t1.retir ed!='1' AND t2.bid IS NOT NULL,1,0)) AS
owned_regular_b ooks,
SUM(IF(t1.retir ed!='1' AND t2.bid IS NULL,1,0)) AS
unowned_regular _books
FROM (
SELECT
book_id AS bid,
LEFT(book_name, 1) AS letter,
IF(is_retired=' 1',1,0) AS retired
FROM
pp_books
) AS t1
LEFT JOIN (
SELECT
book_id AS bid,
cms_users.usern ame AS username,
IF(book_id IS NULL,0,1) AS is_owned
FROM
pp_ubooks
RIGHT JOIN
cms_users
ON
cms_users.user_ id=pp_ubooks.ui d
WHERE
cms_users.usern ame='$username'
OR
cms_users.usern ame IS NULL
) AS t2
ON
t1.bid=t2.bid
GROUP BY
letter;

I am trying to comprehend what might have gone wrong to be causing this
problem. As I mentioned before, it takes only 0.01 seconds to execute
in both phpmyadmin and at the CLI. All other queries on my website
execute super-fast, just not any that use pp_ubooks or pp_books. Even
this query takes more than 5 seconds to execute on the website, but
only 0.00 seconds using the CLI.

Just for the record, my website and the CLI are using the same
database. I think it might have something to do with my table
collations, but I can't be sure if this is the case, and if it is, why
it has such a massive impact. I have tried setting my book tables to
latin-swedish-ci (which is the default), utf8-unicode-ci, and
utf8-general-ci (which all of my other tables are set to), and nothing
seems to work any better.

I have been utterly baffled by this mystery for days now, and if
haven't got any hair left on my head to pull out anymore. If anyone
could suggest anything they believe to be helpful I would really
appreciate it! I have even gone as far as to completely scrap apache2,
mysql-server-5.0 and php4 and to start the installation all over again.
This is what leads me to beleive it could be something to do with my
tables rather than the configuration of anything in particular, but I
am just guessing.

Best wishes.

Daz.
Daz,

Is it possible you're using an older version of the client libs with an
updated MySQL?

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===
Oct 29 '06 #7
Daz wrote:
Pedro Graca wrote:
>Probably a wild-goose chase ... but try

explain
SELECT book_id AS bid, LEFT(book_name, 1) AS letter,
IF(is_retired=' 1',1,0) AS retired FROM pp_books;

both in PHP and in the CLI. See if there's any difference.

Many thanks for your suggestion, I will give it a go. I can never quite
understand what the results of EXPLAIN means, but I guess I'd better
start learning. :)
If there is no difference between the explains you can postpone studying
the EXPLAIN results LOL

--
I (almost) never check the dodgeit address.
If you *really* need to mail me, use the address in the Reply-To
header with a message in *plain* *text* *without* *attachments*.
Oct 29 '06 #8
Daz

Jerry Stuckle wrote:
Daz,

Is it possible you're using an older version of the client libs with an
updated MySQL?
I think you may be on to something there Jerry. I started getting very
impatient and guess it may have had something to do with my downgrading
to 4.2, and then back to 5.0. I stripped everything back to the bare
metal, Apache2, PHP, MySQL. phpmyadmin etc... It was tough to reinstall
everything again as I am a fairly new Linux user, and on this occassion
I had jumped through many more hoops than I needed to originally.
However, it seems to have paid off and everything is now working as it
should be. I can only assume that something was the wrong version.
Perhaps MySQL prefork?

Many thanks for your time.

Daz.

Oct 29 '06 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1254
by: Ganbold | last post by:
Hi, I'm using MySQL 4.0.13 for both master and slave servers. However I have problem starting slave server. Error log says: 030804 14:06:10 Slave I/O thread: connected to master 'repl@202.xxx.xxx.xxx:3306', replication started in log 'backend2-bin.228' at position 43583528 030804 14:11:48 Slave I/O thread exiting, read up to log 'backend2-bin.228', position 64018330
0
1634
by: Daniel Crespo | last post by:
Hi to all, I'm using adodb for accessing mysql and postgres. My problem relies on the mysql access. Sometimes, when I try to execute a query (using ExecTrans method below), I get this error: 'NoneType' object has no attribute 'cursor'
3
6088
by: Juan Antonio Villa | last post by:
Hello, I'm having a problem replicating a simple database using the binary log replication, here is the problem: When the master sends an update to the slave, an example update reads as follows: UPDATE MainInfo SET dAddress='38 Holland Blvd', dCity='miami', dState='FL', dZip='33000', dCountry='USA', dPhone='999987565', dNum='AC15857', dName='Michael A Scott' WHERE did=22'
1
5281
by: PowerLifter1450 | last post by:
I've been having a very rough time installinig mySQL on Linux. I have been following the instructions form here: http://www.hostlibrary.com/installing_apache_mysql_php_on_linux Everytime I get to #./configure it goes through all of the preparing tables and starting mysqlServer and daemon, but than immediaetly says "mysql ended" -- I try to do #make right after anyway, but I get the error "No targets specified and no makefile found" -- Any...
4
4897
by: Federico | last post by:
Hi everybody, I'm evaluating the possibility of using MySQL 5.0 as a database backend for an application we are doing and I'd like to have hardware requirements rough estimates and/or real world experience from people that are already running similar dimensioned database/application. As a rought estimate the database has around 80 tables 4-5 of these are supposed to grow up to 5.000.000 records another 30% will be in the 100.000 range...
29
5502
by: wizofaus | last post by:
I previously posted about a problem where it seemed that changing the case of the word "BY" in a SELECT query was causing it to run much much faster. Now I've hit the same thing again, where basically almost any change I make to how the query is executed (so that it still performs the same function) causes the performance to jump from a dismal 7 or 8 seconds to instantaneous. It's a very simple query of the form: SELECT Min(MyValue)...
13
3416
by: Ciaran | last post by:
Hi All, Is it faster to have mySql look up as much data as possible in one complex query or to have php do all the complex processing and submit lots of simple queries to the mysql database? Cheers, Ciarán
30
2818
by: Einstein30000 | last post by:
Hi, in one of my php-scripts is the following query (with an already open db-connection): $q = "INSERT INTO main (name, img, descr, from, size, format, cat, host, link, date) VALUES ('$name', '$img', '$descr', '$user', '$size', '$format', '$cat', '$host', '$link', '$date')" or die(mysql_error()); And when the query gets executed i get back the following error:
6
38506
Atli
by: Atli | last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users. Anyone should be able to get through this without much trouble. Programming knowledge is not required. Index What is SQL? Why MySQL? Installing MySQL. Using the MySQL command line interface
0
9122
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9001
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8860
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7670
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6496
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5841
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3018
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2280
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1980
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.