Subqueries execute every time you evaluate them (in MySQL anyway, not all RDBMSes), i.e. you’re basically running 7 million queries! Using a JOIN, if possible, will reduce this to 1. Even if adding indexing improves performance of those, you’re still running them.
I would propose a clean version based on DISTINCT ON (see docs): SELECT DISTINCT ON (usr_id) time_stamp, lives_remaining, usr_id, trans_id FROM lives ORDER BY usr_id, time_stamp DESC, trans_id DESC;
CLUSTER If you intend to use CLUSTER, the displayed syntax is invalid. create CLUSTER ticket USING ticket_1_idx; Run once: CLUSTER ticket USING ticket_1_idx; This can help a lot with bigger result sets. Not so much for a single or few rows returned. Postgres remembers which index to use for subsequent calls. If your table isn’t … Read more
Within SQL, there is no requirement that an OR clause breaks early. In other words, it is up to the optimizer whether to check both conditions simutaneously. I am not an expert in the MSSQL optimizer, but I have seen instances where the optimizer has and has not short circuited an OR clause.
First, let’s try FORCE INDEX to pick either ef or fe. The timings are too short to get a clear picture of which is faster, but `EXPLAIN shows a difference: Forcing the range on filetime first. (Note: The order in WHERE has no impact.) mysql> EXPLAIN SELECT COUNT(*), AVG(fsize) FROM files FORCE INDEX(fe) WHERE ext=”gif” … Read more
You have to do a look up for every where condition and for every join…on condition. The two work the same. Suppose we write select name from customer where customerid=37; Somehow the DBMS has to find the record or records with customerid=37. If there is no index, the only way to do this is to … Read more
The reserved prefix that exhibits this behaviour is sp_. Not sp. A stored procedure called spAddUser will be resolved in the normal way without looking for a matching object in master. The relevant quote in books online is Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server … Read more
Assuming relatively few rows in options for many rows in records. Typically, you would have a look-up table options that is referenced from records.option_id, ideally with a foreign key constraint. If you don’t, I suggest to create one to enforce referential integrity: CREATE TABLE options ( option_id int PRIMARY KEY , option text UNIQUE NOT … Read more
My view is that you should benchmark for your usage. I doubt there will be much difference. Bear in mind that while a single benchmark might suggest that one is slightly better, variation in the data over time might change that result. Also note that COALESCE has been part of standard SQL since 1992 – … Read more
You can get the Json easily enough if you have a query wrapper; var qLinq = Query<T>.Where(x => x.name==”jim”); Console.WriteLine(qLinq.ToJson()); There’s also an Explain() method on MongoCursor, so you could do this; var exp = Collection.FindAs<T>(qLinq).Explain() Console.WriteLine(exp.ToJson()); So if you want the time taken, “millis” is in there; var msTaken = exp.First(x => x.Name == … Read more