Tag Info

Hot answers tagged

4

You could try simplifying and using LATERAL for joining the laps table: SELECT races.*, tmptimers.last_start_time, tmplaps. last_updated_at FROM races LEFT JOIN timers AS tmptimers ON tmptimers.user_id = 1 AND tmptimers.race_id = races.id LEFT JOIN LATERAL ( SELECT updated_at AS last_updated_at FROM laps WHERE ...


3

A few things you can do: Use enums or lookups keyed by integer values, or a simple "char" field, instead of varchar sort keys where possible. I'd use an enum because you can control the sort order easily. The only serious downside with an enum is that you can't currently drop values from an enum type. You can add them (including inserting them in the ...


2

Refactoring the query like this gives better performance (from ~7s to ~0.364s on my local): http://pastebin.com/7VpLGdQB EXPLAIN now shows this: http://pastebin.com/q8zEkXbx Joining the node table twice was the bottleneck. The subquery with node and content_type_profile tables is much more efficient. On the Drupal side I'll find a way to change this ...


2

To check exactly how selective your filter is for your particular query, you could execute the following queries: SELECT (SELECT count(*) FROM indi WHERE sex = '2') / (SELECT count(*) FROM indi) * 100 as selectivity; SELECT (SELECT count(*) FROM c_loc WHERE location_id IN (3,4,5,6)) / (SELECT count(*) FROM c_loc) * 100 as ...


2

Two more things in addition to what has been said already: IN with long lists does not scale well (at least it did not in my tests on Postgres 9.1; have to run new tests ..). I found it to be faster to prepare a derived table and JOIN to it. Details in this related answer on SO. If laps is a big table and your query only select a small fraction of rows ...


2

PROBLEM From the posts in your question, I see 3 FULLTEXT indexes. There is one for each column. Why did the query work at all ? MySQL worked with whatever it had. In your case, it searched by a full table scan. That's what the MySQL Query optimizer decided on. SOLUTION What you really need is a single FULLTEXT index with all 3 columns ALTER TABLE ...


1

I would argue for 1 row per data event (so 1440 rows per day) with one static column per data point. This will be easiest to query against any of the fields.


1

OPTION #1 : Use INT UNSIGNED instead of BIGINT If the fields will not exceed 4,294,967,295, change them to INT UNSIGNED ALTER TABLE part1 MODIFY COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT, MODIFY COLUMN first INT UNSIGNED NOT NULL, MODIFY COLUMN second INT UNSIGNED NOT NULL; ALTER TABLE part2 MODIFY COLUMN link INT UNSIGNED NOT ...


1

I have three suggestions SUGGESTION #1 : Rewrite the query You should rewrite the query as follows SELECT http, COUNT( http ) AS count FROM reqs WHERE date >= ( DATE(NOW() - INTERVAL 1 DAY) + INTERVAL 0 SECOND ) GROUP BY http ORDER BY count; or SELECT * FROM ( SELECT http, COUNT( http ) AS count FROM reqs WHERE date >= ( ...


1

You can do this with Soft-NUMA. Here's a Perfmon screen shot from a 4-CPU VM I set up to test using your settings, 2 soft NUMA nodes, 1 CPU to Node 0, 3 CPUs to Node 1. I'm running a CPU intensive query for 20 seconds on node 0, and then the same query on node 2. You can see the CPU activity swap over: I'm running the query via sqlcmd and connecting ...


1

Is there a way to make queries from a database to use only certain core on the host? One way is to use query hint OPTION (MAXDOP 1). The problem with above apporach is that you have to hint everything that you don’t want to be limited by the server-wide setting, or use 0 (i.e. unlimited) for the server-wide setting and hint everything that you do want ...


1

Yes, all the basic optimizations in MySQL 5.5 apply to MariaDB 5.5. However, starting with MariaDB 10, a real fork, not all improvements in 5.6 are in MariaDB codebase, and alternatively, MariaDB has some exclusive features (Hash JOINS). For now, those are not too different, that may change in the future. For a book, where most of the optimizations are ...



Only top voted, non community-wiki answers of a minimum length are eligible