I have this classifieds website, and I have about 7 tables in MySql where all data is stored. I have one main table, called "classifieds".
In the classifieds table, there is a column called classified_id. This is not the PK, or a key whatsoever. It is just a number which is used for me to JOIN table records together.
Ex:
classifieds table: fordon table:
id => 33 id => 12
classified_id => 10 classified_id => 10
ad_id => 'bmw_m3_92923'
This above is linked together by the classified_id column.
Now to the Q, I use this method to fetch all records WHERE the column ad_id matches any of the values inside an array, called in this case $ad_arr:
SELECT mt.*, fordon.*, boende.*, elektronik.*, business.*, hem_inredning.*, hobby.*
FROM classified mt
LEFT JOIN fordon ON fordon.classified_id = mt.classified_id
LEFT JOIN boende ON boende.classified_id = mt.classified_id
LEFT JOIN elektronik ON elektronik.classified_id = mt.classified_id
LEFT JOIN business ON business.classified_id = mt.classified_id
LEFT JOIN hem_inredning ON hem_inredning.classified_id = mt.classified_id
LEFT JOIN hobby ON hobby.classified_id = mt.classified_id
WHERE mt.ad_id IN ('$ad_arr')";
Is this good or would this actually fetch unnecessary information?
Check out this Q I posted couple of days ago. In the comments HLGEM is commenting that it is wrong etc etc. What do you think?
Another rookie question; How to implement Count() here?
Thanks
You are surely returning unnecessary results, to answer your question.
It is a bad habit to get into.
This is a matter of opinion. Are you having performance or scaling issues? If not, then being specific about which columns to return is probably a matter of premature optimization. Duplication of integer join columns isn't going to break the bandwidth bank any time soon.
Strongly disagree with marr75. First becasue if you do this poor techinie in most of your queries, you are adding unnecessary load to virtually every query. Database queries need to be written as well optimized s possible as it is is exceedingly painful to later go bnack and rewrite every query in your datbase becasue you used a known poor techinique. Refactoring in datbases is hard and performance must be considered in design, it is not premature optimaization to use techiniqes that are known to improve performance from the start, it is good design.
Next, you have the maintenance issue. If you are depending onthese columns being in a particular order and someone changes the structure of the databe you are out of liuck. Alos, if someone adds a column that you don't want to show the user (Which is is common) you are out of luck. THis is a very bad techinique and select * should almost never be used ina production system. If someone adds a column, it will be returned inthe query but you need to know what was added and why in order to make the interface do what it needs to do anyway, so you have no maintenance savings by using this poor technique.
Ad Hoc Queries
These are queries that you write to run one time, or on rare occasions.
How large of a result data set must you return that it would take longer to do a SELECT * than type out the column names?
How likely are you to forget a column, add it, and have to run it again?
Your time is more expensive than CPU time. If you're running it once, let the database do the work. SELECT * is fine for ad hoc queries if it will save you time.
There are exceptions, such as Blob fields on large data sets, but you get the point.
Production Queries
These are queries that are stored in your application or database. These queries are run often.
How many times do you have to run a query to make up for the time it would take to name your columns? It adds up fast.
Name your columns in production queries to allow your application to scale better and perform at maximum efficiency. There are other minor advantages, but they're not as exciting.
Summary
SELECT * generally okay.SELECT * always bad.