I have a table I&#39;m doing an ORDER BY on before a LIMIT and OFFSET in order to paginate.

Adding an index on the ORDER BY column makes a massive difference to performance (when used in combination with a small LIMIT). On a 500,000 row table, I saw a 10,000x improvement adding the index, as long as there was a small LIMIT.

However, the index has no impact for high OFFSETs (i.e. later pages in my pagination). This is understandable: a b-tree index makes it easy to iterate in order from the beginning but not to find the nth item.

It seems that what would help is a **counted b-tree index**, but I&#39;m not aware of support for these in PostgreSQL. Is there another solution? It seems that optimizing for large OFFSETs (especially in pagination use-cases) isn&#39;t that unusual.

Unfortunately, the PostgreSQL manual simply says &quot;The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient.&quot;


Improving OFFSET performance in PostgreSQL

I&#39;ve read on some blogs and in some articles related to optimization, how to optimize queries. I read I need to use indexes and make sure all my primary key and foreign keys are set correctly using a good relational database schema.

Now I have a query I need to optimize and I get this on the `EXPLAIN`:

    Using where; Using temporary; Using filesort

I am using MySQL 5.5

I know I am using `WHERE` but not with my temporary table nor filesort? What does this mean? 



MySQL explain Query understanding

I have a SQL table that all of a sudden cannot return data unless I include `with (nolock)` on the end, which indicates some kind of lock left on my table.  

I&#39;ve experimented a bit with [sys.dm_tran_locks][1] to identify that there are in fact a number of locks on the table, but how do I identify *what* is locking them (ie the request element of the [sys.dm_tran_locks][1])?

EDIT: I know about [sp_lock][2] for pre SQL 2005, but now that that sp is deprecated, AFAIK the right way to do this is with [sys.dm_tran_locks][1].  I&#39;m using SQL Server 2008 R2.


  [1]: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql?view=sql-server-2017
  [2]: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql?view=sql-server-2017

How to find out what is locking my tables?

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 distinct items from this table but the distinct is over two columns.

My query works fine but I was wondering if I can get the final result using just one query (without using a sub-query)

Counting DISTINCT over multiple columns

I have a table that has columns like this for example:

    id,col1,col2,col3,col4

Now, I want to check if `ANY` of `col1, col2, col3, col4` have the passed in value.

The long way to do it would be..

    SELECT * FROM table WHERE (col1 = 123 OR col2 = 123 OR col3 = 123 OR col4 = 123);

I guess it&#39;s the opposite version of `IN`.

Is there an easier way to do what I want?

Checking multiple columns for one value

While reading some SQL Tuning-related documentation, I found this:

`SELECT COUNT(*)` :

- Counts the number of rows.
- Often is improperly used to verify the existence of a record.

Is `SELECT COUNT(*)` really that bad?  

What&#39;s the proper way to verify the existence of a record?

SQL: How to properly check if a record exists

I&#39;ve been using indexes on my MySQL databases for a while now but never properly *learnt* about them. Generally I put an index on any fields that I will be searching or selecting using a `WHERE` clause but sometimes it doesn&#39;t seem so black and white.

**What are the best practices for MySQL indexes?**

Example situations/dilemmas:

* If a table has six columns and all of them are searchable, should I index all of them or none of them?

* What are the negative performance impacts of indexing?

* If I have a VARCHAR 2500 column which is searchable from parts of my site, should I index it?



MySQL indexes - what are the best practices?

Are JOIN queries faster than several queries? (You run your main query, and then you run many other SELECTs based on the results from your main query)

I&#39;m asking because JOINing them would complicate A LOT the design of my application

If they are faster, can anyone approximate very roughly by how much? If it&#39;s 1.5x I don&#39;t care, but if it&#39;s 10x I guess I do.

JOIN queries vs multiple queries

I have been seeing quite a large variation in response times regarding `LIKE` queries to a particular table in my database. Sometimes I will get results within 200-400 ms (very acceptable) but other times it might take as much as 30 seconds to return results.

I understand that `LIKE` queries are very resource intensive but I just don&#39;t understand why there would be such a large difference in response times. I have built a btree index on the `owner1` field but I don&#39;t think it helps with `LIKE` queries. Anyone have any ideas?

Sample SQL:

    SELECT gid, owner1 FORM parcels
    WHERE owner1 ILIKE &#39;%someones name%&#39; LIMIT 10

I&#39;ve also tried:

    SELECT gid, owner1 FROM parcels
    WHERE lower(owner1) LIKE lower(&#39;%someones name%&#39;) LIMIT 10

And:

    SELECT gid, owner1 FROM parcels
    WHERE lower(owner1) LIKE lower(&#39;someones name%&#39;) LIMIT 10

With similar results.  
Table Row Count: about 95,000.

PostgreSQL LIKE query performance variations

I&#39;m dealing with a Postgres table (called &quot;lives&quot;) that contains records with columns for time\_stamp, usr\_id, transaction\_id, and lives\_remaining. I need a query that will give me the most recent lives\_remaining total for each usr\_id&lt;br&gt;

1. There are multiple users (distinct usr_id&#39;s)&lt;br&gt;
2. time\_stamp is not a unique identifier: sometimes user events (one by row in the table) will occur with the same time\_stamp.&lt;br&gt;
3. trans\_id is unique only for very small time ranges: over time it repeats&lt;br&gt;
4. remaining\_lives (for a given user) can both increase and decrease over time

example:
&lt;pre&gt;time_stamp|lives_remaining|usr_id|trans_id
-----------------------------------------
  07:00  |       1       |   1  |   1    
  09:00  |       4       |   2  |   2    
  10:00  |       2       |   3  |   3    
  10:00  |       1       |   2  |   4    
  11:00  |       4       |   1  |   5    
  11:00  |       3       |   1  |   6    
  13:00  |       3       |   3  |   1    
&lt;/pre&gt;

As I will need to access other columns of the row with the latest data for each given usr\_id, I need a query that gives a result like this:
&lt;pre&gt;time_stamp|lives_remaining|usr_id|trans_id
-----------------------------------------
  11:00  |       3       |   1  |   6    
  10:00  |       1       |   2  |   4    
  13:00  |       3       |   3  |   1    
&lt;/pre&gt;

As mentioned, each usr_id can gain or lose lives, and sometimes these timestamped events occur so close together that they have the same timestamp! Therefore this query won&#39;t work:

    SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM 
          (SELECT usr_id, max(time_stamp) AS max_timestamp 
           FROM lives GROUP BY usr_id ORDER BY usr_id) a 
    JOIN lives b ON a.max_timestamp = b.time_stamp

Instead, I need to use both time\_stamp (first) and trans\_id (second) to identify the correct row. I also then need to pass that information from the subquery to the main query that will provide the data for the other columns of the appropriate rows. This is the hacked up query that I&#39;ve gotten to work:

    SELECT b.time_stamp,b.lives_remaining,b.usr_id,b.trans_id FROM 
          (SELECT usr_id, max(time_stamp || &#39;*&#39; || trans_id) 
           AS max_timestamp_transid
           FROM lives GROUP BY usr_id ORDER BY usr_id) a 
    JOIN lives b ON a.max_timestamp_transid = b.time_stamp || &#39;*&#39; || b.trans_id 
    ORDER BY b.usr_id

Okay, so this works, but I don&#39;t like it. It requires a query within a query, a self join, and it seems to me that it could be much simpler by grabbing the row that MAX found to have the largest timestamp and trans_id. The table &quot;lives&quot; has tens of millions of rows to parse, so I&#39;d like this query to be as fast and efficient as possible. I&#39;m new to RDBM and Postgres in particular, so I know that I need to make effective use of the proper indexes. I&#39;m a bit lost on how to optimize.

I found a similar discussion &lt;a href=&quot;https://stackoverflow.com/questions/121387/sql-fetch-the-row-which-has-the-max-value-for-a-column&quot;&gt;here&lt;/a&gt;. Can I perform some type of Postgres equivalent to an Oracle analytic function?

Any advice on accessing related column information used by an aggregate function (like MAX), creating indexes, and creating better queries would be much appreciated!

P.S. You can use the following to create my example case:

    create TABLE lives (time_stamp timestamp, lives_remaining integer, 
                        usr_id integer, trans_id integer);
    insert into lives values (&#39;2000-01-01 07:00&#39;, 1, 1, 1);
    insert into lives values (&#39;2000-01-01 09:00&#39;, 4, 2, 2);
    insert into lives values (&#39;2000-01-01 10:00&#39;, 2, 3, 3);
    insert into lives values (&#39;2000-01-01 10:00&#39;, 1, 2, 4);
    insert into lives values (&#39;2000-01-01 11:00&#39;, 4, 1, 5);
    insert into lives values (&#39;2000-01-01 11:00&#39;, 3, 1, 6);
    insert into lives values (&#39;2000-01-01 13:00&#39;, 3, 3, 1);

PostgreSQL - fetch the rows which have the Max value for a column in each GROUP BY group

To quote the [docs][1]:

&gt; When creating an index, the number associated with a key specifies the
&gt; direction of the index, so it should always be 1 (ascending) or -1
&gt; (descending). Direction doesn&#39;t matter for single key indexes or for
&gt; random access retrieval but is important if you are doing sorts or
&gt; range queries on compound indexes.

However, I see no reason why direction of the index should matter on compound indexes. Can someone please provide a further explanation (or an example)?

  [1]: http://www.mongodb.org/display/DOCS/Indexes

Why does direction of index matter in MongoDB?

I got a following table:


    col1 | col2 | col3
    -----+------+-------
    1    | a    | 5
    5    | d    | 3
    3    | k    | 7
    6    | o    | 2
    2    | 0    | 8

If a user searches for &quot;1&quot;, the program will look at the `col1` that has &quot;1&quot; then it will get a value in `col3` &quot;5&quot;, then the program will continue to search for &quot;5&quot; in `col1` and it will get &quot;3&quot; in `col3`, and so on. So it will print out:

    1   | a   | 5
    5   | d   | 3
    3   | k   | 7

If a user search for &quot;6&quot;, it will print out:

    6   | o   | 2
    2   | 0   | 8

How to build a `SELECT` query to do that?


How to do the Recursive SELECT query in MySQL?

I have this cross-database query...

    SELECT
                `DM_Server`.`Jobs`.*,
                `DM_Server`.servers.Description AS server,
                digital_inventory.params,
                products.products_id,
                products.products_pdfupload,
                customers.customers_firstname,
                customers.customers_lastname
            FROM `DM_Server`.`Jobs`
            INNER JOIN `DM_Server`.servers ON servers.ServerID = Jobs.Jobs_ServerID
            JOIN `cpod_live`.`digital_inventory` ON digital_inventory.jobname = Jobs.Jobs_Name
            JOIN `cpod_live`.`products` ON products.products_pdfupload = CONCAT(digital_inventory.jobname, &quot;.pdf&quot;)
            JOIN `cpod_live`.`customers` ON customers.customers_id = products.cID
            ORDER BY `DM_Server`.`Jobs`.Jobs_StartTime DESC LIMIT 50

it runs fine until I make them `LEFT JOIN`s.  I thought that by not specifying a type of join it was assumed to be a `LEFT JOIN`.  Is this not the case?

Difference in MySQL JOIN vs LEFT JOIN

I have a table with index (autoincrement) and integer value. The table is millions of rows long.

How can I search if a certain number appear in the last n rows of the table most efficiently?






mysql select from n last rows

I have a database with hundreds of awkwardly named tables in it (CG001T, GH066L, etc), and I have views on every one with its &quot;friendly&quot; name (the view &quot;CUSTOMERS&quot; is &quot;SELECT * FROM GG120T&quot;, for example). I want to add &quot;WITH SCHEMABINDING&quot; to my views so that I can have some of the advantages associated with it, like being able to index the view, since a handful of views have computed columns that are expensive to compute on the fly.

**Are there downsides to SCHEMABINDING these views?** I&#39;ve found some articles that vaguely allude to the downsides, but never go into them in detail. I know that once a view is schemabound, you can&#39;t alter anything that would impact the view (for example, a column datatype or collation) without first dropping the view, so that&#39;s one, but aside from that? It seems that the ability to index the view itself would far outweigh the downside of planning your schema modifications more carefully.

Downsides to &quot;WITH SCHEMABINDING&quot; in SQL Server?

In output of explain command I found two terms &#39;Seq Scan&#39; and &#39;Bitmap heap Scan&#39;. Can somebody tell me what is the difference between these two types of scan? (I am using PostgreSql)

What is the difference between Seq Scan and Bitmap heap scan in postgres?

My queries get very slow when I add a `limit 1`.
  
I have a table `object_values`  with timestamped values for objects:

     timestamp |  objectID |  value
    --------------------------------
     2014-01-27|       234 | ksghdf

Per object I want to get the latest value:

    SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC LIMIT 1;
(I cancelled the query after more than 10 minutes)

This query is very slow when there are no values for a given objectID (it is fast if there are results).
If I remove the limit it tells me nearly instantaneous that there are no results:

    SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC;  
    ...  
    Time: 0.463 ms

An explain shows me that the query without limit uses the index, where as the query with `limit 1` does not make use of the index:

**Slow query:**


    explain SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC limit 1;  
    QUERY PLAN`
    ----------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=0.00..2350.44 rows=1 width=126)
    -&gt;  Index Scan Backward using object_values_timestamp on object_values  (cost=0.00..3995743.59 rows=1700 width=126)
         Filter: (objectID = 53708)`

**Fast query:**

    explain SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC;
                                                      QUERY PLAN
    --------------------------------------------------------------------------------------------------------------
     Sort  (cost=6540.86..6545.11 rows=1700 width=126)
       Sort Key: timestamp
       -&gt;  Index Scan using object_values_objectID on working_hours_t  (cost=0.00..6449.65 rows=1700 width=126)
             Index Cond: (objectID = 53708)

The table contains 44,884,559 rows and 66,762 distinct objectIDs.  
I have separate indexes on both fields: `timestamp` and `objectID`.  
I have done a `vacuum analyze` on the table and I have reindexed the table.



Additionally the slow query becomes fast when I set the limit to 3 or higher:

    explain SELECT * FROM object_values WHERE (objectID = 53708) ORDER BY timestamp DESC limit 3;
                                                         QUERY PLAN
    --------------------------------------------------------------------------------------------------------------------
     Limit  (cost=6471.62..6471.63 rows=3 width=126)
       -&gt;  Sort  (cost=6471.62..6475.87 rows=1700 width=126)
             Sort Key: timestamp
             -&gt;  Index Scan using object_values_objectID on object_values  (cost=0.00..6449.65 rows=1700 width=126)
                   Index Cond: (objectID = 53708)

In general I assume it has to do with the planner making wrong assumptions about the exectution costs and therefore chooses for a slower execution plan.

Is this the real reason? Is there a solution for this?

All Query Optimization Solutions on Gang of Coders

Total of 17 Query Optimization Solutions