|
|
Posted by pangea33 on 10/28/06 17:31
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_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]
|