Jump to content

Archived

This topic is now archived and is closed to further replies.

algarve4me

Mysql SELECT speeds

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.