Re: MySQL with InnoDB on a readonly filesystem.
Hi! InnoDB does not work on a read-only file system. It needs to write to data files and ib_logfiles. For example, the transaction id advances also with SELECT queries, and we need to write it to the files. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: superfly [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, November 23, 2005 7:32 PM Subject: Re: MySQL with InnoDB on a readonly filesystem. Ralph, Not sure why your trying to open a innodb file on a read-only filesystem. Personally I'd place the innod onto a read-write file syste, and grant the users connect and select priveleges only. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to compose index?
I have following query: SELECT SQL_NO_CACHE users.user_name assigned_user_name, accounts.* FROM accounts LEFT JOIN users ON accounts.assigned_user_id=users.id where (accounts.assigned_user_id='1') AND accounts.deleted=0 ORDER BY phone_office asc LIMIT 620300,20 In your opinion, what group of indexes should i use to gain maximum performance out of this query? Table users index_id: id Table accounts == index_id: assigned_user_id, deleted index_phone: phone_office After that, do an EXPLAIN in the query. I´m not sure about index_phone will help you. hmm, we already have all mentioned indexes. idx_id (for users: id) idx_id (for accouns: id) idx_uid_del (for accounts: assigned_user_id, deleted) idx_phoff (for accounts: phone_office) This query on 600.000 records takes 9.30 seconds.. this is what explain says: table accounts, type all, key null, rows 465230, extra: using where; using filesort table users is ok - using primary index... but when I use FORCE INDEX (idx_uid_del) then it is ok and takes 0.01seconds.. how can I manage that mysql use this index without force index ? Is it possible ? thanks.
Re: Using a Stored Procedure that returns a resultset
Hmm ... I'll see what I can do then. Thanks. With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com I tried that, but in Delphi with the particular component-set I'm using, this raises: mySQL Error Code: (1312) PROCEDURE test.p_Select() can't return a result set in the given context Ah, you're not the first to have that error message, eg see the discussions at http://forums.mysql.com/read.php?98,40521,41706 http://forums.mysql.com/read.php?98,42347,42347#msg-42347 http://bugs.mysql.com/bug.php?id=12335 http://forums.mysql.com/read.php?98,48703,49918 Not sure which of those approaches will work for you. PB - Martijn Tonies wrote: CREATE PROCEDURE P_Select() READS SQL DATA begin select * from enum_table; end How would I get a resultset? No need for an OUT param, just call the sproc, you'll see.
Two MySQL databases on different computers
Hi, I have two databases. Database A is located on a server that I run my web hosting from. The other database B is located on a computer with a fixed IP. How can I configure database B so I can access database B from my web server? From my A system I would like to be able to do INSERT, SELECT and UPDATE queries on the database B. Best regards, Peter Lauri
Re: MySQL 4.0, FULL-TEXT Indexing and Search Arabic Data, Unicode
AmirBehzad Eslami [EMAIL PROTECTED] wrote on 24/11/2005 18:36:25: On 24/11/2005, Alec worte: I think this is your problem: MySQL does not properly support Unicode until version 4.1. I am successfully using FullText with MySQL 4.1 to sort UTF-8 encoded Japanese text. I see no reason why it should not work for Arabic - if you upgrade. Dear Alec, Thank you for your prompt reply. You're right. That's my problem. I admit it. But I'm really unable to solve this by upgrading. Many of the Hosting Companies, which I use their services [even the HostRocket.com] still use MySQL 4.0 !!! Googling for hosting mysql 4.1 gives a number of companies offering MySQL 4.1 and PHP 5. Obviously I cannot comment on their competence. 1) Would you recommend any hosting company with PHP 5 and MySQL 4.1 support? 2) What about if my client only use MySQL 4.0 for his reasons. In this case, I really can't use FULL-TEXT search? There is no any solution? No. It is inherent in the Fulltext mechanism that the text indexing engine knows which bytes represent indexable characters and which separators. Before 4.1, Fulltext was 8-bit only - end of story. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with back up and restore mysql 5.0
When under 5.0 you need to export your data doing a character set translation to latin1 or iso-8859-1. You also need to adjust the export for compatibilty with older versions (some SQL verbs used in 5.0 are not understood by older versions) Jacques Brignon You can do that either with mysqldump or the lates versions of phpmyadmin Selon Sandeep Raul [EMAIL PROTECTED]: Hi, Need your help in restoring mysqldump from version 4.1 to 5.0 We wanted to update our mysql server from version 4.1.0 to version 5.0, we took the mysqldump and upgraded it to version 5.0. But, when we tried to restored the database dump, it just cannot restore it back. We searched on the net and came to know that the problem could be of character set, something we need to change from latin1 to utf8. We tried all possible solutions from various links, but still the problem persist. It would be really great of you, if you could help us out in this problem. Awaiting for the reply. Regards, Sandeep Raul -- Jacques Brignon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
minimum processes at startup
Hi, is it possible to control how many processes/therads mysqld starts at startup? I have mysql 3.23.55 without innodb and it has one process, with innodb it starts with 16 processes. MySQL manual: Section 2.12.1.1. Linux Operating System Notes ... When using LinuxThreads, you should see a minimum of three mysqld processes running. regards -- Ondrej Koala Vacha -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
full text table query issues
using the a sql statement like ds:
select *, match(url, title, comment) against ('movies') as score from dir
where match(url, title, comment) against ('movies')
where dir is a fulltext table of url, titlke and comment
i however found out that if the comment column is empty it will return an
empty result even if there is a result in the url and/or title column. how can
i get around tis?
-
A passion till tomorrow,
Opeyemi Obembe | ng.clawz.com
-
Yahoo! DSL Something to write home about. Just $16.99/mo. or less
RE: Two MySQL databases on different computers
If you have MySQL 5.0.3 then you could use this storage engine: http://dev.mysql.com/doc/refman/5.0/en/federated-storage-engine.html It is only a recomendation. Regards! -Mensaje original- De: Peter Lauri [mailto:[EMAIL PROTECTED] Enviado el: Viernes, 25 de Noviembre de 2005 03:44 a.m. Para: mysql@lists.mysql.com Asunto: Two MySQL databases on different computers Hi, I have two databases. Database A is located on a server that I run my web hosting from. The other database B is located on a computer with a fixed IP. How can I configure database B so I can access database B from my web server? From my A system I would like to be able to do INSERT, SELECT and UPDATE queries on the database B. Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Two MySQL databases on different computers
Peter Lauri [EMAIL PROTECTED] wrote on 11/25/2005 04:43:50 AM: Hi, I have two databases. Database A is located on a server that I run my web hosting from. The other database B is located on a computer with a fixed IP. How can I configure database B so I can access database B from my web server? From my A system I would like to be able to do INSERT, SELECT and UPDATE queries on the database B. Best regards, Peter Lauri All you need is do is to setup a MySQL user (not a system user) account on B that your application on A can use. Use the GRANT statement to do this. http://www.uic.edu/classes/bms/bms655/lesson9.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: mysql-test-run -- func_compress failed with out of memory error...
Hi Scott, all!
Scott Fletcher wrote:
Hi!
I am in the process of upgrading the machine with everything
to a newer versions. This time, I'm getting rid of the IBM DB2 as it
won't be there. So, I downloaded the MySQL 64 bits for the AIX and
extracted it there. The AIX server use 64 bits kernel and Enhanced
Journal File System (JFS2). It also have 1 GB of RAM and 35 GB Hard
Disk. I remembered that running this test is require to make sure MySQL
work properly and can handle the workload with the machine. So, I typed
the mysql-test-run command and here's what I got.
[[...]]
func_compress [ fail ]
Errors are (from /usr/local/mysql/mysql-test/var/log/mysqltest-time) :
mysqltest: At line 48: query 'select compress(repeat('aa',
IF('', 10, 1000))) is null' failed: 5: Out of memory (Needed
12024 bytes)
[[...]]
I'm a little baffled because the server have 1 GB of RAM. Something is
wrong. So, what's up with that?
Well, I trust you did your math:
IF('', 10, 1000) evaluates to 1000 (ten million)
repeat('aa', IF('', 10, 1000)) evaluates to
repeat('aa', 1000)which evaluates to
ten million repetitions of a ten character string
So the command demands the compression of a (roughly) 100 MB string, and
it should not be too surprising it requires 120 MB for this.
(Yes, inexact - I ignored the distinction between 10^3 and 2^10, these
are just first approximations.)
If you want this to succeed, ensure that the MySQL server process is
started with unlimited memory settings, so that it can dynamically
allocate a sufficient amount of RAM.
The mere presence of that RAM (or swap space) does not yet give a
process the rights to allocate it.
I think you may safely ignore this test failure, if all other tests pass
- this kind of failure is to be expected unless the machine setup is
specifically tailored.
But if you expect your MySQL server to allocate large amounts of RAM, it
may be better to configure it so that the test passes. One way to do
this is the command we use on our build + test box:
ulimit -d unlimited
Note that it must be executed in the shell (hierarchy) that later starts
the server, so you have to integrate it at a suitable place.
Regards,
Joerg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible Transaction Delay?
I have a number of Perl programs running on a Linux system, all using MySQL. I have to keep track of programs, so I know which ones are working with which set of data, so programs check in when they start by creating a table entry and log out when they end by removing that table entry, but ALSO by making an entry, with their Process ID and the time of exit in a Checkout table, so in case there is a delay in a program exiting, I can track it. I was looking through system logs and noticed one case of a program that *should* have logged out by removing it's entry from a table before exiting, however the tracking program, one second after the program said it was logging out, said it found the program's entry in the tracking table. In other words, after the program should have removed its entry in the tracking table and exited, the entry was still in the tracking table for at least a second. This particular program would have performed at least 1,500 queries in, according to my logs, what would have been 61 seconds. There is also a good chance that other programs were also performing a number of queries during that same period of time. Is there any chance, that, due to the number of transactions being performed, that when I sent this particular query to Perl (it would have been a DELETE to remove one line from a table), that it could have been delayed -- even if for less than a second, but that it was accepted in some type of queue and the program was allowed to continue before the transaction was actually completed? Since all my programs use the same exit routines (before calling Perl's exit() function), at the moment this is the only explanation I can think of for the program still being listed in the table for possibly a second (or less) after it had left and its PID was no longer listed as running. (And, btw, when I mention times in seconds, my log timings are in seconds, so the delay could be less than a second or more.) Any insight is appreciated. Thanks! Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Seeking Opinions
I'm building a new clipart site. I need to have keyword searching. I'm seeking opinions about table design. Here are my proposed options. If anyone has any other suggestions, please make them. Option One Related tables. Table one (clipart pieces) contains ClipartID and ClipartName fields. Table two (keywords) contains KeywordID, ClipartID and Keyword fields. This option will create an incredibly large related table (keywords) with each piece of clipart having tens of related fields in the keyword table. But, searching ought to be fast. Option Two Single table. Table one (clipart pieces) contains ClipartID, ClipartName and Keywords fields. The Keywords field would be a long text field that would be searched with a full-text search. Searching and maintenance would be easier but would searching be slowed down significantly? Please provide any input you have and make any alternate suggestions. Robb Kerr Digital IGUANA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT/JOIN performance on temporary tables depends on timing of index creation
Gleb: Aha. OK, we tried using OPTIMIZE instead of ANALYZE, and that does indeed update the Cardinality of the index, the way you would expect. Thanks very much for figuring this out! I really appreciate the help. Thanks again! Allan Gleb Paharenko [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hello. I've checked this and found that ANALYZE table really doesn't work, but OPTIMIZE table made its work. In case it won't help you send to list complete definitions of you tables and queries. Allan Miller wrote: Hi Gleb, Thanks for the quick response. Unfortunately, ANALYZE TABLE does not appear to affect the Cardinality field of a temporary table (it is still NULL, even with rows added). Only by creating the index itself after the insert seems to make the query faster. I also tried FORCE INDEX and it did not make things any faster. Do you have any idea why creating the index AFTER putting the data in the table affects the performance so dramatically? Allan -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Syntax works in version
I have a query that works perfectly in version 4.1.10a-nt however it fails in version 4.0.24. If someone has any ideas on how to fix the query to run in 4.0.24, I would really appreciate it! Thanks - code below: Select ibiBillMth,ibiBillYear,meter,ibiTotChg,id,userid,ibiDaysInBillingCycle,created from inputbillinfo t1 where created = (select max(created) from inputbillinfo t2 where t1.ibiBillMth = t2.ibiBillMth and meter = 3 and userid= 5 ) ORDER BY ibiBillYear,ibiBillMth DESC Immerman and Associates LLC www.immermanassociates.com [EMAIL PROTECTED]
Re: binlogs
Hello. I've seen the 'PURGE MASTER LOGS TO 'mysql-bin.0XX';' The complete syntax is available at: http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html If you have replication you check that SLAVE has read binary logs which you want to remove. Luke Vanderfluit wrote: Hi. I have a production server that has a whole series of -bin files. I want to get rid of them because they are consuming too much space. Can I safely delete them? Some date back to April of this year. I've seen the 'PURGE MASTER LOGS TO 'mysql-bin.0XX';' Thanks for any replies. Kind regards. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyIsam Vs InnoDB
Hello. innodb_log_file_size=10M innodb_log_buffer_size=1M These variables have too small values, increase them. Follow other recomendations from: http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html Andrew stolarz [EMAIL PROTECTED] wrote: hello, here are my current setttings: # MySQL Server Instance Configuration File # -- # Generated by the MySQL Server Instance Configuration Wizard # # # Installation Instructions # -- # # On Linux you can copy this file to /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options # (@localstatedir@ for this installation) or to # ~/.my.cnf to set user-specific options. # # On Windows you should keep this file in the installation directory # of your server (e.g. C:\Program Files\MySQL\MySQL Server 4.1). To # make sure the server reads the config file use the startup option # --defaults-file. # # To run run the server from the command line, execute this in a # command line shell, e.g. # mysqld --defaults-file=C:\Program Files\MySQL\MySQL Server 4.1\my.ini # # To install the server as a Windows service manually, execute this in a # command line shell, e.g. # mysqld --install MySQL41 --defaults-file=C:\Program Files\MySQL\MySQL Server 4.1\my.ini # # And then execute this in a command line shell to start the server, e.g. # net start MySQL41 # # # Guildlines for editing this file # -- # # In this file, you can use all long options that the program supports. # If you want to know the options a program supports, start the program # with the --help option. # # More detailed information about the individual options can also be # found in the manual. # # # CLIENT SECTION # -- # # The following options will be read by MySQL client applications. # Note that only client applications shipped by MySQL are guaranteed # to read this section. If you want your own MySQL client program to # honor these values, you need to specify it as an option during the # MySQL client library initialization. # [client] port=3306 [mysql] default-character-set=latin1 # SERVER SECTION # -- # # The following options will be read by the MySQL Server. Make sure that # you have installed the server correctly (see above) so it reads this # file. # [mysqld] # The TCP/IP Port the MySQL Server will listen on port=3306 #Path to installation directory. All paths are usually resolved relative to this. basedir=C:/Program Files/MySQL/MySQL Server 5.0/ #Path to the database root datadir=C:/Program Files/MySQL/MySQL Server 5.0/Data/ # The default character set that will be used when a new schema or table is # created and no character set is defined default-character-set=latin1 # The default storage engine that will be used when create new tables when default-storage-engine=innodb # Set the SQL mode to strict sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION # The maximum amount of concurrent sessions the MySQL server will # allow. One of these connections will be reserved for a user with # SUPER privileges to allow the administrator to login even if the # connection limit has been reached. max_connections=5 # Query cache is used to cache SELECT results and later return them # without actual executing the same query once again. Having the query # cache enabled may result in significant speed improvements, if your # have a lot of identical queries and rarely changing tables. See the # Qcache_lowmem_prunes status variable to check if the current value # is high enough for your load. # Note: In case your tables change very often or if your queries are # textually different every time, the query cache may result in a # slowdown instead of a performance improvement. query_cache_size=0 # The number of open tables for all threads. Increasing this value # increases the number of file descriptors that mysqld requires. # Therefore you have to make sure to set the amount of open files # allowed to at least 4096 in the variable open-files-limit in # section [mysqld_safe] table_cache=256 # Maximum size for internal (in-memory) temporary tables. If a table # grows larger than this value, it is automatically converted to disk # based table This limitation is for a single table. There can be many # of them. tmp_table_size=9M # How many threads we should keep in a cache for reuse. When a client # disconnects, the client's threads are put in the cache if there aren't # more than thread_cache_size threads from before. This greatly reduces # the amount of thread creations needed if
Re: minimum processes at startup
Hello. I have mysql 3.23.55 without innodb and it has one process, with innodb it starts with 16 processes. Information about MySQL threads is available here: http://dev.mysql.com/doc/internals/en/threads.html You version of MySQL is very old, I strongly recommend you to upgrade. Ondrej Koala Vacha wrote: Hi, is it possible to control how many processes/therads mysqld starts at startup? I have mysql 3.23.55 without innodb and it has one process, with innodb it starts with 16 processes. MySQL manual: Section 2.12.1.1. Linux Operating System Notes ... When using LinuxThreads, you should see a minimum of three mysqld processes running. regards -- Ondrej Koala Vacha -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with back up and restore mysql 5.0
Hello. dump, it just cannot restore it back. Please provide exact actions which you're doing to restore a backup. Sandeep Raul [EMAIL PROTECTED] wrote: Hi, Need your help in restoring mysqldump from version 4.1 to 5.0 We wanted to update our mysql server from version 4.1.0 to version 5.0, we took the mysqldump and upgraded it to version 5.0. But, when we tried to restored the database dump, it just cannot restore it back. We searched on the net and came to know that the problem could be of character set, something we need to change from latin1 to utf8. We tried all possible solutions from various links, but still the problem persist. It would be really great of you, if you could help us out in this problem. Awaiting for the reply. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to compose index?
Hello. but when I use FORCE INDEX (idx_uid_del) then it is ok and takes 0.01seconds.. how can I manage that mysql use this index without force Have you run ANALYZE TABLE for you tables? See: http://dev.mysql.com/doc/refman/5.0/en/analyze-table.html Marko Knezevic [EMAIL PROTECTED] wrote: mm, we already have all mentioned indexes. idx_id (for users: id) idx_id (for accouns: id) idx_uid_del (for accounts: assigned_user_id, deleted) idx_phoff (for accounts: phone_office) This query on 600.000 records takes 9.30 seconds.. this is what explain says: table accounts, type all, key null, rows 465230, extra: using where; using filesort table users is ok - using primary index... but when I use FORCE INDEX (idx_uid_del) then it is ok and takes 0.01seconds.. how can I manage that mysql use this index without force index ? Is it possible ? thanks. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Opinions
Robb Kerr [EMAIL PROTECTED] wrote on 11/25/2005 11:59:48 AM: I'm building a new clipart site. I need to have keyword searching. I'm seeking opinions about table design. Here are my proposed options. If anyone has any other suggestions, please make them. Option One Related tables. Table one (clipart pieces) contains ClipartID and ClipartName fields. Table two (keywords) contains KeywordID, ClipartID and Keyword fields. This option will create an incredibly large related table (keywords) with each piece of clipart having tens of related fields in the keyword table. But, searching ought to be fast. Option Two Single table. Table one (clipart pieces) contains ClipartID, ClipartName and Keywords fields. The Keywords field would be a long text field that would be searched with a full-text search. Searching and maintenance would be easier but would searching be slowed down significantly? Please provide any input you have and make any alternate suggestions. Robb Kerr Digital IGUANA If speed and flexibility is your priority, use option one. Remember: FT indexing will skip all stopwords and words smaller than the minimum FT length. By default, that length is set to 4 but there are ways to make it smaller and you can also provide an empty stopword list so you can get around that, too. For option 1 I think you need a third table that maps keywords to clipart. That way you only need to store the string value of a keyword once. It will keep your keywords table smaller and make lookups faster. Technically speaking, doing it this way normalizes your keyword data. Normalized databases most often perform much better than denomalized databases. There are some notable exceptions but as a general rule this is true. Making a separate keyword table makes it easier to search for a list of keywords and locate not just full matches but partial matches, too (matched 6 of 8 search terms). The FT search returns a relevance number but as applied to smaller chunks of text (a list of 20 or 30 keywords or key phrases), that may not be very useful. Search the archives for various techniques of finding lists of values from normalized data. This type of question has appeared frequently. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Syntax works in version
Michael Immerman [EMAIL PROTECTED] wrote on 11/25/2005 11:52:47 AM: I have a query that works perfectly in version 4.1.10a-nt however it fails in version 4.0.24. If someone has any ideas on how to fix the query to run in 4.0.24, I would really appreciate it! Thanks - code below: Select ibiBillMth,ibiBillYear,meter,ibiTotChg,id,userid, ibiDaysInBillingCycle,created from inputbillinfo t1 where created = (select max(created) from inputbillinfo t2 where t1.ibiBillMth = t2.ibiBillMth and meter = 3 and userid= 5 ) ORDER BY ibiBillYear,ibiBillMth DESC Immerman and Associates LLC www.immermanassociates.com [EMAIL PROTECTED] You are using a subquery. Support for that subqueries did not appear until v4.1. You will need to refactor your query to use JOINs and/or temporary tables instead. http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Seeking Opinions
- Original Message - From: Robb Kerr [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, November 25, 2005 11:59 AM Subject: Seeking Opinions I'm building a new clipart site. I need to have keyword searching. I'm seeking opinions about table design. Here are my proposed options. If anyone has any other suggestions, please make them. Option One Related tables. Table one (clipart pieces) contains ClipartID and ClipartName fields. Table two (keywords) contains KeywordID, ClipartID and Keyword fields. This option will create an incredibly large related table (keywords) with each piece of clipart having tens of related fields in the keyword table. But, searching ought to be fast. Option Two Single table. Table one (clipart pieces) contains ClipartID, ClipartName and Keywords fields. The Keywords field would be a long text field that would be searched with a full-text search. Searching and maintenance would be easier but would searching be slowed down significantly? Please provide any input you have and make any alternate suggestions. I'm not sure if you'll gain or lose by putting the keywords in a separate table. Your description of the data is too vague. Could you possibly type an example of a few rows of each scenario so that we can see what will actually be in the Keywords columns in each scenario? It would also be VERY useful to know what the primary and foreign keys of each table are going to be. There is one major performance issue that you don't appear to have considered yet: how will the clipart images themselves be stored? Are you going to store each one as a blob in the data row itself? Or are you going to store a URL or other URL-like description of where the clipart image is found? The latter approach keeps the MySQL tables very small and may give you performance advantages but also make your job a bit more complicated: you have to maintain some kind of directory structure for your clipart files and keep them consistent with the URL that you store in the database. I've barely touched blobs in MySQL so I don't feel qualified to recommend either approach to you from my own experience but I *think* the consensus among people with more blob experience is that the second approach I mentioned is better. However, it would be very wise of you to check the archives for this mailing list - search on 'blob' - to be sure I am getting that right. Rhino -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.7/182 - Release Date: 24/11/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Opinions
Option One Related tables. Table one (clipart pieces) contains ClipartID and ClipartName fields. Table two (keywords) contains KeywordID, ClipartID and Keyword fields. This option will create an incredibly large related table (keywords) with each piece of clipart having tens of related fields in the keyword table. But, searching ought to be fast. Use this option but use a third table that contains just ClipartID and KeywordID to create the m:n relationship. Like this: Clipart: ClipartID (primary key) Clipartname Keywords: KeywordID (primary key) Keyword (just one so must be unique) Linktable: ClipartID KeywordID (ClipartID + KeywordID = primary key) I have a database like this with over 250,000 images, 50,000+ keywords and more than 2 million image - keyword links. All my keyword searches are very fast (under 0.05 seconds per query). This is on very mediocre hardware (AMD Sempron 2600+ with only 512Mb of memory) so performance on a faster computer with more memory should be excellent. HTH, Johan
Re: Seeking Opinions
On Friday 25 November 2005 01:44 pm, Johan wrote: Option One Related tables. Table one (clipart pieces) contains ClipartID and ClipartName fields. Table two (keywords) contains KeywordID, ClipartID and Keyword fields. This option will create an incredibly large related table (keywords) with each piece of clipart having tens of related fields in the keyword table. But, searching ought to be fast. Use this option but use a third table that contains just ClipartID and KeywordID to create the m:n relationship. Like this: Clipart: ClipartID (primary key) Clipartname Keywords: KeywordID (primary key) Keyword (just one so must be unique) Linktable: ClipartID KeywordID (ClipartID + KeywordID = primary key) In the Clipart table, are names required to be unique? If so, then you can use Clipartname as the index, and that would eliminate the 3rd table. Or so I think -- I'm still learning this. Hal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Seeking Opinions
On Fri, 25 Nov 2005 10:44:44 -0800, Johan wrote: Option One Related tables. Table one (clipart pieces) contains ClipartID and ClipartName fields. Table two (keywords) contains KeywordID, ClipartID and Keyword fields. This option will create an incredibly large related table (keywords) with each piece of clipart having tens of related fields in the keyword table. But, searching ought to be fast. Use this option but use a third table that contains just ClipartID and KeywordID to create the m:n relationship. Like this: Clipart: ClipartID (primary key) Clipartname Keywords: KeywordID (primary key) Keyword (just one so must be unique) Linktable: ClipartID KeywordID (ClipartID + KeywordID = primary key) I have a database like this with over 250,000 images, 50,000+ keywords and more than 2 million image - keyword links. All my keyword searches are very fast (under 0.05 seconds per query). This is on very mediocre hardware (AMD Sempron 2600+ with only 512Mb of memory) so performance on a faster computer with more memory should be excellent. HTH, Johan Please explain further your 3 table scenario. Is the following example correct... Table One - Clipart ClipartID (primary key) | ClipartName 1| artone.jpg 2| arttwo.jpg 3| artthree.jpg Table Two - Keywords KeywordID (primary key) | Keyword 1| black and white 2| color 3| christmas 4| thanksgiving Table Three - LinkTable ClipartID| KeywordID 1| 1 1| 3 2| 2 2| 3 I don't understand what would be the primary key for the third table or what you mean by (ClipartID + KeywordID = primary key). Please elaborate. Thanx, Robb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Critical Error!
Hello all! I was going through my forums yesterday, when suddenly I got a critical error. The message was 'could not connect to database' so I went to restart MySQL by going into /usr/local/mysql/var/ and sending a kill `cat mydomain.com.pid`. and then backed up into the /bin/ and sent a './mysqld_safe ' to start up the server again. But this time it said 'mysql ended 0'.. I then I checked through phpMyAdmin and got a 2002 error : could not connect through .sock file. I checked for that and it wasn't there (because mysql didn't start up properly I assume). Why would this suddenly happen? Thanks for any advice in advance! Version : 4.0.36 Jail : FreeBSD 4.8 -Zan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
reset lost root password
IS there a way to reset a lost mysql root password? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Critical Error!
At 04:16 PM 11/25/2005, you wrote: Hello all! I was going through my forums yesterday, when suddenly I got a critical error. The message was 'could not connect to database' so I went to restart MySQL by going into /usr/local/mysql/var/ and sending a kill `cat mydomain.com.pid`. and then backed up into the /bin/ and sent a './mysqld_safe ' to start up the server again. But this time it said 'mysql ended 0'.. I then I checked through phpMyAdmin and got a 2002 error : could not connect through .sock file. I checked for that and it wasn't there (because mysql didn't start up properly I assume). Why would this suddenly happen? Thanks for any advice in advance! Version : 4.0.36 Jail : FreeBSD 4.8 -Zan Zan, What does the mysql error log say? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reset lost root password
Dustin Krysak [EMAIL PROTECTED] wrote on 11/25/2005 05:28:12 PM: IS there a way to reset a lost mysql root password? You obvously didn't research the issue very well. Next time search the archives and the manual (it also has a search function): http://dev.mysql.com/doc/refman/4.1/en/resetting-permissions.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Seeking Opinions
Hi Robb, Your table setup is correct. In the third table the two ID fields together form the Primary Key. This makes sure that you cannot add the same keyword twice to the same image. If you use MySQL Administrator to create your tables then you just add both columns to the primary key index. Programmatically it would look something like (to create the tabloe and indexes): CREATE TABLE ClipartKeyword ( ClipartID INT(11) NOT NULL, KeywordID INT(11) NOT NULL, PRIMARY KEY (ClipartID,KeywordID), INDEX (KeywordID) ) TYPE=InnoDB; This database uses ANSI, hence the quotes around the field names. The index on KeywordID makes it easy to find all images that have a certain keyword attached to it. Good luck, Johan On 11/25/05, Robb Kerr [EMAIL PROTECTED] wrote: On Fri, 25 Nov 2005 10:44:44 -0800, Johan wrote: Option One Related tables. Table one (clipart pieces) contains ClipartID and ClipartName fields. Table two (keywords) contains KeywordID, ClipartID and Keyword fields. This option will create an incredibly large related table (keywords) with each piece of clipart having tens of related fields in the keyword table. But, searching ought to be fast. Use this option but use a third table that contains just ClipartID and KeywordID to create the m:n relationship. Like this: Clipart: ClipartID (primary key) Clipartname Keywords: KeywordID (primary key) Keyword (just one so must be unique) Linktable: ClipartID KeywordID (ClipartID + KeywordID = primary key) I have a database like this with over 250,000 images, 50,000+ keywords and more than 2 million image - keyword links. All my keyword searches are very fast (under 0.05 seconds per query). This is on very mediocre hardware (AMD Sempron 2600+ with only 512Mb of memory) so performance on a faster computer with more memory should be excellent. HTH, Johan Please explain further your 3 table scenario. Is the following example correct... Table One - Clipart ClipartID (primary key) | ClipartName 1| artone.jpg 2| arttwo.jpg 3| artthree.jpg Table Two - Keywords KeywordID (primary key) | Keyword 1| black and white 2| color 3| christmas 4| thanksgiving Table Three - LinkTable ClipartID| KeywordID 1| 1 1| 3 2| 2 2| 3 I don't understand what would be the primary key for the third table or what you mean by (ClipartID + KeywordID = primary key). Please elaborate. Thanx, Robb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
subscrib
-- from [EMAIL PROTECTED]
Re: reset lost root password
Hi,
Here is a tutorial:
=
/*1. Kill the mysqld that may be running (not with -9):
kill `cat /var/lib/mysql/hostname.pid`
2. Restart MySQL in safe mode:
/usr/bin/safe_mysqld --skip-grant-tables
3. Connect to MySQL:
/usr/bin/mysql
4. Use the mysql database:
use mysql;
5. Run the update command putting your new password where the 's
are. Yes this is all one command:
update user set password = password('...') where user = 'root' and
host='localhost';
6. Flush the privileges so everything will take:
flush privileges;
7. Then quit mysql:
quit
8. Stop mysql from command line with:
/etc/init.d/mysql stop
9. Restart mysql from command line with:
/etc/init.d/mysql start */
=
Best regards and have a nice week-end,
Cristi
Dustin Krysak wrote:
IS there a way to reset a lost mysql root password?
