New answers tagged

7

It is not possible to directly connect part of the query text (e.g. GROUP BY) with a specific operation in the final execution plan. You can write a query to find plans that: Contain a Hash Match Aggregate; and The query text contains a GROUP BY clause ...which is not quite the same thing, since this will find plans where the grouping logic was ...


3

Do the percentages and query cost even mean anything? Not really--They have meaning, but they're unreliable. are they referring to the estimated data from the query plan, and do not reflect the real cost? Otherwise how could the same join make up 39% of the "cost" for both, but one takes 2 seconds and one takes 47 seconds? Exactly--The cost is ...


1

ObjectId is a misnomer if it is not unique. You are saying that it takes 4 columns to uniquely identify a row? Rethink. This WHERE does not make sense; it seems like you are over-specifying the row by filtering on so many things, including a flags: WHERE ObjectId = @objectId AND ObjectType = @objectType AND IsDeleted = 0 AND ObjectIdName = ...


4

From the details you've provided it seems reasonable that the IX_VeryLarge non-clustered index would support both queries you've shown in your question. You have the Payload column typed as VARBINARY(MAX) - if you expect large objects to be stored in that column, I'd likely not INCLUDE it in the index since that will cause the index to be much much larger ...


1

(The comments below apply to MySQL; some may apply to other engines.) UUIDs slow things down because of their random nature. Don't use Unicode; use utf8. (Better yet, CHARACTER SET utf8mb4) InnoDB keeps index fragmentation low by design. Rule of Thumb: In a table with millions of rows, a "point query" via the PRIMARY KEY can be expected to take about as ...


1

Don't use MyISAM; use InnoDB. (to avoid "table locks") Build and maintain Summary tables. (often 10x faster) Let's see the slow queries; maybe they can be improved.


2

If all you want is the minimum distance, and not what city is at that distance, SELECT MIN( 6371 * acos( cos(radians(-60.61384878636903)) * cos(radians(st_x(location))) * cos(radians(st_y(location)) - radians(112.80061386895574)) + sin(radians(-60.61384878636903)) * sin(radians(st_x(location)))) ) AS MinDistance FROM ...


0

You can use the MIN function the following way. If you only need the distance and saves it in a table: SELECT MIN(distance) FROM table_name; Or not but you also need the city name SELECT city_name, MIN(distance_alias) FROM(SELECT city_name, SUM(distance) distance_alias FROM table_name GROUP BY city_name)derived_table_alias;



Top 50 recent answers are included