I am doing a database query using PHP and wordpress.
In the query below, it’s taking too long to return the results.
(32 total, Query took 11.4666 seconds.)
The database server is returning a timeout error when the result count is more than 500.
Is there a better way I can query 3 tables?
The schema is like this:
notes table
date_created
date_modified
note_author
note_text
contact_id
usermeta table
user_id
meta_key
meta_value
users
ID (this is user_id in usermeta table)
user_email
SELECT note.date_created as date_created,
note.date_modified as date_modified,
firstname.meta_value as first_name,
lastname.meta_value as last_name,
useremail.user_email as email,
note.note_author as author,
note.note_text as note,
userid.user_id
FROM `notes` as note
LEFT JOIN usermeta AS userid
ON note.contact_id = userid.meta_value
LEFT JOIN users as useremail
ON userid.user_id = useremail.ID
LEFT JOIN usermeta AS firstname
ON userid.user_id = firstname.user_id
LEFT JOIN usermeta AS lastname
ON userid.user_id = lastname.user_id
WHERE userid.meta_key = 'activecampaign_contact_id'
AND firstname.meta_key = 'first_name'
AND lastname.meta_key = 'last_name'
AND note.contact_id = 80426;
I would like to improve the performance of the query so it can handle thousands of records.
Right now my query is returning timeout error when the result exceeds 500.
4
EAV is a difficult schema patter to optimize.
The tables sem to be over-normalized. (No need to split parts of a name across multiple tables.)
These composite indexes will help some.
firstname: INDEX(meta_key, user_id, meta_value)
lastname: INDEX(meta_key, user_id, meta_value)
userid: INDEX(meta_key, meta_value, user_id)
If meta_value is TEXT, leave it out of the index.
If note.contact_id is a VARCHAR, quote `’80426′.
1