Re: MySQL 5 query takes 100x longer than MySQL 4.1.10
mos wrote: I have a simple query in MySQL 5.0.24: insert into table1 (col1) select distinct col1 from bigtable; that will run for 1:14:18. Both tables are MyISAM and table1 was just created with 2 columns and is empty. The select distinct col1 from bigtable takes only 2 minutes to run if I run it by itself (without the Insert statement), so why does inserting it into Table1 take over an hour? This worked fine under MySQL 4.1.10 BigTable has 30 million rows in it and will return approx 7000 distinct values. TIA Mike are your tables indexed? is col1 a primary key? have a look at optimising the table HTH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
creating indexes on a table already containing data
Hi all, I've got a database that has a few thousand rows, I've noticed that some of the search queries (especially the large ones) are taking some time. Im looking at adding indexes to my tables in order to speed up the data retrieval. My question is as follows: At this point in time if I add the indexes to the various tables should I see immediate results in the query times or do I have to wait for new information to enter the database (only new data gets indexed?) When does the data actually get indexed? Is it when its inserted or continually when regards are inserted or updated in the database? Thanks in advance. -- Angelo Zanetti Systems developer *Telephone:* +27 (021) 469 1052 *Mobile:* +27 (0) 72 441 3355 *Fax:*+27 (0) 86 681 5885 * Web:* http://www.zlogic.co.za *E-Mail:* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: creating indexes on a table already containing data
Dan Nelson wrote: In the last episode (Oct 03), Angelo Zanetti said: I've got a database that has a few thousand rows, I've noticed that some of the search queries (especially the large ones) are taking some time. Im looking at adding indexes to my tables in order to speed up the data retrieval. My question is as follows: At this point in time if I add the indexes to the various tables should I see immediate results in the query times or do I have to wait for new information to enter the database (only new data gets indexed?) When does the data actually get indexed? Is it when its inserted or continually when regards are inserted or updated in the database? When you add an index, Mysql builds a complete index of your table immediately. For a few thousand rows it should take under a minute. The index will automatically be updated as you insert rows or change existing ones. thanks Dan and Dan. I will have a look at the mysql site. However I checked and there are 600 rows approx in the database. The query im running has 3 subselect statements and takes quite a while, would you say that indexing wouldn't help as there arent that many rows in the database currently? And therefore consider rewriting parts of the statement? I will add the indexes as good measuse anyway. TIA -- Angelo Zanetti Systems developer *Telephone:* +27 (021) 469 1052 *Mobile:* +27 (0) 72 441 3355 *Fax:*+27 (0) 86 681 5885 * Web:* http://www.zlogic.co.za *E-Mail:* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [SOLVED]LEFT JOIN?
Thanks guys it worked!!! I really appreciate your help. this is the one that worked: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = '$userid' WHERE u.isactive =1 AND bl.userID is null and u.UserID != '$userid'; Michael Stassen wrote: [EMAIL PROTECTED] wrote: Michael's last answer: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = '$userid' WHERE u.isactive =1 AND bl.userID is null; Should do all of what you want except exclude the original user (so that the user cannot become their own buddy). To do that I would change it to read SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = '$userid' WHERE u.isactive =1 AND bl.userID is null and u.UserID != '$userid'; Can you show us some sample data and the result of either of these queries and explain what's wrong? I agree with Michael that this should work for what you need. Shawn Green Database Administrator Unimin Corporation - Spruce Pine I was just writing to say the same thing. I would only add that since userId is numeric, we should all drop the quotes around $userid. SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = $userid WHERE u.isactive =1 AND bl.userID is null AND u.UserID != $userid; Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT JOIN?
Hi guys. I'm having a problem deciding whether a left join is suitable for what i want to do. I have two tables A Users -userID -isactive B BuddyList -userID -buddyID what i want to do is to get all the users from A that don't exist as a buddyID for a user (buddyList) also the user must be active (isactive=1) but i cant get the correct result. Is the LEFT JOINcorrect for this operation or should i try using the NOT EXISTS command? TIA Angelo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN?
thanks shawn it seems to be working but i forgot to add that i need it for a single user ID, in other words it must bring back all user ids in the user table if they do not exist for that user in the buddylist. so what i've tried is this: SELECT u.* FROM users u LEFT JOIN buddylist bl ON u.userID = bl.userID WHERE u.isactive =1 AND u.userid!='$userid' AND bl.userID is null but this still returns some results that exist in the buddyList table. [EMAIL PROTECTED] wrote: Angelo Zanetti [EMAIL PROTECTED] wrote on 06/08/2005 12:06:51 PM: Hi guys. I'm having a problem deciding whether a left join is suitable for what i want to do. I have two tables A Users -userID -isactive B BuddyList -userID -buddyID what i want to do is to get all the users from A that don't exist as a buddyID for a user (buddyList) also the user must be active (isactive=1) but i cant get the correct result. Is the LEFT JOINcorrect for this operation or should i try using the NOT EXISTS command? TIA Angelo Yes, the LEFT join is the correct choice for your query: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.userID WHERE u.isactive =1 AND bl.userID is null; It's that last term (and bl.userID is null) that detects the non-matched users across the LEFT JOIN. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: LEFT JOIN?
Let me rewrite what it's meant to do as i mgiht not have been clear. ok for a single user I want to get all the users (from the user table) that aren't a buddy for that user. users 1 bob 2 tom 3 mike buddylist 1 2 1 3 2 1 2 3 3 1 //therefore if i searched for mike it would return tom as he is not listed as a buddy for mike but bob is. The query is half working but doesnt seem to be working for an individual user. thanks to those who have helped so far... Michael Stassen wrote: Angelo Zanetti wrote: thanks shawn it seems to be working but i forgot to add that i need it for a single user ID, in other words it must bring back all user ids in the user table if they do not exist for that user in the buddylist. so what i've tried is this: SELECT u.* FROM users u LEFT JOIN buddylist bl ON u.userID = bl.userID WHERE u.isactive =1 AND u.userid!='$userid' AND bl.userID is null but this still returns some results that exist in the buddyList table. I'm not sure Shawn's query does what you originally asked. His was SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.userID WHERE u.isactive =1 AND bl.userID is null; which returns active users with no buddies. As I understood it, you wanted active users who aren't on another user's buddy list. I believe that would require joining on buddyId rather than userID, like this: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID WHERE u.isactive =1 AND bl.userID is null; Adding your new requirement that we only look at a single user's buddy list should be simple: SELECT u.UserID FROM Users u LEFT JOIN BuddyList bl ON u.userID = bl.buddyID AND bl.userID = '$userid' WHERE u.isactive =1 AND bl.userID is null; Does that do what you want? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
reporting tools for mysql- OLAP functionality possibly
Hi, I have a system that im busy designing using PHP, mysql apache and linux. I will need to do alot of intense reporting from the system, like viewing statistics for country, province, district etc... so its basically drill down and roll up functionality (OLAP). This will probably run ontop of MySQL. It will also doing nice graphing etc... I have googled and searched on sourceforge but havent found too much, especially that run on linux. Is there anything that any of you can recommend, also that you would not recommend. Please feel free to give comments and links. thanks in advance -- Angelo Zanetti Z Logic www.zlogic.co.za [c] +27 72 441 3355 [t] +27 21 469 1052 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recovery question
try REPAIR TABLE 'tablename' Gleb Paharenko wrote: Hello. REPAIR TABLE ... USE_FRM helps in difficult cases. See: http://dev.mysql.com/doc/mysql/en/repair-table.html Per Jessen [EMAIL PROTECTED] wrote: All, I've got a table with about 25mill rows that was victim of a crash recently. (power-failure). I've been trying to recover it, but I'm not making much progress. From the most recent attempts: myisamchk --safe-recover --force table - recovering (with keycache) MyISAM-table 'table' Data records: 21622679 4988000 7256000 10627000 myisamchk: error: 126 for record at pos 1589881104 MyISAM-table 'table' is not fixed because of errors myisamchk -r --force --tmpdir=/data2/tmp table - recovering (with sort) MyISAM-table 'table' Data records: 12876899 - Fixing index 1 - Fixing index 2 - Fixing index 3 Key 3 - Found wrong stored record at 0 MyISAM-table 'table' is not fixed because of errors Where do I go from here? I've got a backup of the table, but I'm not sure what sort of state it is in.
