Tag Info

New answers tagged

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


0

This is exactly the reason for using normalization to limited extent and uafter performance testing. Normalization comes at cost of joins (sorting). Main purpose of DWH on 5NF is to store data safe, not to retrieve it fast. Alternative 1 There is a concept of Materialized View: a view that saved on hard drive. MySQL does not provide it out of the box, but ...


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


0

If you are unsure - go for normalization http://en.wikipedia.org/wiki/Database_normalization . Any deviation must have strong grounding


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

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


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


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


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


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


-2

Change your date column type to an integer. Store the date as a Unix date in integer. Timestamp Is a lot larger than an int. You'd get some bang out of that.


0

You can run the query via e.g. phpMyAdmin and append the keyword explain. This should give you insights in how the query uses available indexes and where an extra index should help. That said, Drupal's database structure is already pretty decent configured with indexes and such. Do you really need the view to execute that query on every page load? If not, ...


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


0

Take a look here - it's a recommended book list from those who bring you (us) MariaDB. As you will see, many of the books are (also) about MySQL. Take a look here for the differences between MariaDB and MySQL. As I understand it, MariaDB is gradually drifting away from MySQL and will not remain plug and play compatible for long. With most of the differences, ...


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


0

The second approach is much more normalised and what you would expect to see in an OLTP application database. The first approach is more of a de-normalised approach that you would likely see in a data warehouse for reporting purposes. The first approach would probably be faster as the less joins you have in a query the quicker it generally is but this can ...


0

I'm not sure exactly what is causing your "gap", but I think your biggest performance bottleneck is: Join Filter: ((acs.start_ts < ac.audit_ts) AND (acs.start_ts >= (ac.audit_ts - '1 day'::interval))) leading to: Rows Removed by Join Filter: 1953618923 Iterating over 2 billion rows is bound to take some time. That particular join is killing ...


0

First create a table for reference id if it does not exist | ref_id | int(11) | NO | PRI | NULL | auto_increment | | reference_id | int(11) | YES | | NULL | | then create another table to store the types | type_id | int(11) | NO | PRI | NULL | auto_increment | | type ...


0

A better design would be to have a single table to hold all the parties to a payment. If necessary, sub-type this table so companies, proprietors and customers have their own, unique sets of columns (or a separate, related table). The payments table will then have two foreign keys to Parties, let's call them PaymentFromPartyID and PaymentToPartyID, along ...


0

These are general recommendations, as you do not show the full extent of your queries to be performed (which kind of analytics you plan to do). Assuming you do not need real time results, you should just denormalize your data at the end of the period, precalculate once your aggregated results on all necessary timeframes -by day, by week, by month-, and work ...



Top 50 recent answers are included