I have the following query: SELECT DISTINCT FIRST_VALUE(business_id) OVER (PARTITION BY b.sub_category_id ORDER BY AVG(stars) desc, COUNT(*) DESC) business_id, sub_category_id FROM purchase_experience pe JOIN businesses b ON b.id = pe.business_id AND b.status = 'active' AND b.sub_category_id IN (1010 ,1007 ,1034…
I have the following MSQL query, that is very slow SELECT a.id, a.date, a.Title, a.Text, a.Text2, a.Text3, b.xxx, b.yyyy, c.aaaa, c.bbbb FROM table_a a JOIN table_b b on a.Id = b.Id JOIN table_c c on a.eeee = c.id ORDER BY…
I have one query that is taking too long(> 5 minutes) and creating deadlocks virt_assist_bandwidth_incoming has 1115501 rows virt_assist_bandwidth_incoming_media has 25752 rows I do not think these are supper big tables. The query: SELECT * FROM virt_assist_bandwidth_incoming i LEFT JOIN…
I have a collection of data like this: { "items": [ { "product": { "name": "prod1", "price": { "pledge": 1, "cost": 19 } } }, { "product": { "name": "prod2", "price": { "pledge": 2, "cost": 10 } } } ]…
We have Postgres DB design that in simplified form looks like this: CREATE TABLE products ( id varchar, version bigint, profile varchar, PRIMARY KEY(id, profile, version) ); CREATE TABLE product_attributes ( productId varchar, key varchar, value varchar, profile varchar, version…
In our DB we have tables, that have 6-8 M records, this data is static and in order of performance optimization we decided to store some data from this tables in separate table, and for predefined filters use the values…
I have a huge dataset on two tables (users, pos_transactions), and I need to execute a join on them. I wrote the query both with join and subquery approches. Both looks Non-optimal: explain SELECT pt.card_number FROM pos_transactions pt where user_id…
I have a simple subquery in MySQL which produces a set of codes, something like 'LPR','LFR','LFL' (more codes can be returned). Using this in my main query slows it right down, like it can take a minute or 2 to…
I am working with a MySQL database and I have a column (let's call it status_column) which contains comma-separated values. The possible values in this column are 'NA', 'NON_NA', 'PSG_REQUIRED', or any combination of these, such as 'NA,PSG_REQUIRED' or 'PSG_REQUIRED,NON_NA',…
Can someone explain why the optimizer chooses different indexes if Limit is used? MariaDB > EXPLAIN SELECT dms_meta.docid FROM dms_meta WHERE (dms_meta.metid = 3 and dms_meta.value = '2015-10-01') ORDER BY dms_meta.docid ASC limit 25; +------+-------------+----------+-------+---------------+------------+---------+------+------+-------------+ | id | select_type |…