[SOLVED] MySQL optimization question

[cooldude832]

I have a script that is to search a DB for matching critera.  It works, but I think its running slower than it should be.

 

The first query I run is:

$query = "SELECT * FROM ".$table." ".$critera." ".$orderby; 

that works to get me all matching results.

 

My question is what is stored in that query.  If it matches say 5 (row a,b,c,d,e) and i want to use the results of rows a,b,c later down to populate the matching results.  Can i Do this with out requerying?

[cmgmyr]

You can do this:

 

$select = "a, b, c";

$query = "SELECT ". $select ." FROM ".$table." ".$critera." ".$orderby; 

 

So it doesn't include anything that you don't need. You can also use LIMIT to cut it down more.

[neel_basu]

My question is what is stored in that query

Its a selct query it is just to retrive values from the tables. It doesn't stores anything.

[cooldude832]

the problem with select a,b,c is that i don't know a,b,c, till i do the query it tells me what a,b,c are

[cooldude832]

okay what does:

mysql_query ($query);

 

contain in it?

[cmgmyr]

mysql_query ($query); contains all of the rows and columns in the query.

 

so if you do * and have 5 rows and 5 columns it will take all of them. If you only query 1 column it will only take that 1 column with 5 rows.

[cooldude832]

hmm that gives me an idea,  I think my issue is that because my table has 150+ fields and i'm saying select * when all i need is IDs I can say select ID from $table and cut my results down from 150 to 1 * matching results

 

[cooldude832]

$query = "SELECT 'ID' FROM ".$table." ".$critera." ".$orderby;

 

$result=mysql_query ($query); //Return ofthe Number of Matching Results

 

Didnt' work

but later on I say:

do
{
          $adid_array[$i] = $row['ID'];
	  $i++;
    }
  while( $row= mysql_fetch_array($result));

 

any idea?

[cmgmyr]

$query = "SELECT 'ID' FROM ".$table." ".$critera." ".$orderby; 
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
$id = $row['ID'];

echo "The id is: ".$id."<br>";
}

[cooldude832]

Think i got it no single quotes on ID worked and wow its improving thanks

 

[cmgmyr]

yeah, sorry it should just be

$query = "SELECT `ID` FROM ".$table." ".$critera." ".$orderby; 

[cooldude832]

yeah i think you initially misunderstood me a,b,c are the actual rows i need out of the matching rows a-e so select a,b,c would not work but anyways its a big improvement for now.  It could use a second optimization, but for now and its current function its good.