New answers tagged optimization
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
I don't think anyone has come up with a complete answer for your question. This sounds like a complex math question which does not belong on the "Database Administrators" site. With that said, Joe Celko has an article that might help you. Bin Packing Problems: The SQL
Following the famous article "fixing ORDER BY rand()", and in particular the multiple-selection via union, we can write this: ( SELECT `p1`.*, `users`.`username`, `users`.`displayname` FROM pics p1 JOIN (SELECT ceil(rand() * (SELECT max(id) FROM pics)) AS id ) AS p2 ...
50K rows tables are rather small. Before throwing more hardware at it, I'd recommend checking/tuning the following: my.cnf setting: make sure you have good buffers on InnoDB buffer, Key Cache, Query cache, etc. If website is calling same queries over and over, query cache could help. Check slow query log and optimize worst queries. It'd say this would ...
Without query execution plan, my first thoughts are: I. Use datetime variables to remove implicit convertion and use index. DECLARE @day as datetime2(2) = '20141007' DECLARE @dayStart as datetime2(2) = CONCAT(@day,' 00:00:00.000') DECLARE @dayEnd as datetime2(2) = CONCAT(@day,' 23:59:59.999') II. Change EXISTS for INNER JOIN EXISTS (Select * FROM Acks ...
Start with the database and work your way up. Check out the queries you run using the command line before sending them through a framework. You can activate the general log to check every query that's sent to the database if you want to check exactly what, exactly, is arriving at your server after having used the framework. Frameworks can be good (relieving ...
Top 50 recent answers are included