Performance problems with an ordered view in MySQL

Question

I am new to performance tuning in MySQL & need your help concerning a view that will replace a table later-on in our design.

The table to be replaced is called users and has the following attributes: The users2 view has the following attributes:

When I execute a normal SELECT on both objects, they respond at the same time:

SELECT * 
FROM `users`

SELECT * 
FROM `users2`

But an ordered version of these queries result in a different performance: The table is a little slower (takes less than two seconds), the view need about ten times this time:

SELECT * 
FROM `users`
ORDER BY `lastName`, `firstName`

SELECT * 
FROM `users2`
ORDER BY `lastName`, `firstName`

To find out the reason, I let EXPLAIN the two comments:

Obviously, an ALL on table 'a' (addresses) on the attribute Countries_ID is making trouble, so I made the following:

ALTER TABLE addresses ADD INDEX (Countries_ID);

This index didn't change anything at all. So, I ask you for your opinion what can be done better.

Notice 1: Is there a way to create an index on temporary column Countries_ID_2? Notice 2: The users2 view was created with the following SQL query:

CREATE OR REPLACE VIEW users2 AS
(SELECT p.username
    , p.password
    , p.firstName
    , p.lastName
    , p.eMail AS email
    , a.settlement AS city
    , s.name AS country
    , pl.languages
    , p.description
    , p.ID AS ID
    , p.phone1
    , p.phone2
    , CONCAT_WS(' ', a.street, a.addition) AS address
    , p.status
    , p.publicMail
    , ad.name AS Betreuer
FROM addresses a
    INNER JOIN addresses_have_persons ap ON a.ID = ap.Addresses_ID
    INNER JOIN countries c ON a.Countries_ID = c.ID
    INNER JOIN persons p ON a.ID = p.addressID
        AND ap.Persons_ID = p.ID
    INNER JOIN states s ON a.States_ID = s.ID
    INNER JOIN persons_language pl ON p.ID = pl.ID
LEFT JOIN advisors ad ON p.advisorID = ad.ID
-- LEFT JOIN titles t ON t.ID = ad.titleID
);

Notice 3: Although a lot of fields in the persons table are NULL, there is not a single row where these fields are altogether NULL.

EDIT:

CREATE OR REPLACE VIEW persons_language AS
(SELECT lp.Persons_ID AS ID
    , GROUP_CONCAT(DISTINCT l.name ORDER BY l.name SEPARATOR ', ') AS languages
FROM languages l
    , languages_have_persons lp
WHERE l.ID = lp.Languages_ID
GROUP BY lp.Persons_ID);

Without the ORDER BY, the language names are not alphabetically ordered, which I currently want. Perhaps, we could decide to get them in any order, but we'll see.

Currently, I made the following modifications without any performance improvement:

ALTER TABLE addresses ADD INDEX (Countries_ID);
ALTER TABLE addresses ADD INDEX (States_ID);
ALTER TABLE addresses_have_persons ADD INDEX (Addresses_ID);
ALTER TABLE languages ADD INDEX (name);
ALTER TABLE persons ADD INDEX (addressID);
ALTER TABLE persons ADD INDEX (address2ID);
ALTER TABLE persons ADD INDEX (address3ID);
ALTER TABLE persons ADD INDEX (advisorID);

EDIT 2:

I discuss this issue also on another site. The discussions there let me do the following changes to be nearer to the third normal form:

CREATE OR REPLACE TABLE accounts
(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, username VARCHAR(50) NOT NULL UNIQUE
, password VARCHAR(255) NOT NULL
, eMail VARCHAR(100) NOT NULL
, Persons_ID INT NOT NULL
);

INSERT INTO accounts (username, password, eMail, Persons_ID)
SELECT username, password, eMail, ID
FROM persons;

The table persons does contain only the most necessary things and has the following structure now:

The new table persons_information carries all additional information:

I recreated the users2 with the following command:

CREATE OR REPLACE VIEW users2 AS
(SELECT ac.username
    , ac.password
    , p.firstName
    , p.lastName
    , ac.eMail AS email
    , adr.settlement AS city
    , s.name AS country
    , pl.languages
    , pi.description
    , ac.Persons_ID AS ID
    , pi.phone1
    , pi.phone2
    , CONCAT_WS(' ', adr.street, adr.addition) AS address
    , p.status
    , pi.publicMail
    , adv.name AS Betreuer
FROM accounts ac 
    INNER JOIN persons p ON ac.Persons_ID = p.ID
    INNER JOIN persons_information pi ON p.ID = pi.ID
    INNER JOIN addresses adr ON adr.ID = pi.addressID
    INNER JOIN addresses_have_persons ap ON adr.ID = ap.Addresses_ID
        AND ap.Persons_ID = p.ID
    INNER JOIN countries c ON adr.Countries_ID = c.ID
    INNER JOIN states s ON adr.States_ID = s.ID
    INNER JOIN persons_language pl ON p.ID = pl.ID
LEFT JOIN advisors adv ON pi.advisorID = adv.ID
-- LEFT JOIN titles t ON t.ID = adv.titleID
);

The SELECT _ FROM users2 is fast, but if I add an ORDER BY lastName, firstName, it takes about 25 seconds to get the response.

Here are the results of the *EXPLAIN SELECT * FROM users2* command:

And here for the other command:

I also (re)created following indexes:

ALTER TABLE addresses ADD INDEX (Countries_ID);
ALTER TABLE addresses ADD INDEX (States_ID);
ALTER TABLE addresses_have_persons ADD INDEX (Persons_ID);
ALTER TABLE languages ADD INDEX (name);
ALTER TABLE persons_information ADD INDEX (addressID);
ALTER TABLE persons_information ADD INDEX (address2ID);
ALTER TABLE persons_information ADD INDEX (address3ID);
ALTER TABLE persons_information ADD INDEX (advisorID);

I think one reason for the problem is the persons_language view that is created as follows:

CREATE OR REPLACE VIEW persons_language AS
    (SELECT lp.Persons_ID AS ID
        , GROUP_CONCAT(DISTINCT l.name ORDER BY l.name SEPARATOR ', ') AS languages
    FROM languages l
    INNER JOIN languages_have_persons lp ON l.ID = lp.Languages_ID
GROUP BY lp.Persons_ID);

EDIT 3: For those interested, I add the EXPLAIN for the persons_language view:

EDIT 4: After the database meeting today, we decided to delete all objects related to the address information & recreated the view with

CREATE OR REPLACE VIEW `users2` AS
(SELECT ac.username
    , ac.password
    , p.firstName
    , p.lastName
    , ac.eMail AS email
    , pl.languages
    , pi.description
    , ac.Persons_ID AS ID
    , pi.phone1
    , pi.phone2
    , p.status
    , pi.publicMail
    , adv.name AS Betreuer
FROM accounts ac 
    INNER JOIN persons p ON ac.Persons_ID = p.ID
    INNER JOIN persons_information pi ON p.ID = pi.ID
    INNER JOIN persons_language pl ON p.ID = pl.ID
    INNER JOIN advisors adv ON pi.advisorID = adv.ID
WHERE ac.password IS NOT NULL
);

I also created an index with

CREATE INDEX LanguagesPersonsIndex ON `languages_have_persons` (`Languages_ID`, `Persons_ID`);

The EXPLAIN command shows that the new indices are in use and that the delay after a SELECT with an ORDER BY clause with the new, smaller view is about 18 s. Here is the new result: My question is: What could I do more to improve the performance?

Answer 1

SELECT * 
FROM `users`
ORDER BY `lastName`, `firstName`

needs

INDEX(last_name, first_name) -- in that order

Beware of VIEWs; some VIEWs optimize well, some do not.

Please provide SHOW CREATE TABLE for both addresses and addresses_have_persons.

In persons_language, why do you need DISTINCT? Doesn't it have PRIMARY KEY(person, language) (or in the opposite order)? Let's see SHOW CREATE TABLE.

Please provide the EXPLAIN for any query you want to discuss.

Answer 2

The key fault must be the problem. But depending on data volume on the joined tables, it'll anyway be slower. Try To:

• Implement KeyIndexes on ALL attributes used to stablish relationships. (ap.Addresses_ID, a.Countries_ID, p.addressID, ap.Persons_ID, a.States_ID, p.advisorID).
• Declare PK on All 'ID' columns.
• Don't use ORDER or GROUP in the views construction.
• Declare Key Index for attributes that are most used on searches, ordering or grouping.

Tip: The 'INNER' (INNER JOIN) isn't necessary. Is the same of 'JOIN'

Your VIEW "persons_language" would be better like this:

SELECT lp.Persons_ID AS ID, GROUP_CONCAT(DISTINCT l.name ORDER BY l.name SEPARATOR ', ') AS languages
FROM languages_have_persons lp 
JOIN languages l ON l.ID = lp.Languages_ID
GROUP BY lp.Persons_ID;

It's more appropriate because the clauses 'FROM' and 'JOIN' are processed before 'WHERE' clause.

You may boost your mysql memory and cache configurations. Look the my mysql server's configurations (Runs an ERP with weight tables and views):

join_buffer_size= 256M
key_buffer = 312M 
key_buffer_size = 768M
max_allowed_packet = 160M 
thread_stack = 192K 
thread_cache_size = 8
query_cache_limit = 64M
innodb_buffer_pool_size = 1512M
table_cache = 1024M 
read_buffer_size = 4M
query_cache_size = 768M
query_cache_limit = 128M