How can I make this request faster?

Question

How to speed up this request ...?

  SELECT account_id, 
           account_name, 
           account_update, 
           account_sold, 
           account_mds 
           ftp_url         
           ftp_livestatus, 
           number_digits, 
           number_cw,
           client_name, 
           ppc_status, 
           user_name 
 FROM     
          Accounts 
          FTPDetails, 
          SiteNumbers, 
          Clients 
          PPC 
          Users 

 WHERE Accounts.account_id = FTPDetails.ftp_accountid 
 AND Accounts.account_id = SiteNumbers.number_accountid 
 AND Accounts.account_client = Clients.client_id     
 AND Accounts.account_id = PPC.ppc_accountid 
 AND Accounts.account_designer = Users.user_id   
 AND Accounts.account_active = 'active' 
 AND FTPDetails.ftp_active = 'active' 
 AND SiteNumbers.number_active = 'active' 
 AND Clients.client_active = 'active'    
 AND PPC.ppc_active = 'active'   
 AND Users.user_active = 'active' 
 ORDER BY 
          Accounts.account_update DESC

Thank you in advance:)

EXPLAIN query results:

I actually do not have any foreign keys ... I tried to avoid making changes to the database, since soon you will need to carry out a major overhaul.

only primary keys is the identifier of each table, for example. account_id, ftp_id, ppc_id ...

Answer 1

Indices

• You need - at least - an index for each field that is used in the JOIN .

• Field indexes that appear in WHERE or GROUP BY or ORDER BY clauses are also useful in most cases.

• If two or more fields in a table are used in JOIns (either WHERE or GROUP BY or ORDER BY), the combined (combined) index of these (two or more) fields may be better than separate indexes, for example, in the SiteNumbers table SiteNumbers possible indices are a join (number_accountid, number_active) or (number_active, number_accountid) .

• A condition in fields that are logical (ON / OFF, active / inactive) sometimes slows down queries (since indexes are not selective and therefore not very useful). In this case, restructuring (normalizing the father) tables is an option, but you can probably avoid the extra complexity.

---

In addition to the usual tips (study the EXPLAIN plan, add indexes, where necessary, test query options),

I noticed that your request has a partial Cartesian product. The Accounts table has a one-to-many relationship with the three FTPDetails , SiteNumbers and PPC tables. This leads to the fact that if you have 1000 accounts and each account is associated with, say, 10 FTPDetails, 20 SiteNumbers and 3 PPC, the request will return 600 rows for each account (product 10x20x3). Only 600K rows where many data is duplicated.

Instead, you can split the query into three plus one for the underlying data (account and other tables). Thus, only 34K data lines (having shorter lengths) will be transmitted:

 Accounts JOIN Clients JOIN Users (with all fields needed from these tables) 1K rows Accounts JOIN FTPDetails (with Accounts.account_id and all fields from FTPDetails) 10K rows Accounts JOIN SiteNumbers (with Accounts.account_id and all fields from SiteNumbers) 20K rows Accounts JOIN PPC (with Accounts.account_id and all fields from PPC) 3K rows 

and then use the data from 4 client-side queries to show combined information.

---

---

I would add the following indexes:

 Table Accounts index on (account_designer) index on (account_client) index on (account_active, account_id) index on (account_update) Table FTPDetails index on (ftp_active, ftp_accountid) Table SiteNumbers index on (number_active, number_accountid) Table PPC index on (ppc_active, ppc_accountid) 

Answer 2

Use EXPLAIN to find out which index can be used and which index is actually used. Create an appropriate index if necessary.

If FTPDetails.ftp_active contains only two valid entries, 'active' and 'inactive' , use BOOL as the data type.

As a side note: I highly recommend using explicit joins instead of implicit ones:

 SELECT account_id, account_name, account_update, account_sold, account_mds, ftp_url, ftp_livestatus, number_digits, number_cw, client_name, ppc_status, user_name FROM Accounts INNER JOIN FTPDetails ON Accounts.account_id = FTPDetails.ftp_accountid AND FTPDetails.ftp_active = 'active' INNER JOIN SiteNumbers ON Accounts.account_id = SiteNumbers.number_accountid AND SiteNumbers.number_active = 'active' INNER JOIN Clients ON Accounts.account_client = Clients.client_id AND Clients.client_active = 'active' INNER JOIN PPC ON Accounts.account_id = PPC.ppc_accountid AND PPC.ppc_active = 'active' INNER JOIN Users ON Accounts.account_designer = Users.user_id AND Users.user_active = 'active' WHERE Accounts.account_active = 'active' ORDER BY Accounts.account_update DESC 

This makes the query more readable because the join condition is close to the name of the join table.

Answer 3

EXPLAIN, compare different parameters. For starters, I'm sure multiple queries will be faster than this monster. Firstly, since the query optimizer will spend a lot of time studying which connection order is the best (5! = 120 possibilities). And secondly, queries like SELECT ... WHERE ....active = 'active' will be cached (although this depends on the number of data changes).

Answer 4

One of your main problems: x.y_active = 'active'

Problem: Low Power
The active field is a Boolean field with two possible values, so it has a very low power. MySQL (or any SQL in this case will not use the index when 30% or more rows have the same value).
Forcing an index is useless because it will make your query slower rather than faster.

Solution: split your tables
The solution is to split the tables into active columns.
This will exclude all inactive fields from consideration and make the select action as if you have a working index in the xxx-active fields.

Sidenote
Please, never use implicit where connections, it is too error prone and consufing to be useful.
Use syntax instead, such as Oswald's answer .

References:
Cardinality: http://en.wikipedia.org/wiki/Cardinality_(SQL_statements)
Cardinality and indices: http://www.bennadel.com/blog/1424-Exploring-The-Cardinality-And-Selectivity-Of-SQL-Conditions.htm
MySQL Partitioning: http://dev.mysql.com/doc/refman/5.5/en/partitioning.html