Tag Info

New answers tagged

1

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 ...


0

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 ...


2

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 ...


1

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 ...


0

You really need a fast HDD, but you need a properly sized innodb_log_file_size. Why not SSD for MySQL ? I learned something from this layout from a FaceBook Engineer's blog I wrote old posts about this Aug 14, 2013 : How do I determine how much data is being written per day through insert, update and delete operations? Feb 06, 2014 : MySQL on SSD - what ...


1

Use InnoDB because it supports Fast Index Creation.* If you have plenty of RAM, configure a tmpfs partition and point your MySQL tmpdir to that location. * There are many other reasons besides that to stop using MyISAM. MyISAM is known for its table-level locking, tendency to corrupt data, and lack of support for ACID and referential integrity. It's also ...



Top 50 recent answers are included