RE: help with index/query
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
help with index/query
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
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. - 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
