Can you show us the index definition and the related associations? On 17/07/2013, at 5:02 AM, Daniel Vandersluis wrote:
> I did an explain query, like I said, all the joins are indexed (either as > primary keys or actual indexes). In both cases (before and after adding the > attribute), it takes milliseconds to execute the query, but a while before > all data has been collected. > > +----+-------------+-----------------------------+--------+--------------------------------------------+--------------------------------------------+---------+------------------------------------------------+-------+-------------+ > | id | select_type | table | type | possible_keys > | key | > key_len | ref | rows | Extra > | > +----+-------------+-----------------------------+--------+--------------------------------------------+--------------------------------------------+---------+------------------------------------------------+-------+-------------+ > | 1 | SIMPLE | users | index | NULL > | PRIMARY | 4 > | NULL | 23754 | | > | 1 | SIMPLE | documents | ref | > index_documents_on_user_id | index_documents_on_user_id > | 5 | prod-2013-05-28.users.id | 1 > | | > | 1 | SIMPLE | locations | eq_ref | PRIMARY > | PRIMARY | 4 > | prod-2013-05-28.users.location_id | 1 | | > | 1 | SIMPLE | job_applications | eq_ref | PRIMARY > | PRIMARY | 4 > | prod-2013-05-28.users.last_job_application_id | 1 | | > | 1 | SIMPLE | sources | eq_ref | PRIMARY > | PRIMARY | 4 > | prod-2013-05-28.job_applications.source_id | 1 | | > | 1 | SIMPLE | tags_users | ref | > index_tags_on_user_id | index_tags_on_user_id > | 5 | prod-2013-05-28.users.id | 1 > | | > | 1 | SIMPLE | tags | eq_ref | PRIMARY > | PRIMARY | 4 > | prod-2013-05-28.tags_users.tag_id | 1 | Using index | > | 1 | SIMPLE | profiles | ref | > index_profiles_on_user_id_and_profile_type | > index_profiles_on_user_id_and_profile_type | 5 | > prod-2013-05-28.users.id | 1 | Using index | > | 1 | SIMPLE | job_applications_users | ref | > index_job_applications_on_user_id | > index_job_applications_on_user_id | 5 | > prod-2013-05-28.users.id | 1 | | > | 1 | SIMPLE | job_applications_users_join | ref | > index_job_applications_on_user_id | > index_job_applications_on_user_id | 5 | > prod-2013-05-28.users.id | 1 | Using index | > | 1 | SIMPLE | candidate_answers | ref | > uidx_on_candidate_answers | uidx_on_candidate_answers > | 5 | prod-2013-05-28.job_applications_users_join.id | 8 > | Using index | > +----+-------------+-----------------------------+--------+--------------------------------------------+--------------------------------------------+---------+------------------------------------------------+-------+-------------+ > > The join is exactly the same except for the alias: > > LEFT OUTER JOIN `job_applications` `job_applications_users` > ON `job_applications_users`.`user_id` = `users`.`id` > LEFT OUTER JOIN `job_applications` `job_applications_users_join` > ON `job_applications_users_join`.`user_id` = `users`.`id` > > > > On Tuesday, July 16, 2013 2:12:02 PM UTC-4, Pat Allan wrote: > Hi Daniel > Any slowness in indexing is going to be related to the generated SQL query - > when you say the query itself seems fast, how are you comparing it? I'd > recommend running it through EXPLAIN to get some idea of what could be > causing it to be slow. > > Is there any way in which the join is different beyond the aliased name? > > As for upgrading - I'm not sure if there's been any changes related to query > generation, but using the latest releases is always recommended (in this > case, 2.1.0). > > Cheers > > -- > Pat > > > On 17/07/2013, at 3:09 AM, Daniel Vandersluis wrote: > > > Is there any reason an index would suddenly take 3x as long to index after > > adding an extra has_many attribute to the index definition? The query > > itself is completely indexed, and takes about 1.5ms to run (plus data > > collection time, there are about 200k records in the main table that is > > being indexed, plus a bunch of attributes - sphinx reports it as a 466MB > > index). Prior to adding the extra attribute, indexing took about 9 minutes, > > and now it takes 29. The new attribute averages just over 1 record per row, > > with a maximum of 78. > > > > Possibly related is that adding the new attribute causes the query TS > > generates to add a duplicate join (with a different alias) to a join that's > > added by a different attribute (however as mentioned the query itself seems > > to be fast). > > > > I'm using ThinkingSphinx 2.0.11 currently - would updating to TS3 help? > > > > -- > > You received this message because you are subscribed to the Google Groups > > "Thinking Sphinx" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [email protected]. > > To post to this group, send email to [email protected]. > > Visit this group at http://groups.google.com/group/thinking-sphinx. > > For more options, visit https://groups.google.com/groups/opt_out. > > > > > > > > > > > > > > > > > -- > You received this message because you are subscribed to the Google Groups > "Thinking Sphinx" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/thinking-sphinx. > For more options, visit https://groups.google.com/groups/opt_out. > > -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/thinking-sphinx. For more options, visit https://groups.google.com/groups/opt_out.
