This is a known question but the best solution I’ve found is something like: SELECT TOP N * FROM MyTable ORDER BY Id DESC I’ve a table with lots of rows. It is not a posibility to use that query …
Tag: performance
Statistics on Query Time (PostgreSQL)
I have a table with a billion rows and I would like to determine the average time and standard deviation of time for several queries of the form: I have a thousand random values for col1 stored in another table. Is there some way to store how long each of these queries took (in milliseconds) in a separate tab…
How to find Expr#### in Execution Plan
When looking at the actual execution plan for a query in SQL Server Management Studio (SSMS), how do I determine what an expression such as Expr1052 represents? When I identify the costly parts of the query and look at the properties of that operation, there are often references only to these Expressions, or …
Two single-column indexes vs one two-column index in MySQL?
I’m faced with the following and I’m not sure what’s best practice. Consider the following table (which will get large): id PK | giver_id FK | recipient_id FK | date I’m using InnoDB and from what I understand, it creates indices automatically for the two foreign key columns. However, …
Meaning of “Select tables optimized away” in MySQL Explain plan
What is the meaning of Select tables optimized away in MySQL Explain plan? explain select count(comment_count) from wp_posts; +—-+————-+—————————+———————…
Best way to test if a row exists in a MySQL table
I’m trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE … and check to see if the total is non-zero or is …
How to count occurrences of a column value efficiently in SQL?
I have a table of students: I want to query for all students, and an additional column that counts how many students are of the same age: What’s the most efficient way of doing this? I fear that a sub-query will be slow, and I’m wondering if there’s a better way. Is there? Answer This should…
Counting DISTINCT over multiple columns
Is there a better way of doing a query like this: SELECT COUNT(*) FROM (SELECT DISTINCT DocumentId, DocumentSessionId FROM DocumentOutputItems) AS internalQuery I need to count the number of …
How to Change All Sql Columns of One DataType into Another
I have a database (Sql Server 2005) where there are dozens of tables, each of which has a number of columns (on average 10-20) with datatype set to nvarchar(max). This is absolutely killing …
Condition within JOIN or WHERE
Is there any difference (performance, best-practice, etc…) between putting a condition in the JOIN clause vs. the WHERE clause? For example… Which do you prefer (and perhaps why)? Answer The relational algebra allows interchangeability of the predicates in the WHERE clause and the INNER JOIN, so e…