Hi all,
We are running into trouble with slow queries that use ORDER BY
and would like some advice. The main question is: are we experiencing
data-modelling/index issues or fundamental restrictions of MySQL.
Help would be greatly appreciated - see description below.
Patrick
--
Say we have three tables:
1- contact (contact_id, name) with contact_id as the primary index
and another index on name - name is a CHAR. 6717 records.
2- contact_address_index (index_id, contact_id, address_id ) with index_id
as the primary index and two other indexes, one on contact_id, one on
address_id. 7580 records.
3- address (address_id, city) with address_id as the primary index
and another index on city - city is a CHAR. 7071 records.
Note that we can have more than one address per contact and that the same
address may be shared by several contacts.
The following query takes at least 0.5 seconds
using MySQL 3.22.30 (7200 results):
SELECT contact.contact_id, last_name, city
FROM contact, contact_address_index, address
WHERE contact.contact_id = contact_address_index.contact_id AND
contact_address_index.address_id = address.address_id
ORDER BY last_name
The same query without the ORDER BY take less than 0.01 second.
explain gives:
+-----------------------+--------+-------------------------+-------------+---------+----------------------------------+------+-------------+
| table | type | possible_keys | key | key_len |
|ref | rows | Extra |
+-----------------------+--------+-------------------------+-------------+---------+----------------------------------+------+-------------+
| contact_address_index | index | address_idx,contact_idx | contact_idx | 8 |
|NULL | 7580 | Using index |
| contact | eq_ref | PRIMARY | PRIMARY | 4 |
|contact_address_index.contact_id | 1 | |
| address | eq_ref | PRIMARY | PRIMARY | 4 |
|contact_address_index.address_id | 1 | |
+-----------------------+--------+-------------------------+-------------+---------+----------------------------------+------+-------------+
on MySQL 3.23.39, a query like
SELECT contact.contact_id, last_name, city
FROM contact USE INDEX( name_idx ) , contact_address_index, address
WHERE contact.contact_id = contact_address_index.contact_id AND
contact_address_index.address_id = address.address_id
ORDER BY last_name
where name_idx is the index on name (contact table) helps but still
take 0.16 seconds. It is not a good solution to force the index
anyway as if the WHERE of the query is more complicated it may
be better to use another index.
Using inner join under MySQL 3.23 doesn't help.
Any help/ideas would be gratefully received.
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
