# Getting 1000 documents by id from MySQL / Sphinx **URL:** \ **Category:** Sphinx & Full-Text Search\ **Created:** [October 21, 2009, 3:20pm UTC](https://forums.percona.com/t/getting-1000-documents-by-id-from-mysql-sphinx/2081 "2009-10-21T15:20:33Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![nerolabs](https://avatars.discourse-cdn.com/v4/letter/n/cc9497/32.png) [@nerolabs](https://forums.percona.com/u/nerolabs)\ **Post date:** [October 21, 2009, 3:20pm UTC](https://forums.percona.com/t/getting-1000-documents-by-id-from-mysql-sphinx/2081/1 "2009-10-21T15:20:33Z")
Dear Percona Community: I have a successful sphinx installation that is returning 1000 document IDs on common queries, and it performs the search in less that 0.2 seconds in nearly every case. However, when I take these document ids and perform a query like this: select data\_point\_1, data\_point\_2 from my\_table where id in (14084528, 14140843, 14140471, 14140817, 14160141, 14096445, … goes on for 1000 document ids) Here is the explain of the query: ±—±------------±------±------±----------------------- -±------------±--------±----------------------±-----±-- ----------+ | id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra | ±—±------------±------±------±----------------------- -±------------±--------±----------------------±-----±-- ----------+ | 1 | SIMPLE | um | range | PRIMARY | PRIMARY | 8 | NULL | 1000 | Using where | | 1 | SIMPLE | up | ref | unique\_pics,idx\_meta\_id | unique\_pics | 8 | flixz.um.urls\_meta\_id | 1 | | ±—±------------±------±------±----------------------- -±------------±--------±----------------------±-----±-- ----------+ MySQL is taking more than 30 seconds to return the results on the EXACT ids (which of course are also the primary key), and the explain above looks solid. I hired a MySQL consultant to figure out what was going on here, and his response was that the majority of the time MySQL is operating it’s parsing the very long query (1000 document ids) into a parse plan, and then optimizing, and then finally returning the query (which happens pretty quickly). Our database is pretty large (300GB and growing)… We also tried writing the document ids to a temporary table, and doing a join so that it’s the same query every time (keeping the parse path for the query in memory), and that ran even slower. My question is this: What do other users of large databases do when they query MySQL for the document\_ids generated by sphinx to optimize query result times from MySQL? All this kinda takes out the purpose of sphinx returning the 1000 document ids so fast, if MySQL is just going to choke on the query to pull the needed data points on the query for those 1000 documents! Thanks for any help you can provide… Nero ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [October 21, 2009, 3:55pm UTC](https://forums.percona.com/t/getting-1000-documents-by-id-from-mysql-sphinx/2081/2 "2009-10-21T15:55:37Z")
‘Parsing the query’, 30s? Bollocks ) Try faster disks. ---
**Author:** ![nerolabs](https://avatars.discourse-cdn.com/v4/letter/n/cc9497/32.png) [@nerolabs](https://forums.percona.com/u/nerolabs)\ **Post date:** [October 21, 2009, 3:59pm UTC](https://forums.percona.com/t/getting-1000-documents-by-id-from-mysql-sphinx/2081/3 "2009-10-21T15:59:47Z")
8 x 500 GB SATA2 … striped… ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [October 21, 2009, 4:13pm UTC](https://forums.percona.com/t/getting-1000-documents-by-id-from-mysql-sphinx/2081/4 "2009-10-21T16:13:40Z")
| [B]nerolabs wrote on Wed, 21 October 2009 23:29[/B] | | 8 x 500 GB SATA2 ... striped... | It is probably doing other stuff in the meanwhile. 8x 500 GB regular 7200 rpm disks is not that impressive, a single ssd would outperform it for this query. If only most frequently accessed document id's could fit in memory, it would be much quicker. ---
**Author:** ![qspy](https://avatars.discourse-cdn.com/v4/letter/q/a88e57/32.png) [@qspy](https://forums.percona.com/u/qspy)\ **Post date:** [November 3, 2009, 9:32pm UTC](https://forums.percona.com/t/getting-1000-documents-by-id-from-mysql-sphinx/2081/5 "2009-11-03T21:32:33Z")
Nerolabs its very interesting what you writing because i have EXACTLY same problem :D. Grabbing id’s by sphinx very fast, but later all search queries are on slave which choking very much with queries like this. query: # User@Host: root[root] @ localhost # Query\_time: 17 Lock\_time: 0 Rows\_sent: 16 Rows\_examined: 80SELECT pt.post\_text, etc, p._, f.forum\_id, etc, t._, u.username FROM phpbb\_forums f, phpbb\_topics t, phpbb\_users u, phpbb\_posts p, phpbb\_posts\_text pt WHERE p.post\_id IN (1243453, 1243480, 1243644, 1243809, 1243896, … overall 658 id’s are here, counter by [http://www.javascriptkit.com/script/script2/countwords.shtml](http://www.javascriptkit.com/script/script2/countwords.shtml)) AND pt.post\_id = p.post\_id AND f.forum\_id = p.forum\_id AND p.topic\_id = t.topic\_id AND p.poster\_id = u.user\_id ORDER BY pt.post\_id DESC LIMIT 0, 16; explain: ±—±------------±------±-------±-----------------------------------------------±--------±--------±---------------±-----±------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±------------±------±-------±-----------------------------------------------±--------±--------±---------------±-----±------------+| 1 | SIMPLE | pt | range | PRIMARY | PRIMARY | 3 | NULL | 658 | Using where || 1 | SIMPLE | p | eq\_ref | PRIMARY,forum\_id,topic\_id,poster\_id,topic\_id\_2 | PRIMARY | 3 | pt.post\_id | 1 | || 1 | SIMPLE | f | eq\_ref | PRIMARY | PRIMARY | 2 | p.forum\_id | 1 | || 1 | SIMPLE | u | eq\_ref | PRIMARY | PRIMARY | 3 | p.poster\_id | 1 | || 1 | SIMPLE | t | eq\_ref | PRIMARY | PRIMARY | 3 | p.topic\_id | 1 | |±—±------------±------±-------±-----------------------------------------------±--------±--------±---------------±-----±------------+ Dunno why so long, no lock\_time (it says 0) and when i executed it at night it takes “only” 0.11. I got “race condition” sometimes with this queries and then is horror like this (probably myisam LOCKS because quite long queries much at once almost): # User@Host: root[root] @ localhost # Query\_time: 68 Lock\_time: 55 Rows\_sent: 40 Rows\_examined: 3120# Time: 091102 15:44:13# User@Host: root[root] @ localhost # Query\_time: 71 Lock\_time: 57 Rows\_sent: 16 Rows\_examined: 80 etc., etc., dunno why and where this Lock\_time started… ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [November 4, 2009, 1:19pm UTC](https://forums.percona.com/t/getting-1000-documents-by-id-from-mysql-sphinx/2081/6 "2009-11-04T13:19:54Z")
Locking is horrible, switch to InnoDB. Can’t you circumvent this entire problem by storing those other fields in sphinx?