Re: Search for column value in a string variable?
Barry Newton schrieb:
OK, never mind. I finally found the 'locate' function. I knew it had
to be there somewhere!
or just:
... `column` IN ('name1', 'name2', 'name2', ...)
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
过客 schrieb: [...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency. -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
[...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency. lol, but TRUNCATE empties the table... What good are your references? :-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fast relevance sorting of full text search results
Urms schrieb:
I'm using pretty standard approach to sorting search results by relevancy:
SELECT DISTINCT product_name,
MATCH (keywords) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN MODE) AS
rate
FROM _TT
WHERE MATCH ( keywords ) AGAINST ('CONSOLIDATED* 16* bearing*' IN BOOLEAN
MODE ) 0
ORDER BY rate DESC
It works fine as long as the quantity of results is not big. Once the
quantity is about 50,000 and more (I have a very big database) the query
starts working way too slow. Total number of records is about 4 million. It
takes about 2 sec when there are 50,000 records in the result but at the
same time it takes only about 0.006 sec without ORDER BY clause.
you should reformat your query or table structure
for a quick solution:
probably with 50.000 records it exceeds your myisam_sort_buffer_size or
sort_buffer_size, try to raise them
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
Martijn Tonies schrieb: [...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency. lol, but TRUNCATE empties the table... What good are your references? :-) LOL ??? very funny, really why not read the manual before posting? could help me save a lot of time ... only one table is truncated, not the one referencing to this one it is better to have references leading to 'nothing' than to a wrong datarow ... i think this is very easy to understand - better save than sorry! -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
[...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency. lol, but TRUNCATE empties the table... What good are your references? :-) LOL ??? very funny, really why not read the manual before posting? could help me save a lot of time ... You don't have to answer if you want to save time :-) only one table is truncated, not the one referencing to this one I know that. it is better to have references leading to 'nothing' than to a wrong datarow ... i think this is very easy to understand - better save than sorry! Why is a row with an invalid reference better? It's invalid data and you just corrupted your database. The point I'm trying to make is that this part of the documentation is a bit strange, to say at least. The only benefit I could thing of, is being able to re-fill the table with the original data, but then your auto-inc should be turned OFF in between the mass INSERT and normal operations. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help wit query optimization (cont'd)
Perrin Harkins schrieb:
On Jan 4, 2008 5:51 PM, Eben [EMAIL PROTECTED] wrote:
The resultset is paginated on the front end using the
SQL_CALC_FOUND_ROWS functionality...
Usually a bad idea:
http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/
usually ... but i do not know of any index capable of having FULLTEXT and
'normal' fields in one index
WHERE MATCH table_2.field AGAINST ('value')
AND table_2.current = 1
AND table_2.shared_id IN (SELECT shared_id FROM table_1_view)
or am i wrong?
--
Sebastian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
Martijn Tonies schrieb: [...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency. lol, but TRUNCATE empties the table... What good are your references? it is better to have references leading to 'nothing' than to a wrong datarow ... i think this is very easy to understand - better save than sorry! Why is a row with an invalid reference better? It's invalid data and you just corrupted your database. please define 'invalid' - i think invalid is it in booth cases, so an empty invalid is better than a wrong invalid, or not? better have an unpayed bill leading to no costumer than to a wrong customer -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql CPU 100%
Nik schrieb: [...] The server never uses all the Memory, at least 10% always free, and there's loads of free disk space. Just the CPU max's out, causing problems. I've posted below the output of STATUS and SHOW GLOBAL STATUS. Any and all comments would be much appreciated as to how we can get performance back on track. Thanks. Here's the output; [...] | Created_tmp_disk_tables | 159267 | | Created_tmp_files | 4085 | | Created_tmp_tables| 262915 | try raising your tmp_table_size | Handler_read_rnd | 270199887 | | Handler_read_rnd_next | 3334259467 | enable your slow query logging and check your indizes | Open_tables | 511| | Opened_tables | 7045 | try raising your table_cache | Qcache_free_blocks| 1533 | | Qcache_free_memory| 3624048| | Qcache_hits | 1198322| | Qcache_inserts| 1446965| | Qcache_lowmem_prunes | 22914 | | Qcache_not_cached | 262103 | | Qcache_queries_in_cache | 2117 | | Qcache_total_blocks | 9264 | possible, raising qcache size could help too | Sort_merge_passes | 2040 | try raising the sort_buffer | Threads_cached| 44 | | Threads_connected | 12 | | Threads_created | 78 | | Threads_running | 3 | possible this too -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
[...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency. lol, but TRUNCATE empties the table... What good are your references? it is better to have references leading to 'nothing' than to a wrong datarow ... i think this is very easy to understand - better save than sorry! Why is a row with an invalid reference better? It's invalid data and you just corrupted your database. please define 'invalid' - i think invalid is it in booth cases, so an empty invalid is better than a wrong invalid, or not? IMO, you're f***ed in both cases :-) better have an unpayed bill leading to no costumer than to a wrong customer Why is that better? If you TRUNCATEd the table, you know you're doing something wrong/your data is messed up. As I said, what I wanted to point out is that this piece in the documentation is a bit strange. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
Martijn Tonies schrieb: [...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency. lol, but TRUNCATE empties the table... What good are your references? it is better to have references leading to 'nothing' than to a wrong datarow ... i think this is very easy to understand - better save than sorry! Why is a row with an invalid reference better? It's invalid data and you just corrupted your database. please define 'invalid' - i think invalid is it in booth cases, so an empty invalid is better than a wrong invalid, or not? IMO, you're f***ed in both cases :-) better have an unpayed bill leading to no costumer than to a wrong customer Why is that better? If you TRUNCATEd the table, you know you're doing something wrong/your data is messed up. yes, but this was not the point of the discussion the point was why is auto_increment not reset - and the above is the reason for this - i was not discussing if this is good or bad, or if it is good to delete table content, or if the table content was deleted by accident As I said, what I wanted to point out is that this piece in the documentation is a bit strange. yes, i have read it ... ;-) -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
full text search on multiple tables
Hello list I have to make a full text search and I want to do it in many tables. I have deferent tables for books authors and news. Any ideas how to do it? Thank you Nikos
Re: help wit query optimization (cont'd)
On Jan 9, 2008 4:33 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: usually ... but i do not know of any index capable of having FULLTEXT and 'normal' fields in one index Does that matter? It would have to be doing a full scan for SQL_CALC_FOUND_ROWS to work out well. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help wit query optimization (cont'd)
Perrin Harkins schrieb: On Jan 9, 2008 4:33 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: usually ... but i do not know of any index capable of having FULLTEXT and 'normal' fields in one index Does that matter? yes, as written in the mentioned article the test is only relevant with correct used indexes, but MySQL does not use more than one index, so this query cannot all be done with indexes please correct me if i am wrong -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: full text search on multiple tables
nikos schrieb: Hello list I have to make a full text search and I want to do it in many tables. I have deferent tables for books authors and news. Any ideas how to do it? three separate queries or an UNION -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: full text search on multiple tables
On Jan 9, 2008 8:36 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: nikos schrieb: Hello list I have to make a full text search and I want to do it in many tables. I have deferent tables for books authors and news. Any ideas how to do it? three separate queries or an UNION Or a Boolean mode search. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL SELECT Statement with Date help request
Hi List, I am wondering if someone can help me with a query to check what databases are on the MySQL server and then check which of those databases are either partially or completely within the date range i require. The scenario is as follows: db1 : 2007-01-01 to 2007-02-01 db2 : 2007-02-01 to 2007-03-01 db3 : 2007-03-01 to 2007-04-01 db4 : 2007-04-01 to 2007-05-01 db5 : 2007-05-01 to 2007-06-01 db6 : 2007-06-01 to 2007-07-01 I require a select statement to query all the db's to identify records with the date range for eg. 2007-02-15 to 2007-05-12. Logically this tells me that the databases that will have this information will be db2,db3,db4 and db5. My problem is that i have multiple servers running at different locations that uses the same app that writes to the MySQL db. However the amount of databases on each server differs in amount of db's and date ranges for each server. Is there a way of getting such a result with MySQL? Thanks in advance, Craig
Re: full text search on multiple tables
That is a grate solution. The problem is that I must have deferent links for each response. That's the tricky thing! Thank you Sebastian Mendel wrote: nikos schrieb: Hello list I have to make a full text search and I want to do it in many tables. I have deferent tables for books authors and news. Any ideas how to do it? three separate queries or an UNION
Red Hat EL and Datbase Setup
Hi, I am looking for a little advice in setting up Red Hat and MySQL for a large database (at least I consider it to be large). My database will contain 2 large tables that are updated daily. The first table currenly has 19 million records and ~70 columns largely made up of varchar(20), char(5) and integers. It has a natural primary key and a composite index on 3 of the character columns. The second table currently has 400 million records and ~30 columns again made up of varchar(20), char(5) and integers. This table's primary key is defined using 2 columns and also has a composite index on the same 3 columns as the first table. Lastly, I will frequently join the two tables in my queries. My system has 4gb ram, 500 gb hard drive (result of a RAID 5 configuration) and 2 dual core Intel 64 bit procs. I have chosen to use Red Hat EL5. Here are the questions I have to help optimize the performance: * Should I continue with the RAID 5? I am not too concerned of recovery. I am more concerned about I/O performance. * Is there a hard drive partition scheme that would help the performance (separate the large db schema /var/lib/mysql/schema_name)? * Should I partition the tables? There is a natural partition for the 400m table by date; there is not a natural partition for the other. Should I make one up? * Are there specific additions to the /etc/my.cnf that I should add to maximize the systems capabilities? * Please let me know of other things I should consider. Thanks in advance, Jason
RE: Red Hat EL and Datbase Setup
Try reading on RAID1+0, though it's a bit expensive in implementation but its great on READ WRITE.. Basing on the current stable version there is no built in table partitioning.. you can do it in an application level.. -Original Message- From: Jason Vinar [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 09, 2008 10:17 AM To: mysql@lists.mysql.com Subject: Red Hat EL and Datbase Setup Hi, I am looking for a little advice in setting up Red Hat and MySQL for a large database (at least I consider it to be large). My database will contain 2 large tables that are updated daily. The first table currenly has 19 million records and ~70 columns largely made up of varchar(20), char(5) and integers. It has a natural primary key and a composite index on 3 of the character columns. The second table currently has 400 million records and ~30 columns again made up of varchar(20), char(5) and integers. This table's primary key is defined using 2 columns and also has a composite index on the same 3 columns as the first table. Lastly, I will frequently join the two tables in my queries. My system has 4gb ram, 500 gb hard drive (result of a RAID 5 configuration) and 2 dual core Intel 64 bit procs. I have chosen to use Red Hat EL5. Here are the questions I have to help optimize the performance: * Should I continue with the RAID 5? I am not too concerned of recovery. I am more concerned about I/O performance. * Is there a hard drive partition scheme that would help the performance (separate the large db schema /var/lib/mysql/schema_name)? * Should I partition the tables? There is a natural partition for the 400m table by date; there is not a natural partition for the other. Should I make one up? * Are there specific additions to the /etc/my.cnf that I should add to maximize the systems capabilities? * Please let me know of other things I should consider. Thanks in advance, Jason This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom it is addressed. It may contain sensitive and private proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you are not the intended recipient, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. FXDirectDealer, LLC reserves the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted. Any reference to the terms of executed transactions should be treated as preliminary only and subject to our formal confirmation. FXDirectDealer, LLC is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help wit query optimization (cont'd)
On Jan 9, 2008 8:34 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: yes, as written in the mentioned article the test is only relevant with correct used indexes, but MySQL does not use more than one index, so this query cannot all be done with indexes Well, first of all, MySQL 5 does use more than one index, although I'm not sure it can do this with a full text index. http://dev.mysql.com/doc/refman/5.0/en/index-merge-optimization.html Also, there's a lot of room between a full table scan and using every index. Using any index in a way that allows the query to be satisfied without scanning every single row should be enough to make the count query better. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problem - MySQL at 99.9% CPU
Gunnar, You might do some more investigating on these to see if there is an index you could use to speed these up, 15.8 million records might be a full table scan, even if it's not - it's clearly a whole heck of a lot of data and that's going to give you a huge performance hit. I'm not familiar with the app, but perhaps there's a lot of junk in this table? Also, if it's a high volume site, you might run into performance issues around locking with a bunch of writes coming in during that read if the underlying storage engine is MyISAM. Spend some time with explain and the slow queries to see if the indexing will help. MySQL has some good background info here http://dev.mysql.com/doc/refman/5.0/en/explain.html Second, you can try to allocate more server resources to MySQL by reducing the PHP/Apache load (presuming it's all on the same server.) If it's not, only the first suggestion below will be of use. You might look at memcached ( http://www.danga.com/memcached/ ) if there are a number of queries that don't change all that often which can really reduce load on MySQL - giving it more resources to run these more dynamic and more resource intensive queries. Memcache stores objects in RAM so they're ready to use right away instead of a trip to MySQL - note that a MySQL resultset can't be inserted natively, it needs to be converted first - I have a result abstraction class I use that behaves like a mysqlResult, but is really an array of arrays that hold the MySQL data and let me page through it using standard object oriented code like $result-num_rows, $result- fetch_assoc() etc. Also, if there are pages that can be flattened, you can save some system resources there - basically publishing static versions of the templates via script on a schedule to reduce computational load generating a page that only changes once a day or once an hour etc. This saves everywhere because Apache can serve a static HTML template much faster than php or any other language - reducing load on the server even further. While this isn't a PHP list, you can also look at eAccelerator http://eaccelerator.net/ for pre-compiling PHP code saving a bit of load on the server each runtime. They claim 1-10x reduction in server load (of course that's highly dependent on your code etc) but I have seen on a shared host machine I'm on my resource load drop in 1/2. Freeing up those resources for other tasks (like MySQL.) Good luck! Erik On Jan 8, 2008, at 5:47 AM, Gunnar R. wrote: Thank you Erik! HDs are OK, a couple of GB free. Not that it's a lot, but I can't imagine it being too low for MySQL.. I'm aware memory is a bit low, but RAMBUS chips are hard to come by. They don't have them in stock anywhere anymore. Also they are quite expensive. It's almost like you could've bought 1/3rd of a new cheap Dell server for 2x512MB RAMBUS. But if a new box for a reasonable price wouldn't be any faster it's no use anyway... Concerning slow queries, it seems there's a couple of different queries that's being logged. This is one, taking 66 seconds: # Query_time: 66 Lock_time: 0 Rows_sent: 0 Rows_examined: 15857680 SELECT word_id FROM phpbb_search_wordmatch GROUP BY word_id HAVING COUNT(word_id) 263916; Usual time for this seems to be from 12 to 66 seconds. And then there's this, usually taking 10-20 seconds: # Query_time: 12 Lock_time: 0 Rows_sent: 10 Rows_examined: 395960 SELECT t.*, u.username, u.user_id, u2.username as user2, u2.user_id as id2, p.post_username, p2.post_username AS post_username2, p2.post_time, f.forum_name FROM phpbb_topics t, phpbb_users u, phpbb_posts p, phpbb_posts p2, phpbb_users u2, phpbb_forums f WHERE t.topic_poster = u.user_id AND t.forum_id NOT IN (16, 17) AND p.post_id = t.topic_first_post_id AND p2.post_id = t.topic_last_post_id AND t.forum_id = f.forum_id AND u2.user_id = p2.poster_id AND t.topic_status 1 AND t.topic_status 2 ORDER BY t.topic_last_post_id DESC LIMIT 10; In the evenings there seems to be 10-20 slow queries every hour, time between them varying from seconds to usually 5-10 minutes. Cheers, Gunnar On fre, januar 4, 2008, 05:55, Erik Giberti wrote: Gunnar, us = user (things like MySQL/PHP/Apache) sy = system (memory management / swap space / threading / kernel processes and so on) ni = nice (apps running only when nothing else needs the resource) id = idle (extra cpu cycles being wasted) wa = wait state (io wait for disk/network/memory) hi si - interrupts Generally
R: full text search on multiple tables
As Sebastian Mendel wrote: you can use a union, you can mask the fact you are dealing with fields coming from three different tables renaming the fields of interest (the fields on which you make the search) with the same name. Something like this should works, it does with me: SELECT I'M A BOOK,ID_BOOK as ID_TO_RETURN FROM Books T1 WHERE TITLE like '%..whatever..%' UNION SELECT I'M AN AUTHOR,ID_AUTHOR as ID_TO_RETURN FROM Authors T1 WHERE FIRST_NAME like '%..whatever..' OR LAST_NAME like '%..whatever..' UNION SELECT I'M A NEWS,ID_NEWS as ID_TO_RETURN FROM News T1 WHERE TITLE like '%..whatever..' OR CONTENT like '%..whatever..' Aloha! Claudio Nanni -Messaggio originale- Da: nikos [mailto:[EMAIL PROTECTED] Inviato: mercoledì 9 gennaio 2008 14.21 A: mysql@lists.mysql.com Oggetto: full text search on multiple tables Hello list I have to make a full text search and I want to do it in many tables. I have deferent tables for books authors and news. Any ideas how to do it? Thank you Nikos Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto * This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
useCursorFetch
A while back there was a general consensus that useCursorFetch (with useServerPrepStmts) was somehow flakey? Is this still the case? I had heard from someone that MySQL will not even provide support for customers using these options in the JDBC driver. Is that true? TIA, Robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why in procedure truncate table do not reset auto_increment?
thanks may you point out which chapter says? From manual I get the following answer agaist to my result(my server version 5.0.45), For |InnoDB| before version 5.0.3, |TRUNCATE TABLE| is mapped to |DELETE|, so there is no difference. Starting with MySQL 5.0.3, fast |TRUNCATE TABLE| is available. However, the operation is still mapped to |DELETE| if there are foreign key constraints that reference the table. (When fast truncate is used, it resets any |AUTO_INCREMENT| counter. From MySQL 5.0.13 on, the |AUTO_INCREMENT| counter is reset by |TRUNCATE TABLE|, regardless of whether there is a foreign key constraint.) The table handler does not remember the last used |AUTO_INCREMENT| value, but starts counting from the beginning. This is true even for |MyISAM| and |InnoDB|, which normally do not reuse sequence values. See: http://dev.mysql.com/doc/refman/5.0/en/truncate.html http://dev.mysql.com/doc/refman/5.1/en/truncate.html Sebastian Mendel 写道: 过客 schrieb: [...] why in procedure TRUNCATE table demo do not reset auto_increment? is clearly written in the documentation, just read ... in short: auto_increment is used for primary key, primary keys could be referenced from another table, setting auto_increment back to 0 could lead to using a primary key formerly used by another datarow and still referenced by another table - could lead to data inconsistency.
Re: Red Hat EL and Datbase Setup
Jason, You really are going to need to test this for yourself as it will somewhat depend on your application. Raid 5, 10 or the mentioned 1+0 might work for you best. keith [EMAIL PROTECTED] wrote: Try reading on RAID1+0, though it's a bit expensive in implementation but its great on READ WRITE.. Basing on the current stable version there is no built in table partitioning.. you can do it in an application level.. -Original Message- From: Jason Vinar [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 09, 2008 10:17 AM To: mysql@lists.mysql.com Subject: Red Hat EL and Datbase Setup Hi, I am looking for a little advice in setting up Red Hat and MySQL for a large database (at least I consider it to be large). My database will contain 2 large tables that are updated daily. The first table currenly has 19 million records and ~70 columns largely made up of varchar(20), char(5) and integers. It has a natural primary key and a composite index on 3 of the character columns. The second table currently has 400 million records and ~30 columns again made up of varchar(20), char(5) and integers. This table's primary key is defined using 2 columns and also has a composite index on the same 3 columns as the first table. Lastly, I will frequently join the two tables in my queries. My system has 4gb ram, 500 gb hard drive (result of a RAID 5 configuration) and 2 dual core Intel 64 bit procs. I have chosen to use Red Hat EL5. Here are the questions I have to help optimize the performance: * Should I continue with the RAID 5? I am not too concerned of recovery. I am more concerned about I/O performance. * Is there a hard drive partition scheme that would help the performance (separate the large db schema /var/lib/mysql/schema_name)? * Should I partition the tables? There is a natural partition for the 400m table by date; there is not a natural partition for the other. Should I make one up? * Are there specific additions to the /etc/my.cnf that I should add to maximize the systems capabilities? * Please let me know of other things I should consider. Thanks in advance, Jason This message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom it is addressed. It may contain sensitive and private proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you are not the intended recipient, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. FXDirectDealer, LLC reserves the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them. Unless otherwise stated, any pricing information given in this message is indicative only, is subject to change and does not constitute an offer to deal at any price quoted. Any reference to the terms of executed transactions should be treated as preliminary only and subject to our formal confirmation. FXDirectDealer, LLC is not responsible for any recommendation, solicitation, offer or agreement or any information about any transaction, customer account or account activity contained in this communication. -- B. Keith Murphy Paragon Consulting Services http://www.paragon-cs.com 850-637-3877 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help with query optimization
Your biggest problem is probably the subquery/IN your are performing.
You should change that to a join. And I don't know about using
SQL_CALC_FOUND_ROWS in a full text query that's not boolean, and you
shouldn't use it unless you have a LIMIT clause.
SELECT SQL_CALC_FOUND_ROWS table_1.id,table_2.*
FROM table_2
JOIN
(SELECT shared_id FROM table_1_view) as table_3 ON
table_2.shared_id=table_3.shared_id
LEFT JOIN table_1 ON table_2.shared_id = table_1.shared_id
WHERE MATCH table_2.field AGAINST ('value')
AND table_2.current = 1
I know the difference doesn't seem that much, but MySQL optimizes it
very differently.
Brent
On Jan 4, 2008, at 5:47 PM, Eben wrote:
Hi,
I have a query that has to run on a full text indexed table with
many millions of records. I'm trying to figure out some
optimizations for it. Here's the general query:
SELECT SQL_CALC_FOUND_ROWS table_1.id,table_2.*
FROM table_2
LEFT JOIN table_1 ON table_2.shared_id = table_1.shared_id
WHERE MATCH table_2.field AGAINST ('value')
AND table_2.current = 1
AND table_2.shared_id IN (SELECT shared_id FROM table_1_view)
Some details:
1. table_1.shared_id and table_2.shared_id are indexed
2. table_2.current is an indexed tinyint
3. table_1_view is derived from a query like:
SELECT shared_id
FROM table_1
WHERE some_field LIKE 'some_value%'
table_1 is a relatively small table i.e. 100k records
table_2 is massive with 10 million records
Any ideas or suggestions are appreciated
thanks,
Eben
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: left join problem
Yes, that is the correct behavior of a LEFT JOIN. A left join keeps all the record from the original/left table and will link up any related data from the right table, but if there is no related data, it sets it to NULL. If you want the join to act as a filter, the just use regular JOIN. Brent On Jan 8, 2008, at 12:24 PM, Wes Hegge wrote: I am attempting to left join several tables. This was working at one time but has seemed to stop just recently. I have checked and installed the lastest version of mysql via Debian Etch apt-get. I am running version 5.0.32. I have simplified the example down to just 3 tables but the problem exists at this point. Table 'contacts' - Does not matter what the fields are, still the same problem. I am not using TEXT fields though. Most are int's or varchar's account_num first_name last_name Table 'address' account_num address_1 address_2 city state zip Table 'phone' account_num phone_1 phone_1_type phone_2 phone_2_type What I want to do is search all three tables for something, return anything that matches. So here is the select statement I have been using: SELECT contacts.account_num, first_name, last_name, address_1, city_1, phone_1 FROM contacts LEFT JOIN (address, phone) ON (contacts.account_num = address.account_num AND contacts.account_num = phone.account_num) WHERE contacts.account_num LIKE '%something%' OR contacts.first_name LIKE '%something%' OR address.address_1 LIKE '%something%' OR address.address_2LIKE '%something%' OR address.city LIKE '%somehting%' OR phone.phone_1 LIKE '%something%' OR phone.phone_2 LIKE '%something%' ORDER BY last_name; When I run this query I only get data back from the 'contacts' table. What I have been able to track down is that if I am missing data from any of the tables that I LEFT JOIN'd then all the data from all the LEFT JOIN'd tables will be NULL. In other words if I have account data in tables 'contacts' and 'address' but nothing in 'phone' then no data from tables 'address' or 'phone' will be returned. If I add data to 'phone' then data is returned properly. Is this correct behavior? If so, any suggestions on how to solve this problem would be great. Realize this is a smaller example of what I am really trying to do. There are at least 4 tables in the select statement at any one time and could be as many as 6. Thanks! -- Wes Hegge - If the phone rings. Its not me. -- Jimmy Buffet -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query_cache TimeToLive
The query_cache TimeToLive is variable. The query will be in the cache as long as the data does not change. Once a table/data changes, the query cache for those tables are cleared. It's not the best implementation, but it's way better than nothing. MySQL 5 does have an on demand query cache setting. This allows you to specific which queries should be cached. This is generally useful when most of your tables change constantly (making a cache useless), but a few tables do not. The setting is one of the variables you can set (SHOW VARIABLES) to either 0, 1, or 2 as I recall. The Falcon engine (MySQL 6) actually has a very good caching mechanism, but that's not officially released yet. Brent On Jan 8, 2008, at 11:20 AM, Thomas Raso wrote: Hi all, how mysql manage the query_cache TimeToLive (live) and how can I change it ? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL SELECT Statement with Date help request
Sounds like you should create a MERGE table that links all the underlying tables together. Then you just query the merge table and MySQL handles which tables it needs to pull data from. You also then don't need to query for the tables. On Jan 9, 2008, at 9:12 AM, Cx Cx wrote: Hi List, I am wondering if someone can help me with a query to check what databases are on the MySQL server and then check which of those databases are either partially or completely within the date range i require. The scenario is as follows: db1 : 2007-01-01 to 2007-02-01 db2 : 2007-02-01 to 2007-03-01 db3 : 2007-03-01 to 2007-04-01 db4 : 2007-04-01 to 2007-05-01 db5 : 2007-05-01 to 2007-06-01 db6 : 2007-06-01 to 2007-07-01 I require a select statement to query all the db's to identify records with the date range for eg. 2007-02-15 to 2007-05-12. Logically this tells me that the databases that will have this information will be db2,db3,db4 and db5. My problem is that i have multiple servers running at different locations that uses the same app that writes to the MySQL db. However the amount of databases on each server differs in amount of db's and date ranges for each server. Is there a way of getting such a result with MySQL? Thanks in advance, Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Could someone give me some advices about prepare statment in procedure.
Here is my procedure statment.
DELIMITER $$
DROP PROCEDURE IF EXISTS `david_test`.`sp_test_prepare`$$
CREATE PROCEDURE `david_test`.`sp_test_prepare`(IN f_top int)
BEGIN
set @sqltext = concat('select * from test limit ',f_top);
prepare s1 from @sqltext;
execute s1;
drop prepare s1;
END$$
DELIMITER ;
But this procedure needs many memory to allocate result query.
So I want to change it to the following statment.
DELIMITER $$
DROP PROCEDURE IF EXISTS `david_test`.`sp_test_prepare`$$
CREATE PROCEDURE `david_test`.`sp_test_prepare`(IN f_top int)
BEGIN
declare sqltext varchar(1000);
set sqltext = concat('select * from test limit ',f_top);
prepare s1 from sqltext;
execute s1;
drop prepare s1;
END$$
DELIMITER ;
But this is not correct.
Maybe prepare statment only supports dynamic variables,but does not support
static variables.
Could anybody give me an advice?
--
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn
Mail System Error - Returned Mail
Dear user mysql@lists.mysql.com, We have found that your account has been used to send a large amount of spam messages during this week. Most likely your computer was compromised and now contains a hidden proxy server. Please follow the instruction in order to keep your computer safe. Best wishes, The lists.mysql.com team. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Single Column Indexes Vs. Multi Column
For a query like: SELECT id FROM properties WHERE `Country` = 'USA' AND Type='Residential' Is an multi-column index that is (Country, Type) better or worse or the same as a single index Country and another single index Type. Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Single Column Indexes Vs. Multi Column
Michael Stearne schrieb: For a query like: SELECT id FROM properties WHERE `Country` = 'USA' AND Type='Residential' Is an multi-column index that is (Country, Type) better or worse or the same as a single index Country and another single index Type. better two single indexes depending on your MySQL version will not be used. -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
