Usually joins will work faster than inner queries, but in reality it will depend on the execution plan generated by SQL Server. No matter how you write your query, SQL Server will always transform it on an execution plan. If it is “smart” enough to generate the same plan from both queries, you will get … Read more
For testing purposes you can force the use of the index by “disabling” sequential scans – best in your current session only: SET enable_seqscan = OFF; Do not use this on a productive server. Details in the manual here. I quoted “disabling”, because you cannot actually disable sequential table scans. But any other available option … Read more
From the MySQL documentation: The query contained only aggregate functions (MIN(), MAX()) that were all resolved using an index, or COUNT(*) for MyISAM, and no GROUP BY clause. The optimizer determined that only one row should be returned. Basically this means your query uses data that is directly available to MySQL and the query will … Read more
Using temporary means that MySQL need to use some temporary tables for storing intermediate data calculated when executing your query. Using filesort is a sorting algorithm where MySQL isn’t able to use an index for sorting and therefore can’t do the complete sort in memory. Instead it breaks the sort into smaller chunks and then … Read more
You can use the IN predicate, like so: SELECT * FROM table WHERE 123 IN(col1, col2, col3, col4); SQL Fiddle Demo it’s the opposite version of IN. No it is not, It is the same as using the ORs the way you did in your question. To clarify this: The predicate IN or set membership … Read more
Take a look at the following system stored procedures, which you can run in SQLServer Management Studio (SSMS): sp_who sp_lock Also, in SSMS, you can view locks and processes in different ways: Different versions of SSMS put the activity monitor in different places. For example, SSMS 2008 and 2012 have it in the context menu … Read more
You might want a computed index. Let’s create a table: create table sales(day date, amount real); And fill it with some random stuff: insert into sales select current_date + s.a as day, random()*100 as amount from generate_series(1,20); Index it by day, nothing special here: create index sales_by_day on sales(day); Create a row position function. There … Read more
http://www.postgresql.org/docs/8.2/static/using-explain.html Basically, a sequential scan is going to the actual rows, and start reading from row 1, and continue until the query is satisfied (this may not be the entire table, e.g., in the case of limit) Bitmap heap scan means that PostgreSQL has found a small subset of rows to fetch (e.g., from an … Read more
You wont be able to alter/drop the table, unless you drop the view first.
Starting from the answer given by @chaos, but with a few modifications: You should always use ORDER BY if you use LIMIT. There is no implicit order guaranteed for an RDBMS table. You may usually get rows in the order of the primary key, but you can’t rely on this, nor is it portable. If … Read more