Using "select Url from table where Ref like 'foo'" gives these results:
1 row in set (2 min 11.54 sec)
Using "select Url from table where Ref = 'foo'" gives these results:
1 row in set (8 min 47.69 sec)
The same "foo" was used for each one of those queries.
I believe the time problem has to do with the indexes - particularly the
cardinality. But I am not sure based on the documentation. I am also a
little reluctant to use myisamchk -a because I am afraid it will take a long
time. So my questions again are:
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
> -----Original Message-----
> From: Gerald Clark [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, March 21, 2002 11:11 AM
> To: Salada, Duncan
> Cc: '[EMAIL PROTECTED]'
> Subject: Re: help with index/query
>
>
> what about
> select Url from table where Ref = 'foo'
>
> Salada, Duncan wrote:
>
> >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?
>
---------------------------------------------------------------------
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
