Databases
StackOverflow2013
Postsdbo
POSQL : How to reduce multiple time querying the same table ..Query Optimization
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL : How to reduce multiple time querying the same table ..Query Optimization
    primarykey
    data
    text
    <p>I have table which contain company list along with location ( location id as foreign key )</p> <p>and user will search for company with in the location in search box and I want return the companies which matches the location ( location id) and if none of the company exists with in that location I want to show the companies which are available at other location( near to asked location - locations are mapped as near in some manner in the different table based on GEO-SPACIAL Coordinates) </p> <p>so My SQL quires for this process is as below </p> <ol> <li>To get Location ID </li> </ol> <blockquote> <p>$loc_id= SELECT id FROM location WHERE location_name=$locationTerm</p> </blockquote> <ol> <li>Use that Location ID to get the companies in the Company Table</li> </ol> <blockquote> <p>$companies= SELECT * FROM companies WHERE location_id= $loc_id</p> </blockquote> <ol> <li><p>Then if the query returned are less than no of rows (say 3) ie $companies.count()&lt;3 then we need to look for nearby areas to raise the companies result. </p></li> <li><p>Get the nearby locations from the Table <strong>NearByLcoations</strong> (Many to Many) consists of near by locations pair </p></li> </ol> <blockquote> <p>$array_loc_ids= SELECT nearby_loc_id FROM NearbyLocations WHERE loc_id= $loc_id;</p> </blockquote> <ol> <li>Then again query the <strong>companies</strong> table for the array of nearby locations ids </li> </ol> <blockquote> <p>$companies= SELECT * FROM companies WHERE in($array_loc_ids);</p> </blockquote> <p>In this process I am hitting the Table Companies multiple times , Location table one time and on condition fail hitting the nearby location table .. Is there any SQL optimization possible to reduce the query time ... This is working for me but I think is very poor coding and method to achieve the result ... Any good suggestion to optimize the query ???</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USpanindra
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL : How to reduce multiple time querying the same table ..Query Optimization
      1. USpanindra
      1. VTFavorite
    1. CODo these queries really suffer from poor performance? There does not seem to be much room for optimization - whatever room there is does not seem worth the effort to pursue by reorganizing the SQL. I would instead concentrate on making sure good indexes support your queries.
      singulars
      1. POSQL : How to reduce multiple time querying the same table ..Query Optimization
      1. USDaniel B
    2. CO@DanielB currently we are dealing with small size of data ,we are not facing any problem but my worry is quering the TABLE two times in single process. I want to know is this the best method to achieve the result .
      singulars
      1. POSQL : How to reduce multiple time querying the same table ..Query Optimization
      1. USpanindra
    3. COIn this situation it is not bad to query the companies table twice since the queries are completely different and often only the first query need to be made. When optimizing you need to minimize the number of page reads performed in the database, not the number of table queries. You might be able to make a single query to perform the whole operation but I suspect this will perform worse than the two individual queries.
      singulars
      1. POSQL : How to reduce multiple time querying the same table ..Query Optimization
      1. USDaniel B
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload