An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one. Do you think that indexes going to reduce query run time? Any ideas how can i pass through the error code? SQL Query takes 10.1 secs and got over 380 times execution in slow log. SELECT l.ID, post_title, post_content, post_name, post_parent, post_author, post_status, post_modified_gmt, post_date, post_date_gmt FROM ( SELECT wp_posts.ID FROM wp_posts WHERE wp_posts.post_status IN ('publish') AND wp_posts.post_type = 'post' AND wp_posts.post_password = '' AND wp_posts.post_date != '0000-00-00 00:00:00' ORDER BY wp_posts.post_modified ASC LIMIT 100 OFFSET 214000 ) o JOIN wp_posts l ON l.ID = o.ID Indexes that i need to create; MariaDB [database]> ALTER TABLE wp_posts ADD INDEX wp_posts_idx_post_type_post_passw_post_statu (post_type,post_password,post_status); > ERROR 1071 (42000): Specified key was too long; max key length is 1000 > bytes MariaDB [database]> ALTER TABLE wp_posts ADD INDEX wp_posts_idx_post_modified (post_modified); Query OK, 453289 rows affected (10.839 sec) Records: 453289 Duplicates: 0 Warnings: 0 --- CREATE TABLE wp_posts ( ID bigint(20) unsigned NOT NULL AUTO_INCREMENT, post_author bigint(20) unsigned NOT NULL DEFAULT 0, post_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00', post_date_gmt datetime NOT NULL DEFAULT '0000-00-00 00:00:00', post_content longtext COLLATE utf8mb4_unicode_ci NOT NULL, post_title text COLLATE utf8mb4_unicode_ci NOT NULL, post_excerpt text COLLATE utf8mb4_unicode_ci NOT NULL, post_status varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish', comment_status varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open', ping_status varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open', post_password varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', post_name varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', to_ping text COLLATE utf8mb4_unicode_ci NOT NULL, pinged text COLLATE utf8mb4_unicode_ci NOT NULL, post_modified datetime NOT NULL DEFAULT '0000-00-00 00:00:00', post_modified_gmt datetime NOT NULL DEFAULT '0000-00-00 00:00:00', post_content_filtered longtext COLLATE utf8mb4_unicode_ci NOT NULL, post_parent bigint(20) unsigned NOT NULL DEFAULT 0, guid varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', menu_order int(11) NOT NULL DEFAULT 0, post_type varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post', post_mime_type varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '', comment_count bigint(20) NOT NULL DEFAULT 0, PRIMARY KEY (ID), KEY post_name (post_name(191)), KEY type_status_date (post_type,post_status,post_date,ID), KEY post_parent (post_parent), KEY post_author (post_author), KEY wp_posts_idx_post_modified (post_modified) ) ENGINE=MyISAM AUTO_INCREMENT=463265 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

We a running an instance of Apache syncope with around 130k users (ca. 33k with active or pending state). When searching for users by attributes we see long query times often over 50 seconds. One example query might be

select * from user_search_attr 
where schema_id = 'familyName' 
and stringvalue = 'Bergmann'

user_search_attr is a view defined as follows:

CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW user_search_attr AS
select
    u.id AS any_id,
    attrs.schema_id AS schema_id,
    attrs.booleanvalue AS booleanvalue,
    attrs.datevalue AS datevalue,
    attrs.doublevalue AS doublevalue,
    attrs.longvalue AS longvalue,
    attrs.stringvalue AS stringvalue
from
    (SyncopeUser u
left join JSON_TABLE(coalesce(u.plainAttrs, '[]'), '$[*]' 
   COLUMNS (schema_id varchar(255) PATH '$.schema', 
   NESTED PATH '$.values[*]' 
   COLUMNS (booleanvalue int(11) PATH '$.booleanValue', 
            datevalue varchar(32) PATH '$.dateValue', 
            doublevalue double PATH '$.doubleValue', 
            longvalue bigint(20) PATH '$.longValue', 
            stringvalue varchar(255) PATH '$.stringValue'))) attrs on
    (1 = 1))
where
    attrs.schema_id is not null
    and (attrs.booleanvalue is not null
        or attrs.datevalue is not null
        or attrs.doublevalue is not null
        or attrs.longvalue is not null
        or attrs.stringvalue is not null);

As you can see the data comes from the table SyncopeUser which is defined as follows:

CREATE TABLE SyncopeUser (
  id varchar(36) NOT NULL,
  creationContext varchar(255) DEFAULT NULL,
  creationDate datetime(3) DEFAULT NULL,
  creator varchar(255) DEFAULT NULL,
  lastChangeContext varchar(255) DEFAULT NULL,
  lastChangeDate datetime(3) DEFAULT NULL,
  lastModifier varchar(255) DEFAULT NULL,
  status varchar(255) DEFAULT NULL,
  changePwdDate datetime(3) DEFAULT NULL,
  cipherAlgorithm varchar(20) DEFAULT NULL,
  failedLogins int(11) DEFAULT NULL,
  lastLoginDate datetime(3) DEFAULT NULL,
  mustChangePassword int(11) DEFAULT NULL,
  password varchar(255) DEFAULT NULL,
  passwordHistory text DEFAULT NULL,
  securityAnswer varchar(255) DEFAULT NULL,
  suspended int(11) DEFAULT NULL,
  token text DEFAULT NULL,
  tokenExpireTime datetime(3) DEFAULT NULL,
  username varchar(255) DEFAULT NULL,
  plainAttrs longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
              DEFAULT NULL CHECK (json_valid(plainAttrs)),
  REALM_ID varchar(36) DEFAULT NULL,
  SECURITYQUESTION_ID varchar(36) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY U_SYNCPSR_USERNAME (username),
  UNIQUE KEY SyncopeUser_username (username),
  KEY SECURITYQUESTION_ID (SECURITYQUESTION_ID),
  KEY SyncopeUser_realm_id (REALM_ID),
  CONSTRAINT SyncopeUser_ibfk_1 FOREIGN KEY (REALM_ID) 
           REFERENCES Realm (id),
  CONSTRAINT SyncopeUser_ibfk_2 FOREIGN KEY (SECURITYQUESTION_ID)
           REFERENCES SecurityQuestion (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

The relevant column plainAttrs contains the users attibutes as JSON string. One example might be [{"uniqueValue":{"stringValue":"123456789"},"schema":"lockSystemId"},{"values":[{"stringValue":"Bergmann"}],"schema":"familyName"}]. The following indexes are defined on SyncopeUser:

Table      |Non_unique|Key_name            |Seq_in_index|Column_name        |Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|Ignored|
-----------+----------+--------------------+------------+-------------------+---------+-----------+--------+------+----+----------+-------+-------------+-------+
SyncopeUser|         0|PRIMARY             |           1|id                 |A        |     149635|        |      |    |BTREE     |       |             |NO     |
SyncopeUser|         0|U_SYNCPSR_USERNAME  |           1|username           |A        |     149635|        |      |YES |BTREE     |       |             |NO     |
SyncopeUser|         0|SyncopeUser_username|           1|username           |A        |     149635|        |      |YES |BTREE     |       |             |NO     |
SyncopeUser|         1|SECURITYQUESTION_ID |           1|SECURITYQUESTION_ID|A        |          1|        |      |YES |BTREE     |       |             |NO     |
SyncopeUser|         1|SyncopeUser_realm_id|           1|REALM_ID           |A        |          1|        |      |YES |BTREE     |       |             |NO     |

As most normal users have over 15 attributes the user_search_attr view contains over 2M rows. The servers are VMs with currently 8 GB of RAM and 6 CPUs but this can be changed. We are currently running version 11.4.7 of MariaDB in a galera cluster of 3 Servers. Syncope is only connected to one of the servers as this cluster is also used for other applications and we don't want to influnce these other applications. Here are some of the IMHO relevant configuration options:

[mysqld]
aria_pagecache_buffer_size = 32M
basedir = /usr
bind-address = ::
binlog_format = ROW
character_set_server = utf8
collation_server = utf8_general_ci
innodb_adaptive_hash_index = OFF
innodb_autoinc_lock_mode = 2
innodb_buffer_pool_instances = 1
innodb_buffer_pool_size = 4096M
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 16MB
innodb_log_file_size = 512M
key_buffer_size = 1M
log-error = /var/log/mariadb/mariadb.log
log_queries_not_using_indexes = OFF
long_query_time = 4.0
max_allowed_packet = 128M
max_binlog_size = 100M
max_connections = 400
max_heap_table_size = 256M
performance_schema = ON
query_cache_limit = 16M
query_cache_size = 0
query_cache_type = OFF
skip-external-locking
skip-name-resolve = 1
slow_query_log = 0
slow_query_log_file = /var/log/mariadb/slow.log
sort_buffer_size = 4M

table_definition_cache = 800
table_open_cache = 1000
thread_cache_size = 8
thread_stack = 256K
tmp_table_size = 256M
wsrep_on = ON
wsrep_provider = /usr/lib64/galera-4/libgalera_smm.so

As most queries only search for active or pending users one option that I have not tried is indexing or partitioning the table for the status column. Currently we are not in the position of changing the design of the database or even switching to another IDM sollution. We hope to find optimazion potential on the DB layer or maybe some improvements on some queries.

Question : How can I enable partition elimination so it runs faster and scans fewer partitions? I'm working with a large partitioned table and trying to improve performance by ensuring only the relevant partition is scanned. However, the execution plan shows all 590 partitions are being accessed. Environment: azure sql paas Table is partitioned by Created using RANGE RIGHT Clustered primary key on (Id, Created) The procedure: CREATE PROCEDURE dbo.GetNextPage ( @Next BIGINT, @Limit INT ) AS BEGIN SELECT TOP (@Limit) [RowId], [RecordKey], [GroupId], [SourceSystem], [SchemaVersion], [RecordType], [Payload], [CreatedDate] FROM dbo.Event WHERE RowId > @Next ORDER BY RowId ASC END The query returns results, but the execution plan shows it seeks all partitions.

The below query is taking about 1 second to run SELECT taxi_driver_mapping.*, concat("dpk00", taxi_driver_mapping.mapping_payment_plan_id) AS package_text, people.*, taxi.*, company.company_name, country.country_name, state.state_name, city.city_name, peoplecreated.name AS created_name, peopleupdated.name AS updated_name, device_owner FROM taxi_driver_mapping LEFT JOIN company ON taxi_driver_mapping.mapping_companyid = company.cid LEFT JOIN taxi ON taxi_driver_mapping.mapping_taxiid = taxi.taxi_id LEFT JOIN country ON taxi_driver_mapping.mapping_countryid = country.country_id LEFT JOIN state ON taxi_driver_mapping.mapping_stateid = state.state_id LEFT JOIN city ON taxi_driver_mapping.mapping_cityid = city.city_id LEFT JOIN people ON taxi_driver_mapping.mapping_driverid = people.id LEFT JOIN people AS peoplecreated ON taxi_driver_mapping.mapping_createdby = peoplecreated.id LEFT JOIN people AS peopleupdated ON taxi_driver_mapping.mapping_updatedby = peopleupdated.id LEFT JOIN driver_information ON taxi_driver_mapping.mapping_driverid = driver_information.driver_id WHERE (people.name LIKE '%abm1173%' OR people.lastname LIKE '%abm1173%' OR people.email LIKE '%abm1173%' OR company.company_name LIKE '%abm1173%' OR people.phone LIKE '%abm1173%' OR people.id LIKE '%abm1173%' OR people.username LIKE '%abm1173%' OR taxi.taxi_no LIKE '%abm1173%') ORDER BY mapping_id DESC limit 10 offset 0 Below is the explain plan for it: +----+-------------+---------------------+--------+------------------+---------+---------+----------------------------------------------+-------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+--------+------------------+---------+---------+----------------------------------------------+-------+----------------------------------------------------+ | 1 | SIMPLE | taxi_driver_mapping | ALL | NULL | NULL | NULL | NULL | 78718 | Using temporary; Using filesort | | 1 | SIMPLE | company | eq_ref | PRIMARY | PRIMARY | 4 | Eztaxi.taxi_driver_mapping.mapping_companyid | 1 | NULL | | 1 | SIMPLE | taxi | eq_ref | PRIMARY | PRIMARY | 4 | Eztaxi.taxi_driver_mapping.mapping_taxiid | 1 | NULL | | 1 | SIMPLE | country | eq_ref | PRIMARY | PRIMARY | 4 | Eztaxi.taxi_driver_mapping.mapping_countryid | 1 | NULL | | 1 | SIMPLE | state | ALL | PRIMARY,state_id | NULL | NULL | NULL | 3 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | city | ALL | PRIMARY | NULL | NULL | NULL | 2 | Using where; Using join buffer (Block Nested Loop) | | 1 | SIMPLE | people | eq_ref | PRIMARY | PRIMARY | 4 | Eztaxi.taxi_driver_mapping.mapping_driverid | 1 | Using where | | 1 | SIMPLE | peoplecreated | eq_ref | PRIMARY | PRIMARY | 4 | Eztaxi.taxi_driver_mapping.mapping_createdby | 1 | NULL | | 1 | SIMPLE | peopleupdated | eq_ref | PRIMARY | PRIMARY | 4 | Eztaxi.taxi_driver_mapping.mapping_updatedby | 1 | NULL | | 1 | SIMPLE | driver_information | eq_ref | PRIMARY | PRIMARY | 4 | Eztaxi.taxi_driver_mapping.mapping_driverid | 1 | NULL | +----+-------------+---------------------+--------+------------------+---------+---------+----------------------------------------------+-------+----------------------------------------------------+ Below is the table definition for taxi_driver_mapping table: | taxi_driver_mapping | CREATE TABLE taxi_driver_mapping ( mapping_id int(100) NOT NULL AUTO_INCREMENT, mapping_driverid int(100) NOT NULL, mapping_taxiid int(100) NOT NULL, mapping_taxi_model_id int(11) NOT NULL, mapping_companyid int(100) NOT NULL, mapping_countryid int(100) NOT NULL, mapping_stateid int(100) NOT NULL, mapping_cityid int(100) NOT NULL, mapping_startdate timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', mapping_enddate timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', mapping_createdby int(100) NOT NULL, mapping_createdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, mapping_status varchar(3) NOT NULL DEFAULT 'A', mapping_updatedby int(11) DEFAULT NULL, mapping_updatedate timestamp NULL DEFAULT NULL, mapping_payment_plan_id int(11) NOT NULL DEFAULT '0', PRIMARY KEY (mapping_id), KEY ndx_mapping_driverid (mapping_driverid), KEY ndx_mapping_taxiid (mapping_taxiid), KEY ndx_driver_status_taxi_cid (mapping_driverid,mapping_status,mapping_taxiid,mapping_companyid), KEY idx_test (mapping_status,mapping_driverid,mapping_taxiid) ) ENGINE=InnoDB AUTO_INCREMENT=123027 DEFAULT CHARSET=latin1 | Table definition for company table: | company | CREATE TABLE company ( cid int(100) NOT NULL AUTO_INCREMENT, company_name varchar(250) NOT NULL, company_address varchar(250) NOT NULL, current_location text NOT NULL, latitude varchar(250) NOT NULL, longitude varchar(250) NOT NULL, bankname varchar(100) NOT NULL, bankaccount_no varchar(250) NOT NULL, company_country int(11) NOT NULL, company_state int(11) NOT NULL, company_city int(11) NOT NULL, header_bgcolor varchar(25) NOT NULL, menu_color varchar(25) NOT NULL, mouseover_color varchar(25) NOT NULL, time_zone varchar(250) NOT NULL, userid int(11) NOT NULL, company_status varchar(3) NOT NULL DEFAULT 'A', company_type enum('0','1','2','4') NOT NULL DEFAULT '0' COMMENT '0-Company , 1-Kiosk , 2-Corporate 4-Restaurant', drivers_count int(11) NOT NULL DEFAULT '500', created_by int(11) DEFAULT NULL, created_at datetime DEFAULT NULL, updated_by int(11) DEFAULT NULL, updated_at datetime DEFAULT NULL, PRIMARY KEY (cid) ) ENGINE=InnoDB AUTO_INCREMENT=4122 DEFAULT CHARSET=latin1 | I thought that the column mapping_companyid used in the first left join not being indexed is the reason for full table scan. But even after adding the index to it, the query is taking 1 second. Can someone please let me know what could be the issue here? Let me know if any more info. is needed. Thanks

I need to delete rows from a table based on what is present in a temporary table. For me, both of these statements work : DELETE from main_table where id in (select deletable_id from temporary_table); and DELETE main_table from main_table join temporary_table on main_table.id = temporary_table.deletable_id; Which of the two is advisable to use given the fact that main_table will be having a billion of rows and the temporary will have a few thousands.

I have a recursive query like this:

with recursive PCte(id) as 
(
    select p1.id from Product p1 where p1.parent_id is null and p1.serial_number in
    ('123', '124','125', 'n')
    union all
    select p2.id from Product p2 inner join PCte cte on p2.parent_id = cte.id
) 
select * 
from Product p 
left join Product psub on psub.parent_id = p.id 
where p.id in (select c.id from PCte c)

This query have a slow performance with a large number of children, are there any possible optimization ways? If it is possible to replace the recursive CTE with a temporary table, I would like to see an example, thanks a lot.

we have system that quite a lot and often asks database about: SELECT TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, NUMERIC_PRECISION, CHARACTER_MAXIMUM_LENGTH, NUMERIC_SCALE, DATA_TYPE, IS_NULLABLE FROM "information_schema"."columns" WHERE TABLE_NAME = 'some_instances' AND TABLE_SCHEMA = 'public' ORDER BY ORDINAL_POSITION Time to execute this query is 10s+, and it ruins overall performance "information_schema"."columns" contains more than 5m records Postgres version is 9.6 How to speed up such query? UPD. cant paste here execution plan as it's off stackexchange limitation. here is the link: https://github.com/mkdel/code_pile/blob/main/EXPLAIN_ANALYZE_BUFFERS

I have a stored procedure that runs daily. Most days it completes in about 20 minutes, some days it takes an hour. I'm trying to find the cause so I can get it to be more consistent. I looked for other jobs/processes that could be running at the same time and competing for resources, but did not see anything promising. Ran BlitzCache. First result is Plan Cache Instability with 100% plans created in the past 24 hours. But the server was just patched and rebooted last night so I think that is to be expected. The second result is Plan Warnings (warning detected in execution plan). Checked the Execution Plan and saw a recommendation for a missing index. It wants an index on PaidDate and then Include all the other columns in the table. I find the sql recommendations for indexes to be hit/miss, plus this database is a backup from a vendor and I do not have direct control to add indexes at will. Also, as long as I'm not clearing the plan cache I'm doubtful that a missing index would cause this type of inconsistency. Third is Non-SARGable queries. I've included what I believe is the most resource intensive part of the query. The final step is to INSERT into a table from the temp table. There is a CASE statement, but we use CASE statements a lot and do not have this kind of variability in other stored procedures. DROP TABLE IF EXISTS #PPNUnpivotCCO; SELECT CCOMemberID = xw.CCOMemberID, MemberID = e.PrimeID, RateCD = e.RateCode, ContractServiceAreaCD = e.ContractServiceAreaCode, EligCat = e.EligCat, ContractRateAMT = Convert(decimal(15,2),e.ContractRate), Method = e.Method, CCOPremiumAMT = Convert(decimal(15,2),e.PremiumAmount), ---Medical Medical_PartnerCD = med.PartnerCD, Medical_PartnerNM = med.PartnerNM, MedBaseAmt = Convert(decimal(15,2),e.MedBaseAmt), MedRiskRateAmt = Convert(decimal(15,6),e.MedRiskRate), --Numeric length extended to 6 past the decimal C.S. MedAdjAmt = Convert(decimal(15,2),e.MedAdjAmt), AdminWHMedAmt = Convert(decimal(15,2),e.AdminWHMed), StrategicWHMedAmt = Convert(decimal(15,2),e.StrategicWHMed), CommunityWHMedAmt = Convert(decimal(15,2),e.CommunityWHMed), ReserveWHMedAmt = Convert(decimal(15,2),e.ReserveWHMed), MedFinalAMT= Convert(decimal(15,2),e.MedFinal), ---Mental Health Behavioral_PartnerCD = beh.PartnerCD, Behavioral_PartnerNM = beh.PartnerNM, MHBaseAmt = Convert(decimal(15,2),e.MHBaseAmt), MHRiskRateAmt = Convert(decimal(15,6),e.MHRiskRate), --Numeric length extended to 6 past the decimal C.S. MHAdjAmt = Convert(decimal(15,2),e.MHAdjAmt), AdminWHMHAmt = Convert(decimal(15,2),e.AdminWHMH), StrategicWHMHAmt = Convert(decimal(15,2),e.StrategicWHMH), CommunityWHMHAmt = Convert(decimal(15,2),e.CommunityWHMH), ReserveWHMHAmt = Convert(decimal(15,2),e.ReserveWHMH), MHFinalAmt = Convert(decimal(15,2),e.MHFinal), ---NEMT NEMTBaseAmt = Convert(decimal(15,2),e.NEMTBaseAmt), NEMTWithholdAmt = Convert(decimal(15,2),e.NEMTWithhold), StrategicWHNEMTAmt = Convert(decimal(15,2),e.StrategicWHNEMT), CommunityWHNEMTAmt = Convert(decimal(15,2),e.CommunityWHNEMT), NEMTFinalAmt = Convert(decimal(15,2),e.NEMTFinal), ---Dental Dental_PartnerCD = den.PartnerCD, Dental_PartnerNM = den.PartnerNM, DenBaseAmt = Convert(decimal(15,2),e.DenBaseAmt), DenRiskRateAmt = Convert(decimal(15,2),e.DenRiskRate), DenAdjAmt = Convert(decimal(15,2),e.DenAdjAmt), AdminWHDenAmt = Convert(decimal(15,2),e.AdminWHDen), StrategicWHDenAmt = Convert(decimal(15,2),e.StrategicWHDen), CommunityWHDenAmt = Convert(decimal(15,2),e.CommunityWHDen), ReserveWHDenAmt = Convert(decimal(15,2),e.ReserveWHDen), performanceWHDenAmt = Convert(decimal(15,2),e.performanceWHDen), DenFinalAmt = Convert(decimal(15,2),e.DenFinal), MCOTaxAmt = Convert(decimal(15,2),e.MCOTax), HRAAmt = Convert(decimal(15,2),e.HRA), RevenueRAEFinalAMT = COALESCE(e.MedFinal, 0) + COALESCE(e.DenFinal, 0) + COALESCE(e.MHFinal, 0), MemberMonth= CASE WHEN e.RateCode COLLATE database_default IS NULL THEN 0 WHEN e.RateCode COLLATE database_default IN ( SELECT RateCD FROM Reference.Reference.Rate r WHERE r.ExcludeFromMM = 'Y') THEN 0 WHEN e.Prorate > 1 THEN 1 ELSE ROUND(e.Prorate, 5) END , BenefitMonth = DATEFROMPARTS(YEAR(e.BenefitBegin), MONTH(e.BenefitBegin), 1) , StartDT = Convert(date,e.BenefitBegin) , EndDT = Convert(date,e.BenefitEnd), PaidDT = Convert(date,e.PaidDate), PremiumPaymentNotificationID INTO #PPNUnpivotCCO --select e.* FROM VendorDB.dbo.Allocation e INNER JOIN Reference.Reference.CCOIdToMemberID xw -- only active members (solves the issue of multiple MemberId for a single COMemberid ON xw.CCOMemberID = e.ccoMemberID AND xw.MemberID COLLATE database_default = e.PrimeID AND xw.ActiveFLG = 1 LEFT JOIN Reference.Reference.Rate r---updated to left join, for some of the kickers, we do not have the rate codes set up in reference. ON e.RateCode COLLATE database_default = r.RateCD LEFT JOIN Reference.Reference.Partners Med ON ( e.MedPlan COLLATE database_default = Med.PartnerNM OR e.MedPlan COLLATE database_default = Med.PartnerAlt1NM OR e.MedPlan COLLATE database_default = Med.PartnerAlt2NM ) AND Med.ActiveFLG = 1 LEFT JOIN Reference.Reference.Partners beh ON ( e.MHPlan COLLATE database_default = beh.PartnerNM OR e.MHPlan COLLATE database_default = beh.PartnerAlt1NM OR e.MHPlan COLLATE database_default = beh.PartnerAlt2NM ) AND beh.ActiveFLG = 1 LEFT JOIN Reference.Reference.Partners den ON ( e.DenPlan COLLATE database_default = den.PartnerNM OR e.DenPlan COLLATE database_default = den.PartnerAlt1NM OR e.DenPlan COLLATE database_default = den.PartnerAlt2NM ) AND den.ActiveFLG = 1 WHERE 1=1 and Convert(date,e.PaidDate) >= Convert(date,DateAdd(Year,-2,GETDATE())) ---Only pulling paid the last 2 years ; DELETE Enrollment.PremiumCCOMemberMonth where PaidDT >= Convert(date,DateAdd(Year,-2,GETDATE())); INSERT INTO Enrollment.PremiumCCOMemberMonth (CCOMemberID, MemberID, RateCD, ContractServiceAreaCD, EligCat, ContractRateAMT, Method, CCOPremiumAMT, Medical_PartnerCD, Medical_PartnerNM, MedBaseAmt, MedRiskRateAmt, MedAdjAmt, AdminWHMedAmt, StrategicWHMedAmt, CommunityWHMedAmt, ReserveWHMedAmt, MedFinalAmt, Behavioral_PartnerCD, Behavioral_PartnerNM, MHBaseAmt, MHRiskRateAmt, MHAdjAmt, AdminWHMHAmt, StrategicWHMHAmt, CommunityWHMHAmt, ReserveWHMHAmt, MHFinalAmt, NEMTBaseAmt, NEMTWithholdAmt, StrategicWHNEMTAmt, CommunityWHNEMTAmt, NEMTFinalAmt, Dental_PartnerCD, Dental_PartnerNM, DenBaseAmt, DenRiskRateAmt, DenAdjAmt, AdminWHDenAmt, StrategicWHDenAmt, CommunityWHDenAmt, ReserveWHDenAmt, performanceWHDenAmt, DenFinalAmt, MCOTaxAmt, HRAAmt, RevenueRAEFinalAMT, MemberMonth, BenefitMonth, StartDT, EndDT, PaidDT, PremiumPaymentNotificationID ) SELECT CCOMemberID, MemberID, RateCD, ContractServiceAreaCD, EligCat, ContractRateAMT, Method, CCOPremiumAMT, Medical_PartnerCD, Medical_PartnerNM, MedBaseAmt, MedRiskRateAmt, MedAdjAmt, AdminWHMedAmt, StrategicWHMedAmt, CommunityWHMedAmt, ReserveWHMedAmt, MedFinalAmt, Behavioral_PartnerCD, Behavioral_PartnerNM, MHBaseAmt, MHRiskRateAmt, MHAdjAmt, AdminWHMHAmt, StrategicWHMHAmt, CommunityWHMHAmt, ReserveWHMHAmt, MHFinalAmt, NEMTBaseAmt, NEMTWithholdAmt, StrategicWHNEMTAmt, CommunityWHNEMTAmt, NEMTFinalAmt, Dental_PartnerCD, Dental_PartnerNM, DenBaseAmt, DenRiskRateAmt, DenAdjAmt, AdminWHDenAmt, StrategicWHDenAmt, CommunityWHDenAmt, ReserveWHDenAmt, performanceWHDenAmt, DenFinalAmt, MCOTaxAmt, HRAAmt, RevenueRAEFinalAMT, MemberMonth, BenefitMonth, StartDT, EndDT, PaidDT, PremiumPaymentNotificationID FROM #PPNUnpivotCCO Fourth is regarding implicit conversions. > The column [xw].[MemberID] has a data type of varchar which caused > implicit conversion on the column [e].[PrimeID]. They are both varchar, so I'm guessing the implicit conversion is a result of the COLLATE needed because this database has a different collation than the other databases on the server. Does any of this look like the probable cause or do I need to dig in other places?

I'm new to MySQL and have written the following query (see below). **PROBLEM:** The query returns accurate data but takes about 40 seconds to execute. However, When I remove the ORDER BY part, it takes 0.06sec. I have indexes on each of the ORDER BY columns and am unsure what else to do. If anybody can rewrite/recommend changes it'd be greatly informative. **CODE:** SELECT DISTINCT username FROM users WHERE in_progress = 0 AND scraped_from IN (SELECT DISTINCT username FROM source_accounts WHERE group_users = 'New Users' AND (type = 'users' OR type = 'both') AND use = '1') AND username NOT IN (SELECT user_tofollow FROM follow_history WHERE owner_account = 'admin') ORDER BY real_user DESC, IF((last_used) IS NULL,'0','1'), IF((last_update)>3,'1','0'), DATE(last_used), IF((user_ratio)>100,'1','0') LIMIT 1000; **EXPLAIN:** 1 PRIMARY scraped_users ref idx1,scraped_from,in_progress,username in_progress 1 const 687025 Using where; Using temporary; Using filesort 1 PRIMARY eq_ref 767 userstack.users.scraped_from 1 Distinct 2 MATERIALIZED source_accounts ref username,group_users,type,use group_users 767 const 48 Using index condition; Using where; Distinct 3 DEPENDENT SUBQUERY follow_history index_subquery user_tofollow user_tofollow 767 func 1 Using where

I have some production code and am setting up a new development environment. I found some pages were extremely slow to load. I profiled the code and found the slow query. This query is taking **75 seconds** to run in the new dev environment: SELECT orders.*, users.user_flag FROM orders LEFT JOIN users ON users.id = orders.user_id WHERE cancelled = '0' AND is_estimate = '0' ORDER BY orders.updated_date DESC, orders.order_date DESC, orders.creation_date DESC LIMIT 30; - It is a new installation of MySQL via MAMP Pro on a Mac - The version of MySQL is **5.7.25** ... **Exactly the same query** on **exactly the same database schema** takes only **24 ms** on my old machine which has this version: mysql Ver 14.14 Distrib 5.7.18, for osx10.12 (x86_64) using EditLine wrapper So presumably something about the configuration of MySQL on the new machine is causing this slow query with a join and ORDER BY. **What my.cnf settings should I be looking for?** --- **Further tests** (I cannot change the code as it is production code but did these tests to narrow down the problem) Removing the JOIN brings the query time down to about **13ms** SELECT orders.* FROM orders WHERE cancelled = '0' AND is_estimate = '0' ORDER BY orders.updated_date DESC, orders.order_date DESC, orders.creation_date DESC LIMIT 30; Removing the ORDER BY statement brings the time down to about **200ms** (still too slow) SELECT orders.*, users.user_flag FROM orders LEFT JOIN users ON users.id = orders.user_id WHERE cancelled = '0' AND is_estimate = '0' LIMIT 30; (No JOIN or ORDER BY and it runs in 1.5ms) ---- The old MySQL config contains: [mysqld] # Only allow connections from localhost bind-address = 127.0.0.1 max_allowed_packet = 64M The new MySQL config has lots in it: # The following options will be passed to all MySQL clients [client] password = MAMP_root_password_MAMP #port = 9999 socket = /Applications/MAMP/tmp/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] #port = 9999 socket = /Applications/MAMP/tmp/mysql/mysql.sock key_buffer_size = 16M max_allowed_packet = 64M explicit_defaults_for_timestamp = 1 table_open_cache = 64 MAMP_bind-address_MAMP # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # MAMP_skip-networking_MAMP # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 secure_file_priv="" server-id = 1 innodb_buffer_pool_instances = 8 innodb_flush_method = O_DIRECT sync_binlog = 0 [mysqldump] password = MAMP_root_password_MAMP quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M myisam_sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout

The following Sqlite query has a triple self-join on table t: SELECT "update_variable", lhs_acc.name_suffix || ":" || rhs_var.name_suffix, op.span, op.path FROM t op JOIN t lhs_acc ON (lhs_acc.path GLOB op.path || "?*") JOIN t rhs_acc ON (rhs_acc.path GLOB op.path || "?*") JOIN t rhs_var ON (rhs_var.path GLOB op.path || "?*") WHERE (op.name_prefix = "assignment" AND lhs_acc.name_prefix = "assignment_lhs_identifier" AND rhs_acc.name_prefix = "assignment_rhs_atom" AND rhs_var.name_prefix = "assignment_rhs_atom" AND rhs_acc.name_suffix != rhs_var.name_suffix AND lhs_acc.name_suffix = rhs_acc.name_suffix) OR (op.name_prefix = "augmented_assignment" AND lhs_acc.name_prefix = "assignment_lhs_identifier" AND rhs_acc.name_prefix = "assignment_rhs_atom" AND rhs_var.name_prefix = "assignment_rhs_atom") OR (op.name_prefix = "method_call" AND rhs_acc.name_prefix = "method_call_name" AND lhs_acc.name_prefix = "method_call_object" AND rhs_var.name_prefix = "call_argument" AND rhs_acc.name_suffix != rhs_var.name_suffix AND rhs_acc.name_suffix REGEXP "(append|extend|insert|add|update)$") GROUP BY op.span, lhs_acc.name, rhs_var.name It is far too slow for my application. I can accelerate it enough by restricting each instance of t to the lines I am interested in, which somehow duplicates the WHERE conditions. So, the FROM clause becomes: FROM (SELECT * FROM t WHERE t.name_prefix IN ("assignment", "augmented_assignment", "method_call")) op JOIN (SELECT * FROM t WHERE t.name_prefix IN ("assignment_lhs_identifier", "method_call_object") ) lhs_acc ON (lhs_acc.path GLOB op.path || "?*") JOIN (SELECT * FROM t WHERE t.name_prefix IN ("assignment_rhs_atom", "method_call_name") ) rhs_acc ON (rhs_acc.path GLOB op.path || "?*") JOIN (SELECT * FROM t WHERE t.name_prefix IN ("assignment_rhs_atom", "call_argument") ) rhs_var ON (rhs_var.path GLOB op.path || "?*") Is there some technique which would lead to a less verbose / ugly / redundant query ? --- Edit: The test duplication (but not the ugliness) can be avoided as follows: SELECT "update_variable", lhs_acc.name_suffix || ":" || rhs_var.name_suffix, op.span, op.path FROM (SELECT * FROM t WHERE t.name_prefix IN ("assignment", "augmented_assignment", "method_call")) op JOIN t lhs_acc ON (lhs_acc.name_prefix = (CASE op.name_prefix WHEN "method_call" THEN "method_call_object" ELSE "assignment_lhs_identifier" END) AND (lhs_acc.path GLOB op.path || "?*")) JOIN t rhs_acc ON (rhs_acc.name_prefix = (CASE op.name_prefix WHEN "method_call" THEN "method_call_name" ELSE "assignment_rhs_atom" END) AND (rhs_acc.path GLOB op.path || "?*")) JOIN t rhs_var ON (rhs_var.name_prefix = (CASE op.name_prefix WHEN "method_call" THEN "call_argument" ELSE "assignment_rhs_atom" END) AND (rhs_var.path GLOB op.path || "?*")) WHERE op.name_prefix = "augmented_assignment" OR (op.name_prefix = "assignment" AND lhs_acc.name_suffix = rhs_acc.name_suffix AND rhs_acc.name_suffix != rhs_var.name_suffix) OR (op.name_prefix = "method_call" AND rhs_acc.name_suffix REGEXP "append|extend|insert|add|update)$" AND rhs_acc.name_suffix != rhs_var.name_suffix) GROUP BY op.span, lhs_acc.name, rhs_var.name

I've an online game the game server execute more than 1M queries [ SELECT , REPLACE , UPDATE ] every 10 sec the problem the memory usage still increasing i dun know why ?? i mean if we suppose that memory usage at the moment is 2000M and it increases to 3000M when the game server execute the queries . why don't the memory usage back again to 2000M or around value after excuting ??? .. it stills increasing and increasing This Problem Makes The Following Error Occur 2016-7-14 16:17:52 -- ERROR: Database ExecuteSQL(MySQL client run out of memory) occur mysql error(SELECT * FROM cq_tutor WHERE tutor_id = 16090848 AND Betrayal_flag = 0). 2016-7-14 16:17:52 -- ERROR: Database ExecuteSQL(Lost connection to MySQL server during query) occur mysql error(SELECT * FROM cq_tutor WHERE Student_id = 16090848 AND Betrayal_flag = 0 LIMIT 1). btw when i restart the game server the memory usage back to 2000M and still increasing too .. I want to know the reason of memory usage increasing i stopped caching of mysql but the same problem occur mysql version is 4.0.17 the client i use is winmysqladmin 1.4 mysql> SHOW VARIABLES; +------------------------------+----------------------------------- | Variable_name | Value | +------------------------------+----------------------------------- | back_log | 50 | basedir | E:\ZFSERVER\MYSQL\ | binlog_cache_size | 32768 | bulk_insert_buffer_size | 8388608 | character_set | latin1 | character_sets | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 | | concurrent_insert | ON | connect_timeout | 5 | convert_character_set | | datadir | E:\ZFSERVER\MYSQL\data\ | default_week_format | 0 | delay_key_write | ON | delayed_insert_limit | 100 | delayed_insert_timeout | 300 | delayed_queue_size | 1000 | flush | OFF | flush_time | 1800 | ft_boolean_syntax | + -> SHOW STATUS; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Aborted_clients | 36 | | Aborted_connects | 0 | | Bytes_received | 41352179 | | Bytes_sent | 84359720 | | Com_admin_commands | 181 | | Com_alter_table | 2 | | Com_analyze | 0 | | Com_backup_table | 0 | | Com_begin | 0 | | Com_change_db | 64 | | Com_change_master | 0 | | Com_check | 1 | | Com_commit | 0 | | Com_create_db | 0 | | Com_create_function | 0 | | Com_create_index | 0 | | Com_create_table | 252 | | Com_delete | 70 | | Com_delete_multi | 0 | | Com_drop_db | 0 | | Com_drop_function | 0 | | Com_drop_index | 0 | | Com_drop_table | 276 | | Com_flush | 0 | | Com_grant | 0 | | Com_ha_close | 0 | | Com_ha_open | 0 | | Com_ha_read | 0 | | Com_insert | 292241 | | Com_insert_select | 0 | | Com_kill | 0 | | Com_load | 0 | | Com_load_master_data | 0 | | Com_load_master_table | 0 | | Com_lock_tables | 0 | | Com_optimize | 2 | | Com_purge | 0 | | Com_rename_table | 0 | | Com_repair | 9 | | Com_replace | 6 | | Com_replace_select | 0 | | Com_reset | 0 | | Com_restore_table | 0 | | Com_revoke | 0 | | Com_rollback | 0 | | Com_savepoint | 0 | | Com_select | 15736 | | Com_set_option | 12 | | Com_show_binlog_events | 0 | | Com_show_binlogs | 0 | | Com_show_create | 25 | | Com_show_databases | 27 | | Com_show_fields | 2760 | | Com_show_grants | 0 | | Com_show_keys | 9 | | Com_show_logs | 0 | | Com_show_master_status | 0 | | Com_show_new_master | 0 | | Com_show_open_tables | 0 | | Com_show_processlist | 2 | | Com_show_slave_hosts | 0 | | Com_show_slave_status | 0 | | Com_show_status | 1934 | | Com_show_innodb_status | 0 | | Com_show_tables | 57 | | Com_show_variables | 59 | | Com_slave_start | 0 | | Com_slave_stop | 0 | | Com_truncate | 0 | | Com_unlock_tables | 0 | | Com_update | 2138 | | Connections | 207 | | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 0 | | Created_tmp_files | 0 | | Delayed_insert_threads | 0 | | Delayed_writes | 0 | | Delayed_errors | 0 | | Flush_commands | 1 | | Handler_commit | 4 | | Handler_delete | 1 | | Handler_read_first | 3363 | | Handler_read_key | 36141 | | Handler_read_next | 68771 | | Handler_read_prev | 0 | | Handler_read_rnd | 132 | | Handler_read_rnd_next | 3039359 | | Handler_rollback | 63 | | Handler_update | 10 | | Handler_write | 292202 | | Key_blocks_used | 2157 | | Key_read_requests | 459308 | | Key_reads | 352 | | Key_write_requests | 197090 | | Key_writes | 197085 | | Max_used_connections | 24 | | Not_flushed_key_blocks | 0 | | Not_flushed_delayed_rows | 0 | | Open_tables | 0 | | Open_files | 0 | | Open_streams | 0 | | Opened_tables | 1906 | | Questions | 315904 | | Qcache_queries_in_cache | 0 | | Qcache_inserts | 0 | | Qcache_hits | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 0 | | Qcache_free_memory | 0 | | Qcache_free_blocks | 0 | | Qcache_total_blocks | 0 | | Rpl_status | NULL | | Select_full_join | 0 | | Select_full_range_join | 0 | | Select_range | 129 | | Select_range_check | 0 | | Select_scan | 2691 | | Slave_open_temp_tables | 0 | | Slave_running | OFF | | Slow_launch_threads | 0 | | Slow_queries | 0 | | Sort_merge_passes | 0 | | Sort_range | 22 | | Sort_rows | 132 | | Sort_scan | 6 | | Table_locks_immediate | 311455 | | Table_locks_waited | 0 | | Threads_cached | 0 | | Threads_created | 206 | | Threads_connected | 4 | | Threads_running | 1 | | Uptime | 21423 | +-------------------------+----------+ ----------------------------------------------- select che from arenaa where mapid=81513 and status=1 select nameb from arenac where mapid=81529 DELETE FROM arenaa where mapid=81529 select nameb from arenac where mapid=81530 select che from arenaa where mapid=81501 and status=2 REPLACE INTO arenac set mapid=\"81502\",nameb=\"%user_name\" select namea from arenaa where mapid=81505 ------------------------------------------------ -- ---------------------------- -- Table structure for arenaa -- ---------------------------- DROP TABLE IF EXISTS arenaa; CREATE TABLE arenaa ( mapid int(100) NOT NULL default '0', namea varchar(100) NOT NULL default '0', nameb varchar(100) NOT NULL default '0', status int(100) NOT NULL default '0', che int(100) NOT NULL default '1', UNIQUE KEY imapid (mapid) ) TYPE=MyISAM; -- ---------------------------- -- Records of arenaa -- ---------------------------- ############################################# SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for arenac -- ---------------------------- DROP TABLE IF EXISTS arenac; CREATE TABLE arenac ( mapid int(100) NOT NULL default '0', nameb varchar(100) NOT NULL default '0', UNIQUE KEY imapid (mapid) ) TYPE=MyISAM; -- ---------------------------- -- Records of arenac -- ---------------------------- thanks for reading ,,

I have two tables on Mysql 8 Server. One of is *products (InnoDB)* other is *product_filter (InnoDB)*. I would like to take count for pagination but it takes more than 10 seconds every time. I had lots of investigation about query. I try to improve query. Try use *IN*, *Sub Query*, *Join* instead of *where exist* but I cannot improve anyway. Products table has ~241k and product_filter table has ~90m rows. Here is Query: select count(*) as aggregate from products where is_approved = 1 and published = 1 and is_b2b = 0 and (Timestamp(last_sale_date) > '2020-09-25 08:16:26' or last_sale_date is null ) and exists ( SELECT * from product_filter where products.id = product_filter.product_id and brand_id = 35 and brand_model_id = 781 ) and product_type_id = 1 and products.deleted_at is null; Here are Schemes and Indexes of Tables products table schema CREATE TABLE products ( id int unsigned NOT NULL AUTO_INCREMENT, product_type_id int unsigned NOT NULL DEFAULT '1', product_display_type_id int unsigned NOT NULL DEFAULT '1', parent_product_id int unsigned DEFAULT NULL, is_b2b tinyint(1) NOT NULL DEFAULT '0', description varchar(6000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, short_description varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, stock_quantity int NOT NULL DEFAULT '0', minimum_cart_quantity int NOT NULL DEFAULT '1', sku varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, merchant_id int unsigned DEFAULT NULL, price double(8,2) DEFAULT NULL, is_guaranteed tinyint(1) NOT NULL DEFAULT '0', guarantee_duration int NOT NULL DEFAULT '0', old_price double(8,2) DEFAULT NULL, currency_id int unsigned NOT NULL DEFAULT '1', currency_price double(8,2) DEFAULT NULL, currency_old_price double(8,2) DEFAULT NULL, estimated_shipping_date int DEFAULT NULL, free_shipping tinyint(1) DEFAULT NULL, published tinyint(1) NOT NULL DEFAULT '0', mark_as_new tinyint(1) NOT NULL DEFAULT '0', featured_product tinyint(1) NOT NULL DEFAULT '0', show_on_home_page tinyint(1) NOT NULL DEFAULT '0', manufacturer_id int unsigned DEFAULT NULL, name text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, images json DEFAULT NULL, height int DEFAULT NULL, width int DEFAULT NULL, length int DEFAULT NULL, weight int DEFAULT NULL, authenticity int NOT NULL, is_active tinyint(1) NOT NULL DEFAULT '1', is_approved tinyint(1) NOT NULL DEFAULT '1', list_order int NOT NULL DEFAULT '0', crawled_at timestamp NULL DEFAULT NULL, merged_at timestamp NULL DEFAULT NULL, merge_end tinyint(1) NOT NULL DEFAULT '0', merge_start tinyint(1) NOT NULL DEFAULT '0', last_sale_date timestamp NULL DEFAULT NULL, created_at timestamp NULL DEFAULT NULL, updated_at timestamp NULL DEFAULT NULL, deleted_at timestamp NULL DEFAULT NULL, product_condition int NOT NULL DEFAULT '1', slug varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, PRIMARY KEY (id), KEY products_manufacturer_id_foreign (manufacturer_id), KEY products_product_display_type_id_foreign (product_display_type_id), KEY products_parent_product_id_foreign (parent_product_id), KEY products_merchant_id_foreign (merchant_id), KEY products_b2b_approved_published_ls_date_ptype_id_d_at_IDX (is_b2b,is_approved,published,last_sale_date,product_type_id,deleted_at) USING BTREE, KEY products_currency_id_foreign (currency_id), KEY products_search_1_IDX (is_approved,published,is_active,is_b2b,last_sale_date,product_type_id,deleted_at) USING BTREE, KEY products_product_type_id_is_b2b_merchant_id_deleted_at_IDX (product_type_id,is_b2b,merchant_id,deleted_at) USING BTREE, KEY products_p_t_id-is_b-is_apr-pbh-is_acv-lst_sl-mrnt_id-dltt_IDX (product_type_id,is_b2b,is_approved,published,is_active,last_sale_date,merchant_id,deleted_at) USING BTREE, KEY products_list_order_IDX (list_order) USING BTREE, KEY products_search_2_IDX (id,is_approved,published,is_active,is_b2b,last_sale_date,product_type_id,deleted_at) USING BTREE, CONSTRAINT products_currency_id_foreign FOREIGN KEY (currency_id) REFERENCES currencies (id), CONSTRAINT products_manufacturer_id_foreign FOREIGN KEY (manufacturer_id) REFERENCES manufacturers (id), CONSTRAINT products_merchant_id_foreign FOREIGN KEY (merchant_id) REFERENCES users (id), CONSTRAINT products_parent_product_id_foreign FOREIGN KEY (parent_product_id) REFERENCES products (id), CONSTRAINT products_product_display_type_id_foreign FOREIGN KEY (product_display_type_id) REFERENCES product_display_types (id), CONSTRAINT products_product_type_id_foreign FOREIGN KEY (product_type_id) REFERENCES product_types (id) ) ENGINE=InnoDB AUTO_INCREMENT=282754 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; product_filter table schema: CREATE TABLE product_filter ( id bigint unsigned NOT NULL AUTO_INCREMENT, brand_id int unsigned NOT NULL, brand_model_id int unsigned NOT NULL, body_types_models_id int unsigned NOT NULL, body_types_models_years_id int unsigned NOT NULL, engine_years_id int unsigned NOT NULL, engine_years_horsepowers_id int unsigned NOT NULL, category_id int unsigned DEFAULT NULL, product_id int unsigned NOT NULL, product_vehicle_id int unsigned NOT NULL, created_at timestamp NULL DEFAULT NULL, updated_at timestamp NULL DEFAULT NULL, deleted_at timestamp NULL DEFAULT NULL, PRIMARY KEY (id), KEY product_filter_brand_id_IDX (brand_id) USING BTREE, KEY product_filter_body_types_models_FK (body_types_models_id), KEY product_filter_body_types_models_years_FK (body_types_models_years_id), KEY product_filter_brand_models_FK (brand_model_id), KEY product_filter_engine_years_FK (engine_years_id), KEY product_filter_engine_years_horsepowers_FK (engine_years_horsepowers_id), KEY product_filter_categories_FK (category_id), KEY product_filter_product_vehicles_FK (product_vehicle_id), KEY product_filter_brand_id_model_id_IDX (brand_id,brand_model_id) USING BTREE, KEY product_filter_brand_id_product_id_IDX (brand_id,product_id) USING BTREE, KEY product_filter_product_id_brand_id_IDX (product_id,brand_id) USING BTREE, KEY product_filter_search_1_IDX (product_id,brand_id,brand_model_id,body_types_models_id,body_types_models_years_id,engine_years_id,engine_years_horsepowers_id) USING BTREE, CONSTRAINT product_filter_body_types_models_FK FOREIGN KEY (body_types_models_id) REFERENCES body_types_models (id), CONSTRAINT product_filter_body_types_models_years_FK FOREIGN KEY (body_types_models_years_id) REFERENCES body_types_models_years (id), CONSTRAINT product_filter_brand_models_FK FOREIGN KEY (brand_model_id) REFERENCES brand_models (id), CONSTRAINT product_filter_brands_FK FOREIGN KEY (brand_id) REFERENCES brands (id), CONSTRAINT product_filter_categories_FK FOREIGN KEY (category_id) REFERENCES categories (id), CONSTRAINT product_filter_engine_years_FK FOREIGN KEY (engine_years_id) REFERENCES engine_years (id), CONSTRAINT product_filter_engine_years_horsepowers_FK FOREIGN KEY (engine_years_horsepowers_id) REFERENCES engine_years_horsepowers (id), CONSTRAINT product_filter_FK FOREIGN KEY (product_vehicle_id) REFERENCES product_vehicles (id), CONSTRAINT product_filter_products_FK FOREIGN KEY (product_id) REFERENCES products (id) ) ENGINE=InnoDB AUTO_INCREMENT=101854289 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; Query Explain Output: id|select_type|table |partitions|type|possible_keys |key |key_len|ref |rows |filtered|Extra | --|-----------|--------------|----------|----|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|---------------------------|-------|--------------------------------|------|--------|---------------------------------| 1|SIMPLE |products | |ref |PRIMARY,products_b2b_approved_published_ls_date_ptype_id_d_at_IDX,products_search_1_IDX,products_product_type_id_is_b2b_merchant_id_deleted_at_IDX,products_p_t_id-is_b-is_apr-pbh-is_acv-lst_sl-mrnt_id-dltt_IDX,products_search_2_IDX|products_search_1_IDX |2 |const,const |121055| 5|Using where; Using index | 1|SIMPLE |product_filter| |ref |product_filter_brand_id_IDX,product_filter_brand_models_FK,product_filter_brand_id_model_id_IDX,product_filter_brand_id_product_id_IDX,product_filter_product_id_brand_id_IDX,product_filter_search_1_IDX |product_filter_search_1_IDX|12 |otomodul.products.id,const,const| 58| 100|Using index; FirstMatch(products)| Query Explain Analyze Output: -> Aggregate: count(0) (actual time=1568.310..1568.310 rows=1 loops=1) -> Nested loop semijoin (cost=41715.82 rows=354199) (actual time=2.630..1566.071 rows=5575 loops=1) -> Filter: ((products.product_type_id = 1) and (products.is_b2b = 0) and ((cast(products.last_sale_date as datetime) > '2020-09-25 08:16:26') or (products.last_sale_date is null)) and (products.deleted_at is null)) (cost=745.59 rows=6053) (actual time=0.114..234.822 rows=190001 loops=1) -> Index lookup on products using products_search_1_IDX (is_approved=1, published=1) (cost=745.59 rows=121055) (actual time=0.108..163.109 rows=190014 loops=1) -> Index lookup on product_filter using product_filter_search_1_IDX (product_id=products.id, brand_id=35, brand_model_id=781) (cost=53.72 rows=59) (actual time=0.007..0.007 rows=0 loops=190001)

I have a very simple query which is randomly performing poorly in production. I have the EXPLAIN ANALYZE outputs for both the good case and the bad case. MySQL version is 8.0.31. This is the query: SELECT COUNT(distinct(u.Id)) FROM Users AS u LEFT JOIN Bots ON u.Id = Bots.UserId WHERE Bots.UserId IS NULL; Essentially, bots is a separate table of userIDs with some additional bot-specific columns. The query is finding out how many users are not bots. Schema:

------------------------------------------------------------------------------+
| Bots  | CREATE TABLE Bots (
  UserId varchar(26) NOT NULL,
  Description text,
  OwnerId varchar(190) DEFAULT NULL,
  LastIconUpdate bigint DEFAULT NULL,
  ... truncated
  PRIMARY KEY (UserId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

| Users | CREATE TABLE Users (
  Id varchar(26) NOT NULL,
  CreateAt bigint DEFAULT NULL,
  UpdateAt bigint DEFAULT NULL,
  DeleteAt bigint DEFAULT NULL,
  Username varchar(64) DEFAULT NULL,
  Password varchar(128) DEFAULT NULL,
  AuthData varchar(128) DEFAULT NULL,
  AuthService varchar(32) DEFAULT NULL,
  Email varchar(128) DEFAULT NULL,
  EmailVerified tinyint(1) DEFAULT NULL,
  Nickname varchar(64) DEFAULT NULL,
  FirstName varchar(64) DEFAULT NULL,
  LastName varchar(64) DEFAULT NULL,
  Position varchar(128) DEFAULT NULL,
   ... truncated
  PRIMARY KEY (Id),
  UNIQUE KEY Username (Username),
  UNIQUE KEY AuthData (AuthData),
  UNIQUE KEY Email (Email),
  KEY idx_users_update_at (UpdateAt),
  KEY idx_users_create_at (CreateAt),
  KEY idx_users_delete_at (DeleteAt),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

Users.Id is varchar(26) and a PK. For Bots, UserId is PK with same varchar(26). Ignore the distinct in the query for now. We don't actually need that and the problem at hand is unrelated to it. With that out of the way, let's get into the problem. The system has around 100K rows in Users and 10 rows in Bots. Most of the times, the query finishes in couple of ms. Here is an EXPLAIN output of the good case

Aggregate: count(distinct u.Id) (cost=71155.80 rows=1) (actual time=463.397..463.397 rows=1 loops=1)
    -> Filter: (bots.UserId is null) (cost=58476.70 rows=126791) (actual time=0.051..234.996 rows=131491 loops=1)
        -> Nested loop antijoin (cost=58476.70 rows=126791) (actual time=0.050..223.681 rows=131491 loops=1)
            -> Covering index scan on u using idx_users_update_at (cost=14099.85 rows=126791) (actual time=0.038..34.614 rows=131496 loops=1)
            -> Single-row covering index lookup on Bots using PRIMARY (UserId=u.Id) (cost=0.25 rows=1) (actual time=0.001..0.001 rows=0 loops=131496)

You can see that it converts the LEFT JOIN with IS NULL into an antijoin and takes around 400ms, and around 200ms is going into calculating the distinct. All good here. But here is the case with the bad execution:

Aggregate: count(distinct u.Id) (cost=71154.70 rows=1) (actual time=10298.826..10298.826 rows=1 loops=1)
    -> Filter: (bots.UserId is null) (cost=58475.80 rows=126789) (actual time=0.045..9853.564 rows=131489 loops=1) 
        -> Nested loop antijoin (cost=58475.80 rows=126789) (actual time=0.044..9840.607 rows=131489 loops=1) 
            -> Covering index scan on u using idx_users_update_at (cost=14099.65 rows=126789) (actual time=0.035..151.337 rows=131494 loops=1) 
            -> Single-row covering index lookup on Bots using PRIMARY (UserId=u.Id) (cost=0.25 rows=1) (actual time=0.074..0.074 rows=0 loops=131494)

You can see that the execution plan is exactly the same, but somehow the nested loop antijoin is now taking 9.8s rather than 200ms: actual time=0.044..9840.607. I have been scratching my head to find an explanation but unable to. And even more interestingly, this is the query that actually fixes the problem: EXPLAIN ANALYZE SELECT COUNT(distinct(u.Id)) FROM Users AS u WHERE u.Id NOT IN (SELECT UserId from Bots);

---+
| -> Aggregate: count(distinct u.Id)  (cost=203038.61 rows=1) (actual time=339.482..339.482 rows=1 loops=1)
    -> Nested loop antijoin  (cost=119428.11 rows=836105) (actual time=0.170..103.207 rows=154346 loops=1)
        -> Covering index scan on u using idx_users_update_at  (cost=19095.51 rows=167221) (actual time=0.092..30.890 rows=154351 loops=1)
        -> Single-row index lookup on  using  (UserId=u.Id)  (actual time=0.000..0.000 rows=0 loops=154351)
            -> Materialize with deduplication  (cost=1.25..1.25 rows=5) (actual time=0.056..0.056 rows=5 loops=1)
                -> Filter: (Bots.UserId is not null)  (cost=0.75 rows=5) (actual time=0.019..0.030 rows=5 loops=1)
                    -> Covering index scan on Bots using PRIMARY  (cost=0.75 rows=5) (actual time=0.017..0.027 rows=5 loops=1)

Effectively, this is being translated into the same thing isn't it? The sub-query gets materialized into a table, and then antijoined into Users again. But with this query, the runtime is always within the 300-400ms range and never regresses. What gives? Is this a known quirk of MySQL that I am unaware of?

I have java app that is using MySQL in the backend. I have the following table: A = int, B = varchar, C = timestamp A | B | C 1 | 100 | 2022-03-01 12:00:00 2 | 200 | 2022-03-01 12:00:01 3 | 100 | 2022-03-01 12:00:01 4 | 200 | 2022-03-01 12:00:02 5 | 600 | 2022-03-01 12:00:03 1 | 100 | 2022-03-01 12:00:06 5 | 700 | 2022-03-01 12:00:07 2 | 200 | 2022-03-01 12:00:08 9 | 100 | 2022-03-01 12:00:08 On every X seconds, query should be run, and it should process 5 records where column C > LAST_PROCESSED_TIMESTAMP. This LAST_PROCESSED_TIMESTAMP is updated after each run. What I need is - I want to select these 5 rows, but not to include the rows if columns A and B are going to repeat in some fetches that are going to happen in the future. Example: for table above: First run - select 5 1 | 100 | 2022-03-01 12:00:00 2 | 200 | 2022-03-01 12:00:01 = '2022-03-01 12:00:00' LIMIT 5 ) a LEFT JOIN ( SELECT A,B FROM TABLE WHERE C >= '2022-03-01 12:00:00' LIMIT 5, 18446744073709551615 ) b ON ( a.A=b.A AND a.B=b.B ) WHERE b.A IS NULL; and also (this one is probably NOT OK, since it will select MAX of C even if not in first 5, so for my example, it would include 2 | 200 | 2022-03-01 12:00:08 inside of the first run - not what I need): SELECT A, B, MAX(C) FROM TABLE WHERE C >= '2022-03-01 12:00:00' GROUP BY A, B ASC LIMIT 5;

I am currently designing a mobile application that sends push notifications to users subscribed to a set of topics. The list of possible topics changes infrequently (not more than once per year), while users are frequently added or removed, and their subscription list changes frequently. I have researched both relational databases and key/value databases, and am struggling to determine the best type of database for this problem. I have several parameters/restrictions on the design of the database: 1. Given any

, quickly retrieve the list of topics that a user is subscribed to 2. Given any

, quickly retrieve the list of all users subscribed to that topic 3. Allow persistence to disk as a single file to allow easy network transfer across machines, but cross-platform compatibility is not required as the file will only be accessed across homogenous machines. 4. Preference is given to proven and robust solutions; but if using a prebuilt solution, it must be free or open source (working with a strict budget) 5. Can use as much disk space as needed, but must not require excessive amounts of CPU or RAM 6. Built-in replication capability and consistency assurance is preferred, but not required 7. macOS solutions are preferred, Linux-only solutions are permitted, Windows-only solutions are discouraged My thinking is that since there is both an association between users and topics, I need two key/value tables as in the following schema:

UserTable  = {userID : [topic]}
TopicTable = {topicID: [user]}

where

{:}

denotes a hash map (such as

::map

in C++),

[...]

denotes a dynamic-length array (such as

::vector

in C++). Note that

is a pointer to specific

(userID, topic)

key-value pair in

, and

is a pointer to a specific

(topicID, user)

key-value pair in

. This is a data structure I was able to implement in C++ successfully, and this satisfies requirements (1), (2), (5), and (7). Unfortunately, there doesn't seem to be an industry-standard method of efficiently serializing pointers to disk, which seemingly make (3) and (6) impossible. The cereal or boost C++ libraries have methods for this purpose, but I don't know if they are designed for real-time performance, or if they could be considered to satisfy (4). All of these issues make me think that perhaps I need to rethink my database schema, but then I'm back to having issues with (1) and (2). I have thought of several ways of doing so. One would be to store the data as JSON and serialize directly to disk with a fast and compact binary representation such as BSON, but this would seem to involve an excessive amount of I/O operations as the table grows larger, and reduce performance due to the increased number of cache misses and page loads required. I could use a relational database such as Python's

, which seems to meet all of the requirements, but my lack of background in databases makes me unable to determine how I would make this data fit the relational model (would the primary key be

, or

? How could I ensure fast lookups by either key, when a user could potentially subscribe to 100 different topics, or a topic might have 100,000 users? Surely I wouldn't make one column for each of the ~10,000 possible topics, when it is rare for a user to subscribe to more than 10?) I've also considered Erlang's

as it is one of the only databases I have found besides Python's

which is tightly coupled with a general-purpose programming language, and it seems to support (6) nicely. Unfortunately, I haven't been able to find any clear information about the performance of

or Erlang in general. **Important Note** I understand many users may suggest I benchmark each of these options. Unfortunately, since I am new to database management, even developing a benchmark for each of these options will require a significant investment into learning the associated frameworks. This is also a startup company whose mobile application has not yet been released, so any benchmark is likely a poor representation of real-world traffic. As with any new app, most likely user onboarding will be a slow and gradual process, but I want to avoid what would amount to an unintentional DoS attack if the app goes "viral" and garners a large number of downloads quickly. This is why the numbers given above are loose estimates of a worst-case scenario (with the exception of possible topics, which is guaranteed to be ~10k for the foreseeable future), and why I am unable to perform adequate benchmarking as user activity remains a significant unknown variable that cannot be eliminated prior to launch. **EDIT** @FrankHeikens mentioned in comments that my requirement (3) is highly restrictive. I originally sought a single-file model due to the ease of backing up or transferring such a database; however, if replication and backup capabilities are included in a solution this requirement can be relaxed.

Need your insights. Thank you for taking time and sharing. **Context** We have a 10 sql scripts which has more than 10000 lines of code and most of them are scripts and some code in each script are irrelevant **Problem** I need to identify which scripts and which parts of the script actually being applied on the DB so that we can re-structure the scripts . I at least need to share stats for my safety. I don't have any idea how to start - DML operations - DDL operations - Deadlocks I'm a junior DBA never had done this activity and I don't what compelled them to push it to me. May be I'm asking too much kindly at least push me in right direction so I can start somewhere.

We have provided sql tuning set as input to the analysis task of performance analyzer

variable l_task_id VARCHAR2(64);

exec :l_task_id:=dbms_sqlpa.create_analysis_task(
     sqlset_name => ''
    , sqlset_owner=>''
    ,task_name => '');

Now i want to list all the tasks associated with the sql tuning set. I have tried below ways but they are not working

select parameter_name,parameter_value 
from dba_advisor_exec_parameters 
where task_name='' and parameter_name='SQLSET_NAME';

But the result is showing "NOT USED" for column parameter_value |parameter_name | parameter_value| |-------------------- | ------------------| |SQLSET_NAME UNUSED||

I am working on a query that is running for reports and causing timeouts as it tends to run right over 30 seconds. I feel the UDFs are causing the issue, but I am not sure what can get me a positive gain in performance. I have put the plan at: https://www.brentozar.com/pastetheplan/?id=tUOdeVhykH for review. Any thoughts or help would be great.

sql
ALTER function [dbo].[ISM_fnGetUID_TS] ()
returns @Results table
  (
    UserID int --primary key
  )
as -- body of the function
begin

  insert  @Results
  select  dbo.ISM_fnGetUID_Lite()

  return
end


ALTER FUNCTION [dbo].[ISM_fnGetUID_Lite] ()  
RETURNS int  
AS  
BEGIN  
  return  (
    select UserID = case
      when app_name() like '%@App' then cast(left(app_name(), charindex('@', app_name()) - 1) as int)
      else coalesce(  
        convert(smallint,substring(context_info(),1,2)),
        (select UserID from dbo.TS_User u where (u.UserName = system_user and u.Disabled = 0)))  
    end
  )
END

In MYSQL, I have a select query which is usually fetch the data from 4M records. There I'm using like operator with % and % this is not using indexing filter and reading total 4M records to fetch 2 records. How can I optimize it. SELECT r_ss.siteId, r_ss.referrer, r_ss.supplierhash, min(r_ss.bid_floor) as bid_floor, sum(r_ss.adCall_yesterday) as adCall_yesterday, sum(r_ss.adCall_today) as adCall_today FROM reporting.total_data_for_site r_ss WHERE (r_ss.adCall_today > 1 OR r_ss.adCall_yesterday > 100) AND CASE WHEN ( '1367,1397,1624' IS NOT NULL AND '1367,1397,1624' '' ) THEN r_ss.siteId IN ( SELECT * FROM tmp_site ) ELSE 1 = 1 END AND r_ss.deviceid in (38,39,41) AND r_ss.referrer LIKE CONCAT('%','watchfree', '%') group by r_ss.siteId,r_ss.referrer ;