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

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

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.

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

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

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

if where is not restrictive, use an index on lastposttime

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

don't use limit with a high offset

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

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

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 !

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!

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 (