Tag Info

New answers tagged

3

For the simple case, GREATEST() on sub-selects may perform best: SELECT GREATEST((SELECT userid FROM guestuser ORDER BY userid DESC LIMIT 1) ,(SELECT userid FROM olduser ORDER BY userid DESC LIMIT 1)) AS userid; If you want to change query using max function: SELECT GREATEST((SELECT max(userid) FROM guestuser) ,(SELECT ...


3

Try it this way: select userid from ( (select userid from guestuser order by userid DESC LIMIT 1) union all (select userid from olduser ORDER BY userid DESC LIMIT 1) ) sq order by userid DESC LIMIT 1;


1

In addition to @tombom's suggestions, creating an index on (user_id, post_id) instead of (or in addition, but the less indexes the better) separate indexes on user_id and post_id will simplify the query, probably getting rid of the filesort and temporary table, plus giving you the benefits of a covering index. This will probably lower the query execution ...


0

I did some testing last night related to this problem. My test on Sample table (2044398 record, 95% fragmentation) shows that 10,000 rows are returned in about 6s. After I rebuilded clustered index (fragmentation 0.35%) query was executed in 2.5s. Next, I have added 10000 records to that same table and again fragmentation was 70.33 %. Finally, same query ...


1

you can do that but it needs some trickery. imagine you got this: select ... from a, b, c, d, e ... this type of query is always reordered. but if you do ... select ... from a JOIN b ... JOIN c JOIN d JOIN c ... then PostgreSQL will only reorder join_collapse_limit tables. you can reduce this variable to a low value to force PostgreSQL into your order. ...


0

Good suggestion Dave, but wouldn't that give Nihad problems with keeping the ID's unique between his Nodes: I.e. Node one uses ID 101, Node 2 uses ID 101 before it has replicated transaction from Node one from the Shipped Transaction log (depends on setup, but in the peer to peer situation I described, it was extremely busy at all Nodes as the Client app ...


0

Instead of simply eschewing GUIDs as your clustering key, why not change how you're assigning them? Are you using NEWID() to generate your key? If so, use NEWSEQUENTIALID() and you'll likely do a lot better on fragmentation.


1

In a previous role at a different Organisation to where I am now employed, we faced a similar decision whereby the GUID issue (as pointed out in the link) was a factor for us (both Width and poor choice regarding Index fragmentation) - but we needed to move to Transactional Replication (Peer-to-Peer) to allow our Sites to be Geographically separated. In our ...


0

With the assumption of no timesheet entry spanning midnight, I have added a column person_date varchar(30) not null This is being calculated overnight with update activity set person_date = concat(person_id , '_',date(actual_start) ) where person_date=''; I have also simplified the inside query to: SELECT a1.id, a1.person_id, UNIX_TIMESTAMP( ...


0

Put the list of values into a temporary table, and then perform an INNER JOIN to it. Most SQL optimizers and engines can handle joins much better than they can handle an IN operation. If the list is long, this also allows you to define an index on the temporary table to further assist the optimizer



Top 50 recent answers are included