Re: Table Design
Hi wultsch, Thanks a lot. Every thing is going fine. I am only concerned with duplicate index, as it is using disk space. Is there any solution so that i can ignore duplicate index by altering the table design. OR i have to end up with duplicate index. Thanks, Krishna Chandra Prajapati On Thu, Apr 24, 2008 at 9:27 PM, Rob Wultsch [EMAIL PROTECTED] wrote: On Thu, Apr 24, 2008 at 6:40 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi All, Below is the table design on mysql server. CREATE TABLE `coupon_per_course` ( `coupon_id` int(10) unsigned NOT NULL default '0', `course_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`coupon_id`,`course_id`), KEY `idx_coupon_per_course` (`coupon_id`), KEY `idx_coupon_per_course_1` (`course_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; In my view index idx_coupon_per_course should not be there. Since coupon_id is a primary key. so it will be utilized for searching. Before removing index idx_coupon_per_course mysql do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.06 sec) After removing index idx_coupon_per_course mysql do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.07 sec) I am not able to understand why after removing the index idx_coupon_per_course, it is taking more time. As it must take less time. Some other statistics are mysql select count(*) from coupon_per_course; +--+ | count(*) | +--+ | 296218 | +--+ mysql select count(distinct coupon_id) from coupon_per_course; +---+ | count(distinct coupon_id) | +---+ |211519 | +---+ Please suggest me the correct table design. Thanks in advance. Thanks, -- Krishna Chandra Prajapati Hi Krishna, I have run into similar issues in the past and have ended up having duplicative indexes. The multi column indexes have higher cardinality and although it should not be an issue, lookup on the first portion of the index alone is not as efficient. I would love to know why this is/what I am dong wrong. Are you having issues with INSERT speed, or the size of the your indexes? Posting your explain (extended) and show index may be helpful. For whatever it is worth, I always suggest explicit joins and using AS: SELECT sql_no_cache ac.plan FROMcoupon_per_course AS cpc INNER JOIN affiliate_coupon AS ac USING(coupon_id) WHERE cpc.course_id = 213336 AND ac.coupon_code='TST0G0' I think it makes queries much easier to read and understand. -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- Krishna Chandra Prajapati
Re: Spatial data and mysql
I just did a quick look at the documentation on the mysql spatial extension and it seems like over kill for what you are looking for. An easy way to approximate the search for all points a given distance from another is to simply use a bounding box. An index on the X and Y coordinates of the point then will make the search fast. something like this... SELECT * FROM points WHERE x = minx AND x = maxx AND y = miny AND y = maxy If your data is evenly distributed in the space about 21% of the returned points will be outside the distance you want. You can then use a script to scan the result to find and reject the points you don't want. I do this for a mapping project I have on a web site. I don't have a lot of data so I can't say how well the performance is. Even if you have a huge data set, as long as your result sets weren't too big, this should be pretty fast. If the points you are dealing with are latitude longitude coordinates, I have the formula you need to calculate the distance written in PHP if you want it. Rob Wultsch wrote: I have been storing points in mysql without use of the spatial extension. I do not forsee the need to ever store more than points, and am wondering if the spatial extensions would offer any significant advantages. I have looked a bit for tutorials, etc... and have not found much. One feature that I would like is to be able to find all points withen X distance from of point Y, without doing a table scan. Would the spatial index (Rtree) be able to achieve this? Are there any good tutorials (or heaven forbid, books) that anyone can suggest? Should I go hang out with the cool kids that are using postGIS ;) -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Design
Krishna Chandra Prajapati schrieb: Hi All, Below is the table design on mysql server. CREATE TABLE `coupon_per_course` ( `coupon_id` int(10) unsigned NOT NULL default '0', `course_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`coupon_id`,`course_id`), KEY `idx_coupon_per_course` (`coupon_id`), KEY `idx_coupon_per_course_1` (`course_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; In my view index idx_coupon_per_course should not be there. Since coupon_id is a primary key. so it will be utilized for searching. Before removing index idx_coupon_per_course mysql do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.06 sec) After removing index idx_coupon_per_course mysql do benchmark(100,(select sql_no_cache ac.plan from affiliate_coupon ac, coupon_per_course cpc where ac.coupon_code='TST0G0' and ac.coupon_id = cpc.coupon_id and cpc.course_id = 213336)); Query OK, 0 rows affected (0.07 sec) I am not able to understand why after removing the index idx_coupon_per_course, it is taking more time. As it must take less time. Some other statistics are mysql select count(*) from coupon_per_course; +--+ | count(*) | +--+ | 296218 | +--+ mysql select count(distinct coupon_id) from coupon_per_course; +---+ | count(distinct coupon_id) | +---+ |211519 | +---+ as you can see above, is the PRIMARY KEY(`coupon_id`,`course_id`) not only larger caused by having two fields indexed, also by having more index entries so it seems not unusual to me that it takes more time to search this index ... -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problems w/ Replication over the Internet
Hmmm... no more ideas or suggestions anybody? :( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API routines and cobol
Michael wrote: Has anyone successfully called the C API routines for MySQL from COBOL? Dude, April 1 was, like, a month ago now. You may have better luck finding an ODBC bridge for your COBOL environment, which let you access MySQL indirectly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Display more than 2500 rows
The first question that occurs to me is, Why on earth would you want an app to display 2500 rows? You must have one incredible monitor with a resolution beyond my wildest dreams! I would look into the LIMIT predicate and use it to grab say 50 rows at a time, or fewer, and post a marker so you know how to interpret the Next and Previous commands that you provide on the form. Just a thought. Arthur On Thu, Apr 24, 2008 at 12:44 PM, Velen [EMAIL PROTECTED] wrote: Hi, May be it's not the right forum i'm posting to. I have a Mysql Query : Select a.code,b.description, b.other_details,a.qty,a.price from xyz a, bcd b where a.code=b.code and a.id='5' order by a.id This is running fine but when using VB6 to display it in a Msflexgrid, it's a nightmare! It will take about 3-5 mins to display around 2500 rows. Can anyone suggest a better alternative to Msflexgrid or how to improve the speed on msflexgrid? Thanks. Regards, Velen
Database cache corrupted
Hi, I am using zabbix (monitoring software) with mysql. zabbix goes zombie and complains with messages suggesting that Database cache perhaps is corrupted. How can I check and fix it? I am using Centos 5.1, cpu Intel(R) Xeon(R) CPU and 1 Gb of RAM. my.cnf is as follows: [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock back_log = 50 max_connections = 100 max_connect_errors = 10 table_cache = 1024 max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 128M sort_buffer_size = 8M join_buffer_size = 3M thread_cache_size = 8 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 default_table_type = InnoDB thread_stack = 192K transaction_isolation = REPEATABLE-READ tmp_table_size = 64M log_slow_queries = /var/log/mysqld/slow-query-log long_query_time = 5 log_long_format tmpdir = /tmp log_queries_not_using_indexes = /var/log/mysqld/not-indexes.log expire_logs_days = 2 server-id = 1 key_buffer_size = 8M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover skip-bdb innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 600M innodb_data_file_path = ibdata1:128M;ibdata2:50M:autoextend:max:12800M innodb_file_io_threads = 4 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 8192 EOF Thanks in advance! -- -- Open Kairos http://www.openkairos.com Watch More TV http://sebelk.blogspot.com Sergio Belkin - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migration from 32-bit to 64-bit MySQL
I would like to move from 32-bit to 64-bit MySQL within the next year. Unfortunately, there is not a lot of documentation on migration or anything else regarding 64bit MySQL. My current setup consists of one master and two slaves (all using 32bit and MySQL 5.0). I am looking to add a 64bit slave to the mix. What is the difference between 32-bit and 64-bit? Is this a good idea? Can it be done? What would make this go wrong?
Re: Migration from 32-bit to 64-bit MySQL
Mike wrote: I would like to move from 32-bit to 64-bit MySQL within the next year. Unfortunately, there is not a lot of documentation on migration or anything else regarding 64bit MySQL. My current setup consists of one master and two slaves (all using 32bit and MySQL 5.0). I am looking to add a 64bit slave to the mix. What is the difference between 32-bit and 64-bit? Is this a good idea? Can it be done? What would make this go wrong? I have made this migration on multiple servers. It has never been any trouble. Your biggest gain would probably be the ability to address more RAM. I would just dump the database from the 32-bit platform and import it into the 64-bit server. Keith -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
On Fri, 25 Apr 2008, B. Keith Murphy [EMAIL PROTECTED] wrote: I would just dump the database from the 32-bit platform and import it into the 64-bit server. By dump do you mean mysqldump, or some other process? -- Tim McDaniel, n00b, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf On 4/25/08 11:23 AM, Mike [EMAIL PROTECTED] wrote: I would like to move from 32-bit to 64-bit MySQL within the next year. Unfortunately, there is not a lot of documentation on migration or anything else regarding 64bit MySQL. My current setup consists of one master and two slaves (all using 32bit and MySQL 5.0). I am looking to add a 64bit slave to the mix. What is the difference between 32-bit and 64-bit? Is this a good idea? Can it be done? What would make this go wrong? - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spatial data and mysql
On Fri, Apr 25, 2008 at 12:27 AM, Chris W [EMAIL PROTECTED] wrote: I just did a quick look at the documentation on the mysql spatial extension and it seems like over kill for what you are looking for. An easy way to approximate the search for all points a given distance from another is to simply use a bounding box. An index on the X and Y coordinates of the point then will make the search fast. something like this... SELECT * FROM points WHERE x = minx AND x = maxx AND y = miny AND y = maxy Thank you for your response. While I rather dislike using hacks the above would probably work well enough for my purposes. Unfortunately I do most of my work in a version of MySQL 5.0 (3.23.49) so I don't have access to the index merge optimization so the above would only use one index for me, but that would probably be good enough. One more reason to try to get my sys admin to upgrade... -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory?
Re: Migration from 32-bit to 64-bit MySQL
Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory? - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you.
Re: Migration from 32-bit to 64-bit MySQL
Olaf Stein wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory? Seriously, 18 gb isn't too big to do a mysqldump. And I really wouldn't advise you trying to do a binary copy. You are just asking for trouble. Plan ahead and you can do this on a slave without any problem, import the data on the new server and sync it back up without any problems. -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
Mike wrote: I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. Worst-case expansion for SQL data from binary to text format is about 5:1, which applies mainly to numeric data, not text. That's only 90 GB; I carry a bigger hard drive in my backpack, which I use for moving files between machines. Heck, my iPod holds more than that. You don't even have to store a second copy of the data. You can do something like pipe the mysqldump through a tool like nc (netcat) from the old machine to the new. With a decent GigE network connection between the two, the transfer should complete in about an hour. Add in a little data compression and you can probably cut that in half. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
Every statement should be executed on the slave from the masters binary log so in my opinion you should be ok On 4/25/08 12:20 PM, Mike [EMAIL PROTECTED] wrote: That what I want to do, but I'm not sure if the data will propagate right. Because of lack of documentation for 64bit. On Fri, Apr 25, 2008 at 12:03 PM, Olaf Stein [EMAIL PROTECTED] wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory? - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you.
Re: Migration from 32-bit to 64-bit MySQL
That what I want to do, but I'm not sure if the data will propagate right. Because of lack of documentation for 64bit. On Fri, Apr 25, 2008 at 12:03 PM, Olaf Stein [EMAIL PROTECTED] wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory?
Re: Migration from 32-bit to 64-bit MySQL
On Fri, Apr 25, 2008 at 12:08 PM, B. Keith Murphy [EMAIL PROTECTED] wrote: Olaf Stein wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory? Seriously, 18 gb isn't too big to do a mysqldump. And I really wouldn't advise you trying to do a binary copy. You are just asking for trouble. Plan ahead and you can do this on a slave without any problem, import the data on the new server and sync it back up without any problems. -- Keith Murphy I know you can take a mysqldump and copy over the data directory. I not sure what you mean by binary copy. Can you please explain? We have one database in memory that why we are moving over to 64bit. I'm planing like a year ahead of time.
Re: Migration from 32-bit to 64-bit MySQL
I am in process of planning 32 to 64 migration as well. I googled the following, but it could be only relevant to a specific application: It should be noted that, when switching between 32bit and 64bit server using the same data-files, all the current major storage engines (with one exception) are architecture neutral, both in endian-ness and bit size. You should be able to copy a 64-bit or 32-bit DB either way, and even between platforms without problems for MyISAM, InnoDB and NDB. For other engines it doesn't matter (CSV, MEMORY, MERGE, BLACKHOLE and FEDERATED) either the engine doesn't have a disk storage format or the format they use is text based (CSV) or based on MyISAM (MERGE; and therefore not an issue). The only exception is Falcon, which is only available in MySQL 6.0. It is generally recommended from MySQL that a dump and reload of data for absolute compatibility for any engine and major migration. The googled link: http://wikis.sun.com/display/WebStack/MySQL64bitARC Any comments on this? Mihail On Apr 25, 2008, at 12:03 PM, Olaf Stein wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory?
changing ip addresses
Hi all, I've got a problem. A client of ours changed ip addresses on their mysql server, and I believe MySL was set up to listen on the old ip address. How can I see what ip address mysql is set to listen on, and how can I change it? Thanks! Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
Mike wrote: I not sure what you mean by binary copy. Can you please explain? A binary copy means copying the MySQL data directory directly, rather than do a mysqldump, which converts the data to text format. The text dump is converted back to binary format for disk storage on loading it back into the new database. That conversion through a machine-neutral format is why it's always guaranteed to work. Moving binary data between machines only works when both machines play by the same rules. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to move the data directory?
boll wrote: I would like to know the correct way to move the mysql data directory to a different disk partition, so that the data can be accessed by mysql under linux or windows. I'm using Ubuntu 7.1 and Windows XP. I wrote these steps for 4.0, I think they should work for you http://rajshekhar.net/blog/archives/90-Moving-the-MySQLs-datadir-directory..html Make sure you do a clean shutdown of mysqld before doing the steps (i.e. no kill -9 business) I copied the data directory to a separate partition. I then changed the datadir in my.cnf, but Mysql would not start, warning: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' Check mysqld.err file to see your errors. -- raj shekhar facts: http://rajshekhar.net | opinions: http://rajshekhar.net/blog Yoda of Borg are we: Futile is resistance. Assimilate you, we will 'Borg? Sounds Swedish.' - Lily, Star Trek First Contact -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query multiple tables
Hi, I have 4 tables and need to use 1 query for displaying : prodbarcode,prod_description,stock_on_hand,qty, (qty-stock_on_hand) Variance, cost_price, (Variance*cost_price) Var_Amount for a specific store in StCount. StCount contains several sessionid for 1 store. C_Sess contains all the prodbarcode for each sessionid Then for the description, I need to use the table Prod and to match the qty with stock_on_hand, I need to use table Snap. VERY IMPORTANT: I need to have all prodbarcode in Snap as well as all prodbarcode in C_Sess. i.e. if a prodbarcode exist in Snap and does not exist in C_sess, the qty for that prodbarcode=0 , if a prodbarcode exist in C_Sess and does not exist in Snap, the Stock_on_hand for that prodbarcode=0 I'm using VB to do this with while ...wend loops and if... end ifs but it is taking me about 20 secs to display about 3400 items. As I am expecting to have more than 20,000 records soon, I need a query which will be much quicker! Please help. Table: Snap +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | prodbarcode | varchar(25) | | | | | | cost_price| double | | | 0 | | | stock_on_hand | float | YES | | NULL| | | store | varchar(15) | YES | | NULL| | +---+-+--+-+-+---+ Table: C_Sess +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | sn | double | YES | MUL | NULL| | | sessionid | double | YES | MUL | NULL| | | prodbarcode | varchar(50) | YES | MUL | NULL| | | qty | double | YES | | NULL| | | cdate | date| YES | | NULL| | | ctime | varchar(12) | YES | | NULL| | +-+-+--+-+-+---+ Table: StCount +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | sessionid | double | YES | MUL | NULL| | | cdate | date | YES | MUL | NULL| | | ctime | varchar(12) | YES | | NULL| | | userid| varchar(35) | YES | | NULL| | | store | varchar(25) | YES | MUL | NULL| | | team | varchar(255) | YES | | NULL| | | checkby | varchar(35) | YES | | NULL| | | chkdate | date | YES | | NULL| | | auditedby | varchar(35) | YES | | NULL| | | auddate | date | YES | | NULL| | | approveby | varchar(35) | YES | | NULL| | | appdate | date | YES | | NULL| | | remarks | varchar(50) | YES | | NULL| | +---+--+--+-+-+---+ Table: Prod +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | prod_code| varchar(15) | | PRI | | | | prodBarCode | varchar(25) | | MUL | | | | prod_description | varchar(50) | | | | | | prod_type| varchar(25) | | MUL | | | | prod_reference | varchar(35) | | MUL | | | +---+--+--+-+-+---+ Regards, Velen
