Re: Dictionary
What you are looking for is called a Word List. I would search for something like the following: word list spellcheck DanB Scott Hamm [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I've been trying to google to no avail for English dictionary (with definitions) in any format that I can download and import into MySQL. Do anyone know where I can find it? Thanks in advance, Scott -- Power to people, Linux is here. -- 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]
Query: Order for the Earliest Latest date
[GENERAL INFO] I have two tables I'm working with. One table (Sites) contains contact information for every customer site that we deal with. The other table (Incidents) contains all the support calls we've made. [QUERY] I'm trying to generate a list of sites that HAD a support incident within a known date range, and order them so that the site that has the OLDEST support call is FIRST in the list. I'm using: SELECT DISTINCT id_Site FROM Incident WHERE Time = $date1 AND Time = $date2 ORDER BY Time DESC Which gives me a list of sites that had a support incident between the dates, but doesn't really sort them correctly. It simply orders them by who had the earliest support call. I'm looking for the site who's LAST support call is the EARLIEST. [Incident TABLE] Field Type Null Default Links to Comments MIME id int(11) No Time int(11) No 0when call came in text/plain Description varchar(100) No brief description Notes text No operator notes id_Site int(11) No 0 site - id ... Thanks for any pointers. DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query: Order for the Earliest Latest date
Peter Brawley [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Dan, I'm trying to generate a list of sites that HAD a support incident within a known date range, and order them so that the site that has the OLDEST support call is FIRST in the list. It's the (oft-asked-for) groupwise-max query. Here's one way, assuming you have MySQL 4.1 or later ... SELECT id_site, time AS 'Earliest Last Support' FROM incident AS i1 WHERE time = ( SELECT MAX( e2.time) FROM incident AS i2 WHERE i2.id_site = i1.id_site ) ORDER BY id_site; If your MySQL version is earlier than 4.1, change the subquery to a stage 1 query into a temp table then select order by from that. Bummer ... I'm running MySQL 4.0. I've never done a temp-table query. But, I'll give it a shot! Thanks DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tips for better performance
Marko Knezevic [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Here are my tables and queries i am running on them with index explanations. Hope this will help. ACCOUNTS TABLE: +-+--+--+-+-+ | Field | Type | Null | Key | Default +-+--+--+-+-+ | id | varchar(36) | | PRI | | assigned_user_id| varchar(36) | YES | MUL | NULL | deleted | tinyint(1) | | | 0 +-+--+--+-+-+ USERS TABLE: +---+--+--+-+-+ | Field | Type | Null | Key | Default | +---+--+--+-+-+ | id| varchar(36) | | PRI | | +---+--+--+-+-+ SELECT users.user_name assigned_user_name, accounts.* FROM accounts LEFT JOIN users ON accounts.assigned_user_id=users.id where accounts.deleted=0 ORDER BY name asc LIMIT 20,20 Explain says: id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra 1,SIMPLE,accounts,index,NULL,Name,151,NULL,888466,Using where 1,SIMPLE,users,eq_ref,PRIMARY,PRIMARY,36,sugarcrm.accounts.assigned_user_id,1, I have also other SELECTS but with different ORDER BY's (this select uses name, but also it could be sorted on city, state, phone_fax, phone_office, phone_alternate..) FIRST: Typically, you need indexes on fields that are specified in your JOIN/WHERE clause: accounts.assigned_user_id users.id accounts.deleted SECOND: Why are your id fields all VARCHAR(36)? These are huge keys! I would recommend using an INT as the id (that allows over 4 billion unique id's). If you have some internal id that is 36 characters long, have two id fields -- one for the relational-key-id (INT), and then your long string id for your clients to use. DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing query WHERE date0
Devananda [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Dan Baker wrote: Eric Bergen [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] When you add that index are more than 30% of the rows in the table DateTimeNext1126215680? There are currently 28.53% of the rows that have DateTimeNext1126215680 Does this mean something of interest? If so, what? Thanks DanB Dan Baker wrote: I have lots of tables that are similar in nature: id int(11) PRI NULL auto_increment Name varchar(30) DateTimeNext int(11) The DateTimeNext field represents when this records needs attention. A value of zero indicates it is being ignored. There are times when *lots* of records DateTimeNext values will be zero. I want to find all records in the database that need attention today, so a typical query looks like: SELECT id,Name FROM tbl WHERE DateTimeNext1126215680 When I EXPLAIN this query, I get the following: table type possible_keys key key_len ref rows Extra Site, ALL, NULL,NULL, NULL, NULL, 53587,Using where If I add an index for DateTimeNext, the EXPLAIN shows: table type possible_keys key key_len ref rows Extra Site,ALL,DateTimeNext,NULL,NULL,NULL,53587,Using where It appears that the index does NO good in this query. Is there anything I can do to optimize this query? Alter the table to improve the query? Do anything to not scan the entire stinkin' table? Thank you, DanB You may want to take a look at this page: http://dev.mysql.com/doc/mysql/en/how-to-avoid-table-scan.html Another possibility would be to change your data structures so that you can use an equality, rather than a range scan. For example, make DateTimeNext into a date or datetime field (rather than an int), and then alter your SELECT statement to be SELECT id,Name FROM tbl WHERE DateTimeNext = DATE(NOW()); I did notice that if I use an = comparison, that it will use the index. Unfortunately, I need all records that are after a given date, and every record has a different date, so I can't use an = comparison. It does seem strange that the = will use the index, but a or won't. Thanks for the idea. DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Optimizing query WHERE date0
I have lots of tables that are similar in nature: id int(11) PRI NULL auto_increment Name varchar(30) DateTimeNext int(11) The DateTimeNext field represents when this records needs attention. A value of zero indicates it is being ignored. There are times when *lots* of records DateTimeNext values will be zero. I want to find all records in the database that need attention today, so a typical query looks like: SELECT id,Name FROM tbl WHERE DateTimeNext1126215680 When I EXPLAIN this query, I get the following: table type possible_keys key key_len ref rows Extra Site, ALL, NULL,NULL, NULL, NULL, 53587,Using where If I add an index for DateTimeNext, the EXPLAIN shows: table type possible_keys key key_len ref rows Extra Site,ALL,DateTimeNext,NULL,NULL,NULL,53587,Using where It appears that the index does NO good in this query. Is there anything I can do to optimize this query? Alter the table to improve the query? Do anything to not scan the entire stinkin' table? Thank you, DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing query WHERE date0
Eric Bergen [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] When you add that index are more than 30% of the rows in the table DateTimeNext1126215680? There are currently 28.53% of the rows that have DateTimeNext1126215680 Does this mean something of interest? If so, what? Thanks DanB Dan Baker wrote: I have lots of tables that are similar in nature: id int(11) PRI NULL auto_increment Name varchar(30) DateTimeNext int(11) The DateTimeNext field represents when this records needs attention. A value of zero indicates it is being ignored. There are times when *lots* of records DateTimeNext values will be zero. I want to find all records in the database that need attention today, so a typical query looks like: SELECT id,Name FROM tbl WHERE DateTimeNext1126215680 When I EXPLAIN this query, I get the following: table type possible_keys key key_len ref rows Extra Site, ALL, NULL,NULL, NULL, NULL, 53587,Using where If I add an index for DateTimeNext, the EXPLAIN shows: table type possible_keys key key_len ref rows Extra Site,ALL,DateTimeNext,NULL,NULL,NULL,53587,Using where It appears that the index does NO good in this query. Is there anything I can do to optimize this query? Alter the table to improve the query? Do anything to not scan the entire stinkin' table? Thank you, DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored function
I'm running MySQL 4.0.23-standard, and trying to create a stored function. Am I doing something wrong, or can I not create a stored function in 4.0.23? What I want is a stored function to take an int from a column and alter it (mask off the low 16-bits). SQL-query : CREATE FUNCTION hello( s CHAR( 20 ) ) RETURNS CHAR( 50 ) RETURN CONCAT( 'Hello, ', s, '!' ) MySQL said: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( s CHAR( 20 ) ) RETURNS CHAR( 50 ) RETURN CONCAT( 'Hello, Thanks DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Birthday strategy
Pooly [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi, I would like to display a list of members who have their birthday a given day (today for instance). My idea is to store their birth date in a column, and then query the table against the column. But the query would be like : select id from members where MONTH(birthday) = MONTH(NOW()) AND DAY(birthday)=DAY(NOW()) but it would perform a entire table scan with that. What would be your best strategy for that sort of query ? And how would you deal with 29th of february ? You could store a special-birthday-date, which forces the year to a known value (like 2000). So, everyone's specialdate field would be in the range 1-1-2000 (0:0:0 o'clock) to 12-31-2000 (23:59:59 o'clock). Then, you could do a simple range comparison: specialdate = begin-today AND specialdate = end-today. This would also allow you to easily display weekend birthdays on Friday (have the end-date be midnight on Sunday). DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Partial Filtering
Blue Wave Software [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I'm having one of those slow brain days. I want a partial filter egg. All records where field1 begins with ABC any body know the where clause to do this. In Access it's where field1 = 'ABC*' but I can't find the MYSQL equivalent, or isn't there one. I believe you are looking for the following syntax: SELECT id FROM table WHERE field1 LIKE 'abc%' DanB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
