Date query optimization
Hi folks, I do a considerable amount of queries based on a date, and or date range. I have not had much luck with optimizing these queries. In some cases I use a date field and others a datetime field. The following query searches through 34,000 + records, while specifiying the exact date searches through 9 records. 'ROWS: 9 SEARCHED explain select a.submitid,a.url,a.submitdate,a.name,a.company,a.address1,a.city,a.state,a.z ipcode,a.country,a.email,a.phone,a.keywords,a.title,a.description,a.submitte dby from submit as a inner join re_idx as b on a.submitid = b.submitid where a.submitdate = '2003-07-01'; ROWS: 34,000 + searched explain select a.submitid,a.url,a.submitdate,a.name,a.company,a.address1,a.city,a.state,a.z ipcode,a.country,a.email,a.phone,a.keywords,a.title,a.description,a.submitte dby from submit as a inner join re_idx as b on a.submitid = b.submitid where year(a.submitdate)=2003 and month(a.submitdate)=7 and dayofmonth(a.submitdate)=1; --and year(a.submitdate)=2003 and month(a.submitdate)=7 and dayofmonth(a.submitdate)15; Notice the 2nd where statement, this is how I typically do my date queries (and it is slow). This is because I might also be searching for a range of dates (as in the commented out and clause above). Is the to_days function faster than these date functions, or have any effect whatsoever? Thanks for the help. Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date query optimization
Keith, The normal way to do a search for a range of dates would be ... WHERE a.submitdate BETWEEN '2003-07-01' AND '2003-07-14'; Is that what you're looking for? submitdate happens to be a DATETIME field. Your suggested query doesn't pull any results. Must I convert the field first? But then is my query unoptimized? Should I consider storing date parts into my database; seperate column for year - month - date and (time parts..) Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Mull in show fields and table keeps crashing
I'm not sure what errors are being reported to the server. I'm not sure how to get those. I am not local to the box. Is there a way to look at the log files without being at the box? Karl - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Karl J. Stubsjoen' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 2:20 PM Subject: RE: Strange Mull in show fields and table keeps crashing The `MUL` indicates that the column have multiple values. When the server `crashes`, what errors are being reported log file? -Original Message- From: Karl J. Stubsjoen [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 24, 2003 4:13 PM To: [EMAIL PROTECTED] Subject: Strange Mull in show fields and table keeps crashing Hello, I have this table: CLUB Field Type Null Key Default Extra clubid int(11) PRI NULL auto_increment clubusgf int(11) UNI 0 program char(3) url varchar(75) email varchar(75) phone varchar(14) fax varchar(14) address1 varchar(75) MUL address2 varchar(75) city varchar(75) notes varchar(255) zip varchar(10) clubname varchar(75) contact_primary varchar(40) state char(2) contact_secondary varchar(40) See address1 above the the MUL next to it. What is that? Also, this table keeps crashing, it crashes when I make an edit to anything in this field. So I copy the column, move the data over and then I can make edits in this field. However, another field in my table will get this strange MUL indication. From this point forward then, any changes to the data in that column will cause the table to crash. I've succesfully repaired the table a 1/2 dozen times or so... and copied/renamed about 4 of the columns as they took on this MUL characteristic. Any ideas how to fix this problem? Any ideas what is going on? Here is my version of MySQL: Server version: 4.0.0-alpha Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to retrieve Insert Into Select From records effected
Hello, How can I determin what erros occur from an Insert into select from statement? Namely, I'm looking for any duplicate record errors. It is important to inform the user (in a web browser) whether or not a particular insert would fail. Must I perform a lookup first, or is there a way to capture this error, or look up a list of errors for the last SQL command. Thanks, Karl - 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
Request for help - Table Crashing
Hello,
This is my 2nd post, not to sound pushy, just raising the urgency of my
dilemna. My table is crashing way to often (I'd like it to not crash at
all).
I'm not sure what to do, how to trouble shoot, and how to fix my problem. I
will post the structure of the table here, as well as the pertinent
hardware/software supporting my DB.
I'm wondering if the problem might be with the FullText indexes?
Please Advise.
Karl
TABLE STRUCTURE:
# MySQL dump 8.16
#
# Host:Database: submitsearch
#
# Server version4.0.0-alpha
#
# Table structure for table 'submit'
#
CREATE TABLE submit (
submitid int(11) NOT NULL auto_increment,
name varchar(50) default '',
title varchar(80) NOT NULL default '',
url varchar(100) NOT NULL default '',
email varchar(50) default '',
keywords varchar(255) NOT NULL default '',
subject varchar(50) default '',
phone varchar(50) default '',
company varchar(50) default '',
address1 varchar(50) default '',
address2 varchar(50) default '',
city varchar(50) default '',
state varchar(25) default '',
zipcode varchar(25) default '',
country varchar(50) default '',
description varchar(255) default NULL,
service varchar(30) NOT NULL default '',
dbedit timestamp(14) NOT NULL,
amount varchar(16) NOT NULL default '0',
submitdate datetime default NULL,
phone2 varchar(30) NOT NULL default '',
callstatus char(2) NOT NULL default 'NC',
notes varchar(255) NOT NULL default 'Add your comments here (max 255
char.)',
caller varchar(10) NOT NULL default '',
followupdate varchar(15) NOT NULL default '',
pending enum('Y','N') default 'Y',
famappvd enum('Y','N') default 'N',
optkeywords varchar(255) NOT NULL default '',
opturl varchar(255) NOT NULL default '',
srvcOptCode varchar(16) NOT NULL default '',
srvcAuthCode varchar(16) NOT NULL default '',
srvcExpDate date NOT NULL default '-00-00',
srvcStatus varchar(10) NOT NULL default '',
preferred int(11) NOT NULL default '0',
submittedby varchar(15) NOT NULL default '',
PRIMARY KEY (submitid),
UNIQUE KEY url_unique (url),
KEY service (service),
KEY name_index (name),
KEY opturl_index (opturl),
KEY optkeywords_index (optkeywords),
KEY submitdate_index (submitdate),
FULLTEXT KEY full_4_index (title,keywords,description,url)
) TYPE=MyISAM PACK_KEYS=1;
SERVER OPERATING SYSTEM / SOFTWARE:
---
ASP Script IIS 5.0
Windows 2000 Server Running MySQL ODBC
Makes ODBC connection with MySQL server
FreeBSD Box with MySQL 4.0.0-alpha
-
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
Distribute MySQL with VB App
Hello, Is it possible to distribute a MySQL db with a visual basic application - in other words, use MySQL as the backend DB for a Visual Basic app? Karl Stubsjoen www.excelbus.com/info-m - 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
Kill Thread
Hello, I need to kill a thread. I've issued a command which has locked a table (delete from table where id = 9) was the command and it is taking a very very long time. Now I'd like to kill that thread. However, I can't read the the thread ID because the ID scrolls out of view in my little Win98 dos window when I issue: Show Processlist. So, any suggestions? We are running mysql on a linux server (ver 4.0???) and connect from a Win98 MySql emulation. Thanks Karl www.excelbus.com ..opportunity knocking.. - 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: Delete Match Against
Wooohooo! that is the first bug i ever found!
Do I get any points : )
Karl
www.excelbus.com
..opportunity knocking..
-Original Message-
From: Sergei Golubchik [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 21, 2001 6:33 AM
To: Karl J. Stubsjoen
Cc: MySql list
Subject: Re: Delete Match Against
Hi!
On Nov 20, Karl J. Stubsjoen wrote:
Hello,
I am trying to perform the following delete command:
delete from my_table where match(keywords,title,description)
against('this_word')
When I perform a similar select and count with the above query, I come up
with 47 records found. However, when I issue the above delete command, no
records are deleted. Is there a limitation to issuing the delete command
with the Match and Against syntax in a where clause?
Thank you!
Karl
It's a bug - thanks for spotting this.
It'll be fixed in the next version.
Regards,
Sergei
--
MySQL Development Team
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
___/
-
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
-
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
Delete Match Against
Hello,
I am trying to perform the following delete command:
delete from my_table where match(keywords,title,description)
against('this_word')
When I perform a similar select and count with the above query, I come up
with 47 records found. However, when I issue the above delete command, no
records are deleted. Is there a limitation to issuing the delete command
with the Match and Against syntax in a where clause?
Thank you!
Karl
-
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
Lock Table for new index?
Hello, I have a fairly active and large DB. I need to create a new index on a column. It will take (just guessing) 15-30 minutes for the entire creation. I've got different people hittiing this table all the time, mostly with new inserts (not really any updates). My question is: Can I create this new index without locking the table, or should I lock it? Thanks! - 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
Search Engines
Hello, I need to create a search engine out of a few MySQL tables I should say: I need to search MySQL records like a search engin might. However, my first go ended up as a complete failure because it is highly un-optimized to search for (as an example) %apple% in all of the available text fields. Any ideas about where I can look to set up a database optimized for searching in this way? Thanks, Karl - 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
Hash Tables / Indexes
Hello, Can someone explain hash tables or hash indexes and if we can take advantage of them in MySQL? Thanks! Karl - 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
Replicating MySQL
Hello, How could we replicate our MySQL DB from one server to another? As a back up measure, incase our pages fail to connect to ServerA, it'll then try to connect to ServerB? Thanks! Karl ...and what is the word I am looking for, it isn't replicate but something else? - 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
How to configure MySQL for Unix
Hello, We have loaded MySQL on an Apache Linux machine, I have recently tried to connect and am getting the following error: C:\mysql\binmysql -hdaffy -uroot -p Enter password: ERROR 1130: Host 'workstation01.fenzer' is not allowed to connect to this MySQL server Any ideas what we need to do? Thanks, Karl - 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
