RE: Indexing? (Warning: relative newbie.)
To answer your questions in no particular order, YES you can speed it up with indexing. You might want to first create an index on ( blocksize AND physmessage_id ). Why, you might ask, index on physmessage_id? Because then the db won't have to do a fetch on items from the table since it's in the INDEX itself, saving any unnecessary reads. Realistically, I can't see that taking more than a few seconds, at most, to execute. However, making the index might take a serious bit of time. Please let us all know how it does or does not work. Tim... -Original Message- From: Ken D'Ambrosio [mailto:k...@jots.org] Sent: Wednesday, June 24, 2009 11:07 AM To: mysql@lists.mysql.com Subject: Indexing? (Warning: relative newbie.) Hi, all. I'm a long-time MySQL user who's only recently had to start learning some administrative stuff, largely because I finally have a decently-sized database. My database is about 100 GB; I'm using it -- via dbmail (www.dbmail.org) -- as a mail server for my company. While dbmail is well-and-good with its IMAP front-end, I'm thinking of writing a Python front-end to do some queries directly against MySQL. But some of them take a l-o-n-g time. As an example, I've got a table with slightly over a million records; I'd like to be able to show (say) only IDs of messages under a half-MB. The query would look something like this: select physmessage_id,blocksize from dbmail_messageblks where blocksize 50; That query takes 50 minutes. A smidge long to wait. So I said, Huh. That's impressive. And I tried it without the physmessage_id: select blocksize from dbmail_messageblks where blocksize 50; That took 14 seconds. A bit more in my timeframe. Can I optimize this with indexing? Should I be using a different DB engine? Is there a site/book I should be learning DBA fundamentals from that might offer me direction for stuff like this? Sorry for all the newbie questions, but I haven't done serious database stuff since Foxbase/dBase III days. Things have changed a little since then. Thanks! -Ken -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=tlit...@tgrnet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Anyone using LVM for backing up?
We have a 20 gig db (that includes the MYIs and MYDs and FRMs). We are wondering how long LVM snapshots take.. in that how long might the DB be read-locked? Do we have to read-lock it and flush tables? Are we talking half a second, ten-seconds, 20 minutes? Currently, when we copy the raw files from one directory to another, it takes about 20 mins and brings the DB to it's proverbial knees. When we copy the files with the db server down, it takes 10 minutes or so. Tim... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: restoring mysql db doesn't restore user passwords
My theory would be that it's an OLD-PASSWORDS issue. It would seem that you might have used the old_passwords=1 in your original configuration my.cnf but it's not in your new configuration file. -Original Message- From: Adam Williams [mailto:awill...@mdah.state.ms.us] Sent: Monday, June 15, 2009 7:41 PM To: mysql@lists.mysql.com Subject: restoring mysql db doesn't restore user passwords I'm running Fedora 11 i386 with Mysql 5.1.32. I dumped my mysql databases with: mysqldump -u root -pxxx --lock-all-tables --all-databases /root/mysql-backup/all-db.sql and then wiped the operating system and reinstalled. Then I ran mysqladmin -u root password to set my root password. Then I needed to restore all of the databases so I ran: mysql --verbose -u root -p /root/mysql-backup/all-db.sql and that seems to have restored everything, there is the correct data in my databases, however, users can not connect. For example, this user was working prior to the reinstall: mysql -u adam -px ERROR 1045 (28000): Access denied for user 'adam'@'localhost' (using password: YES) but in the mysql.user table, adam exists and has his password set from the dump file: mysql select User,Password from mysql.user where user = 'adam'; +--+--+ | User | Password | +--+--+ | adam | 2bf6b1712b10928e | | adam | 2bf6b1712b10928e | | adam | 2bf6b1712b10928e | +--+--+ 3 rows in set (0.06 sec) It is the same with all of the other users also. They have their password set from the dump file, but they can't log in with what their password was either. Any ideas on how to get the passwords restored? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=tlit...@tgrnet.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Why can't I kill the query cache?
Also titled, I want this to run slow ALL the time... I have a group of dreadful queries that I have to optimize. Some take 20-30 seconds each -- the first time that I run them. But then they never seem to take that long after the first time (taking less than a second then). If I change the keywords searched for in the where clauses, then they take a long time again... so it's the query-cache or something just like it. BUT, I am doing this each time : flush tables; reset query cache; set global query_cache_size=0; SELECT SQL_NO_CACHE DISTINCT ca.conceptid AS headingid, And still it's not avoiding the cache. Is there a cache I'm missing? Tim... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Simple data, simple query giving me a brain-ache
Ok, I have a select statement which must return the distinct names, sorted by ranking (lowest to highest). Seems absurdly simple, right, and I'm sure it would be... look at this example CREATE TABLE IF NOT EXISTS HowToExample ( Name VARCHAR( 32 ), Ranking INTEGER ) ENGINE=MyISAM; INSERT INTO HowToExample ( Name, Ranking ) VALUES ( 'First', 1 ), ( 'Second', 2 ), ( 'Last', 3 ), ( 'First', 4 ); In this case it works correctly, and I get First, Second, Last! YAY SELECT Name, Ranking FROM HowToExample GROUP BY Name ORDER BY Ranking; If we CHANGE the contents as follows, however, we get : TRUNCATE TABLE HowToExample; INSERT INTO HowToExample ( Name, Ranking ) VALUES ( 'First', 4 ), ( 'Second', 2 ), ( 'Last', 3 ), ( 'First', 1 ); Then the same SELECT query fails, and gives me Second, Last, First! I can't determine an appropriate HAVING clause nor any method of getting this in a single query... Any clues? Tim... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[Q] FULLTEXT index question
Can one make a composite index with FULLTEXT for one column and standard indexing on another? For instance we have a table CREATE TABLE OurData ( TheText TEXT, TheLanguageID INTEGER ); We have a FULLTEXT index on TheText, but want to be able to do searches on TheText AND TheLanguageID. So, an index like ( FULLTEXT TheText, TheLanguageID ) would be nice. Is this possible? Tim... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(Q) FullText (UTF8)
We are using MySQL 5.0.22 on CENTOS/redhat linux. The table and database
character-sets are all utf8.
We have a database supporting numerous languages. Of course, full-text works
beautifully with most of the languages.
But Chinese and Japanese are giving us problems, and there is NO reason why it
should be a problem since we are taking measures to help the database see
word-breaks.
When we insert the Chinese and Japanese passages, they have spaces (normal
ASCII $14-#32) between each word (verified). So basically if you have two
words like {APPLE}{DRUM} then we put {APPLE} then space then {DRUM}. If you
have UTF-8 then you can look at this sample, 三坐标测量机 固定架
When we try to match either {APPLE} or {DRUM} individually (or technically
三坐标测量机 or 固定架 ) then MySQL fails to find a match against anything. But
clearly it should find those.
MySQL is only finding matches for Japanese and Chinese on exact full-string
matches, which is clearly less than ideal.
I have already changed the ft min length setting to 1, to no avail.
What is going wrong, and how do I fix this?
Here is my sample query (selecting for ONE word
select *
from category_attributes
where match ( value ) against ( '三坐标测量机' ) 0
When I replace the word with固定架 then it still doesn't match anything. And
there is a row with merely
三坐标测量机 space固定架
Tim...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query not returning Data
Oh boy.. having the date stored as a varchar in that particular format will be profoundly problematic. You might want to store it -MM-DD or the SQL BETWEEN will mangle the expected return results. Does it work (return a non-empty result-set) when you omit the LIMIT clause? Does it work (return a non-empty result-set) when you omit the board_action_date BETWEEN comparator clause? Tim... -Original Message- From: russbucket [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 10, 2007 11:06 AM To: mysql@lists.mysql.com Subject: Query not returning Data Sorry about double post, I am having problems with my ISP. I have the following query: SELECT * FROM Sight_Hearing_Help WHERE 'type_help' = Eye Exam Glasses AND 'board_action_date' BETWEEN 07-01-2007 AND 12-31-2007 LIMIT 0 , 60; Returns empty row every time. The board_action_date is a varchar field. Not a date field. I have also tried using form 2007-07-01. Additional information: SUSE 10.2, MySQL 5.0.26-14 Any help would be appreciated! -- Russ Registered Linux user #441463 -- 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]
RE: MySQL database synchronizing from 2 locations
Replication works with Windows (we do it extensively here at work). And it's definitely one option. But if there are any problems, then without some monitoring mechanism, you'll not be alerted if replication chokes (all that will happen is that updates to the slave will seemingly just stop). You can implement any of a bunch of alerting and self-repair mechanisms. Other methods of transferring the data are similarly challenging. You can do an automated MySQLDUMP on a periodic basis with with a --master-data option (in case you do perform the replication) just to be sure OR just do MySQLDumps each night (depending on the volume). It all depends on the degree and need for whatever level of synchronicity. Tim... -Original Message- From: C K [mailto:[EMAIL PROTECTED] Sent: Thursday, August 02, 2007 2:08 PM To: mysql@lists.mysql.com Subject: MySQL database synchronizing from 2 locations Hello, My client has a mfg. unit at 65 Km from a city in India. He wants to connect to his corporate office in the city. Both offices will use same data and same ERP system. He is using Win 2K3 server and MySQL 5.0.17. Is it possible to make them synchronized at a particular or regular intervals? How? Please give details. Options I think - Replication (is it possible for Windows?) Cluster (Is it possible?) Manual Sync by using Navicat or any other tool (other tools please) Please help. Prior Thanks, CPK -- Keep your Environment clean and green. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Function in Query has Terrible Impact on Indexes Used
My guess, without seeing your database CREATE TABLE statement for this particular table would be that the DATE_FORMAT returns a string, while the LAST_DAY function returns a date-time. If your TLINE_INV_DATE is a VAR/CHAR then use DATE_FORMAT around the LAST_DAY. If TLINE_INV_DATE is a DATE/TIME then use a STR_TO_DATE around the Date_Format to see if it makes a difference. Just my humble guess. Tim... -Original Message- From: Caplan, Michael [mailto:[EMAIL PROTECTED] Sent: Monday, March 12, 2007 1:12 PM To: mysql@lists.mysql.com Subject: WOW: Function in Query has Terrible Impact on Indexes Used Hi There, I'm having terrible results with a query that uses functions to define dynamically conditions in the WHERE clause. However, if I change the query to not use these functions, the query flies because it uses proper indexes. The query without functions is as follows: SELECT TLINE_WCODE, SUM(TLINE_UNITS) FROM f_trans_lines WHERE TLINE_INV_DATE BETWEEN '2007-03-01' AND '2007-03-31' GROUP BY TLINE_WCODE When describing this query, it says that it is using the index_4 index, which is an index of the TLINE_INV_DATE column. 1, 'SIMPLE', 'f_trans_lines', 'range', 'Index_4', 'Index_4', '4', '', 1, 'Using where; Using temporary; Using filesort' However, here I swap out the statically defined dates for functions: SELECT TLINE_WCODE, SUM(TLINE_UNITS) FROM f_trans_lines WHERE TLINE_INV_DATE BETWEEN DATE_FORMAT(SYSDATE(), '%Y-%m-01') AND LAST_DAY(SYSDATE()) GROUP BY TLINE_WCODE Describe says that it is using a different key (index_1), which is an index of TLINE_WCODE. This execution path offers terrible performance. 1, 'SIMPLE', 'f_trans_lines', 'index', '', 'Index_1', '11', '', 520366, 'Using where' Any ideas why using the functions forces an index change, and how can I fix this so I don't have to give up the flexibility of the functions? Thanks, Mike E-mail messages may contain viruses, worms, or other malicious code. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective action against such code. Henry Schein is not liable for any loss or damage arising from this message. The information in this email is confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this e-mail by anyone else is unauthorized. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: migrating 4.0 to 5.0
Here's what we did and still do : Our 4.x tables and databases were/are in Latin-1 and all the 5.x tables are/were in utf8. That means that the entire regiment of items (every column, every table, every database) in the old system (4.1) was latin-1 and all the destination items in 5.x were entirely utf8. Sumary : We used MySQLdump to dump the files and then I use a tool to replace all occurrances of latin1 to utf8 then I use mysql command-line client to load / execute those resultant dump-files. For reference, our MY.INI files say (not sure if it is actually heeded) : default-character-set=latin1 Steps : 1 Here is a single table dump line from the dump portion of my scripts : @mysqldump --quick --default-character-set=latin1 -uroot --password=secretpassword --port=3306 --skip-set-charset --skip-comments --add-drop-table -c -C -h databasehost -r UseThisFileForInput.sql DatabaseToUse --tables AddressListTables 2 Then we run a program to search/replace all occurrances of latin1 to utf8 (shareware program called search-replace)... but other replacement tools will probably work too @sr32 /u /i /p /q /n f:\DailyBackupLogic\*.sql /slatin1 /rutf8 Your replacement methods will be different in form, but likely identical in function. 3 Then we use this line to load it into the database : mysql --local-infile=1 -uroot --password=secretpassword --port=3310 -DNewDatabasename -hImprovedDatabaseServer --port=3310 -b -C -e \. UseThisFileForInput.sql Tim... -Original Message- From: Matthias Henze [mailto:[EMAIL PROTECTED] Sent: Monday, March 05, 2007 2:30 AM To: MySQL General Subject: migrating 4.0 to 5.0 hi, i've still serious trouble in migrating databases createted with 4.0 to 5.0. the problems is still the charset. i'm connecting to mysql with php and when i try to use the 5.0 db german special chars are messed up. afaik 4.0 uses latin charset and 5.0 utf8 by default. can some one please give me a hint how to migrate ? i think i'm just too stupid :-) TIA matthias -- 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]
