...

View Full Version : optimization frustration!



jeromepelletier
09-04-2006, 07:26 PM
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?

GJay
09-04-2006, 08:05 PM
would putting the indexes on (type,postedby) and (type,receiver) maybe help?

jeromepelletier
09-04-2006, 11:52 PM
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 :)

NancyJ
09-05-2006, 12:48 PM
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

jeromepelletier
09-07-2006, 01:13 PM
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 :)

Fumigator
09-07-2006, 04:42 PM
Wrap print_r in <pre> tags and it makes it very readable.



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

jeromepelletier
09-10-2006, 12:52 AM
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*



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum