I've this classifieds website, and that i have about 7 tables in MySql where all information is saved. I've one primary table, known as "classifieds".

Within the classifieds table, there's a column known as classified_id. This isn't the PK, or perhaps a key whatsoever. It's really a number which is often used that i can 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 related together through the classified_id column.

How to the Q, I personally use this process to fetch all records In which the column ad_id matches the values in a array, contacted this situation $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 good or would this really fetch unnecessary information?

Read this Q I published day or two ago. Within the comments HLGEM is leaving comments that it's wrong and so forth. What is your opinion?

http://stackoverflow.com/questions/2782275/another-rookie-question-how-to-implement-count-here

Thanks

Strongly disagree with marr75. First becasue should you choose this poor techinie in much of your queries, you're adding unnecessary load to just about any query. Database queries have to be written too enhanced s possible because it is is quite painful to later go bnack and rewrite every query inside your datbase becasue you used a recognized poor techinique. Refactoring in datbases is difficult and gratifaction should be considered in design, it's not premature optimaization to make use of techiniqes that are recognized to improve performance from the beginning, it's good design.

Next, you will find the maintenance problem. If you're depending onthese posts finding yourself in a specific order and someone changes the dwelling from the databe you're from liuck. Alos, if a person adds a column that you won't want to show the consumer (That is is typical) you're at a complete loss. This can be a very bad techinique and choose * should rarely be utilized ina production system. If a person adds a column, it will likely be came back inthe query but you should know that which was added and why to be able to result in the interface do what it must do anyway, so you've no maintenance savings applying this poor technique.

You're surely coming back unnecessary results, to reply to your question.

It's a bad habit to get involved with.

Random Queries

They are queries that you simply email run once, or on rare occasions.

What size of the result data set should you return it would take more time to perform a SELECT * than type the column names?

How likely are you currently to forget a column, add it, and also have to operate it again?

Your time and effort is much more costly than CPU time. If you are running it once, allow the database perform the work. SELECT * is okay for random queries if you will save time.

You will find exceptions, for example Blob fields on large data sets, however, you understand.

Production Queries

They are queries which are saved inside your application or database. These queries are run frequently.

The number of occasions is it necessary to operate a query to replace with time it might decide to try title your posts? It accumulates fast.

Title your posts being produced queries to permit the application to scale better and perform at maximum efficiency. You will find other minor advantages, but they are less exciting.

Summary

  • Add Hoc Queries : SELECT * generally okay.
  • Production Queries: SELECT * always bad.
  • It's okay to become a little lazy, but be wise about this.