I'm currently trying to optimise a heavy script (query) that is taking 4.9452 seconds to run. Then there is a PHP while loop that sets some arrays up based on whats returned, however that is another issue.
EDIT - The users table is ~9000 rows, the user_address table is ~3000 rows, with shop_countries ~200 rows.
Here is my query:
SELECT
users.id AS user_id,
users.unique_code AS user_unique_code,
users.first_name AS user_first_name,
users.surname AS user_surname,
users.organisation AS user_organisation,
users.telephone AS user_telephone,
users.email AS user_email,
users.password AS user_password,
users.newsletter AS user_newsletter,
user_address.id AS user_address_id,
user_address.user_id AS user_address_user_id,
user_address.nickname AS user_address_nickname,
user_address.address_type AS user_address_type,
user_address.address_line_1 AS user_address_line_1,
user_address.address_line_2 AS user_address_line_2,
user_address.address_line_3 AS user_address_line_3,
user_address.city AS user_address_city,
user_address.postcode AS user_address_postcode,
user_address.country_id AS user_address_country_id,
shop_countries.printable_name
FROM users
LEFT JOIN user_address ON users.id = user_address.user_id
LEFT JOIN shop_countries ON shop_countries.id = user_address.country_id
WHERE (users.surname != "" OR users.first_name != "")
ORDER BY users.surname ASC, users.first_name ASC;
I need to select all users regardless of if they have an address assigned to them, and then attach any countries if there IS an address available.
Here is the result of EXPLAIN EXTENDED
So far, it seems that if I were to change the LEFT JOIN to just JOIN then the query executes in less than a second. With the problem being I need all the users regardless of if they have an address.
PHP Script
For anyone wanting to see the corresponding PHP function for reference on my design, please see below:
function getAllUsersWithAddresses() {
$customers = array();
$sql = 'SELECT
users.id AS user_id,
users.unique_code AS user_unique_code,
users.first_name AS user_first_name,
users.surname AS user_surname,
users.organisation AS user_organisation,
users.telephone AS user_telephone,
users.email AS user_email,
users.password AS user_password,
users.newsletter AS user_newsletter,
user_address.id AS user_address_id,
user_address.user_id AS user_address_user_id,
user_address.nickname AS user_address_nickname,
user_address.address_type AS user_address_type,
user_address.address_line_1 AS user_address_line_1,
user_address.address_line_2 AS user_address_line_2,
user_address.address_line_3 AS user_address_line_3,
user_address.city AS user_address_city,
user_address.postcode AS user_address_postcode,
user_address.country_id AS user_address_country_id,
shop_countries.printable_name
FROM users
LEFT JOIN user_address ON users.id = user_address.user_id LEFT JOIN shop_countries ON shop_countries.id = user_address.country_id WHERE (users.surname != "" OR users.first_name != "") ORDER BY users.surname ASC, users.first_name ASC;';
$users_query = mysql_query($sql);
$customers = array();
while($row = getData($users_query)) {
if(!isset($customers[$row['user_id']])) {
$customers[$row['user_id']] = array(
'id' => $row['user_id'],
'unique_code' => $row['user_unique_code'],
'first_name' => $row['user_first_name'],
'surname' => $row['user_surname'],
'organisation' => $row['user_organisation'],
'telephone' => $row['user_telephone'],
'email' => $row['user_email'],
'password' => $row['user_password'],
'newsletter' => $row['user_newsletter']
);
}
if(isset($customers[$row['user_id']]) && !empty($row['user_address_type'])) {
$customers[$row['user_id']]['addresses'][$row['user_address_type']][] = array(
'id' => $row['user_address_id'],
'user_id' => $row['user_address_user_id'],
'nickname' => $row['user_address_nickname'],
'address_type' => $row['user_address_type'],
'address_line_1' => $row['user_address_line_1'],
'address_line_2' => $row['user_address_line_2'],
'address_line_3' => $row['user_address_line_3'],
'city' => $row['user_address_city'],
'postcode' => $row['user_address_postcode']
);
} else {
$customers[$row['user_id']]['addresses'] = array();
}
}
return $customers;
}
Create an index for your foreign ids
I can't see your explain plan - it would have been helpful if you had posted it in your question. However even if I could, its not much help without knowing the structure of the database - please include the create table syntax and the indexes. In an ideal world you would also include the index cardinality.
There are only two ways to make a query go faster. Reduce the number of rows the database needs to examine or make it easier for the DBMS to find the rows you want to return.
I would guess that most of the cost comes from the 2 LEFT JOINs in the query. Surely you have a complete list of countries? There aren't that many. Hence you should be able to replace the second outer join with an inner join.
WHERE (users.surname != "" OR users.first_name != "")
Using 'OR' in a mysql query can radically change its behaviour. Do you have a lot of customers with a missing name? If it's less than 5%, then....
WHERE LENGTH(users.surname) AND LENGTH(users.first_name)
...more than 5% and indexed....
WHERE users.surname LIKE '_%' AND users.first_name LIKE '_%'
But the biggest win comes from ensuring you have indexes on primary and foreign keys.