Hi, Upon closer examination of the query, it seems that both ensembl_object_type, and xref_index does not have ensembl_id in the 1st position for the index. So the hints would not work any way. I would still appreciate if someone points out the syntax error I got.
Looking at the new schemaCore table at http://www.ensembl.org/Docs/wiki/html/EnsemblDocs/SchemaCore.html ensembl_id is the referred by transcript_stable_id:stable_id, etc. This points to the problem of incompatible format between ensembl_id (number e.g. 7263) and stable_id (string e.g. ENSX000...). Will this be resolved? Thanks Wilfred On Thu, 29 Nov 2001, Wilfred Li, Ph.D. wrote: > 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
