MySQL Speed Performance

[nyah_RMC]

dear all,

We're doing a database search where we have 9 tables. If we execute a query (1 table) it runs fast, only if the record is less than a hundred....when we execute a much complex query like

"select * from tbl1, tbl2 where $select Like $var And tbl1.Name Like tbl2.Name"....well the operation timed out!

is there a way to speed up my query performance...we've also done some indexing here but still not working....and in the future we'll be putting millions of records to it..!!!!

any suggestions will be appreciated!

[phpman]

if it times out then there might be something wrong with the query. generally it is not that slow. you could also set the time

set_time_limit (0); //some large files can take a while

[nyah_RMC]

thanks..ill keep it in mind..but sometimes it cant be done easily..

and also, the fact that MySQL has many limitations when it comes to query statements, i might as well analyze and research about its strength and downfalls!...way to go!...

query limitations includes:
1. can join tables but with two queries...it cant use the SQL statement JOIN..
2. it cant support sub queries (select * from [select * blah..])..
and more....cant enumerate it all here!

and also im having problem with the indexing and the cardinality..anyway, what's the relationship between this two???

[phpman]

umm I don't know what you are talking aobut. mysql will do JOIN and it will do sub selects. I beleive the sub selects are in version 3.33 or 4.0 can't remember which one. there was a thread on here that it was talked about.

[phpman]

JOIN

and

subSelects


don't look to hard. and it must be something you are doing becasue I don't have those problems, even on a 80,000 row rable.


as far as indexing, it can only index varchar and int fields I beleive.

Indexing

[nyah_RMC]

but these functionalities are available only on newer versions like 4.0 onwards...they have available versions but we're waiting for the beta version. ....nyways, thanks for ur help dude!

my real problem is the full text indexing..in my query, for example..i want to search for the word "coconut methyl ester"...when i execute this query, it returned all records containing coconut...not the exact record i want..and sometimes when i try to search the exact word...it didnt return a recordset. ...that's the problem im trying to look for answer!!!....but anyways, thank u again for ur time!

[phpman]

well the onl thing that is not in the early version is the subselects

but the rest goes far back as the version I have, 3.22 so you should be fine.

what exactly are you doing, what is your code you use to find "cocunut"

[nyah_RMC]

here's my code dude!

$strField = "tblHoldings.".$select;  
$strCriteria ="'".$bsearch."'";
if ($select=="Title" || $select=="PublicationDate" || $select=="ISSN" || $select=="ISBN"){
    $strselect="Select tblHoldings.HoldingsID,tblHoldingsAuthorName.HoldingsID,tblHoldings.Title,tblHoldings.PublicationDate, 
               tblHoldings.AgencyCode,tblHoldingsAuthorName.AuthorName,tblHoldings.MaterialType
           from tblHoldings,tblHoldingsAuthorName 
               Where MATCH($strField) Against($strCriteria)";
    $strcompare=" And tblHoldings.HoldingsID Like tblHoldingsAuthorName.HoldingsID";
    $strOrder = "Order by tblHoldings.Title";

}elseif ($select=="AuthorName"){
    $strselect="Select tblHoldings.HoldingsID,tblHoldingsAuthorName.HoldingsID,
               tblHoldings.Title,tblHoldings.PublicationDate, tblHoldings.AgencyCode, 
           tblHoldingsAuthorName.AuthorName,tblHoldings.MaterialType
           from tblHoldings, tblHoldingsAuthorName Where MATCH(tblHoldingsAuthorName.AuthorName)AGAINST($strCriteria)";
    $strcompare=" And tblHoldings.HoldingsID Like tblHoldingsAuthorName.HoldingsID";
    $strOrder =" Order by tblHoldings.Title";  
}elseif ($select=="PublisherName"){
   $strselect = "Select tblHoldings.HoldingsID,tblHoldingsPublisherName.HoldingsID,
                tblHoldingsPublisherName.PublisherName,tblHoldings.Title, 
                tblHoldings.PublicationDate, tblHoldings.AgencyCode,
                 tblHoldingsAuthorName.AuthorName,tblHoldings.MaterialType
                 FROM tblHoldingsPublisherName,tblHoldings,tblHoldingsAuthorName
                 Where Match(tblHoldingsPublisherName.PublisherName) Against($strCriteria)"; 
   $strcompare = " AND tblHoldings.HoldingsID Like tblHoldingsPublisherName.HoldingsID AND 
                  tblHoldings.HoldingsID Like tblHoldingsAuthorName.HoldingsID"; 
   $strOrder =" Order by tblHoldings.Title"; 
}elseif ($select=="SubjectHeadings"){ 
    $strselect="Select tblHoldings.HoldingsID, tblHoldingsSubject.HoldingsID,tblHoldingsAuthorName.HoldingsID,
               tblHoldings.Title,tblHoldings.PublicationDate,tblHoldings.AgencyCode,
               tblHoldingsAuthorName.AuthorName,tblHoldings.MaterialType 
               from tblHoldings,tblHoldingsSubject,tblHoldingsAuthorName
               where Match(tblHoldingsSubject.SubjectHeadings) Against($strCriteria)";
    $strcompare=" And tblHoldings.HoldingsID Like tblHoldingsSubject.HoldingsID And tblHoldingsAuthorName.HoldingsID Like 
                tblHoldingsSubject.HoldingsID";
    $strOrder=" Order by tblHoldings.Title"; 
}   
  
   foreach ($check as $key=>$value) {
                   $str .= "tblHoldings.AgencyCode Like " . "'".$key. "'"." Or ";
                   }
               $strfinal = substr($str, 0, -4);            
            
        $strquery2= " And tblHoldings.MaterialType Like ";
            if ($selectMaterial=="all" || $selectMaterial=="selected"){
              $strquery3= "";
            } elseif ($selectMaterial=="book"){
              $strquery3= $strquery2 . "'book'";
            } elseif ($selectMaterial=="index"){
              $strquery3= $strquery2 . "'index'";
            } elseif ($selectMaterial=="serials"){
              $strquery3= $strquery2 . "'serials'"; 
            } elseif ($selectMaterial=="nonprint"){
              $strquery3= $strquery2 . "'nonprint'"; 
            } elseif ($selectMaterial=="thesis"){
              $strquery3= $strquery2 . "'thesis'";
            } elseif ($selectMaterial=="others"){
              $strquery3= $strquery2 . "'others'";        
            } 
        
$strSQL =  $strselect ." AND "."(".$strfinal.")".$strquery3.$strcompare. $strOrder;        
$result= mysql_query ("$strselect And ($strfinal) $strquery3 $strcompare $strOrder"); 

[phpman]

that is becasue you are using MATCH instead of LIKE. if you use LIKE and then you can use the %name% it will find it all. match is like equal I believe.