1

I am looking for ways to improve the performance of some relatively complicated queries against both PostgreSQL and MySQL databases. (One of the requirements of the application which the database is supporting is that it must be "cross-compatible" as much as possible).

For this reason, I chose to use PHP's RedBeanPHP project to automatically create the database structure on the fly, since I'm not very experiences in database management. The queries themselves are built using my own custom made query builder ( a PHP script ) which works with my own custom GUI interface ( screenshots can be provided if desired ).

I am running the following query:

SELECT
    COUNT( DISTINCT( lead.id ) )
FROM lead WHERE lead.id IS NOT NULL
AND (  ( lead.fname IS NOT NULL AND lead.fname <> ? )  OR  ( lead.lname IS NOT NULL AND lead.lname <> ? )  )
AND  lead.id IN ( SELECT lead_phone.lead_id FROM lead_phone LEFT JOIN phone ON lead_phone.phone_id = phone.id WHERE phone.valid = :phone_valid )
AND  lead.id IN ( SELECT lead_phone.lead_id FROM lead_phone LEFT JOIN phone ON lead_phone.phone_id = phone.id WHERE phone.line_type IN ( ?,? ) )
AND  lead.id IN ( SELECT email_lead.lead_id FROM email_lead LEFT JOIN email ON email_lead.email_id = email.id WHERE email.valid_domain = :email_valid_domain )
AND  lead.country IN ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )
AND  lead.id IN ( SELECT lead_tag.lead_id FROM lead_tag LEFT JOIN tag ON lead_tag.tag_id = tag.id WHERE tag.tag LIKE :tag_tag )
AND  lead.fname <> :lead_fname

on a data set with is over 10 million records large. It is currently taking between 2 and 3 minutes to run this query, which is problematic since this query ideally should run in less than 1 minute.

The PostgreSQL database server which is in need of performance optimization is running on an Amazon RDS db.r3.8xlarge instance, while the application is on an r4.16xlarge instance, so I'm pretty much maxed out right now for single hardware instances.

Here are unique constraints / unique indexes already placed on the following columns (some are not relevant to this specific query, but the query above is an example query)

  • email.id
  • email.email
  • ip.ip
  • language.lang
  • lead.id
  • leadmeta.id
  • phone.number_numbers_only
  • skype.skype

I'd greatly appreciate any advice that can be provided to help nudge me in the right direction. I've considered the following options but I'm not experienced or knowledgeable enough with database management to know how it will impact the systems:

  • Adding indexes to any "searchable" columns in searchable tables
  • Creating temporary tables to store the information from the sub-queries and then running the top-level query based on the information in the temporary tables
  • Creating a database server cluster ( I've done a little bit of research into this, but I don't understand it will enough to understand what it even is )

Please note that because i'm using an AWS/RDS server, I don't have that many options regarding editing the database server's configuration itself, or at least I'm not aware of how to.

Edit 1: Query Plans

The following links are the results when I run "EXPLAIN" on both the MySQL and PostrgreSQL envrironments. Please remember that the MySQL environmnet is a dev/QA environmnet which has under 10,000 records total. The queries themselves are identical, but obviously the data isnt.

In order to ensure that no information is missing, I've uploaded and shared the results to a shared Google Sheets link:

Edit 2: Populated Query

I realize that it's a lot harder to work without a sample query that actually has some data in it, so here's an example of a query ( without the COUNT )

SELECT
    DISTINCT( lead.id )
FROM lead
WHERE lead.id IS NOT NULL
AND (  ( lead.fname IS NOT NULL AND lead.fname <> '' )  OR  ( lead.lname IS NOT NULL AND lead.lname <> '' )  )
AND  lead.id IN ( SELECT lead_phone.lead_id FROM lead_phone LEFT JOIN phone ON lead_phone.phone_id = phone.id WHERE phone.valid = '1' )
AND  lead.id IN ( SELECT lead_phone.lead_id FROM lead_phone LEFT JOIN phone ON lead_phone.phone_id = phone.id WHERE phone.line_type IN ( 'mobile','landline_or_mobile' ) )
AND  lead.id IN ( SELECT email_lead.lead_id FROM email_lead LEFT JOIN email ON email_lead.email_id = email.id WHERE email.valid_domain = '1' )
AND  lead.country IN ( 'AU','AT','CA','DK','FO','FI','DE','GL','HK','IS','IE','IT','JM','KW','LI','LU','MY','MC','NL','AN','NZ','NF','NO','PT','QA','SA','SG','ZA','ES','SE','CH','AE','GB','AX' )
AND  lead.id IN ( SELECT lead_tag.lead_id FROM lead_tag LEFT JOIN tag ON lead_tag.tag_id = tag.id WHERE tag.tag LIKE '%Binary%' )
AND  lead.fname <> 'None'  GROUP BY lead.id ORDER BY id ASC

Edit 3: Update

I've removed DISTINCT from the query generator, which shaved a little bit of time off of the query, but i'm still experiencing very slow performance overall

Edit 4: Update

So, I've re-written the query generator which is building the queries to produce what looks like much more efficient queries. I'll post the results once I'm finished testing, but one thing that I completely overlooked was that the hard drive is full, so I'm in the process of resizing it and increasing the volume. I'll update with results from both procedures once all is complete.

Edit 5: Update

New query builder worked great in dev and QA, crashed and burned in production. Going to re-read everything here and see what works.

0

First , no point in discussing index when query is not clear nor proper.

Second and also most important,when you hv use so many condition then still why use DISTINCT.

ARe you not able to elimindate Duplicate record ?In absense of data "DISTINCT" is misleading.

Als when you get your query correct then use count(*),meanwhile you can only use *.

IMHO,my script is in right direction if not right.

SELECT
    *
FROM lead l  (  ( lead.fname IS NOT NULL AND lead.fname <> ? )  OR  ( lead.lname IS NOT NULL AND lead.lname <> ? )  )
AND  lead.country IN ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? )
AND  lead.id IN ( SELECT lead_tag.lead_id FROM lead_tag LEFT JOIN tag ON lead_tag.tag_id = tag.id WHERE tag.tag LIKE :tag_tag )
AND  lead.fname <> :lead_fname

AND exists( SELECT lead_phone.lead_id FROM lead_phone LEFT JOIN phone ON lead_phone.phone_id = phone.id WHERE  l.id=lead_phone.lead_id  and phone.valid = :phone_valid and phone.line_type IN ( ?,? ))

AND  exists ( SELECT email_lead.lead_id FROM email_lead LEFT JOIN email ON email_lead.email_id = email.id 
WHERE l.id=email_lead.lead_id and   email.valid_domain = :email_valid_domain )
  • There are many cases where leads may fit under multiple conditions, so the ID's are duplicated when they are included in multiple sub-queries. I'll update question with a "populated" query so that you can see what i'm actually requesting. – bdb.jack May 1 '17 at 11:45
  • Did you change any of the WHERE clauses? If so, please summarize or highlight which parts you changed. – Rick James May 1 '17 at 16:26
  • @RickJames,sorry my format is not good.i will format it later on.But my change are visible and i hv use and exists clause.My query is shorter than your.And writing 2 seperate "lead.id in ..FROM lead_phone" sub query is wrong.It can be combine into one. – KumarHarsh May 1 '17 at 17:05
0

The query, a bit more readable:

SELECT DISTINCT( lead.id )
    FROM  lead
    WHERE  lead.id IS NOT NULL
      AND  ( ( lead.fname IS NOT NULL
                      AND  lead.fname <> '' )
              OR  ( lead.lname IS NOT NULL
                      AND  lead.lname <> '' ) 
           )
      AND  lead.id IN (
        SELECT  lead_phone.lead_id
            FROM  lead_phone
            LEFT JOIN  phone  ON lead_phone.phone_id = phone.id
            WHERE  phone.valid = '1' 
                      )
      AND  lead.id IN (
        SELECT  lead_phone.lead_id
            FROM  lead_phone
            LEFT JOIN  phone  ON lead_phone.phone_id = phone.id
            WHERE  phone.line_type IN ( 'mobile','landline_or_mobile' ) 
                      )
      AND  lead.id IN (
        SELECT  email_lead.lead_id
            FROM  email_lead
            LEFT JOIN  email  ON email_lead.email_id = email.id
            WHERE  email.valid_domain = '1' 
                      )
      AND  lead.country IN ( 'AU','AT','CA','DK','FO','FI','DE',
                'GL','HK','IS','IE','IT','JM','KW','LI','LU','MY','MC',
                'NL','AN','NZ','NF','NO','PT','QA','SA','SG','ZA','ES',
                'SE','CH','AE','GB','AX' 
                          )
      AND  lead.id IN (
        SELECT  lead_tag.lead_id
            FROM  lead_tag
            LEFT JOIN  tag  ON lead_tag.tag_id = tag.id
            WHERE  tag.tag LIKE '%Binary%' 
                      )
      AND  lead.fname <> 'None'
    GROUP BY  lead.id
    ORDER BY  id ASC
  • We have already discussed the cryptic lead.id IS NOT NULL. If I read the MySQL EXPLAIN correctly, it is not a big issue. (Still changing to 1=1 would be less cryptic. TRUE is another option.)

  • lead_phone LEFT JOIN phone is mentioned twice, and the Optimizer failed to combine the two references -- but you probably can. Simply combine the WHERE clauses.

  • This pattern: AND id IN ( SELECT lead_id FROM .. LEFT JOIN .. ON .. WHERE .. ) seems to work correctly, but inefficiently. Removing LEFT would (I think) give you the same answer, but faster: AND id IN ( SELECT lead_id FROM .. JOIN .. ON .. WHERE .. ). Depending on the version you are running, turning into this might be better (or the Optimizer might be doing it): AND EXISTS ( SELECT lead_id FROM .. JOIN .. ON .. WHERE .. ).

  • I think, but can't tell for sure, whether you violated one of my Rules of Thumb: "Normalize, but don't over-normalize". I guess that lead_phone, email_lead, and lead_tag are "many:many" mapping tables for normalizing?

  • Here are my tips on improving the indexes in such mapping tables.

  • For lead_tag and tag (maybe others), you are probably better off getting rid of tag and replacing tag_id with the actual tag. (A possible case of "over-normalizing".)

  • Some columns seem to be unnecessarily NULL. Change to NOT NULL where appropriate. (Clue: key_len = 5.)

  • OK, so I'd like to explain about the normalization. I'm not actually familiar with the term, but basically, the 'lead_tag', 'lead_phone' etc. are bridge tables allowing for MANY::MANY relation to other objects which have other / addition properties which aren't related to the lead itself, but need to be queryable from the lead itself. – bdb.jack May 1 '17 at 16:30
  • Another reason why it is usually handy to see all relevant SHOW CREATE TABLEs. (I might have seen the other columns and not made my point.) – Rick James May 1 '17 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.