mysql空表select 查询很慢_MySQL简单选择查询很慢

I have a large mysql-table with about 110.000.000 items

The Table Design is:

CREATE TABLE IF NOT EXISTS `tracksim` (

`tracksimID` int(11) NOT NULL AUTO_INCREMENT,

`trackID1` int(11) NOT NULL,

`trackID2` int(11) NOT NULL,

`sim` double NOT NULL,

PRIMARY KEY (`tracksimID`),

UNIQUE KEY `TrackID1` (`trackID1`,`trackID2`),

KEY `sim` (`sim`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Now I want to query a normal query:

SELECT trackID1, trackID2 FROM `tracksim`

WHERE sim > 0.5 AND

(`trackID1` = 168123 OR `trackID2`= 168123)

ORDER BY sim DESC LIMIT 0,100

The Explain statement gives me:

+----+-------------+----------+-------+---------------+------+---------+------+----------+----------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+----------+-------+---------------+------+---------+------+----------+----------+-------------+

| 1 | SIMPLE | tracksim | range | TrackID1,sim | sim | 8 | NULL | 19980582 | 100.00 | Using where |

+----+-------------+----------+-------+---------------+------+---------+------+----------+----------+-------------+

The query seems to be very slow(about 185 seconds), but i don't know if it is only because of the amount of items in the table. Do you have a tip how I can speedup the query or the table-lookup?

解决方案

Mostly I agree with Drap, but the following variation of the query might be even more efficient, especially for larger LIMIT:

SELECT TS2.*

FROM (

SELECT tracksimID, sim

FROM tracksim

WHERE trackID1 = 168123

AND sim > 0.5

UNION

SELECT trackSimID, sim

FROM tracksim

WHERE trackid2 = 168123

AND ts.sim > 0.5

ORDER BY sim DESC

LIMIT 0, 100

) as PreQuery

JOIN TrackSim TS2 USING (TrackSimID);

Requires (trackID1, sim) and (trackID2, sim) indexes.