[Solved] SQL Query to find highest rated
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

SQL Query to find highest rated

SQL Query:

Aim:

Write a SQL query to retrieve the address of highest rated Phills Coffee in United States addressing format.

id   name         house   street          city          state  zip country rating
1   Best Buy        34    Main St        Carson           CA  98064 USA      9
2   Phills Coffee  4568   Sepulveda Blvd Torrance         CA  50833 USA      6
3   Starbucks        3    Ocean Blvd     Long Beach       WA  45093 USA      9
4   Phills Coffee   214   Carson St      Huntington Beach PA  89435 USA      4

US Addressing Format (For people outside USA):

http://bitboost.com/ref/international-address-formats/united_states/

My attempt:

SELECT house, street, city,
       state,country,zip 
  FROM table 
 WHERE name="Phills Coffee" 
 ORDER BY rating DESC LIMIT 1

Am I doing wrong? Or How can I improve this query?

Thanks,

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `table` ADD INDEX `table_idx_name_rating` (`name`,`rating`);
ALTER TABLE `table` ADD INDEX `table_idx_rating` (`rating`);
The optimized query:
SELECT
        table.house,
        table.street,
        table.city,
        table.state,
        table.country,
        table.zip 
    FROM
        table 
    WHERE
        table.name = 'Phills Coffee' 
    ORDER BY
        table.rating DESC LIMIT 1

Related Articles



* original question posted on StackOverflow here.