Tag Info

New answers tagged

0

It looks to me like you're querying lots of data in a big index, so it's slow. Nothing notably wrong there. If you're on PostgreSQL 9.3 or 9.4, you could try to see if you can get an index-only scan by making this into a covering index of sorts. CREATE INDEX idx_traffic_partner_only ON traffic (dt_created, clicks, impressions) WHERE campaign_id IS NULL ...


0

I realized one thing: I can easily obtain the ID of the combination by other means, so what I need most now is to obtain the array of categories, which is best done by the SET approach suggested by Rick James. However, I don't want to delete this my answer, because it best answers the question as written (especially the EDIT in the question). It's below. ...


1

How about the SET datatype. With that, you could represent all combinations of 14 categories in 14 bits (2 bytes).


1

The MySQL SET datatype is a disaster - as are any type of array datatypes (supported unfortunately by many RDBMSs). Check out my answer to another question here. As I note, MySQLs SET is a breach of Codd's second rule - no repeating group datatypes. It is also completely non-portable should you wish to change RDBMS. If you wish to store that data this way ...


1

Query You UPDATE statement looks good except for one major problem. Fixed and re-formatted with some other minor improvements: UPDATE line_items li SET product_id = d.latest_product_id FROM products p JOIN vendors v ON v.id = p.vendor_id JOIN vendorgroups vg ON vg.id = v.vendorgroup_id JOIN ...


3

Try to execute the query with gather_plan_statistics hint. Then use dbms_xplan to display exec plan. You will see E-rows(estimated) and A-rows(Actual). This is where I would start, to check whether the optimizer is wrong in it's assumptions or not. Also check v$sql_plan of the running query and check whether TEMP space is really used or not. Especially on ...


0

Using temporary and filesort are not as evil as everyone makes them out to be. Do not split a DATE into year, month, and day columns; it will cause more pain than it is worth. I prefer (in your specific case) to do GROUP BY LEFT(c.date, 7), although it may not speed up anything. "Summary Tables" is the way to go for that type of query, assuming you have ...


0

I ended up doing this, to do it in batches of 1krow (which takes about .2s per batch if there's nothing to update, 2s otherwise; basically it has the same total speed per row if it's in batches of 10k, 5k or 1k): SET SESSION sql_mode = 'strict_all_tables'; SET SESSION sql_warnings = 1; \W DELIMITER // DROP PROCEDURE IF EXISTS update_dmf_lifespan// CREATE ...


1

query_cache_type = 0 query_cache_size = 0 Seriously, turning off the QC is probably best. especially based on what you said. Keep in mind that every INSERT (or UPDATE) to a table causes all entries in the QC to be purged. Furthermore, if the QC size is large, (say, over 50M), the purge time slows down the write. The ENGINE's cache is important to both ...


1

The following images help us to understand briefly: Source


1

As far as your query goes, it seems you've followed SQL best practices so there are no easy, significant performance boosts I could find. Now, sorry to state the obvious, but make sure you have enough indexes and that you are using those indexes, and check if all your stats are up to date. Database Engine Tuning Advisor is a great tool to check both of ...


0

On files, add these compound indexes: INDEX(filesystem_fk, file_status, file_tsuid, created_time), INDEX(filesystem_fk, file_status, created_time) (I am not sure which will help more.)


1

First step: This is SQL. You don't need to make separated queries for every status type. SELECT sh_table.`status`, SUM( CASE WHEN ( date(temp.max_created_at) BETWEEN DATE_SUB(DATE(NOW()), INTERVAL 1 DAY) AND DATE_SUB(DATE(NOW()), INTERVAL 0 DAY)) THEN 1 ELSE 0 END ) AS INTERVAL1, SUM( CASE WHEN ( date(temp.max_created_at) BETWEEN ...



Top 50 recent answers are included