Deleting millions of rows in MySQL
DELETE FROM `table` WHERE (whatever criteria) ORDER BY `id` LIMIT 1000 Wash, rinse, repeat until zero rows affected. Maybe in a script that sleeps for a second or three between iterations.
query-optimization
Why would using a temp table be faster than a nested query?
“should be” is a hazardous thing to say of database performance. I have often found that temp tables speed things up, sometimes dramatically. The simple explanation is that it makes it easier for the optimiser to avoid repeating work. Of course, I’ve also seen temp tables make things slower, sometimes much slower. There is no … Read more
Oracle , != , ^= operators
I have tested the performance of the different syntax for the not equal operator in Oracle. I have tried to eliminate all outside influence to the test. I am using an 11.2.0.3 database. No other sessions are connected and the database was restarted before commencing the tests. A schema was created with a single table … Read more
How to estimate SQL query timing?
This can be done when using SHOW PROFILES syntax. When you open a MySQL session, you could set the variable “profiling” to 1 or ON. mysql> SET profiling = 1; So all the statements sent to the server will be profiled and stored in a historical and shown later by typing the command: mysql> SHOW … Read more
How to optimise MySQL queries based on EXPLAIN plan
Depends on what you’re going for and what the query is. Generally, for every line in EXPLAIN that has a Using where, you need to have it using an index (possible keys and keys column). These are your filters and include WHERE and ON. Having it say Using index is even better. It means there’s … Read more
Does query plan optimizer works well with joined/filtered table-valued functions?
In this case, it’s an “inline table valued function” The optimiser simply expands (unnests) it if it’s useful (or view). If the function is treated as “black box” by the outer query, the quickest way is to compare IO shown in SSMS vs IO in profiler. Profler captures “black box” IO that SSMS does not. … Read more
How to avoid “Using temporary” in many-to-many queries?
Here’s a simplified example I did for a similar performance related question sometime ago that takes advantage of innodb clustered primary key indexes (obviously only available with innodb !!) http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/ You have 3 tables: category, product and product_category as follows: drop table if exists product; create table product ( prod_id int unsigned not null … Read more
MySQL: Can I do a left join and pull only one row from the join table?
Let me sum up what I understood: you’d like to select each ticket and its last solution. I like using the following pattern for this kind of question as it avoids the subquery pattern and is therefore rather good where performance is needed. The drawback is that it is a bit tricky to understand: SELECT … Read more
SELECT DISTINCT is slower than expected on my table in PostgreSQL
While there is no index skip scan in Postgres yet, emulate it: WITH RECURSIVE cte AS ( ( — parentheses required SELECT product_id FROM tickers ORDER BY 1 LIMIT 1 ) UNION ALL SELECT l.* FROM cte c CROSS JOIN LATERAL ( SELECT product_id FROM tickers t WHERE t.product_id > c.product_id — lateral reference ORDER … Read more
How to find out what is locking my tables?
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