GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

No index by order by

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • No index by order by

    Hi Friends,

    i have some Problems with no index Select * order by

    Sample1

    SELECT userid, username, birthday FROM bb2_users WHERE birthday LIKE '%-12-15' ORDER BY username ASC;


    Explain

    EXPLAIN SELECT userid, username, birthday FROM bb2_users WHERE birthday LIKE '%-12-15' ORDER BY username ASC;


    RESULT

    id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE bb2_users ALL NULL NULL NULL NULL 8039 Using where; Using filesort


    LOG

    # User@Host: TEST @ localhost []# Query_time: 0.016334 Lock_time: 0.000026 Rows_sent: 5 Rows_examined: 8038SET timestamp=1260872528;SELECT userid, username, birthday FROM bb2_users WHERE birthday LIKE '%-12-15' ORDER BY username ASC;


    Index set to table birthday and username

    Can you help me, I have other questions

  • #2
    I can't help you because you haven't actually asked a question. This is my favorite guide on how to ask a question so it's easy for people to answer: http://catb.org/~esr/faqs/smart-questions.html

    Comment


    • #3
      Hello,

      I need help how I can improve my queries. Share index does not work.
      Can I rewrite the query? But how?
      Sorry for my English

      the_condor

      Comment


      • #4
        The leading % in your LIKE expression is preventing the index from being used. You should look at the user comments in the MySQL manual, which explain how you can do birthday queries. Look at the page on date and time functions.

        Comment


        • #5
          Hello xaprb,

          thank you for the fast Replay, i will look in the Mysql manual.


          Here a other Query , no index

          mysql-slow.log

          # User@Host: FORUM @ localhost []# Query_time: 0.038074 Lock_time: 0.000040 Rows_sent: 100 Rows_examined: 8010SET timestamp=1261154801;SELECT userid, username, email, homepage, regdate, userposts, showemail, usercanemail, receivepm, rankingpoints FROM bb2_users WHERE rankingpoints ORDER BY rankingpoints ASC LIMIT 0,100;


          EXPLAIN

          id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE bb2_users index NULL rankingpoints 8 NULL 100 Using where


          possible_keys = Null
          Key = rankingpoints

          Table Structure

          CREATE TABLE IF NOT EXISTS `bb2_users` ( `userid` int(11) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(50) NOT NULL DEFAULT '', `password` varchar(50) NOT NULL DEFAULT '', `sha1_password` varchar(40) NOT NULL DEFAULT '', `email` varchar(150) NOT NULL DEFAULT '', `userposts` mediumint(7) unsigned NOT NULL DEFAULT '0', `groupcombinationid` int(11) unsigned NOT NULL DEFAULT '0', `rankid` int(11) unsigned NOT NULL DEFAULT '0', `title` varchar(50) NOT NULL DEFAULT '', `regdate` int(11) unsigned NOT NULL DEFAULT '0', `lastvisit` int(11) unsigned NOT NULL DEFAULT '0', `lastactivity` int(11) unsigned NOT NULL DEFAULT '0', `usertext` text NOT NULL, `aufgabe` text NOT NULL, `portrait` text NOT NULL, `signature` text NOT NULL, `disablesignature` tinyint(1) NOT NULL DEFAULT '0', `icq` varchar(30) NOT NULL DEFAULT '', `aim` varchar(30) NOT NULL DEFAULT '', `yim` varchar(30) NOT NULL DEFAULT '', `msn` varchar(30) NOT NULL DEFAULT '', `homepage` varchar(250) NOT NULL DEFAULT '', `birthday` date NOT NULL DEFAULT '0000-00-00', `avatarid` int(11) unsigned NOT NULL DEFAULT '0', `gender` tinyint(1) NOT NULL DEFAULT '0', `showemail` tinyint(1) NOT NULL DEFAULT '0', `admincanemail` tinyint(1) NOT NULL DEFAULT '1', `usercanemail` tinyint(1) NOT NULL DEFAULT '1', `invisible` tinyint(1) NOT NULL DEFAULT '0', `usecookies` tinyint(1) NOT NULL DEFAULT '1', `styleid` int(11) unsigned NOT NULL DEFAULT '0', `langid` int(11) NOT NULL DEFAULT '0', `activation` int(11) unsigned NOT NULL DEFAULT '0', `blocked` tinyint(1) NOT NULL DEFAULT '0', `daysprune` smallint(5) unsigned NOT NULL DEFAULT '0', `timezoneoffset` char(3) NOT NULL, `startweek` tinyint(1) NOT NULL DEFAULT '0', `dateformat` varchar(10) NOT NULL DEFAULT '', `timeformat` varchar(10) NOT NULL DEFAULT '', `emailnotify` tinyint(1) NOT NULL DEFAULT '0', `notificationperpm` tinyint(1) NOT NULL DEFAULT '0', `buddylist` text NOT NULL, `ignorelist` text NOT NULL, `receivepm` tinyint(1) NOT NULL DEFAULT '1', `emailonpm` tinyint(1) NOT NULL DEFAULT '0', `pmpopup` tinyint(1) NOT NULL DEFAULT '0', `umaxposts` smallint(5) unsigned NOT NULL DEFAULT '0', `showsignatures` tinyint(1) NOT NULL DEFAULT '1', `showavatars` tinyint(1) NOT NULL DEFAULT '1', `showimages` tinyint(1) NOT NULL DEFAULT '1', `ratingcount` smallint(5) unsigned NOT NULL DEFAULT '0', `ratingpoints` mediumint(7) unsigned NOT NULL DEFAULT '0', `threadview` tinyint(1) NOT NULL DEFAULT '0', `useuseraccess` tinyint(1) NOT NULL DEFAULT '0', `isgroupleader` tinyint(1) NOT NULL DEFAULT '0', `rankgroupid` int(11) NOT NULL DEFAULT '0', `useronlinegroupid` int(11) NOT NULL DEFAULT '0', `allowsigsmilies` tinyint(1) NOT NULL DEFAULT '1', `allowsightml` tinyint(1) NOT NULL DEFAULT '0', `allowsigbbcode` tinyint(1) NOT NULL DEFAULT '1', `allowsigimages` tinyint(1) NOT NULL DEFAULT '1', `emailonapplication` tinyint(1) NOT NULL DEFAULT '0', `acpmode` tinyint(3) NOT NULL DEFAULT '1', `acppersonalmenu` tinyint(1) NOT NULL DEFAULT '0', `acpmenumarkfirst` tinyint(3) NOT NULL DEFAULT '0', `acpmenuhidelast` tinyint(3) NOT NULL DEFAULT '0', `usewysiwyg` tinyint(1) unsigned NOT NULL DEFAULT '0', `useawh` tinyint(3) unsigned NOT NULL DEFAULT '1', `pmtotalcount` int(11) unsigned NOT NULL DEFAULT '0', `pminboxcount` int(11) unsigned NOT NULL DEFAULT '0', `pmnewcount` int(11) unsigned NOT NULL DEFAULT '0', `pmunreadcount` int(11) unsigned NOT NULL DEFAULT '0', `ip_save` varchar(255) NOT NULL, `reg_ipaddress` varchar(15) NOT NULL DEFAULT '', `guthaben` bigint(20) NOT NULL DEFAULT '0', `canedittitle` tinyint(1) NOT NULL DEFAULT '0', `gw_g` int(50) NOT NULL DEFAULT '0', `gw_v` varchar(50) NOT NULL DEFAULT '0', `gut_ava_ba` tinyint(1) NOT NULL DEFAULT '0', `gut_ava_ea` tinyint(1) NOT NULL DEFAULT '0', `Birthday_Bot` varchar(4) NOT NULL DEFAULT '', `loginlog` int(12) NOT NULL DEFAULT '0', `steuerkl` tinyint(1) NOT NULL DEFAULT '0', `hpblock` int(1) NOT NULL DEFAULT '0', `ut` int(1) NOT NULL DEFAULT '1', `acp_userpic` text NOT NULL, `acp_usertext` varchar(75) NOT NULL DEFAULT '', `qreplyview` int(11) NOT NULL DEFAULT '0', `wordmixerstellt` int(7) NOT NULL DEFAULT '0', `wordmixgeloest` int(7) NOT NULL DEFAULT '0', `wmblock` tinyint(1) NOT NULL DEFAULT '0', `adminfield` varchar(250) NOT NULL DEFAULT '', `adminfieldname` varchar(250) NOT NULL DEFAULT '', `firstvisit` int(10) unsigned NOT NULL, `rankingpoints` bigint(20) NOT NULL DEFAULT '0', `regip` varchar(15) NOT NULL DEFAULT '000.000.000.000', `dev_prof` varchar(30) NOT NULL, `useslide` int(11) NOT NULL DEFAULT '0', `is_online` int(11) NOT NULL DEFAULT '0', `last_ping` text NOT NULL, `liga_session` varchar(255) NOT NULL, `wikiview` int(11) NOT NULL DEFAULT '1', `wikiviewhover` int(11) NOT NULL DEFAULT '1', `readrules` int(11) NOT NULL DEFAULT '0', `rss_feeds` text NOT NULL, `rss_time` int(11) NOT NULL, PRIMARY KEY (`userid`), KEY `username` (`username`), KEY `rankid` (`rankid`), KEY `avatarid` (`avatarid`), KEY `activation` (`activation`), KEY `groupcombinationid` (`groupcombinationid`), KEY `reg_ipaddress` (`reg_ipaddress`), KEY `rankingpoints` (`rankingpoints`), KEY `username_2` (`username`), KEY `birthday` (`birthday`), KEY `rankgroupid` (`rankgroupid`), KEY `rankingpoints_2` (`rankingpoints`), KEY `userposts` (`userposts`), KEY `birthday_2` (`birthday`), KEY `username_3` (`username`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14446 ;


          what can I do to improve the query.

          Thank you and have a nice weekend.

          the_condor

          Comment


          • #6
            remove redundant indices and don't base your opinion on a single query run.

            Comment


            • #7
              Thank you for Replay gmouse,

              i will check this!

              Ok here a very hard Query, what can i do for a better Performance?



              EXPLAIN SELECT bb2_threads.topic, bb2_threads.threadid, bb2_threads.lastposttime, bb2_threads.views, bb2_users.userid, bb2_threads.boardid AS parentid, bb2_users.usernameFROM bb2_threadsLEFT JOIN bb2_users ON ( bb2_users.userid = bb2_threads.lastposterid )LEFT JOIN bb2_boards ON ( bb2_boards.boardid = bb2_threads.boardid )WHERE bb2_threads.boardidIN ( 0, 34, 313, 312, 314, 334, 320, 331, 324, 325, 326, 327, 328, 329, 330, 335, 336, 338, 339, 120, 317, 192, 185, 190, 199, 206, 234, 332, 333, 337, 247, 248, 249, 250, 251, 252, 253, 254, 255, 256, 257, 258, 259, 631, 263, 264, 265, 266, 267, 268, 269, 270, 272, 273, 274, 275, 276, 277, 278, 279, 280, 281, 282, 283, 284, 288, 289, 290, 291, 292, 293, 294, 295, 296, 297, 630, 301, 302, 303, 304, 344, 345, 346, 347, 348, 349, 352, 353, 383, 378, 380, 381, 413, 414, 415, 437, 455, 633, 634, 611, 629, 650, 654, 656, 664, 668, 674, 675, 676, 678, 679, 680, 681, 682, 683, 684, 685 )ORDER BY bb2_threads.lastposttime DESCLIMIT 5



              id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE bb2_threads range boardid boardid 4 NULL 53294 Using where; Using filesort1 SIMPLE bb2_users eq_ref PRIMARY,userid,userid_2 PRIMARY 4 forum.bb2_threads.lastposterid 1 1 SIMPLE bb2_boards eq_ref PRIMARY,boardid PRIMARY 2 forum.bb2_threads.boardid 1 Using index

              Comment


              • #8
                if where is not restrictive, use an index on lastposttime

                Comment


                • #9
                  Hello gmouse,

                  i have add this index, here the responce!


                  id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE bb2_threads index boardid lastposttime 4 NULL 5 Using where1 SIMPLE bb2_users eq_ref PRIMARY,userid,userid_2 PRIMARY 4 forum.bb2_threads.lastposterid 1 1 SIMPLE bb2_boards eq_ref PRIMARY,boardid PRIMARY 2 forum.bb2_threads.boardid 1 Using index


                  thats Ok ? or you have a better Query for me.

                  I have any more bad Querys, here a other one


                  SELECT MIN(p.postid) AS minpost, MAX(p.postid) AS maxpost, t.threadid, MIN(p.posttime) AS starttime, MAX(p.posttime) AS lastposttime, (COUNT(*)-1) AS posts FROM bb2_posts p, bb2_threads t WHERE t.threadid=p.threadid GROUP BY t.threadid LIMIT 1000, 500


                  Explain


                  id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE t index PRIMARY,threadid threadid 3 NULL 23 Using index1 SIMPLE p ref threadid,threadid_2 threadid 4 forum.t.threadid 8 Using where



                  mmhhh i need a better Performance Query!! This Query is very slow.

                  Thank you and have a nice Day

                  Comment


                  • #10
                    don't use limit with a high offset

                    Comment


                    • #11
                      Thanks gmouse for the Tips!

                      I could optimize my database by 10%, thats nice ) )


                      Here a other Query, thats very Slow and high Rows


                      EXPLAINSELECTb.*, t.topic, t.prefix AS threadprefix, i.*, bv.lastvisitFROM bb2_boards bLEFT JOIN bb2_threads t ON (t.threadid=b.lastthreadid)LEFT JOIN bb2_icons i USING (iconid)LEFT JOIN bb2_boardvisit bv ON (bv.boardid=b.boardid AND bv.userid='9865')ORDER by b.parentid ASC, b.boardorder ASC;



                      id select_type table type possible_keys key key_len ref rows Extra1 SIMPLE b ALL NULL NULL NULL NULL 172 Using filesort1 SIMPLE t eq_ref PRIMARY,threadid,threadid_2 PRIMARY 3 forum.b.lastthreadid 1 1 SIMPLE i eq_ref PRIMARY PRIMARY 4 forum.t.iconid 1 1 SIMPLE bv eq_ref PRIMARY,userid PRIMARY 8 forum.b.boardid,const 1


                      I have Set a Key to boardorder and parentid but without success (
                      Do you have a solution for me?

                      Thank you

                      the_condor

                      Comment


                      • #12
                        make sure you use one multi-column index, use force index if necessary

                        Comment


                        • #13
                          Thanks for the Reply,

                          I have no idea to change the Query.


                          FORCE INDEX(KEY) in the Query give me a Error.

                          the_condor

                          P.S Merry Christmas !

                          Comment


                          • #14
                            SELECT
                            b.*, t.topic, t.prefix AS threadprefix, i.*
                            , bv.lastvisit
                            FROM bb2_boards b FORCE INDEX(name of index)
                            LEFT JOIN bb2_threads t ON (t.threadid=b.lastthreadid)
                            LEFT JOIN bb2_icons i USING (iconid)
                            LEFT JOIN bb2_boardvisit bv ON (bv.boardid=b.boardid AND bv.userid='9865')
                            ORDER by b.parentid ASC, b.boardorder ASC;

                            sure you have a multi-column index?

                            MC!

                            Comment


                            • #15
                              I do not know!

                              here the Create Tab from bb2_boards


                              CREATE TABLE IF NOT EXISTS `bb2_boards` ( `boardid` smallint(3) unsigned NOT NULL AUTO_INCREMENT, `styleid` int(11) unsigned NOT NULL DEFAULT '0', `parentid` int(11) unsigned NOT NULL DEFAULT '0', `parentlist` text NOT NULL, `childlist` text NOT NULL, `boardorder` mediumint(7) unsigned NOT NULL DEFAULT '1', `title` varchar(250) NOT NULL DEFAULT '', `password` varchar(25) NOT NULL DEFAULT '', `description` text NOT NULL, `prefixuse` tinyint(1) NOT NULL DEFAULT '0', `prefixrequired` tinyint(1) NOT NULL DEFAULT '0', `mustuseprefix` tinyint(1) NOT NULL, `prefix` text NOT NULL, `threadtemplateuse` tinyint(1) NOT NULL DEFAULT '0', `threadtemplate` text NOT NULL, `posttemplateuse` tinyint(1) NOT NULL DEFAULT '0', `posttemplate` text NOT NULL, `threadcount` int(11) unsigned NOT NULL DEFAULT '0', `postcount` int(11) unsigned NOT NULL DEFAULT '0', `lastthreadid` int(11) unsigned NOT NULL DEFAULT '0', `lastposttime` int(11) unsigned NOT NULL DEFAULT '0', `lastposterid` int(11) unsigned NOT NULL DEFAULT '0', `lastposter` varchar(50) NOT NULL DEFAULT '0', `allowratings` tinyint(1) NOT NULL DEFAULT '1', `daysprune` smallint(5) unsigned NOT NULL DEFAULT '0', `sortfield` varchar(20) NOT NULL DEFAULT '', `sortorder` varchar(5) NOT NULL DEFAULT '', `threadsperpage` smallint(5) unsigned NOT NULL DEFAULT '0', `postsperpage` smallint(5) unsigned NOT NULL DEFAULT '0', `postorder` tinyint(1) NOT NULL DEFAULT '0', `countuserposts` tinyint(1) NOT NULL DEFAULT '1', `hotthread_reply` smallint(5) unsigned NOT NULL DEFAULT '0', `hotthread_view` smallint(5) unsigned NOT NULL DEFAULT '0', `moderatenew` tinyint(2) NOT NULL DEFAULT '0', `enforcestyle` tinyint(1) NOT NULL DEFAULT '0', `closed` tinyint(1) NOT NULL DEFAULT '0', `isboard` tinyint(1) NOT NULL DEFAULT '0', `invisible` tinyint(1) NOT NULL DEFAULT '0', `showinarchive` tinyint(1) NOT NULL DEFAULT '1', `externalurl` varchar(255) NOT NULL DEFAULT '', `schablone` longtext NOT NULL, `done_field` int(1) NOT NULL DEFAULT '0', `guthaben` tinyint(1) NOT NULL DEFAULT '1', `allowuserrating` int(11) unsigned NOT NULL DEFAULT '0', `merge_disable` int(1) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`boardid`), KEY `invisible` (`invisible`), KEY `password` (`password`), KEY `boardid` (`boardid`), KEY `externalurl` (`externalurl`), KEY `boardorder` (`boardorder`), KEY `parentid` (`parentid`), KEY `lastthreadid` (`lastthreadid`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=700 ;


                              I've tried all, no success (

                              Comment

                              Working...
                              X