0

Assume this general SQL query takes too long to execute. Which of the 4 options below can be done to improve its performance? Why? This is just for the understanding of SQL, not a dbms specific.

  1. CREATE INDEX IDX ON Vaccinations (COUNT(*), Site)

  2. Wrap the query in a view and ask users to query from the view.

  3. CREATE AGGREGATE INDEX ON Vaccinations(COUNT(*))

  4. CREATE INDEX IDX ON Vaccinations (Site)

SELECT Site, COUNT(*) AS Number of Vaccinations
FROM Vaccinations
GROUP BY Site;

A few rows from the Vaccinations table is shown here:

Table sample

Personally, I'm leaning towards 1. Since the count(*) is the only thing being used in the SELECT...FROM as a condition. However, I'm not entirely sure what the "aggregate index" option entails. Can someone explain when it's preferred over normal index? Thanks!

The Impaler
  • 45,731
  • 9
  • 39
  • 76
Charmander
  • 109
  • 14
  • 1
    Indexing is **highly vendor-specific** - so please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s May 16 '22 at 04:26
  • you can look at this https://stackoverflow.com/questions/50863962/speeding-up-queries-that-use-aggregate-functions-by-using-indexes – chatter May 16 '22 at 04:30
  • How about say, in oracle SQL? – Charmander May 16 '22 at 07:55
  • Run your horses. Get the execution plan with each options and compare. Having said that #4 looks like a decent candidate to read the entire table. – The Impaler May 16 '22 at 13:15

0 Answers0