please help! i have a fairly large innoDB table with 800mb (index 500 mb, data 300mb) and 1.8 million data sets.
the server has 8 gig ram. the statement SELECT id FROM table1 WHERE cityname = 'bla' (cityname has been indexed) takes 0.0002 seconds and returns 0 rows, which was expected. the statement: SELECT id from table1 WHERE id IN ( SELECT id FROM table1 WHERE cityname = 'bla ) still returns 0 rows but takes 12.9 seconds!! does anyone know why it takes so long and if there is anything i can do? thank you for your help, i really appreciate it.