Hi, I have a table (named "text") looking like this, somewhat simplified:
+-------+--------+ | id | string | +-------+--------+ | 1 | Here | | 2 | is | | 3 | some | | 4 | text | | 5 | . | | 6 | And | | 7 | it's | | 8 | in | | 9 | a | | 10 | table | | 11 | . | [...] It contains about 12 million rows, but for simple queries, selects are very fast (ie. mostly sub-second). However, this self join mysql> SELECT a.string, b.string FROM text a, text b WHERE a.string = 'something' and b.string = 'other' and b.string_id = a.string_id+1; is comparatively slow (over a minute to retreive 20 rows), suggesting that indexes are not used. Does anyone know how to optimize this query, or know of another way to select "string x, if the next string is y"?. An obvious answer would of course be "Do you really need a mysql table for this? There are a gazillion full text indexing packages out there!". Yes, I do need to store it in a mysql table - it's part of a much larger database. Thanks in advance. Lars Nygaard, The Text Laboratory, University of Oslo SHOW INDEX output: +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+ | oslo2 | 0 | PRIMARY | 1 | string_id | A | 12162868 | NULL | NULL | | | oslo2 | 1 | string | 1 | string | A | 434388 | NULL | NULL | | | oslo2 | 1 | string | 2 | string_id | A | 12162868 | NULL | NULL | | | oslo2 | 1 | string_id | 1 | string_id | A | 12162868 | NULL | NULL | | | oslo2 | 1 | string_id | 2 | string | A | 12162868 | NULL | NULL | | +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+---------+ EXPLAIN SELECT output: +-------+--------+--------------------------+---------+---------+------+------+-------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-------+--------+--------------------------+---------+---------+------+------+-------------------------+ | a | range | string | string | 40 | NULL | 3159 | where used; Using index | | b | eq_ref | PRIMARY,string,id | PRIMARY | 4 | func | 1 | where used | +-------+--------+--------------------------+---------+---------+------+------+-------------------------+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
