-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Excuse the wrapping..
Any ideas on how to speed this up?
takes 10 secs on a p2 300.
If i could get the second table to not scan all 132,775 rows, it
would be great. I have indexes in it:
mysql> show keys from suppliersiclink;
+-----------------+------------+-------------+--------------+---------
- ----+-----------+-------------+----------+
| Table | Non_unique | Key_name | Seq_in_index |
| Column_name | Collation | Cardinality | Sub_part |
+-----------------+------------+-------------+--------------+---------
- ----+-----------+-------------+----------+
| suppliersiclink | 1 | sic_link | 1 | id
| | A | NULL | NULL | suppliersiclink |
| 1 | sic_index | 1 | sic_code | A |
| NULL | NULL | suppliersiclink | 1 | supplier_id |
| 1 | sup_id | A | NULL | NULL |
+-----------------+------------+-------------+--------------+---------
- ----+-----------+-------------+----------+
Also, this query does not work in mySQL 3.23.33
any idea why?
mysql> explain
-> SELECT
supplier.id,supplier.company_name,supplier.contact_name,supplier.addre
ss_street,
->
supplier.address_city,supplier.address_state,supplier.address_zip,supp
lier.phone_business,
->
supplier.url,supplier.miniweb_live,supplier.miniweb_name,supplier.spec
ial_live,
->
subcatsicbond.sub_catid,subcatsicbond.siccode,specials.id,specials.s_t
itle
-> FROM subcatsicbond
-> LEFT JOIN suppliersiclink ON
subcatsicbond.siccode=suppliersiclink.sic_code
-> LEFT JOIN supplier ON suppliersiclink.sup_id=supplier.id
-> LEFT JOIN specials ON supplier.id=specials.sup_id
-> WHERE ((subcatsicbond.sub_catid=20) AND
(supplier.max_latitude<=32.99)
-> AND (supplier.min_latitude>=31.55) AND
(supplier.min_longitude>=89.25)
-> AND (supplier.max_longitude<=90.97))
-> ORDER BY supplier.company_name ASC LIMIT 0,10;
+-----------------+------+---------------+-------------+---------+----
- --------------------+--------+------------+
| table | type | possible_keys | key | key_len |
| ref | rows | Extra |
+-----------------+------+---------------+-------------+---------+----
- --------------------+--------+------------+
| subcatsicbond | ref | supplier_id | supplier_id | 4 |
| ??? | 7 | | suppliersiclink |
| ALL | sic_index | NULL | NULL | NULL
| | 132775 | | supplier | ref | vendor_key |
| vendor_key | 4 | suppliersiclink.sup_id | 20 | where
| used | specials | ALL | supplier_key | NULL |
| NULL | NULL | 2 | |
+-----------------+------+---------------+-------------+---------+----
- --------------------+--------+------------+
4 rows in set (0.00 sec)
- ---------------------
Johnny Withers
[EMAIL PROTECTED]
p. 601.853.0211
c. 601.954.9133
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com>
iQA/AwUBOpP9vbFNxPoD98ryEQJPkwCgjIQ7hlHAk17sAQfSW38w0PkAEaoAoLIk
AczTsf21QrUIwPfxiKstSf2Z
=Kwl9
-----END PGP SIGNATURE-----
---------------------------------------------------------------------
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
