|
|
Posted by Daz on 10/28/06 16:45
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_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.
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;
Navigation:
[Reply to this message]
|