optimization frustration!

for some reason, this isnt working too well.

Array ( [0] => 1 [id] => 1 [1] => SIMPLE [select_type] => SIMPLE [2] => chat [table] => chat [3] => range [type] => range [4] => reciever,type,postedby [possible_keys] => reciever,type,postedby [5] => type [key] => type [6] => 255 [key_len] => 255 [7] => [ref] => [8] => 5552 [rows] => 5552 [9] => Using where; Using filesort [Extra] => Using where; Using filesort )

is what im getting when i explain my query, it consist of a couple or's and and's


$mychat="EXPLAIN SELECT * from chat where type='chest'";
$mychat .=" OR type='auction' AND postedby='Admin'";
$mychat .=" OR type='mutes' AND reciever='$player'";
$mychat .=" OR type='tomutes' AND reciever='$player'";

$mychat .=" order by ID DESC LIMIT 50";
$mychat2=mysql_query($mychat) or die("Chat Error: 4052");
$mychat3=mysql_fetch_array($mychat2);


I have type and reciever and ID and postedby all indexed, theres 20,000 rows and its searching 5000 so its semi working...but i need to get that down even more...any ideas?

would putting the indexes on (type,postedby) and (type,receiver) maybe help?

would putting the indexes on (type,postedby) and (type,receiver) maybe help?
ha genious :P went from a couple thoughsand rows to a couple hundred thanks :)

OK smarty pants ;) Try this one on for size


SELECT
MAX(tblairports.fldResort) AS ap_destname,
IFNULL(tblairports.fldResort, tbloffers.fldDestination) AS dest_name,
fldDestinationAPC,
MAX(fldAPName),
MIN(fldPrice) AS price,
MAX(fldCountry) AS country,
fldBoardBasis, MAX(fldFlyingTime) AS fldFlyingTime,
COUNT(fldOfferID) AS offercount
FROM
tbloffers
RIGHT JOIN
tblClients
ON
tblClients.fldClientID=tbloffers.fldClientID
LEFT JOIN
tblairports
ON
fldDestinationAPC=fldAPCode
WHERE
NOT fldExclude
AND fldDepartureDate >= '2006-10-01'
AND fldDepartureDate <= '2006-10-11'
AND (tblairports.fldResort='Turkey' OR tblairports.fldCountry='Turkey' OR tbloffers.fldDestination='Turkey')
AND tbloffers.fldSearchTypes = 'all, med/europe/canaries'
GROUP BY
dest_name, fldBoardBasis
ORDER BY
price


id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tbloffers range fldClientID_2,fldClientID,fldSearchTypes,fldDepart... fldDepartureDate 3 NULL 253328 Using where; Using temporary; Using filesort
1 SIMPLE tblClients eq_ref PRIMARY PRIMARY 4 icelolly_dev.tbloffers.fldClientID 1 Using where
1 SIMPLE tblairports eq_ref PRIMARY PRIMARY 3 icelolly_dev.tbloffers.fldDestinationAPC 1 Using where

woah, that one might need a new thread :P you know how its in a table, does it display like that or you made it that way, ive been using print_r but tis a pain to read :)

Wrap print_r in <pre> tags and it makes it very readable.


print "<pre>\n";
print_r($blah);
print "</pre>\n";

Wrap print_r in <pre> tags and it makes it very readable.


print "<pre>\n";
print_r($blah);
print "</pre>\n";




ahhh of course thanks! *bows*