Hi, Philip,
Here is the query as you suggested:
SELECT ox.ensembl_id, x.dbprimary_id, x.display_id, db.db_name
FROM objectXref ox, Xref x, externalDB db
WHERE ox.ensembl_id IN ('7263', '7318', '8991', '17508')
AND x.xrefid = ox.xrefid
AND db.externalDBId = x.externalDBId;
>
> Maybe "WHERE ... IN ('7263', '7318', '8991', '17508') "
>
This change descreased the time between 20.21 sec to 23.71 sec. But repeating the
original
query (no single quote) only used 21.63 sec. The differences may be due to
server load at execution time. So string or number datatype is not the cause.
>
> EXPLAIN SELECT <your whole SQL statement here>
>
Here is the explain result:
+-------+--------+---------------+---------------------+---------+----------------+--------+-------------------------+
| table | type | possible_keys | key | key_len | ref |
|rows | Extra |
+-------+--------+---------------+---------------------+---------+----------------+--------+-------------------------+
| ox | index | NULL | ensembl_object_type | 45 | NULL |
|133764 | where used; Using index |
| x | eq_ref | PRIMARY | PRIMARY | 4 | ox.xrefId |
| 1 | |
| db | eq_ref | PRIMARY | PRIMARY | 4 | x.externalDBId |
| 1 | |
+-------+--------+---------------+---------------------+---------+----------------+--------+-------------------------+
3 rows in set (0.17 sec)
mysql> show index from objectXref;
+------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
|Collation | Cardinality | Sub_part | Packed | Comment |
+------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+---------+
| objectXref | 0 | ensembl_object_type | 1 | ensembl_object_type |
|A | NULL | NULL | NULL | NULL |
| objectXref | 0 | ensembl_object_type | 2 | ensembl_id |
|A | NULL | NULL | NULL | NULL |
| objectXref | 0 | ensembl_object_type | 3 | xrefId |
|A | NULL | NULL | NULL | NULL |
| objectXref | 1 | xref_index | 1 | objectxrefId |
|A | NULL | NULL | NULL | NULL |
| objectXref | 1 | xref_index | 2 | xrefId |
|A | NULL | NULL | NULL | NULL |
| objectXref | 1 | xref_index | 3 | ensembl_object_type |
|A | NULL | NULL | NULL | NULL |
| objectXref | 1 | xref_index | 4 | ensembl_id |
|A | NULL | NULL | NULL | NULL |
+------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+---------+
7 rows in set (0.18 sec)
It seems that mysql is not using the index xref_index, but using ensembl_object_type.
I tried to use hint (mysql version 3.23.25 beta)
SELECT ox.ensembl_id, x.dbprimary_id, x.display_id, db.db_name
FROM Xref x, externalDB db, objectXref ox
USING INDEX (xref_index)
WHERE ox.ensembl_id IN ('7263', '7318', '8991', '17508')
AND x.xrefid = ox.xrefid
AND db.externalDBId = x.externalDBId;
ERROR 1064: You have an error in your SQL syntax near 'USING INDEX (xref_index)
WHERE ox.ensembl_id IN ('7263', '7318', '8991', '17508'' at line 3
Anyone has tried using index hint before?
Thanks
Wilfred
---------------------------------------------------------------------
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
