No index by order by

MySQL & MariaDB Other MySQL® Questions
1

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: [URL]http://catb.org/~esr/faqs/smart-questions.html[/URL]

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

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.

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

6

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

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

8

if where is not restrictive, use an index on lastposttime

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

10

don’t use limit with a high offset

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

12

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

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 !

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!

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 (

16

http://dev.mysql.com/doc/refman/5.0/en/multiple-column-index es.html