Tag Info

Hot answers tagged

2

Try making a temp table with just the data you need and doing a join to that. For each self join do another temp table. I would start with one at a time and check the performance.


2

Here is the query to order it the way the question has it SELECT c.cat_name,p.prod_name FROM products p INNER JOIN category c ON p.cat_id = c.cat_id ORDER BY prod_name; Here is that query formatted SELECT CONCAT('Category : ',c.cat_name,', Product ; ',p.prod_name) DisplayLine FROM products p INNER JOIN category c ON p.cat_id = c.cat_id ORDER BY ...


2

The following indexes may help you: CREATE INDEX stkx_custloc_ind1 ON customer_location USING btree (location_id, customer_id); CREATE INDEX stkx_custrecpt_ind1 ON customer_receipt USING btree (customer_id, receipt_id); CREATE INDEX stkx_recptprod_ind1 ON receipt_product USING btree (receipt_id, status COLLATE pg_catalog."default", ...


2

This is as you say a very simple query, so the optimal index here is very clear. Thanks for showing the whole table structure, indexes and full query, not all people do that. You indexes are in the good direction, but you must know that (in general, there are other query plans like index_merge) a table scan can only use one index. In this case, as you are ...


1

I'd actually think about adding a status column to the table tblstockingorders and having a trigger on that table that injects the status into the tblstatushistory. Granted you'd have to do a 1-time update to all the rows in tblstockingorders (something similar to your query above) and set their last status, but this would give you best overall performance ...


1

Multiplying the rows is invalid for several reasons: Many times, the rows examined are an approximation (based on statistics, not accurate), good for query plan selection, but not for performance calculation The total number of rows examined on a nested loop join (A, B) is not rows_examined_on_table_A * rows_examined_on_table_B, but ...


1

As you are repeating this query for multiple months then you will be continually re-aggregating the same rows. For example the rows in the first month will always be brought back by the t1.post_date < @report_date criteria so will be re-processed for every month. To avoid this I'd probably consider working through it in an iterative way a month at a ...


1

Most likely the statistics on the field were out of date, adding the index create/updated statistics with a full table scan. More info on statistics https://mariadb.com/kb/en/mariadb/documentation/optimization-and-tuning/engine-independent-table-statistics/


1

Just looking at your definitions (thank you very much for that) I see: No index on to support WHERE dbo.EntryTypes.EntryType = 'Error'. (But this may not be needed if it is a small table with just a few entries.) Your fk_* columns in MAIN have check constraints, but no indexes. You should create some indexes since they are used to join to several ...


1

My diagnosis from the information that is currently available: Sounds like your tables are for whatever reason mostly uncached. The first run brings all data into cache. The plans probably have tons of random IO. This is extremely slow to run on a disk, and almost unnoticeable in memory. Therefore, I guess that the new edition does not provide you as much ...



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