>I think I figured the problem out, but I still do not understand
>what happened, the following is showing how I got the query to
>work. I basically had to re-import suppliersiclink table to get this
>to work right in MySQL 3.23.33, but why? The data was fine in
>MySQL 3.22.
>
>mysql> select * from suppliersiclink limit 10;
>+----+----------------+--------+-----------+
>| id | tstamp | sup_id | sic_code |
>+----+----------------+--------+-----------+
> | 1 | 00000000000000 | 523 | 07810000
> | 2 | 00000000000000 | 1500 | 07810000
> | 3 | 00000000000000 | 6179 | 07810000
> | 4 | 00000000000000 | 6486 | 07810000
> | 5 | 00000000000000 | 6620 | 07810000
> | 6 | 00000000000000 | 6894 | 07810000
> | 7 | 00000000000000 | 7284 | 07810000
> | 8 | 00000000000000 | 8949 | 07810000
> | 9 | 00000000000000 | 9450 | 07810000
> |10 | 00000000000000 | 9451 | 07810000
>+----+----------------+--------+-----------+
>10 rows in set (0.01 sec)
>
>mysql> select * from suppliersiclink2 limit 10;
>+----+-------+----------+
>| id | supid | sic_code |
>+----+-------+----------+
>| 1 | 523 | 07810000 |
>| 2 | 1500 | 07810000 |
>| 3 | 6179 | 07810000 |
>| 4 | 6486 | 07810000 |
>| 5 | 6620 | 07810000 |
>| 6 | 6894 | 07810000 |
>| 7 | 7284 | 07810000 |
>| 8 | 8949 | 07810000 |
>| 9 | 9450 | 07810000 |
>| 10 | 9451 | 07810000 |
>+----+-------+----------+
>10 rows in set (0.11 sec)
>
>mysql> describe suppliersiclink;
>+----------+---------------+------+-----+---------+----------------+
>| Field | Type | Null | Key | Default | Extra |
>+----------+---------------+------+-----+---------+----------------+
>| id | int(11) | | MUL | NULL | auto_increment |
>| tstamp | timestamp(14) | YES | | NULL | |
>| sup_id | int(11) | | MUL | 0 | |
>| sic_code | varchar(20) | | MUL | | |
>+----------+---------------+------+-----+---------+----------------+
>4 rows in set (0.02 sec)
>
>mysql> describe suppliersiclink2;
>+----------+------------------+------+-----+---------+----------------+
>| Field | Type | Null | Key | Default | Extra |
>+----------+------------------+------+-----+---------+----------------+
>| id | int(10) unsigned | | MUL | NULL | auto_increment |
>| supid | int(10) unsigned | | MUL | 0 | |
>| sic_code | varchar(8) | | MUL | | |
>+----------+------------------+------+-----+---------+----------------+
>3 rows in set (0.00 sec)
>
>mysql> select * from suppliersiclink where sic_code='07810000';
>Empty set (0.00 sec)
>
>mysql> select * from suppliersiclink where sic_code LIKE '07810000%';
>+--------+----------------+--------+-----------+
>| id | tstamp | sup_id | sic_code |
>+--------+----------------+--------+-----------+
> | 1 | 00000000000000 | 523 | 07810000
>
><--- cut --->
>
> |132654 | 00000000000000 | 104805 | 07810000
> |132665 | 00000000000000 | 104815 | 07810000
>+--------+----------------+--------+-----------+
>115 rows in set (0.04 sec)
>
>mysql> select * from suppliersiclink2 where sic_code='07810000';
>+--------+--------+----------+
>| id | supid | sic_code |
>+--------+--------+----------+
>| 1 | 523 | 07810000 |
>
><--- cut --->
>
>| 132665 | 104815 | 07810000 |
>+--------+--------+----------+
>115 rows in set (0.06 sec)
>
>mysql> SELECT
>supplier.id,supplier.company_name,supplier.contact_name,supplier.addre
>ss_street,supplier.address_city,
> ->
>supplier.address_state,supplier.address_zip,supplier.phone_business,su
>pplier.url,supplier.miniweb_live,
> ->
>supplier.miniweb_name,supplier.special_live,subcatsicbond.sub_catid,su
>bcatsicbond.siccode,suppliersiclink2.id
> -> FROM supplier,subcatsicbond,suppliersiclink2
> -> WHERE ((suppliersiclink2.sic_code=subcatsicbond.siccode) AND
> -> (supplier.id=suppliersiclink2.supid)
> -> AND (supplier.max_latitude<=32.99) AND (supplier.min_latitude>=31.55)
> -> AND (supplier.min_longitude>=89.25) AND (supplier.max_longitude<=90.97)
> -> AND (subcatsicbond.sub_catid=37));
>
><--- cut --->
>
>23 rows in set (1.00 sec)
>
>
>---------------------
>Johnny Withers
>[EMAIL PROTECTED]
>p. 601.853.0211
>c. 601.954.9133
Sir, the increase in speed (mentioned in your first posting) was
caused by switching from a LEFT JOIN to a more efficient inner join.
The fact that the query worked after reimporting the data suggests
that something went wrong the first time you imported, and the data
got screwed up.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
