Re[2]: MySQL Techniques

> Run analyze on your tables:
> see: http://www.mysql.com/doc/en/ANALYZE_TABLE.html
> Then use explain on your queries to see what the engine is doing and
> what suggestions it makes for indexes.  If practical, follow the advice.
> see: http://www.mysql.com/doc/en/EXPLAIN.html

Well, I guess my inexperience is glaringly obvious here... I'm not
making much sense of the results of doing the above. Is it mainly to
see which indexes are being used?

> Let's say you have a full text search running on a description field in
> your track table ... that is a slow and painful query, no matter how you
> slice it; but it will be slower and more painful if you're joining at
> the same time as doing the table scan of keywords.  So in this case, I
> might do 2 queries, the initail full text table scan just against the
> track table and then once you have that smaller recordset build or run a
> second query that does all the joining -- that could be accomplished 2
> ways, first by having the first query dump into a temporary table that's
> then used in an outer join in the second -- or if there is an
> identifying key in the track database (AlbumID???) then you could build
> a query on the fly using an 'IN' clause in the select that is build from
> the AlbumIDs returned by query 1.

Right now, I'm doing three initial queries, one to create a temp table
for each normal table that reduces the number of possibilities. Then,
I do a query on the temp tables to match up corresponding ID (the
album id #s). On some searches (with common terms like "love" or
"hope" in titles, etc.) it's still taking 10 seconds or so perform the
search... and I'm sure it's the join that's bogging it down. Is this
sort of what you meant though?

~ The U.P. Web Maestro (Nathan Lyle)

  E-Mail: nathan(at)upwebmaestro.com
  Online: www.upwebmaestro.com
   Phone: (906)485-4806

"But what... is it good for?" - Engineer at the Advanced Computing Systems Division of IBM, 1968, commenting on the microchip.

HWG hwg-techniques mailing list archives, maintained by Webmasters @ IWA