Hmm, if you run the sql_query_range query yourself, what does it return?
--
Pat
On 15/12/2009, at 11:24 AM, Steve H wrote:
> Hi Pat,
>
> Here is the sql_query:
>
> sql_query = SELECT SQL_NO_CACHE `businesses`.`id` * 1 + 0 AS `id` ,
> CAST(`businesses`.`name` AS CHAR) AS `name`, CAST(GROUP_CONCAT
> (DISTINCT IFNULL(`cs_categories`.`name`, '0') SEPARATOR ' ') AS CHAR)
> AS `category_names`, CAST(CONCAT_WS(' ', `city`.`city`,
> `city`.`state_code`) AS CHAR) AS `city`, `businesses`.`id` AS
> `sphinx_internal_id`, 4054125678 AS `class_crc`, '4054125678' AS
> `subclass_crcs`, 0 AS `sphinx_deleted`, IFNULL(`businesses`.`name`,
> '') AS `name_sort`, GROUP_CONCAT(DISTINCT IFNULL(IFNULL
> (`cs_categories`.`name`, ''), '0') SEPARATOR ' ') AS
> `category_names_sort`, CONCAT_WS(' ', IFNULL(`city`.`city`, ''), IFNULL
> (`city`.`state_code`, '')) AS `city_sort`, `businesses`.`lat` AS
> `lat`, `businesses`.`lon` AS `lon` FROM `businesses` LEFT OUTER
> JOIN `categorizations` ON (`businesses`.`id` =
> `categorizations`.`business_id`) LEFT OUTER JOIN `cs_categories` ON
> (`cs_categories`.`id` = `categorizations`.`cs_category_id`) LEFT
> OUTER JOIN `city` ON `city`.city_id = `businesses`.city_id WHERE
> `businesses`.`id` >= $start AND `businesses`.`id` <= $end GROUP BY
> `businesses`.`id` ORDER BY NULL
> sql_query_range = SELECT IFNULL(MIN(`id`), 1), IFNULL(MAX(`id`), 1)
> FROM `businesses`
>
> We turned on logging for every query in mysqld to troubleshoot this.
> This is the last query run:
>
> SELECT SQL_NO_CACHE `businesses`.`id` * 1 + 0 AS `id` , CAST
> (`businesses`.`name` AS CHAR) AS `name`, CAST(GROUP
> _CONCAT(DISTINCT IFNULL(`cs_categories`.`name`, '0') SEPARATOR ' ') AS
> CHAR) AS `category_names`, CAST(CONCAT_WS(' ', `city`.`city`,
> `city`.`state_code`) AS CHAR) AS `city`, `businesses`.`id` AS
> `sphinx_internal_id`, 4054125678 AS `class_crc`, '4054125678' AS
> `subclass_crcs`, 0 AS `sphinx_deleted`, IFNULL(`businesses`.`name`,
> '') AS `name_sort`, GROUP_CONCAT(DISTINCT IFNULL(IFNULL
> (`cs_categories`.`name`, ''), '0') SEPARATOR ' ') AS
> `category_names_sort`, CONCAT_WS(' ', IFNULL(`city`.`city`, ''), IFNULL
> (`city`.`state_code`, '')) AS `city_sort`, `businesses`.`lat` AS
> `lat`, `businesses`.`lon` AS `lon` FROM `businesses` LEFT OUTER
> JOIN `categorizations` ON (`businesses`.`id` =
> `categorizations`.`business_id`) LEFT OUTER JOIN `cs_categories` ON
> (`cs_categories`.`id` = `categorizations`.`cs_category_id`) LEFT
> OUTER JOIN `city` ON `city`.city_id = `businesses`.city_id WHERE
> `businesses`.`id` >= 2365000001 AND `businesses`.`id` <= 2365744349
> GROUP BY `businesses`.`id` ORDER BY NULL
>
> Which returns zero rows. In fact, the last 15+ queries return zero
> rows. The businesses table has no records with ids
> 2034010806...2500480805. And of course, the maximum id is
> 15250646237. We need *all* the rows indexed, of course... :)
>
> So should we be increasing the step_size to like 10 million?
>
> -Steve
>
> On Dec 14, 2:58 pm, Pat Allan <[email protected]> wrote:
>> Hi Steve
>>
>> Thinking Sphinx uses left outer joins (because that's what ActiveRecord
>> uses, and it's the best fit in this situation), and yes, one doc is one row.
>>
>> There's nothing odd in your index definition - so let's have a look at the
>> sql_query value of the source business_core_0, in
>> config/development.sphinx.conf (or production - they should be the same).
>> You should be able to run it manually, see if it returns the right results
>> (perhaps over a particular step, to keep things reasonably fast and
>> manageable).
>>
>> --
>> Pat
>>
>> On 15/12/2009, at 3:26 AM, Steve H wrote:
>>
>>
>>
>>> Thanks for the lightning-fast reply Pat. :)
>>
>>> define_index do
>>> indexes :name, :sortable => true
>>> indexes categories_and_tags(:name), :as
>>> => :category_names, :sortable => true
>>> indexes [city.city, city.state_code], :as => :city, :sortable =>
>>> true
>>> set_property :group_concat_max_len => 8192
>>> # attributes
>>> has lat, lon
>>> end
>>
>>> Every business has at least one category or tag, but I'm kind of
>>> assuming here that the indexer is doing a full left join (ie, not an
>>> inner join).
>>
>>> Could something in this block be causing the problem? And is 1 doc =
>>> 1 row in the db?
>>
>>> -Steve
>>
>>> On Dec 13, 7:03 pm, Pat Allan <[email protected]> wrote:
>>>> Hi Steve
>>
>>>> What does your define_index block for Business look like?
>>
>>>> --
>>>> Pat
>>
>>>> On 14/12/2009, at 1:44 PM, Steve H wrote:
>>
>>>>> OK first off -- I'm not 100% certain that 1 doc = 1 record. But if it
>>>>> DOES then, sphinx definitely didn't collect enough. We have over 13
>>>>> million rows in our database, but this was the output after indexing:
>>
>>>>> -----------------------------------------------
>>>>> indexing index 'business_core'...
>>>>> collected 1199 docs, 0.7 MB
>>>>> collected 0 attr values
>>>>> sorted 0.0 Mvalues, 100.0% done
>>>>> sorted 0.1 Mhits, 100.0% done
>>>>> total 1199 docs, 685923 bytes
>>>>> total 1381.345 sec, 496.56 bytes/sec, 0.87 docs/sec
>>>>> distributed index 'business' can not be directly indexed; skipping.
>>>>> Started successfully (pid 32011).
>>>>> -----------------------------------------------
>>
>>>>> After reading some of the docs, I thought... maybe I have to increase
>>>>> the sql_range_step in the sphinx.yml or increase the
>>>>> group_concat_max_len property. So I did those things, but the output
>>>>> after indexing is the same (albeit much much faster :)
>>
>>>>> -----------------------------------------------
>>>>> Stopped search daemon (pid 2810).
>>>>> Generating Configuration to /home/steve/insiderpages/config/
>>>>> sphinx_dev.sphinx.conf
>>>>> Sphinx 0.9.8.1-release (r1533)
>>>>> Copyright (c) 2001-2008, Andrew Aksyonoff
>>
>>>>> using config file '/home/steve/insiderpages/config/
>>>>> sphinx_dev.sphinx.conf'...
>>>>> indexing index 'business_core'...
>>>>> collected 1199 docs, 0.1 MB
>>>>> collected 0 attr values
>>>>> sorted 0.0 Mvalues, 100.0% done
>>>>> sorted 0.0 Mhits, 100.0% done
>>>>> total 1199 docs, 79605 bytes
>>>>> total 0.447 sec, 177901.38 bytes/sec, 2679.53 docs/sec
>>>>> distributed index 'business' can not be directly indexed; skipping.
>>>>> Started successfully (pid 6365).
>>>>> -----------------------------------------------
>>
>>>>> What's the problem?
>>
>>>>> -Steve
>>
>>>>> PS:
>>>>> mysql> select count(id) from businesses;
>>>>> +-----------+
>>>>> | count(id) |
>>>>> +-----------+
>>>>> | 13395683 |
>>>>> +-----------+
>>>>> 1 row in set (0.00 sec)
>>
>>>>> mysql> select max(id) from businesses;
>>>>> +-------------+
>>>>> | max(id) |
>>>>> +-------------+
>>>>> | 15250646126 |
>>>>> +-------------+
>>>>> 1 row in set (0.00 sec)
>>
>>>>> --
>>
>>>>> You received this message because you are subscribed to the Google Groups
>>>>> "Thinking Sphinx" group.
>>>>> To post to this group, send email to [email protected].
>>>>> To unsubscribe from this group, send email to
>>>>> [email protected].
>>>>> For more options, visit this group
>>>>> athttp://groups.google.com/group/thinking-sphinx?hl=en.
>>
>>> --
>>
>>> You received this message because you are subscribed to the Google Groups
>>> "Thinking Sphinx" group.
>>> To post to this group, send email to [email protected].
>>> To unsubscribe from this group, send email to
>>> [email protected].
>>> For more options, visit this group
>>> athttp://groups.google.com/group/thinking-sphinx?hl=en.
>
> --
>
> You received this message because you are subscribed to the Google Groups
> "Thinking Sphinx" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/thinking-sphinx?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"Thinking Sphinx" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/thinking-sphinx?hl=en.
