I am hoping I can get some help with a difficulty I am having with a table of mine. I am going to give a lot of background info, so please bear with me. First, let me say that I using 3.23.49. Now some info about the table. It currently has 5687391 rows. There are 3 columns in the table: ID (int), Url (varchar(255)), and Ref (varchar(255)). There are two indexes for the table: one on ID and the other on the first 15 characters of Ref.
Now my problem: select queries on this table with no joins searching for a constant in the Refs column only are extremely slow. Here is what all the sql statements look like "select Url from table where Ref like 'foo'". Pretty simple right? Here's the deal this table is used only by a little app that I wrote. Each time the app runs it queries this table as shown above about 4600 times with different values for "foo". Each query seems to be taking about 2 minutes to execute. This seems odd to me because I tested it while I was writing the app and the queries were taking a little over a second. So I took a look at my table structure and my indexes. I noticed from SHOW INDEX that the CARDINALITY for the index on the Ref column is NULL. From reading about CARDINALITY in the manual that NULL value seems like a very bad thing. Every time my app runs (about once a month) I dump all of the rows in this table and insert about 5 million new rows. I do not drop the table and recreate it. I began thinking that perhaps the dumping and inserting is perhaps creating havoc with my index on the Ref column. So without further ado, here are my questions for the list: Is having a NULL CARDINALITY bad? Do I have to rebuild the index each time I dump the entire table and insert millions of rows? If so, what is the best way to do that - myisamchk -a? Duncan -- Duncan Salada | Titan | www.titan.com/testeval Email: [EMAIL PROTECTED] | Voice: 301-925-3222x375 | Fax: 301-925-3216 --------------------------------------------------------------------- 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
