--On Sunday, June 13, 2004 12:56 -0700 David Blomstrom <[EMAIL PROTECTED]> wrote:
Aha! I added "EXPLAIN," like this...
Please read the docs. And use the mysql CLI/monitor tool.
Explain does just that, it EXPLAINs to you, the database programmer, what the MySQL engine will do when it goes about satisfying your query. In the case below I can tell you that only a full text search will help (please also look at reference documentation available at http://dev.mysql.com/ for that).
In simple terms an index is exactly the same as an index for an encyclopedia. You give have a key, say the name of a city, and you want to know where you can find more information on that city. You look that city up in the index, and it says go to page 3127, so you then flip to page 3127 and read all about say, Portugal. Now say you wanted to know all cities with a population greater than 5000. The encyclopedia doesn't have an index for this lets say. So what do you do? You read each and every single entry for every city. Throwing out the ones that don't match and writing down the ones that do, very time consuming. But if the encyclopedia had an index that listed (and ranked) each city by its population you could look there and quickly find them, and what pages they are on.
A database index does PRECISELY the same thing for your database server. IT tells it where (on the disk/in the MYD data file) it can find a record. IF one doesn't exist for the KEY you're asking for it does what you'd do, it reads the whole book! A database also needs a little more information than that to do somethin intelligent, like in my example above with the populations, this is where *CORRECT* column types come in. For gods sake if it's an int, store it as one. It'll store smaller, and indexes will work as you expect on them. A sickeningly common mistake of many beginners is to use CHAR/VARCHAR or BLOB (TEXT, TINYTEXT...) for everything, and not to use indexes.
In your case because of the leading % and trailing % wildcards a full table scan is inevitable. What you want is a full text index, not a normal index, this allows you to look very efficiently for keywords and pull them out of the database with great speed. It's akin to an index that lists every word in a book, and what pages that word occurs on (what records or tuples in database speak).
Try rewriting your query like this (note how I add LIMIT, this tells the database not to send us rows we're not going to use or display, this is another tool you should read up on):
SELECT Name, Residents, Pop, Capital FROM basics WHERE MATCH (Capital) AGAINST ('volcan') LIMIT 10;
but first execute this on your database:
CREATE FULLTEXT INDEX CapitalFTSIDX (Capital);
Or restrict your searching to things ending in %'s and use a standard index/key.
HTH!
$res = mysql_query ("EXPLAIN SELECT Name, Residents, Pop, Capital FROM basics where Capital like '%VOLCAN%'") or die (mysql_error());
but none of my data displays at all.
However, I did indeed strip out all my keys so I could get my tables published online. I probably only needed to get rid of the foreign keys, but I didn't want to take any chances.
Anyway, I added a primary key, but the table still loads slowly. However, I'm guessing that it isn't enough to have a key on a table - that key also has to be cited in your query/select statements. Is that right?
Thanks.
-- Michael Loftis Modwest Sr. Systems Administrator Powerful, Affordable Web Hosting
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
