BlameThePixel!

[Not Logged in] - [Login] [Register] [Lost Password?]

BTForum » BlameTheOffTopic Forums » BlameTheGeneralOffTopic » Any SQL geniuses in da house?

CBWhiz
Looking For Status
Send PM
Posts: 1044
Threads: 130
Money: £1343.20 (D)
(+ Friend)
Not online within the last half an hour

How can I optimize this further, and why isnt mysql using timeindex:

Query:
\n

SELECT DISTINCT f_forums.id AS id FROM f_forums INNER JOIN f_threads ON f_threads.forum = f_forums.id LEFT OUTER JOIN f_read ON f_read.thread = f_threads.id AND f_read.name = '$loggedin' WHERE ( f_read.time < f_threads.time OR f_read.thread IS NULL ) AND f_threads.time > 0

\n

EXPLAIN output:

table	type	possible_keys	key	key_len	ref	rows	Extra
f_threads	ALL	forumindex,timeindex	NULL	NULL	NULL	641	Using where; Using temporary
f_forums	eq_ref	PRIMARY,id,id_2	PRIMARY	4	f_threads.forum	1	Using where; Using index
f_read	ref	threadindex	threadindex	4	f_threads.id	11	Using where; Distinct

How can I optimize this further, and why isnt mysql using timeindex:

Query:
[code]
SELECT DISTINCT f_forums.id AS id
FROM f_forums
INNER JOIN f_threads ON f_threads.forum = f_forums.id
LEFT OUTER JOIN f_read ON f_read.thread = f_threads.id AND f_read.name = '$loggedin'
WHERE (
f_read.time < f_threads.time OR f_read.thread IS NULL
) AND f_threads.time > 0
[/code]

EXPLAIN output:
<table border="0" cellpadding="5">
<tr><th>table</th><th>type</th><th>possible_keys</th>                    <th>        key</th><th>key_len</th><th>ref</th><th>rows</th>                    <th>Extra</th></tr><tr><td valign="top" bgcolor="#DDDDDD">f_threads</td><td valign="top" bgcolor="#DDDDDD">ALL</td><td valign="top" bgcolor="#DDDDDD">forumindex,timeindex</td><td valign="top" bgcolor="#DDDDDD"><i>NULL</i></td><td align="right" valign="top" bgcolor="#DDDDDD"><i>NULL</i></td><td valign="top" bgcolor="#DDDDDD"><i>NULL</i></td><td align="right" valign="top" bgcolor="#DDDDDD">641</td><td valign="top" bgcolor="#DDDDDD">Using where; Using temporary</td></tr><tr><td valign="top" bgcolor="#CCCCCC">f_forums</td><td valign="top" bgcolor="#CCCCCC">eq_ref</td><td valign="top" bgcolor="#CCCCCC">PRIMARY,id,id_2</td><td valign="top" bgcolor="#CCCCCC">PRIMARY</td><td align="right" valign="top" bgcolor="#CCCCCC">4</td><td valign="top" bgcolor="#CCCCCC">f_threads.forum</td><td align="right" valign="top" bgcolor="#CCCCCC">1</td><td valign="top" bgcolor="#CCCCCC">Using where; Using index</td></tr><tr><td valign="top" bgcolor="#DDDDDD">f_read</td><td valign="top" bgcolor="#DDDDDD">ref</td><td valign="top" bgcolor="#DDDDDD">threadindex</td><td valign="top" bgcolor="#DDDDDD">threadindex</td><td align="right" valign="top" bgcolor="#DDDDDD">4</td><td valign="top" bgcolor="#DDDDDD">f_threads.id</td><td align="right" valign="top" bgcolor="#DDDDDD">11</td><td valign="top" bgcolor="#DDDDDD">Using where; Distinct</td></tr></table>

25.08.03 16:20
Post #1

[Hide Sig (3)] [Profile] [Quote]

meiapaul
Statusless
Send PM
Posts:
Threads:
Money: £0.00 (D)
(+ Friend)
Not online within the last half an hour

sorry, i dont have a clue

25.08.03 17:59
Post #2

[Hide Sig (0)] [Profile] [Quote]

ReadMe
Absent
Send PM
Posts: 2820
Threads: 85
Money: £43.42 (D)
(+ Friend)
Not online within the last half an hour

try http://www.codingforums.com they generally know what they're talking about on any coding/ web design subject

________________
Cant be arsed to remake my sig.

26.08.03 18:30
Post #3

[Hide Sig (7)] [Profile] [Quote]

Post Reply

Your Comments:

Donate to BlameThePixel:

[22 Queries, Page Loaded in 1.788062 Seconds]

ShoutMeUp

Xmas Greetings from waka waka waka waka ChristmasRiddle MERRY CHRISTMAS EVERYONE! Spleet Except for Spleet. TheAbdBoy Always bummin' a brother out. Spleet Happy New Year everyone! But Spleet. TheAbdBoy

Word Association

All

-10 Ago-

MiddleEastern AlphaWolf camel Bloopy toe TheAbdBoy moose knuckle Bloopy MeatLoaf Spleet IdDoAnything4Lo ve AlphaWolf rub n tug TheAbdBoy tugboat The Pope rope Bloopy race TheAbdBoy

-Latest-

Must be logged in to add new words

FictoLeague

You have to be logged in to vote...

Member Stats

Date: 23.11.24.
Members: 4731.
Latest: sdsakldsaldklasdsdsa
Active:
0 user(s)
1 guest(s)

On chat:
Lots of people

Files: 3330

BlameThePixel!

Main

Forum

Sig Management

Files

Money Bliss

PixVille

Fun

Games

Dialectizer

BTRedirect

ClanSites

HELP!

Links

Users

ShoutMeUp

Word Association

FictoLeague

Member Stats