Group, Here is my query:
explain select phones.*, ops.plid, ops.box, ops.mac from phones, ops where (ops.box = 'Mcds') or (ops.box = 'Mn3300') and (phones.suffix1 = ops.phone) order by ops.mac My describe of the phones table and the ops table: Phones: +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | | MUL | NULL | auto_increment | | first_name | varchar(255) | YES | | NULL | | | last_name | varchar(255) | YES | | NULL | | | identifier | varchar(255) | YES | | NULL | | | prefix1 | char(3) | YES | | NULL | | | suffix1 | varchar(10) | YES | MUL | NULL | | | prefix2 | char(3) | YES | | NULL | | | suffix2 | varchar(4) | YES | | NULL | | | location | varchar(255) | YES | | NULL | | | mod_date | varchar(30) | YES | | NULL | | | vacant | char(1) | YES | | NULL | | | centrex | char(1) | YES | | NULL | | | voice_mail | varchar(255) | YES | | NULL | | | jack | varchar(10) | YES | | NULL | | | division | varchar(255) | YES | | NULL | | | unit | varchar(255) | YES | | NULL | | | hr_emp_no | varchar(10) | YES | | NULL | | | no_print | char(1) | YES | | NULL | | +------------+--------------+------+-----+---------+----------------+ Ops: +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | | MUL | NULL | auto_increment | | phone | varchar(10) | YES | MUL | NULL | | | plid | varchar(25) | YES | | NULL | | | vacant | char(1) | YES | | NULL | | | box | varchar(30) | YES | | NULL | | | loc | varchar(30) | YES | | NULL | | | jack | varchar(30) | YES | | NULL | | | mac | varchar(20) | YES | | NULL | | | phone_type | varchar(30) | YES | | NULL | | +------------+-------------+------+-----+---------+----------------+ And here is my explain of the query: table type possible_keys key key_len ref rows Extra ops ALL phone NULL NULL NULL 345 where used; Using temporary; Using filesort phones ALL suffix1 NULL NULL NULL 809 where used I am very sure that my bottle-neck has to do with the creation of a temporary file for sorting. What can I do to optimize this. Thanks to all. --------------------------------------------------------------------- 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
