Re: Half Hour Sub-query in MySQL vs. 5 Seconds in VFP?
On Fri, Jun 19, 2009 at 11:27 AM, Brent Baisleybrentt...@gmail.com wrote: It sounds like you want to use spatial indexes, but they only became available in v4.1 http://dev.mysql.com/doc/refman/5.0/en/create-index.html http://dev.mysql.com/doc/refman/5.0/en/using-a-spatial-index.html That feels like the right thing (spatial calculations = spatial indexes?) but I looked at the docs and my head exploded. Can anyone recommend a good book that takes me through it gently? That said I'm intreged by the MBRContains and the Polygon functions... If I read those right I could create a simplified circle (probably just an octogon) to help eliminate false positives in the corners when using a plain square as the enclosure. You don't have to do any re-architecture to change you subquery to a join: SELECT custzip FROM customers JOIN (SELECT zip FROM zipcodes WHERE degrees(acos(sin(radians(39.0788994))*sin(radians(latitude))+ cos(radians(39.0788994))*cos(radians(latitude))*cos(radians(-77.1227036-longitude*60*1.1515 5) AS zips ON custzip=zip Will that work after a where clause? Multiple Times? For example... (pseudo-code...) SELECT * FROM customers WHERE saleslastyear 10 JOIN (SELECT zip FROM etc) AS zips ON custzip=zip JOIN (SELECT MAX(date) FROM phonecalls) AS LastCalledOn ON custid=custid Just from thinking about that... I assume that the only limitation is that in a subselect you can do something like WHERE NOT IN (select etc) but with a JOIN you are assuming a positive relationship? For example using the JOIN methods above there isn't a way to simply do AND custid NOT IN (SELECT custid FROM ordersplacedthisyear) other than doing exactly that and adding this clause to the saleslastyear clause. (In this particular case a column lastorderdate in customer that was programatically updated on ordering would also be useful but I'm thinking examples here... ;) ) I've never seen JOIN used outside of a traditional SELECT t1.*,t2.* FROM table1 AS t1 LEFT JOIN table2 AS t2 ON t1.id=t2.id type of structure so I kinda feel like I have a new toy... Thanks! -- 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: Anyone using LVM for backing up?
Hi Tim, We use LVM snapshots all the time. They are essentially instantaneous with our 90GB innodb database files. A command to generate the snapshot could be: sudo /usr/sbin/lvcreate --snapshot --name mysqlsqlbackup --size 15G / dev/system/data01 Please let me know if you have any questions. -Tom On Jun 22, 2009, at 4:41 PM, Little, Timothy wrote: 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=tmcgona...@online-buddies.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Anyone using LVM for backing up?
What we do to start is the following: ) open 2 windows to the server running the mysql instance ) in one window, ) run the mysql cli and issue command 'flush tables with read lock' ) stop the slave, if this is a running slave ) run either show master status or show slave status, whichever is appropriate, to get log position ) in the other window ) run the sync command several times ) create the snapshots ) back in the first window ) unlock that tables ) start the slave, if appropriate ) back in the other window ) mount the snapshots We do it this way to minimize the time the server we're syncing from is in read lock. If anyone sees any flaws in this, please let me know. There's a lot more, of course, involving rsync and change master. I just dealt with the beginning part. On Mon, Jun 22, 2009 at 3:41 PM, Little, Timothy tlit...@thomaspublishing.com wrote: 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=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Anyone using LVM for backing up?
Little, Timothy wrote: 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? Take a look at mylvmbackup which takes care of flushing tables, creating and destroying the snapshot, etc: http://www.lenzg.net/mylvmbackup/ Expect a serious performance hit while the lvm snapshot is active. ds -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
