# Can't get this query optimized **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [November 18, 2009, 7:54pm UTC](https://forums.percona.com/t/cant-get-this-query-optimized/1295 "2009-11-18T19:54:34Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![SamG](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@SamG](https://forums.percona.com/u/SamG)\ **Post date:** [November 18, 2009, 7:54pm UTC](https://forums.percona.com/t/cant-get-this-query-optimized/1295/1 "2009-11-18T19:54:34Z")
So - I have 3 tables: feed\_datastore, feed\_index\_site, and site. feed\_datastore has uuid | ts | data (which is a text column with serialized data) feed\_index\_site is site\_ID | uuid site has site\_ID | xxxx (bunch of other columns - eg one being country\_ID). feed\_datastore has data from each site and feed\_index\_site connects site\_ID to uuid What I’m trying to do is extract uuid, data from feed\_datastore based on the country\_ID in site. I get something like this: SELECT a.ts,a.uuid,a.data FROM `feed_index_site` b, `feed_datastore` a, `site` c WHERE a.uuid=b.uuid AND b.site\_ID=c.site\_ID AND c.country\_ID=4 ORDER BY ts DESC LIMIT 6 The resulting EXPLAIN has “Using index; Using temporary; Using filesort” on table c (site) - which is killing performance. If I remove the ORDER BY ts the temporary/filesort disappear. I’ve tried a bunch of indexes which I thought would work, and even altered the feed\_datastore table so that it is ordered by ts DESC but nothing works - anyone have a clue how I can make this run smoother? ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [November 19, 2009, 3:52am UTC](https://forums.percona.com/t/cant-get-this-query-optimized/1295/2 "2009-11-19T03:52:41Z")
add country\_ID to the datastore table (and why is feed\_index\_site a seperate table?) then aadd an index on (country\_ID,ts). ---
**Author:** ![SamG](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@SamG](https://forums.percona.com/u/SamG)\ **Post date:** [November 19, 2009, 11:10am UTC](https://forums.percona.com/t/cant-get-this-query-optimized/1295/3 "2009-11-19T11:10:27Z")
I went a key,value approach - so feed\_datastore’s key is ‘uuid’, and the feed\_index\_site relates/connects `feed_datastore` and `site` The reason I cannot add the country\_ID value to `feed_datastore` is because the country value could change in the site table, requiring the system to then go back and update that value in `feed_datastore` anytime country\_ID was changed in `site` ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [November 19, 2009, 4:32pm UTC](https://forums.percona.com/t/cant-get-this-query-optimized/1295/4 "2009-11-19T16:32:41Z")
That sucks. You might try this, which will only be fast for countries appearing often. Add an index on table c on (site\_ID,country\_ID) Add an index on table a on (ts) SELECT a.ts,a.uuid,a.data FROM `feed_datastore` a FORCE INDEX(name of index on the ts\_field) STRAIGHT\_JOIN `feed_index_site` b ON(uuid) STRAIGHT\_JOIN `site` c ON(site\_ID) WHERE c.country\_ID=4 ORDER BY a.ts DESC LIMIT 6 If this is not fast, provide the output of EXPLAIN. ---
**Author:** ![SamG](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@SamG](https://forums.percona.com/u/SamG)\ **Post date:** [November 19, 2009, 6:25pm UTC](https://forums.percona.com/t/cant-get-this-query-optimized/1295/5 "2009-11-19T18:25:41Z")
Wanted to quickly thank you for trying to help here gmouse. I had both indexes already created. Unfortunately when I try to run your query, I get an ambiguous error on both straight\_joins (something I’ve never used before). I read up on it and modified your query to this: EXPLAIN SELECT a.ts, a.uuid, a.data FROM `feed_datastore` a FORCE INDEX (ts) STRAIGHT\_JOIN `feed_index_site` b ON ( a.uuid = b.uuid ) STRAIGHT\_JOIN `site` c ON (b.site\_ID=c.site\_ID) WHERE c.country\_ID =51 ORDER BY a.ts DESC LIMIT 6 id select\_type table type possible\_keys key key\_len ref rows Extra1 SIMPLE a index NULL ts 4 NULL 113512 1 SIMPLE b ref PRIMARY,site\_ID PRIMARY 42 data.a.uuid 1 Using index1 SIMPLE c eq\_ref PRIMARY,country\_ID,country\_ID\_2,site\_ID,site\_ID\_2,… PRIMARY 3 data.b.site\_ID 1 Using where The problem is its basically going through the entire feed\_datastore table - not so bad at 115k rows, but that will bog down really quickly (at peak it will be growing 100k a day). (I have some extra indexes as I’ve been trying to figure out what would work). ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [November 21, 2009, 2:41pm UTC](https://forums.percona.com/t/cant-get-this-query-optimized/1295/6 "2009-11-21T14:41:46Z")
> > The problem is its basically going through the entire feed\_datastore table - not so bad at 115k rows not true, please learn to read explain output when limit is used.