Does this request get unnecessary information? Should I change the request?

Question

I have this site for ads, and I have about 7 tables in MySql where all the data is stored. I have one main table called "ads".

The declaration table has a column called class_id. This is not a PC or key at all. This is just a number that is used to share table entries.

Example:

classifieds table: fordon table: id => 33 id => 12 classified_id => 10 classified_id => 10 ad_id => 'bmw_m3_92923' 

This is linked above using the classic_id column.

Now in Q, I use this method to retrieve all the records. The WHERE column ad_id matches any of the values ​​inside the 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 will it really lead to unnecessary information?

Check out this Q that I posted a couple of days ago. In the comments, HLGEM comments that this is wrong, etc. What do you think?

Another rookie; How to implement Count () here?

thanks

Answer 1

You are sure to return unnecessary results to answer your question.

It is a bad habit to join.

Answer 2

I completely disagree with marr75. Firstly, if you do this poor maintenance in most of your requests, you add unnecessary workload for almost every request. Database queries should be written as optimally as possible, as it is extremely painful to switch to bnack later and rewrite each query in your database because you used a well-known technical technique. Database refactoring is complex, and performance should be considered in the design, premature optimization of the use of technologies that are known to increase productivity from the very beginning, this is a good design.

Then you have a maintenance problem. If you depend on these columns in a specific order and someone changes the structure of the database that you are not using. Alos, if someone adds a column that you don't want to show to the user (which is shared), you're out of luck. This is a very bad technique, and choosing * should almost never be used in a production system. If someone adds a column, it will be returned in the request, but you need to know what was added, and why, in order to make the interface do everything that it needs, so you don’t have any cost savings when using this bad technique.

Answer 3

Special Requests

These are the requests that you write to run once or in rare cases.

How large is the result data array, will it take you longer to do SELECT * than enter the column names?

How likely are you to forget the column, add it and run it again?

Your time is more expensive than processor time. If you run it once, let the database do the job. SELECT * is suitable for special queries if it saves you time.

There are exceptions, such as Blob fields on large datasets, but you get the point.

Production Inquiries

These are queries that are stored in an application or database. These requests are often executed.

How many times do you need to run a query to compensate for the time it takes to name your columns? It folds fast.

Name your columns in production queries so your application can scale and run with maximum efficiency. There are other minor advantages, but they are not so exciting.

Summary

• Add Hoc Requests: SELECT * usually ok.
• Production queries: SELECT * always bad.
• Good to be a little lazy, but be smart.

Answer 4

This is a matter of opinion. Do you have performance or scaling issues? If not, then the specific question of which columns to return is probably a matter of premature optimization. Duplicating integer join columns is not going to interrupt the bandwidth bank any time soon.