SEO

Hi Guys,

Firstly, this is the only time I have ever encountered this problem and
searching archives or google shed no luck since yesterday so here I am .

I have a table described below:

mysql> describe containers;
+--------------------+------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+--------------------+------------------------+------+-----+---------+----------------+
| internal_id | mediumint(20) unsigned | NO | PRI | NULL |
auto_increment |
| category_id | smallint(20) unsigned | YES | MUL | NULL
| |
| user_id | mediumint(20) unsigned | YES | MUL | NULL
| |
| parts_amount | int(2) | NO | | 0
| |
| file_name | varchar(64) | NO | MUL |
| |
| file_format | varchar(5) | NO | MUL |
| |
| file_info | text | NO | | NULL
| |
| file_description | text | YES | | NULL
| |
| admin_comments | text | YES | | NULL
| |
| is_approved | tinyint(1) | YES | MUL | 0
| |
| is_shared | tinyint(1) | YES | MUL | 1
| |
| is_deleted | tinyint(1) | YES | | 0
| |
| upload_date | bigint(10) | NO | MUL | 0
| |
| downloads | int(11) | YES | MUL | 0
| |
| last_download_date | bigint(10) | NO | MUL | 0
| |
| rating | decimal(3,1) | YES | MUL | 0.0
| |
| ftp_site | smallint(6) | NO | MUL | 0
| |
| total_votes | int(11) | NO | MUL | NULL
| |
| total_dnloads | int(11) | NO | | NULL
| |
| total_votes_ave | float | NO | | 0
| |
| total_votes_sum | int(11) | NO | | NULL
| |
| file_img | varchar(120) | NO | | NULL
| |
| file_extended_info | text | NO | | NULL
| |
| file_exist | tinyint(4) | NO | MUL | 0
| |
| post_options | varchar(20) | NO | | NULL
| |
+--------------------+------------------------+------+-----+---------+----------------+
25 rows in set (0.00 sec)

mysql> select count(*) from containers;
+----------+
| count(*) |
+----------+
| 9504 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from containers where upload_date < 1209208414 and
category_id = 120;
+----------+
| count(*) |
+----------+
| 795 |
+----------+
1 row in set (0.01 sec)

And I have queries like these:

select * from containers where upload_date < 1209208414 and category_id =
120 order by upload_date desc limit 0,25

and

select * from containers where upload_date < 1209208414 and category_id =
120 order by upload_date desc limit 175,25

These queries are dynamically generated and is is being paged for browser
display so the second query means I am on the 8th page for 25 items each
page.

The problem is, offsets 0...150 (LIMIT [0...150],25) will not return any
results while 175 onwards will. This happens only when I am filtering
category_id 120, all other categories does not yield this odd result. I have
no clue whatsoever what is going on, executing the query directly from the
server yields the same results. Now, if I omit either the order by or limit
clauses I get results all through out.

Hope someone can shed some light.

Jervin

You may want to check on the version you are running. There have been
a few odd bugs in various MySQL versions in regards to limits and
order by filtering. Although it usually involved joins and/or unions.
If you can't or don't want to upgrade your MySQL version, you can try
restructuring your query like this:

select * from (
select * from containers where upload_date < 1209208414 and
category_id =
120 order by upload_date desc ) as filter
limit 175,25

Technically, it's the same query and should return the same results.
It will be a little more intensive, since the inner query returns all
records, then a limit is imposed.

Brent Baisley
Systems Architect


On Apr 26, 2008, at 7:22 AM, j's mysql general wrote:

> Hi Guys,
>
> Firstly, this is the only time I have ever encountered this problem
> and
> searching archives or google shed no luck since yesterday so here I
> am .
>
> I have a table described below:
>
> mysql> describe containers;
> +--------------------+------------------------+------+-----+---------
> +----------------+
> | Field | Type | Null | Key | Default |
> Extra |
> +--------------------+------------------------+------+-----+---------
> +----------------+
> | internal_id | mediumint(20) unsigned | NO | PRI | NULL |
> auto_increment |
> | category_id | smallint(20) unsigned | YES | MUL | NULL
> | |
> | user_id | mediumint(20) unsigned | YES | MUL | NULL
> | |
> | parts_amount | int(2) | NO | | 0
> | |
> | file_name | varchar(64) | NO | MUL |
> | |
> | file_format | varchar(5) | NO | MUL |
> | |
> | file_info | text | NO | | NULL
> | |
> | file_description | text | YES | | NULL
> | |
> | admin_comments | text | YES | | NULL
> | |
> | is_approved | tinyint(1) | YES | MUL | 0
> | |
> | is_shared | tinyint(1) | YES | MUL | 1
> | |
> | is_deleted | tinyint(1) | YES | | 0
> | |
> | upload_date | bigint(10) | NO | MUL | 0
> | |
> | downloads | int(11) | YES | MUL | 0
> | |
> | last_download_date | bigint(10) | NO | MUL | 0
> | |
> | rating | decimal(3,1) | YES | MUL | 0.0
> | |
> | ftp_site | smallint(6) | NO | MUL | 0
> | |
> | total_votes | int(11) | NO | MUL | NULL
> | |
> | total_dnloads | int(11) | NO | | NULL
> | |
> | total_votes_ave | float | NO | | 0
> | |
> | total_votes_sum | int(11) | NO | | NULL
> | |
> | file_img | varchar(120) | NO | | NULL
> | |
> | file_extended_info | text | NO | | NULL
> | |
> | file_exist | tinyint(4) | NO | MUL | 0
> | |
> | post_options | varchar(20) | NO | | NULL
> | |
> +--------------------+------------------------+------+-----+---------
> +----------------+
> 25 rows in set (0.00 sec)
>
> mysql> select count(*) from containers;
> +----------+
> | count(*) |
> +----------+
> | 9504 |
> +----------+
> 1 row in set (0.00 sec)
> mysql> select count(*) from containers where upload_date <
> 1209208414 and
> category_id = 120;
> +----------+
> | count(*) |
> +----------+
> | 795 |
> +----------+
> 1 row in set (0.01 sec)
>
> And I have queries like these:
>
> select * from containers where upload_date < 1209208414 and
> category_id =
> 120 order by upload_date desc limit 0,25
>
> and
>
> select * from containers where upload_date < 1209208414 and
> category_id =
> 120 order by upload_date desc limit 175,25
>
> These queries are dynamically generated and is is being paged for
> browser
> display so the second query means I am on the 8th page for 25 items
> each
> page.
>
> The problem is, offsets 0...150 (LIMIT [0...150],25) will not return
> any
> results while 175 onwards will. This happens only when I am filtering
> category_id 120, all other categories does not yield this odd
> result. I have
> no clue whatsoever what is going on, executing the query directly
> from the
> server yields the same results. Now, if I omit either the order by
> or limit
> clauses I get results all through out.
>
> Hope someone can shed some light.
>
> Jervin