CODEWITHSUNDEEP
mysqlquery-performance
Andrew Simeou
Andrew Simeou

Reputation: 91

Can anyone assist me with optiising my query to reduce result time?

I have written a MYSQL query without much expertise in this area but as my database has increased in size, I'm finding the results are taking far too long to be returned. I can understand why but I can't figure out how to better group my query so that MYSQL isn't searching through the entire database to return the results. I know there is a far more efficient way to do this but I can't figure out how. If I remove the ORDER BY statement, the results are returned in less than a quarter of the time. As it stands now with a table that has 180,000 entries in it (members), it's taking about 4 seconds to return the results.

SELECT members.mem_id, members.username, members.online, 
       members.dob, members.regdate, members.sex, 
       members.mem_type, members.aboutme, 
       geo_cities.name AS city, 
       geo_countries.name AS country, photos.photo_path
        FROM members
        LEFT JOIN geo_cities
        ON members.cty_id=geo_cities.cty_id
        LEFT OUTER JOIN geo_countries
        ON geo_cities.con_id=geo_countries.con_id
        RIGHT OUTER JOIN photos
        ON members.mem_id=photos.mem_id
        WHERE (photos.main=1 
          AND photos.approved=1
          AND members.banned!="1"
          AND members.profile_photo="1"
          AND members.profile_essentials="1"
          AND members.profile_user="1")
        ORDER BY lastdate DESC
        LIMIT 12

Upvotes: 0

Views: 39

Answers (1)

O. Jones
O. Jones

Reputation: 108776

It looks like you want to show the most recent 12 members who meet certain criteria.

A few things.

  1. Your RIGHT JOIN on photos is actually an ordinary inner JOIN: its columns appear in your WHERE clause.
  2. You probably need compound indexes on the members and photos tables.
  3. SELECT many columns FROM ... JOIN ... ORDER BY column... LIMIT 12 is a notorious performance antipattern: It constructs a complex result set, then sorts the whole thing, then discards almost all of it. Wasteful.
  4. You have WHERE....members.banned != "1" Inequality filters like this make SQL work harder (==slower) than equalities. If you can change that to = "0" or something like that do it.

(I guess your lastdate column is in your members table, but you didn't tell us that in your question.)

So try something like this to find the twelve members you want to display.

          SELECT members.mem_id
            FROM members
            JOIN photos ON members.mem_id=photos.mem_id
           WHERE photos.main=1 
             AND photos.approved=1
             AND members.banned!="1"
             AND members.profile_photo="1"
             AND members.profile_essentials="1"
             AND members.profile_user="1")
           ORDER BY lastdate DESC
           LIMIT 12

That gets you the ids of the twelve members you want. Use it in your main query.

    SELECT members.mem_id, members.username, members.online, 
           members.dob, members.regdate, members.sex, 
           members.mem_type, members.aboutme, 
           geo_cities.name AS city, 
           geo_countries.name AS country, photos.photo_path
      FROM members
      LEFT JOIN geo_cities    ON members.cty_id=geo_cities.cty_id
      LEFT JOIN geo_countries ON geo_cities.con_id=geo_countries.con_id
      JOIN photos ON members.mem_id=photos.mem_id
    WHERE members.mem_id IN (
          SELECT members.mem_id
            FROM members
            JOIN photos ON members.mem_id=photos.mem_id
           WHERE photos.main=1 
             AND photos.approved=1
             AND members.banned!="1"
             AND members.profile_photo="1"
             AND members.profile_essentials="1"
             AND members.profile_user="1")
           ORDER BY lastdate DESC
           LIMIT 12
         )
    ORDER BY lastdate DESC
    LIMIT 12

This finds the twelve members you care about, then pulls out only their records, instead of pulling all the records.

Then, create a compound index on members(profile_photo, profile_essentials, profile_user, banned, lastdate). That compound index will speed up your WHERE clause a great deal.

Likewise, create a compound index on photos(mem_id, main, approved, photo_path).

Things always get exciting when databases start to grow! Read Markus Winand's online book https://use-the-index-luke.com/

Upvotes: 1

Related Questions