Nasty Lookups in Query plan

I’ve been trying to get rid of expensive lookups in my query plan but cannot seem to get my head around it. I understand the idea of creating a covering index to eliminate the need for lookups but I don’t know how to approach it with a complicated plan like this.

Any ideas on how to approach this would really be appreciated.

Query plan can be found here: https://www.brentozar.com/pastetheplan/?id=HyBbQ0eRr

Thanks

“or” search query instead of “and” in Unlimited Elements

I use Unlimited elements to make an slider which will take team members from a custom post type. But now i want to get people out of the custom post type. for this i would like to use an “or” query instead of the normal “and” query unlimited elements uses. is there anyway to swap the “or” query to an “and” query?

Creating new table from a query results

Hi I want to create a table of output from this query,Table is giving USR_ID Kindly help I’m novice to SQL

select USR_ID from USR,company@,employee where SK_USR_ID=USR_ID and Assignedto=employeeID and SK_Dept_ID=1 and employeeID not in ( ‘30420’,’30418′) minus

select USR_ID from USR,sts_company,employee where SK_USR_ID=USR_ID and Assignedto=employeeID and SK_Dept_ID=1 and employeeID not in ( ‘30420’,’30418′) and USR_ID in (select USR_ID from ENTERPRISE_USR where SK_ENTERPRISE_TYPE_ID= 2 or SK_ENTERPRISE_TYPE_ID= 3 or SK_ENTERPRISE_TYPE_ID=4)

SQL User Query by Multiple Roles using PHP

I can successfully get all my WordPress users using the following code:

global $  wpdb; $  sql = 'SELECT * FROM ' . $  wpdb->users; $  users = $  wpdb->get_results( $  sql, 'ARRAY_A' ); 

However, I need to filter the users with multiple roles (only get “role1” and “role2”). I have tried various methods including the following which does not work:

global $  wpdb; $  sql = '     SELECT ID, display_name FROM wp_users      INNER JOIN wp_usermeta ON ( wp_users.ID = wp_usermeta.user_id )       INNER JOIN wp_usermeta AS mt1 ON ( wp_users.ID = mt1.user_id )      WHERE 1=1      AND (        (          (            ( mt1.meta_key = 'wp_capabilities' AND mt1.meta_value LIKE '%role1%' )         )          AND          (            (              ( mt1.meta_key = 'wp_capabilities' AND mt1.meta_value LIKE '%role2%' )           )         )       )     )      ORDER BY user_login ASC     '; $  users = $  wpdb->get_results( $  sql, 'ARRAY_A' ); 

How can this sql query be injected?

I’ve a php code where I get the page number from a GET request and then run a sql query to select records from the database by the page number

$  maxPerPage = 20;  $  page = $  _GET["p"];  $  applicants = DB::query('SELECT * FROM registrees ORDER BY id DESC LIMIT  '.$  page*$  maxPerPage.','.$  maxPerPage); 

My question is can someone inject an SQL query in this code ? and if it could happen, I need examples of the sql-injection that can run here.

The problem here that the $ page is multiplied with $ maxPerPage if I tried to add any string in $ page php will throw this error A non-numeric value encountered.

Any ideas ?

query returns the same value multiple times

I have following command :

select * from Books  JOIN Keys ON Books.Bnumber = Keys.Bnumber where ( Books.Author1 like '%,%' and Books.Author1 like '%,John%' or (Books.Author2 like '%,John%' and Books.Author2 like '%,%')) and Name1 like '%' and Keys.Keyword like '%' order by NAME1 ; 

The query returns the same record multiple times.

The repeating parts in query (Books.Author1 like '%'…) are needed, for my application requires it. ( I think it should have nothing to do with my problem)

What could cause this ?

Why Stored Procedure taking extra time then , normal query execution(MYSQL)?

I have one stored procedure which returning single but taking 16+ seconds when I call that but only .5 second, if I run that by normal query.

Procedure

    DELIMITER $  $   DROP PROCEDURE IF EXISTS `local_db`.`getAllMatchedStockByCompany` $  $   CREATE     PROCEDURE `local_db`.`getAllMatchedStockByCompany`(     IN Company_ID BIGINT(20)     )     BEGIN         SELECT t1.*     FROM ((SELECT t2.SRTitle,t2.UrlPart,t2.ScheduledTime,t2.Photo,TRIM(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t2.SRDescription),' ',''),' ',             30), '\r\n\t\r\n\r\n\t', ''),'\r\n\t\r\n\t',''),'\r\n\t',''),'’',''))AS Description, 'stock-research' AS from_tbl,t2.IsActive            ,t2.CompanyID FROM t2           ) UNION           (SELECT t3.ReportTitle,t3.UrlPart,t3.CreatedDate,t3.ReportImage,TRIM(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t3.Description),' ',''),' ',                 30),'\r\n\t\r\n\t',''),''','`')),IF(t3.SpecialReport = 1,'special-report','report'),t3.IsActive         ,t3.CompanyID FROM t3           )          ) t1          WHERE CompanyID = Company_ID AND IsActive = 1          ORDER BY ScheduledTime DESC;     END$  $    DELIMITER ; 

the total execution time of this is 16 seconds with 1 row. But If I use below query

SELECT t1.*     FROM ((SELECT t2.SRTitle,t2.UrlPart,t2.ScheduledTime,t2.Photo,TRIM(REPLACE(REPLACE(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t2.SRDescription),' ',''),' ',             30), '\r\n\t\r\n\r\n\t', ''),'\r\n\t\r\n\t',''),'\r\n\t',''),'’',''))AS Description, 'stock-research' AS from_tbl,t2.IsActive            ,t2.CompanyID FROM t2           ) UNION           (SELECT t3.ReportTitle,t3.UrlPart,t3.CreatedDate,t3.ReportImage,TRIM(REPLACE(REPLACE(SUBSTRING_INDEX(REPLACE(fnStripTags(t3.Description),' ',''),' ',                 30),'\r\n\t\r\n\t',''),''','`')),IF(t3.SpecialReport = 1,'special-report','report'),t3.IsActive         ,t3.CompanyID FROM t3           )          ) t1          WHERE CompanyID = 931 AND IsActive = 1          ORDER BY ScheduledTime DESC; 

It took only .5 to 1 seconds with one row.

Can anyone of you let me why this happening or How I can improve my SP execution time?

Thanks

Form in menu ux design query

So I have a use case where we have a button that is to trigger the “reassign” flow

this obviously needs a list of people to reassign to (1 or many)

I have a multiselect typeahead creating the list. This is consistent with the rest of the UI/UX design.

I am however not happy with the button creating a dropdown menu, and have the select +2 buttons (a short cut for “assign to me” and “assign”)

I do not want a modal. Any other thoughts of how to make this better both UX and UI

Previous:

enter image description here

ATTEMPT 1:

enter image description here

Password Auditing Query

The IT Security department is getting audited and the auditor had approached our IT Security team and requests to see results of the password review process, which compares existing database of passwords which are hashed and custom salt against the password policy requirements.If I am not wrong,I should not be providing them these results.Am I correct.Can someone please explain