|
|
Posted by Daz on 10/28/06 16:30
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_time` 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_dateformat` varchar(14) NOT NULL default 'D M d, Y g:i a',
`user_new_privmsg` smallint(5) unsigned NOT NULL default '0',
`user_unread_privmsg` smallint(5) unsigned NOT NULL default '0',
`user_last_privmsg` int(11) NOT NULL default '0',
`user_emailtime` int(11) default NULL,
`user_allowhtml` tinyint(4) default '1',
`user_allowbbcode` tinyint(4) default '1',
`user_allowsmile` tinyint(4) default '1',
`user_allowavatar` tinyint(4) NOT NULL default '1',
`user_allow_pm` tinyint(4) NOT NULL default '1',
`user_allow_viewonline` 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_type` tinyint(4) NOT NULL default '3',
`user_sig_bbcode_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_list_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_time` (`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.retired='1',1,0 )) AS total_retired,
SUM(IF(t1.retired!='1',1,0 )) AS owned_regular,
SUM(IF(t1.retired='1' AND t2.bid IS NOT NULL,1,0 )) AS
owned_retired_books,
SUM(IF(t1.retired='1' AND t2.bid IS NULL,1,0)) AS
unowned_retired_books,
SUM(IF(t1.retired!='1' AND t2.bid IS NOT NULL,1,0)) AS
owned_regular_books,
SUM(IF(t1.retired!='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.username 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.uid
WHERE
cms_users.username='$username'
OR
cms_users.username 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.
Navigation:
[Reply to this message]
|