Mysql SELECT speeds

[algarve4me]

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

[wickning1]

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.

[algarve4me]

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?

[code]
$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"];
}
[/code]

[wickning1]

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.