Jump to content


Photo

Mysql SELECT speeds


  • Please log in to reply
3 replies to this topic

#1 algarve4me

algarve4me
  • Members
  • PipPip
  • Member
  • 16 posts
  • LocationPortugal

Posted 22 March 2006 - 03:18 PM

I normally use the Mysql query wildcard for searching my table. After recent modifications to my table, I am now concerned about the search speed and tying up the available memory.

Is there a general concensus on which method is the quickest to return the results from a table, assuming that the table has around 200 columns?

Does it make a difference depending upon the number of columns that are searched?

SELECT * FROM table WHERE a=b and c=d and e=f.

Would I be better off using

SELECT column1, column2, column3 FROM table WHERE a=b and c=d and e=f



#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 22 March 2006 - 03:33 PM

SELECT * is slower because it transfers data from all 200 columns back to your client. If you're not using all 200, you shouldn't transfer all 200.

If you ARE going to use all 200, SELECT * is fine.

Of course, make sure your WHERE conditions use indexes. That's the biggest issue.

#3 algarve4me

algarve4me
  • Members
  • PipPip
  • Member
  • 16 posts
  • LocationPortugal

Posted 22 March 2006 - 04:02 PM

Hi Wickning, thank you for your prompt reply.

If I use the query below, should I create an index on a4m?
If the column a4m is a primary, do I still need to create an index?
Should the columns be listed in the order that they appear in the table?

$result = mysql_query("select propertyname, propertytype, country, region, propertylocation from table where a4m='$a4m' ");

while($r=mysql_fetch_array($result)){
    $formpropertyname=$r["propertyname"];
    $formpropertytype=$r["propertytype"];
    $formcountry=$r["country"];
    $formregion=$r["region"];
    $formpropertylocation=$r["propertylocation"];
}


#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 22 March 2006 - 09:58 PM

You should definitely have an index on a4m, but if it is a PRIMARY KEY then that counts as an index.

You can list columns in any order you like, it won't make any difference.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users