Re: write-protection for some tables while other are writable
On 8/23/06, Gregor Reich [EMAIL PROTECTED] wrote: Hi all Is there a possibility to have some tables write-protected while others in the same db are not (and yet the write-protected ones are updatable through the replication mechanism, ie. there are tables on a slave server). I guess that both, LOCK TABLES and read-only in my.cnf, don't get this result. How about only granting select rights to the user? /Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow queries
Hi list I have 5 tables: words (word_id int auto_increment, word varbinary(40)) (has ~3.5M rows) with the keys: PRIMARY KEY (`word_id`),UNIQUE KEY `word_ind` (`word`) phrases (phrase_id int auto_increment, phrase varbinary(100)) (has ~11M rows) with the keys: PRIMARY KEY (`phrase_id`),UNIQUE KEY `phrase_ind` (`phrase`) phrase_words (phrase_id, word_id) (has ~31M rows) with: UNIQUE KEY `phrase_ind` (`phrase_id`,`word_id`) KEY `word` (`word_id`), KEY `phrase` (`phrase_id`) word_searches (word_id, search_date date, search hour char(2), amount smallint, type char(8), location char(2)) with: KEY `word_search` (`word_id`), KEY `id_search` (`search_date`), KEY `word_date` (`word_id`,`search_date`) (and a similar for phrase_searches, these two tables are merge tables with one table for each month, each table having 15-30M rows) phrases are built of words identified by phrase_words (these are not human language words and phrases but rather random bytes where some are human readable). Now, I'm trying to find out how many times has word 1..n been searched for and how many times has phrases containing 1..n been searched for? These queries take a really long time to execute, first I select for the words: explain sELECT w.word as word, w.word_id as word_id, sum(ws.amount) as amount FROM words w, word_searches ws WHERE ws.word_id=w.word_id AND w.word IN (p, xyz, zzz, abc) AND ws.search_date = '2006-07-17' AND ws.search_date = '2006-08-16' group by ws.word_id; ++-+---+---+-+-+-+--+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+-+-+-+--+--+--+ | 1 | SIMPLE | w | range | PRIMARY,word_ind| word_ind| 42 | NULL |4 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | ws| ref | word_search,id_search,word_date | word_search | 4 | statistics.w.word_id | 15 | Using where | ++-+---+---+-+-+-+--+--+--+ and then for phrases: explain SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM phrase_words pw, phrase_searches ps WHERE pw.word_id IN (966,1,1250,1741) AND pw.phrase_id = ps.phrase_id AND ps.search_date = '2006-07-17' AND ps.search_date = '2006-08-16' GROUP by pw.word_id; ++-+---+---+-+---+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+-+---+-+-++-+ | 1 | SIMPLE | pw| range | phrase_ind,word,phrase | word | 4 | NULL| 226847 | Using where | | 1 | SIMPLE | ps| ref | phrase_search,id_search,phrase_date | phrase_search | 4 | statistics.pw.phrase_id | 15 | Using where | ++-+---+---+-+---+-+-++-+ The queries takes 40s-several minutes on a dual xeon 3GHz with 4GB ram only running Msql. Can someone see something I've done wrong? I have the same data in flat files with one word and phrase on each row and one file for each day and doing grep/sort/uniq -c in all thoose files is quicker on a slower server with a lot of other procesess and with the files nfs mounted. mysqladmin status doesn't show any slow queries: Uptime: 1215323 Threads: 2 Questions: 2191970 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 64 Queries per second avg: 1.804 Thanks in advance /Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow queries
On 8/17/06, Chris [EMAIL PROTECTED] wrote: Jon Molin wrote: Hi list I have 5 tables: words (word_id int auto_increment, word varbinary(40)) (has ~3.5M rows) with the keys: PRIMARY KEY (`word_id`),UNIQUE KEY `word_ind` (`word`) phrases (phrase_id int auto_increment, phrase varbinary(100)) (has ~11M rows) with the keys: PRIMARY KEY (`phrase_id`),UNIQUE KEY `phrase_ind` (`phrase`) phrase_words (phrase_id, word_id) (has ~31M rows) with: UNIQUE KEY `phrase_ind` (`phrase_id`,`word_id`) KEY `word` (`word_id`), KEY `phrase` (`phrase_id`) word_searches (word_id, search_date date, search hour char(2), amount smallint, type char(8), location char(2)) with: KEY `word_search` (`word_id`), KEY `id_search` (`search_date`), KEY `word_date` (`word_id`,`search_date`) (and a similar for phrase_searches, these two tables are merge tables with one table for each month, each table having 15-30M rows) phrases are built of words identified by phrase_words (these are not human language words and phrases but rather random bytes where some are human readable). Now, I'm trying to find out how many times has word 1..n been searched for and how many times has phrases containing 1..n been searched for? These queries take a really long time to execute, first I select for the words: explain sELECT w.word as word, w.word_id as word_id, sum(ws.amount) as amount FROM words w, word_searches ws WHERE ws.word_id=w.word_id AND w.word IN (p, xyz, zzz, abc) AND ws.search_date = '2006-07-17' AND ws.search_date = '2006-08-16' group by ws.word_id; ++-+---+---+-+-+-+--+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+-+-+-+--+--+--+ | 1 | SIMPLE | w | range | PRIMARY,word_ind| word_ind| 42 | NULL |4 | Using where; Using temporary; Using filesort | | 1 | SIMPLE | ws| ref | word_search,id_search,word_date | word_search | 4 | statistics.w.word_id | 15 | Using where | ++-+---+---+-+-+-+--+--+--+ and then for phrases: explain SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM phrase_words pw, phrase_searches ps WHERE pw.word_id IN (966,1,1250,1741) AND pw.phrase_id = ps.phrase_id AND ps.search_date = '2006-07-17' AND ps.search_date = '2006-08-16' GROUP by pw.word_id; ++-+---+---+-+---+-+-++-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+---+-+---+-+-++-+ | 1 | SIMPLE | pw| range | phrase_ind,word,phrase | word | 4 | NULL| 226847 | Using where | | 1 | SIMPLE | ps| ref | phrase_search,id_search,phrase_date | phrase_search | 4 | statistics.pw.phrase_id | 15 | Using where | ++-+---+---+-+---+-+-++-+ The problem is it's picking the word index which apparently is returning 226,000+ areas. Test this: EXPLAIN SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM phrase_words pw, phrase_searches ps IGNORE INDEX (word) WHERE pw.word_id IN (966,1,1250,1741) AND pw.phrase_id = ps.phrase_id AND ps.search_date = '2006-07-17' AND ps.search_date = '2006-08-16' GROUP by pw.word_id; (that should ignore the 'word' index and instead use the 'phrase' index). Unfortunately didn't that help, it leads to: ++-+---+---+-+---+-+-+-+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+---+-+---+-+-+-+--+ | 1 | SIMPLE | ps| range | phrase_search,id_search,phrase_date | id_search | 3 | NULL | 3836930
Re: Slow queries
On 8/17/06, Chris [EMAIL PROTECTED] wrote: Unfortunately didn't that help, it leads to: ++-+---+---+--- | id | select_type | table | type | possible_keys | key | key_len | ref | rows| Extra | ++-+---+---+--- | 1 | SIMPLE | ps| range | phrase_search,id_search,phrase_date | id_search | 3 | NULL | 3836930 | Using where; Using temporary; Using filesort Yeh it's finding a lot more rows there which isn't what you want so the extra time isn't surprising. Does rewriting the query to be an inner join help? EXPLAIN SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM phrase_words pw INNER JOIN phrase_searches ps ON (ps.phrase_id=pw.phrase_id) WHERE pw.word_id IN (966,1,1250,1741) AND ps.search_date = '2006-07-17' AND ps.search_date = '2006-08-16' GROUP by pw.word_id; or even: EXPLAIN SELECT pw.word_id as word_id, sum(ps.amount) as amount FROM phrase_words pw, phrase_searches ps WHERE pw.phrase_id = ps.phrase_id AND pw.word_id IN (966,1,1250,1741) AND ps.search_date = '2006-07-17' AND ps.search_date = '2006-08-16' GROUP by pw.word_id; (which puts the join between the two tables first). That didn't help either. Same amount of rows as my first join and about the same speed as well (only a few seconds differing when executed). That would help with this discussion too: http://lists.mysql.com/mysql/201015 ;) Yes, it'd be sweet if that mysql internals guru revelead her/him-self from the cloud of guruness and spoke the true way of doing it. What pisses me off most is that 'grep -E ^word$| word$|^word | word 2006/07/*/phrases |wc -l' is so much quicker than the db :( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedures
On 7/25/06, Chris White [EMAIL PROTECTED] wrote: On Tuesday 25 July 2006 02:10 am, Jon wrote: CREATE PROCEDURE sp_test1 (IN some_limit int) select * from some_table limit some_limit; and CREATE PROCEDURE sp_test2 (IN some_table table) select * from some_table; Well, first off with stored procedures the format is: DELIMITER $$ CREATE PROCEDURE name () BEGIN .. END $$ DELIMITER ; DELIMITER is done so you can use ;'s within the stored procedure. no need for using blocks and setting delimiter when it's only a single query The other thing too is that you're trying to select a table by a variable. That doesn't quite work, and I've tried a dozen or so variations myself hoping it would. You know, I'd almost LIKE someone to go No you're wrong, you just need to do this... ;) Well, that's kinda what I want to hear. A simple yes or no for both tables and limits. I take it tables are a nono considering you've tried so many things. Anyone who knows if the same is true for limits?
