Re: is there a way to optimize like '%..%' searches ?
What about partitioning the database? On 7/1/06, Dan Buettner [EMAIL PROTECTED] wrote: Wow, that is a tough one. My question would be, how often is older data really accessed? Could you start incorporating a default date range like past 3 months or past 1 year into all searches, but allow people to override it if needed? Then if you add an index on the timestamp column it would help any searches with a date clause. Dan On 6/30/06, Martin Jespersen [EMAIL PROTECTED] wrote: It's basically a log that people needs to be able to search with wildcards in... the log grows many thousand records per day and never gets smaller, so searches just gets slower and slower. There is a sort field, the timestamp which is used in the searches, but it only makes the searches lsower yet instead of helping in the query, since all that does is sort by timestamp desc basically the query works like this: some searches for foo bar baz and i create an sql that looks like: select * from table where logline like '%foo%bar%baz%' order by timestamp desc. I have wrekced my brian plenty but have not come up with any otehr way of doing it that gives the needed flexibility in the searces. Since what is searched for is not words as such - most loglines are actually a single word on the form somethingspecialcharsomethingspecialcharsomethingspecialcharsomethingspecialcharsomethingspecialchar and so on - the logline is varibale length and variable number of entities between the sepcial chars (even the special chars are very varied) and of no specific format, thus the needed flexibility in the searches. If i coud i would changes the log format, but that is not possible since this database has loglines going all the way back to the 1980's (with more old lines being added as well as new ones) and the format has changed many times since then... Basically i am stuck with a very crappy heap of data i need to be able to search in a smart manner. Fulltext seaching would have been ideal if i was able to do boolean macthes with leading wildcard, but without it is useless :/ btw the result doesn't need scoring for relevance at all - what is searched for is always the newest matches to the searchterm, regardless of relevance (relevance could become handy at a later stage tho, but i dare not even think about it atm) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: number of rows in EXPLAIN for unpacked vs packed tables
Hi Dan, The contents of both tables is identically. I checked both tables with SHOW INDEX FROM ... and the cardinalities are exactly the same. Nevertheless, thanks for mentioning that those numbers are estimates. Regards, Cor - Original Message - From: Dan Buettner [EMAIL PROTECTED] To: C.R.Vegelin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, June 30, 2006 4:36 PM Subject: Re: number of rows in EXPLAIN for unpacked vs packed tables Cor - Those numbers are an estimate, not a hard number, of how many rows MySQL thinks it will have to exmaine to find what you're looking for. They could be different because your compressed table is brand-new and all the indexes are clean and up to date, whereas your original table has possibly been inserted, updated, and deleted from, causing the key information to be less accurate. You could try running a CHECK TABLE tablename EXTENDED on the old one, which should update all the key info, and then checking your EXPLAIN results again. Be aware it could take a while for a large table and will lock the table for the duration. If your indexes are different on the compressed table that would make a difference too, as MySQL might well be basing its estimate off a different index. Dan On 6/30/06, C.R.Vegelin [EMAIL PROTECTED] wrote: Hi All, I compressed a MyISAM table successfully with MYISAMPACK, followed by MYISAMCHK. Both tables (MyISAM + Compressed ) have exactly the same number of rows with Count(*). But when I give a SELECT query with EXPLAIN on both tables, I get different number of rows. For example: EXPLAIN SELECT ... FROM data ... shows 1983 rows, but the same EXPLAIN SELECT ... FROM pack ... shows 2064 rows. Any idea why ? Regards, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Scheduled Backups Issue...
I am attempting to set and and schedule automated MySQL backups using the MySQL Administrator Tool downloaded from mysql.com. All of my back ups work fien when I perform them manually. However none of the scheduled backups ever get done. I am not sure where the glicth is but here is what the details are... I have a remote server (*nx box colocated at an offsite location). MySQL 5.0.21 is on the box. Logged in as root to the MySQL. Created a Backup Project. Added Schema to it. Under Advanced left all options at default EXCEPT changed to Lock All Tables. Set a local path to save the back ups. Set backup to run daily. Saved and said to use the root as the user to perform the back up. Nada... Please advise if I am being a toal DFU or what. Thanks, DB - Yahoo! Music Unlimited - Access over 1 million songs.Try it free.
need one query
Hi all, I Have a table with followin structure where i have orgunit and parent and value orgunitparent value 12 10 x 1512 y 1612 z 1712 p Now here 15,16, and 17 are the children of 12 and 12 is the child of 10.Now i need a query which gives the sum of all the children of 12 and nturn 12 itself . i.e I need the sum(values(15+16+17+12)).Is there a query which gives the sum of all these.It will be very helpful if i am able to get it in a single query. Thanks and regards, venu.
Re: Recommended backup scripts for mysql databases
Thanks for all your help. I now have a nice backup script running. What I'm planning to do a little later this summer is to set up a RAID backup server on my DSL connection ftp downloading the mysql databases every night from my co-location server. This way I will have a nice archive of dumps on two locations to be even safer. In my setup I now have 3 my.sh scripts executed at the same time through a cronjob. Could it be problematic to do this at the same time or does it not matter? The databases aren't large. Cheers, Andreas
Re: need one query
orgunit parent value 12 10 x 15 12 y 16 12 z 17 12 p Now here 15,16, and 17 are the children of 12 and 12 is the child of 10.Now i need a query which gives the sum of all the children of 12 and nturn 12 itself . i.e I need the sum(values(15+16+17+12)).Is there a query which gives the sum of all these.It will be very helpful if i am able to get it in a single query. SELECT parent, parent + SUM(orgunit) FROM table GROUP BY parent; PB - VenuGopal Papasani wrote: Hi all, I Have a table with followin structure where i have orgunit and parent and value orgunit parent value 12 10 x 15 12 y 16 12 z 17 12 p Now here 15,16, and 17 are the children of 12 and 12 is the child of 10.Now i need a query which gives the sum of all the children of 12 and nturn 12 itself . i.e I need the sum(values(15+16+17+12)).Is there a query which gives the sum of all these.It will be very helpful if i am able to get it in a single query. Thanks and regards, venu. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.9.8/380 - Release Date: 6/30/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: what are those MySQL files for?
Those are the files which contain the data in each table in your MySQL databases. I think the .myd files contain the data, the .myi files contain indexes, and the .frm files contain schema information. Douglas Sims [EMAIL PROTECTED] On Jun 30, 2006, at 11:47 PM, Octavian Rasnita wrote: Hi, In the /data/database_name directory I have found a few files I don't know what they are used for. I have seen that some of them are pretty big. I don't think it is safe to delete them, but can I do something to decrease their size at least? Here are those files and their sizes in MB: 1 #sql-2a91_cdf.frm 397 #sql-2a91_cdf.MYD 253 #sql-2a91_cdf.MYI 1 #sql-6094_2.frm 397 #sql-6094_2.MYD 1 #sql-6094_2.MYI 2 #sql-6094_2.TMD Thank you. Teddy -- 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: what are those MySQL files for?
In the last episode (Jul 01), Douglas Sims said: On Jun 30, 2006, at 11:47 PM, Octavian Rasnita wrote: In the /data/database_name directory I have found a few files I don't know what they are used for. I have seen that some of them are pretty big. I don't think it is safe to delete them, but can I do something to decrease their size at least? Here are those files and their sizes in MB: 1 #sql-2a91_cdf.frm 397 #sql-2a91_cdf.MYD 253 #sql-2a91_cdf.MYI 1 #sql-6094_2.frm 397 #sql-6094_2.MYD 1 #sql-6094_2.MYI 2 #sql-6094_2.TMD Those are the files which contain the data in each table in your MySQL databases. I think the .myd files contain the data, the .myi files contain indexes, and the .frm files contain schema information. In general, yes, files ending in .MY? are database files. These particular files are actually temporary database files (because they start with #sql-). They're created when you run an ALTER TABLE command and are deleted when the procedure finishes. If you run SHOW PROCESSLIST and don't see any active queries, then those files were probably left over from a system crash and can be deleted. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Full Text Search across 2 tables.
I have 2 tables which have full text index on each of their columns. Table 1 - forums_topics field - topic Table 2 - forums_messages field - message Is it possible to search them both in one query and determine which table the result is being returned from in the search results? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: howto set mysql to readonly
put this in your my.cnf read_only this would put the DB in a read only mode, except for the slave threads and the super users, which/who can still do writes, this option is mostly used on slaves though . see http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html Kishore Jalleda http://kjalleda.googlepages.com On 6/30/06, Jehan PROCACCIA [EMAIL PROTECTED] wrote: OK so it seems to be a bad idea ... I was expecting that I missed a magic command like set readonly on all databases ... however, still thinking in a probably bad solution , what about setting unix file system acces mode to the database files to read only (400) wouldn't be a good idea ? (if I don't care about clients trying to write, i just want those trying to read to be able to do so ) Dan Buettner wrote: I personally would be wary of a solution like what you're proposing (locking all tables, that is). The problem I see is this - you lock all tables and proceed to move your data over to another host. Meanwhile, clients could well be queueing up insert or update commands that are simply blocking, waiting for you to release the locks. At the end, when you either release the locks or shutdown the database server, those clients' operations may complete, but against the outdated databases on the old host, or they may go into a deadlock waiting for the host to come back (and not come out of it), or they may fail ... or you may have users who think their computer is frozen and reboot, losing work. Seems risky, too much potential for data loss. I would insist on finding a window in which to shut down the database server and accomplish the migration in an orderly fashion. Dan On 6/30/06, Brent Baisley [EMAIL PROTECTED] wrote: Instead of locking the table, why not just change the privileges for the specific accounts so they have select only privileges? Then you still of write access through admin accounts if need be. - Original Message - From: Jehan PROCACCIA [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, June 30, 2006 6:28 AM Subject: howto set mysql to readonly Hello I need to move my databases from on server to an other. As lots of data are in production I cannot just stop mysql for 1/2 an hour and reopen it on the new server. What I expect to do is while backuping and restoring to the new server, I wish to set the original server in read only mode so that nobody can write in the databases while transfering to the new one. I've seen the LOCK table and flush commands, but I'am not sure if this is the right method, and how to use them. Lock table just locks tables as it's name implies and not a whole database ? is there a kind of lock all databases ? thanks. -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Full Text Search across 2 tables.
Maybe something like this: select ft.topic, fm.message from forums_topics ft, forums_messages fm match (ft.topic, fm.message) against (...) if topic is null then the hit is from fm and vice versa... haven't tried it, so might not work :) Steffan A. Cline wrote: I have 2 tables which have full text index on each of their columns. Table 1 - forums_topics field - topic Table 2 - forums_messages field - message Is it possible to search them both in one query and determine which table the result is being returned from in the search results? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
