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] <javascript:>.
> > To post to this group, send email to
> > [email protected]<javascript:>
> .
> > 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.
