First, you don't say how much memory your system has. If it has less
than 500Meg, you should look into adding some. Mysql will slow down
if has to run off swap space.
Second, yes, that is a lot of rows for an old p2 to sift through. Do
you need to search on all columns? if not, consider moving those
columns to another table, and joining as needed to get at the info.
On 21 Feb 2001, at 11:41, Johnny Withers wrote:
> -----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
>
John Jensen
520 Goshawk Court
Bakersfield, CA 93309
661-833-2858
---------------------------------------------------------------------
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
