Try this order: INDEX(emailed, -- for the `WHERE` user_id, id) -- for the `ORDER BY Note: In this context, IS NULL optimizes similar to = constant. So, I put that column first, thereby handing all the WHERE. After that, all the ORDER BY columns. Hence, it would be performed entirely in the Index's BTree. (Cf "covering" or "Using index").

Approach 1: A fulltext index can't do everything a LIKE condition can do. The most common problem I see preventing use of fulltext is that MySQL's implementation only indexes whole words, so if your search is for a partial word it won't help. It will take time to create the index, but if it's important to avoid downtime, you should definitely get acquainted with using https://www.percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html or https://github.com/github/gh-ost . These are both free tools that allow schema changes (including adding an index) without blocking clients from using the table in the meantime. We used pt-osc at my last job to run hundreds of schema changes per week during peak hours. Approach 2: Adding a JSON column alone would not help, without also creating an index for the expression you want to search for. Have you used JSON in MySQL before? You should do a proof of concept with the version of MySQL you're using to see if it works, before you decide on this. There are a lot of limitations and caveats with using JSON in MySQL. I usually recommend not to use JSON. Further reading: https://dev.mysql.com/doc/refman/8.0/en/json.html https://dev.mysql.com/doc/refman/8.0/en/json-functions.html https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html#json-column-indirect-index https://dev.mysql.com/doc/refman/8.0/en/create-index.html#create-index-multi-valued Approach 3: This is also called an inverted index. It can work well, but as you said it requires some work to maintain the inverted index, adding values as you add data. A comment above suggests using a trigger, which does work but think that also counts as adding more code. Which of those approaches would solve the situation best? I'd prefer the fulltext index if it meets the needs of the searches you need to support. The advantage is that it takes the least code to maintain the index, and that's a pretty big advantage. But every time I hear the question "which is best?" I know that it's the wrong question. There is no "best" solution for all circumstances. Your project has its own requirements, and you need to choose the solution that fits those requirements. Any solution might be best for one person's application but it won't work for another application.

I don't see a case for full text search here. You can provide your input string as is to this query: SELECT t.id, firstname, lastname FROM user_table t JOIN ( SELECT split_part(fullname, ' ', 1) AS firstname , split_part(fullname, ' ', 2) AS lastname FROM unnest(string_to_array('firstname1 lastname1, firstname2 lastname2, firstname3 lastname3', ', ')) fullname ) input USING (firstname, lastname); An index on (firstname, lastname) should help performance while the input list isn't too long. For retrieving more than a couple percent of all rows, a sequential scan is faster anyway. Related: https://stackoverflow.com/a/8612456/939860 https://dba.stackexchange.com/questions/166867/postgresql-importing-thousands-of-columns-as-an-array/166905#166905 https://dba.stackexchange.com/questions/46410/how-do-i-insert-a-row-which-contains-a-foreign-key/46477#46477

Sargable If the datatype of executed is DATE, then remove the function call. If it is TIMESTAMP or DATETIME, then change the test to: executed >= UTC_DATE() AND executed < UTC_DATE() + INTERVAL 1 DAY In addition, you need this composite index: INDEX(account_uuid, code, -- in any order executed) -- last This index should significantly speed up the UPDATE. (I am guessing that only one row is affected??) 1000 Are you looping through a lot of rows, doing 1000 at a time? How long is each iteration taking? Decrease the "1000" so that it does takes only a few seconds. Building good index: http://mysql.rjweb.org/doc.php/index_cookbook_mysql In almost all cases, MySQL will use only one index in one [sub]query.

The "Table-per-whatever" Model almost always breaks down eventually. It's usually when some "Administrative" function needs to go across the 20,000 tables you've built up in one query, but the DBMS limits you to just 61 tables in any one query (MySQL). Any half-decent DBMS with sensibly-indexed Tables will cope with your first structure (post_id, group_id, post_content) with ease.

You won’t get close to SSD speeds no matter how you set up HDDs (well, unless you give them a cache). That said, the impact on your application performance is going to be against your IO time only. If you don’t do a lot of IO (because you have so much memory that nothing really hits the disk) then this could be fine. I find that if you build it (a database with fast storage) then they (IO intensive SQL) will come, so you’ve probably already made this a requirement going forwards. In reality, you may be trying to solve a problem that doesn’t exist - are you really going to wear out your SSDs faster than you can afford? The network attached storage idea with RAID is fine as a general storage solution, but you’re going to want SSDs if your application is already relying on your database being supported by SSD.

Is it worth it to do the WHERE part to prevent the unnecessary updates? Yes. Every update to a row even if nothing has actually changed causes a write to the log and potentially to the data file⁰. This extra IO load may directly affect performance. It may also require more by way of locks which could further harm concurrency, including your reads not just other writes. As well as the extra IO caused by the write itself there may be other knock-on effects: if you are log shipping for backups then you'll use more space and bandwidth there. Differential backups may also be larger than they'd otherwise need to be. And of course if you are using replication, mirroring, and other such, these NoOp writes will take effect there too. Also, if you are using temporal tables you will find an extra row in the history, so you'll consume more space too that way, possibly permanently. There's no triggers / No replication setup If you are not using any of the above now, you might choose to later. Or someone after you may. The A1/5 aren't under index, they're mostly nvarchar(50) or similar Whether these were indexed or not shouldn't affect the performance of the decision to write or not. Row count is fairly small (around 200k-300k) Always be aware that your data may grow significantly over time. If not - which use-case would make it worth it? As I'd say it is definitely worth it most of the time, I'll flip that question to “which use case would make it not worth it?”: For small updates where you can afford not to care about any of the above, your code is far simpler if you don't include the filtering clause. If the resulting filtering clause is long and complex then this will remove a potential source of odd bugs. Your WHERE clause example as it is might need to be more complex/ugly if the affected columns are NULLable, so may already be buggy, because A1 <> src.A1 may make it skip an update if either m.A1 or src.A1 are NULL but the other isn't². If you are using heavy checks, perhaps a sub-query that you can't somehow factor out of the SET and WHERE clauses¹ then the extra work there might dwarf the extra IO of unnecessary updates. [0] as pointed out by Charlieqace in a comment, the experiments documented in https://www.sql.kiwi/2010/08/the-impact-of-non-updating-updates.html show that the data pages are not modified in a number of cases, so my previous wording was inaccurate. Given other concerns (the article doesn't test if locking is affected, triggers and temporal table activities do still fire, there is some extra log activity, …) I would still recommend checking before updating everywhere this does not result in convoluted code. [1] CTEs are usually an answer to this, though not always, and the query planner might be clever enough not to run the identical sub-query twice anyway but I'd not want to reply upon that. [2] because not only is NULL not equal to NULL, it is also not not-equal to NULL.

The compiler is taking your IN clause and attempting to optimize by removing duplicate values. It does this by taking all of your parameters, sorting them, merging them in order using Merge Interval, and doing a Nested Loop Join on the result. The problem is that this can take a very long time to compile with a large number of values: each one needs its own virtual table, resulting in a Constant Scan and a Compute Scalar along with a big Concatenation at the end. This is what is causing the slowdown at compile time, before the query is run. Meanwhile, the OPTION (RECOMPILE) version can embed the parameters directly into the query, which means that the values would be folded together before the optimization phase of the compile even starts, speeding up overall compile time significantly. This at the cost of recompiling on every run. The upshot of all of this is that very long IN lists, as well as lots of parameters, can be very inefficient. I suggest that instead you consider using a Table Valued Parameter, a temp table, or a table variable (in all cases indexed with a primary clustering key) and simply joining that in the normal fashion. As to the actual query itself, there are a number of strange things with it. Your final result is just COUNT(*) so it's unclear what the point of the giant PIVOT was in the first place, over a normal GROUP BY. Likewise it's unclear why most of the tables are even there, or what the final result is supposed to signify. Once you've worked that out, why LEFT JOIN and not INNER JOIN? Are the join columns nullable? The CROSS APPLY isn't actually applying any outer references, it could be CROSS JOIN, and in itself serves no purpose in the query.

Your filter l_shipdate Is not very selective, we can see from the plan that it was responsible for removing only 293,696 rows and it ended up needing to use 19,701,655. If it was to use an index to read those rows one by one it would probably have been much slower than the sequential scan of the table. Should I drop it? If this is the only query you are running and the only filter being used, then probably. Otherwise, there's not enough information to go on. The index would probably be useful if you wanted to see the rows for 1 particular day. The index might be better off with some additional columns in it. Impossible to say without knowing your application.

Yes. And probably a lot. You never gain performance using character(n)(alias char(n)). You don't gain anything at all because that type is outdated, mostly useless, and https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_char.28n.29 . Related: https://dba.stackexchange.com/questions/89429/would-index-lookup-be-noticeably-faster-with-char-vs-varchar-when-all-values-are/89433#89433 But you saw that bit in the quote from https://www.postgresql.org/docs/current/datatype-character.html where it says: apart from increased storage space when using the blank-padded type char(n) is the blank-padded type. And column names like comment indicate mostly blank-padded waste. text or varchar remove the bloat and everything around your table becomes substantially faster, as your avg. row size probably shrinks to less than half. More tuples per data page means fewer pages to read and process per query, and that is the most important factor for performance there is. While being at it, order columns favorably to make it more efficient, yet: Column | Type | Collation | Nullable | Default ---------------+---------------+-----------+----------+--------- orderkey | integer | | not null | custkey | integer | | | orderdate | date | | | shippriority | integer | | | totalprice | numeric(12,2) | | | orderstatus | varchar(1) | | | orderpriority | varchar(15) | | | clerk | varchar(15) | | | comment | varchar(79) | | | Why? https://stackoverflow.com/a/7431468/939860 I kept the length restrictions with varchar(n), but if those are just arbitrary, use text instead. A tiny bit faster, yet. (And less corner-case hassle.) See: https://dba.stackexchange.com/questions/20974/should-i-add-an-arbitrary-length-limit-to-varchar-columns/21496#21496

You need an index on orders (o_orderdate), which should avoid the expensive sequential scan. If it doesn't, and the sequential scan is actually slower than the index scan, PostgreSQL is probably not configured properly: either random_page_cost is to high for your hardware, or effective_cache_size is too low. Disabling parallel query (max_parallel_workers_per_gather = 0) will also favor an index scan.

"A search in company_aliases is as fast as a search in companies, so it's a viable workaround to query companies and, if no result was found, query company_aliases. The total query time is still below 2 ms. Essentially, I am just moving the lazy evaluation of the OR into my PHP code. But this shouldn't be faster than executing the same logic entirely in the DB." That's not the same logic. The query is saying give me the rows in companies that either have the same name as this value or can be found in company_aliases with this value. There's nothing which tells it to stop when it finds a match. You can expand the OR condition manually: select * from `companies` where `name` = ? and `companies`.`deleted_at` is null union all select * from `companies` where exists (select null from `company_aliases` where `companies`.`id` = `company_aliases`.`company_id` and `name` = ? ) and `companies`.`deleted_at` is null and ( `name` <> ? or `name` is null) This should give you a plan which uses an index on companies.name to satisfy the first part, and it should use an index on company_aliases.name followed by a loop to companies based on id for the second part. Assuming those indexes exist, you should be fine.

Your query falls under https://www.postgresql.org/docs/current/parallel-safety.html : The following operations are always parallel restricted: Plan nodes that reference a correlated SubPlan. The top nested loop could be parallelized, but it would have to be gathered before dispatching to the SubPlan. There is just no point in doing it that way.

This is not a good idea. Take it from me. A long time back I coded a check into sp_BlitzCache that found plans with Key Lookups in them, and then compared that operator's cost to the total cost of the plan. If it was >50% or something, I'd flag it as an expensive one. I would not do this ever again. The thing is, cost has absolutely zero Kelvin real world meaning. This has become more obvious with operator times being present in query plans. Going around fixing every Key Lookup isn't likely to solve your worst performance problems, and is very likely to end you up with a lot of very wide indexes. Your better bet is to find queries to tune based on average CPU consumption, or ones that are known to cause specific business pains, and tune the actually-slow parts of those plans. Key Lookups don't indicate a performance problem, just like high costs for a plan or operator don't. You need to get the actual execution plan to figure out what's under-performing.

I hope I understood correctly the question, if not please let me know. Try: SELECT p.game_id AS gameId FROM players p INNER JOIN units u ON p.id = u.player_id WHERE u.unit_type_id in (1,2) GROUP BY p.game_id HAVING COUNT(DISTINCT u.unit_type_id) =2 ; https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=f90fd3d4c5300a4e4ed3e7323243f89d This will return the players.game_id which both have unit_type_id (1,2). If you have three or more u.unit_type_id in you have to change HAVING COUNT(DISTINCT u.unit_type_id) =2 ; to the number of the records in the in condition

This is maybe the most common planning issue we see here. The planner assumes the 165862 qualifying records are spread randomly throughout the 26424052 rows of the index (well, it thinks the numbers are 151602 and 25695264, as it uses estimates not the actual counts--that doesn't change anything though.) Therefore it thinks it can stop early after scanning 11/165862 of the index. But in reality this customers records are deficient in later timestamps, so it ends up needing to walk 1760471/25695264 of the index before it finds 11 of them, which is over 1000 times more than it thought. There is nothing you can do to get the planner to stop assuming the records are randomly scattered. What you could do is force it not to use the idx_itinerary_timestamps_last_segment_arrival_at index for ordering, by changing the ORDER BY to order by itinerary_timestamps.last_segment_arrival_at+interval '0 seconds' desc. This means it will have to read all 165862 records and sort them. Which is not ideal, but probably better than the current plan. If the performance of this were important enough, it might be worth it to denormalize by copying user_id into itinerary_timestamps, then you would just need an index on (user_id, last_segment_arrival_at).

After reading @J.D 's response, I went in and performed several ANALYZE queries on the selects I previously did. It became quite apparent that for few specific queries specified by 'WHERE index = X', MariaDB wasn't using indexes, and instead returned the whole collection of lines, later filtering them by the 'WHERE' criteria. What ended up solving this issue was the 'ANALYZE TABLE table_name' query, which, to my understanding, updates the collection of known indexes in databases records. This resulted all of my queries successfully use indexes from now on and perform queries in a reasonable amount of time. I've found this post to be helpful, especially in case the index ignoring issue has deeper roots: https://dba.stackexchange.com/questions/221092/mysql-query-optimizer-not-using-indexes

I'm not aware of something that aggregates the statistics of each batch. The actual execution plan might total it for you somewhere in the plan XML or properties? If you execute a single batch at a time, you can use Richie Rump's simple yet awesome http://statisticsparser.com/ tool to aggregate the individual statistics of each statement into totals. It even formats it all into nice readable tables, both for the individual statistics and the totals at the end. It will parse both TIME and IO statistics. If you use https://www.sentryone.com/plan-explorer and open the actual execution plan of your batch in it, it also gives you the aggregated TIME statistics on the toolbar.

There is nothing magical you can do to make this faster. Your options are: faster disks more RAM, and make sure the table is cached in RAM throw more workers at it by increasing max_parallel_workers_per_gather

There are a couple of indexes that might help a query of your kind. Like, a btree index with inverted sort order (possibly multicolumn, or "covering"). See: https://dba.stackexchange.com/questions/39589/optimizing-queries-on-a-range-of-timestamps-two-columns/39599#39599 Or a GiST or SP-GiST index on daterange. See: https://stackoverflow.com/a/22111524/939860 However, while your filters are hardly selective, indexes can only do so much. What makes your query expensive is "unnesting" rows with long time ranges to many rows before folding duplicate days with DISTINCT and counting. That can be improved gradually. Faster EXISTS variant https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e81678ab24633937116d87d3a40f2e5a has an expensive flaw. You still COUNT(DISTINCT dates.day), but there's no need any more as EXISTS already eliminated duplicates. count(*) is substantially faster. Plus some other minor modifications: WITH dates AS ( SELECT day FROM calendar WHERE day BETWEEN '2021-04-14' AND '2022-04-13' ) SELECT p.product_id, count(*) AS days FROM dates d CROSS JOIN products p WHERE EXISTS ( SELECT FROM sales_periods s WHERE s.product_id = p.product_id AND d.day >= s.since AND d.day db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e1dd0550ead55a4450a8bb953219d51a Pre-select rows from sales_periods Another possible optimization, especially while indexes on the main table don't help anyway, or for more selective filter ranges: eliminate irrelevant sales early: WITH s AS ( SELECT product_id, since, till FROM sales_periods WHERE since = '2021-04-14' ) SELECT p.product_id, count(*) AS days FROM ( SELECT day FROM calendar WHERE day BETWEEN '2021-04-14' AND '2022-04-13' ) d CROSS JOIN products p WHERE EXISTS ( SELECT FROM s WHERE s.product_id = p.product_id AND d.day >= s.since AND d.day db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e1dd0550ead55a4450a8bb953219d51a Aside: Your calendar table holds days till the year 2222, which seems like excessive waste. There is no need to handle a table of 81083 rows. But that's a minor issue. Much faster with range aggregation What I really want to post is the following query making use of range aggregation, and then count the days in the range. 200x faster in my hands with your sample data (~ 3 ms vs ~ 650 ms for your original query). But there's a snag: we need multiranges introduced with Postgres 14. Create this auxiliary function to count days in a datemultirange: CREATE OR REPLACE FUNCTION f_days_in_multirange(datemultirange) RETURNS int LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE BEGIN ATOMIC SELECT sum(upper(r) - lower(r))::int FROM unnest($1) t(r); END; COMMENT ON FUNCTION f_days_in_multirange(datemultirange) IS 'Counts days in given datemultirange. Input with any unbounded range results in NULL value!'; See: https://dba.stackexchange.com/questions/300507/what-does-begin-atomic-end-mean-in-a-postgresql-sql-function-procedure/300512#300512 Then the query can be: SELECT product_id , CASE WHEN sales_ct = 1 THEN upper(date_range) - lower(date_range) ELSE f_days_in_multirange(date_range) END AS days FROM ( SELECT product_id, count(*) AS sales_ct , range_agg(daterange(since, till, '[]')) * multirange(daterange('2021-04-14', '2022-04-13', '[]')) AS date_range FROM sales_periods WHERE since = '2021-04-14' GROUP BY 1 ) sub; db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=e1dd0550ead55a4450a8bb953219d51a Most of your resulting date ranges consist of a single range, and most of those come from a single source. So I added a cheap count sales_ct and used that to take a shortcut to great effect (almost 10x). CASE WHEN sales_ct = 1 THEN .... Depending on your actual data distribution, other shortcuts may be possbile. You need to understand https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN , https://www.postgresql.org/docs/current/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE , and the https://www.postgresql.org/docs/current/functions-range.html#RANGE-OPERATORS-TABLE to compute the intersection of the ranges.