Re: sql parser
On Sun, Nov 09, 2003 at 12:48:42AM -0600, Carl Karsten wrote: It looks like sql_yacc.y is what is used to define the syntax that MySql uses. Yes. It has been a while sense I took a compiler class, so forgive my ignorance. is sql_yacc.y used to generate the parser which is then compiled, or is it used at runtime? sql_yacc.yy is used to produce sql_yacc.cc which is then compiled as part of building MySQL. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 56 days, processed 2,113,302,066 queries (434/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help constructing query ...
I have a table of full URLs and IPs and am using the following query to return
distinct web requests by domain. Using SUBSTRING_INDEX it only returns the
domain part of the URL:
SELECT SUBSTRING_INDEX(url, '/', 3) as topsites, count(distinct ip) as count
from tablename WHERE SUBSTRING_INDEX(url, '/', 3) LIKE '%mydomain%' group by
topsites order by count
Example output:
topsitescount
http://www.mydomain.com5
http://mydomain.com 3
My question is how do I modify the query to get it to merge requests for the
same domain by ignoring the www. so that the above would return:
http://mydomain.com 8
I think it has something to do with adding
REPLACE('url', 'www.', '')
but I can't figure out where to put it to make it work.
Thanks!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: sql parser
Yes, the sql_yacc.y is used to generate the sql parser. The parser is used more to run the sql rather than to break it into its tokens. Using the lex files would be closer to your stated goals. The file lex.h in the same directory as sql_yacc.y contains all the tokens mysql uses to break apart an SQL statement. sql_lex.cc is the lexer for mysql, although it is non-trivial code. If your needs are for your editor to fully understand the SQL statement, look into the lex files. If your editor only needs to color code stuff, you could possibly get away with just using the published list of mysql keywords for highlighting. -Original Message- From: Carl Karsten [mailto:[EMAIL PROTECTED] Sent: Sunday, November 09, 2003 12:49 AM To: [EMAIL PROTECTED] Subject: sql parser I am looking for code that will take an SQL command and break it into it's parts. I have found some attempts, but none that use the code from an actual SQL engine, and big surprise, they don't work 100%. I have a few goals: 1) developer tool: cut/paste the SQL command and get a pretty format display - color, each component on a separate line, sub selects indented, etc. How often have you done this by hand and missed a paren? 2) part of an app: user is given a form with a bunch of textbox's. The form has a 'basic query' and each textbox has properties defining how to augment the basic query. For each input the user gives, augment the query. This would be much easier if I had something to break the basic query up into it's parts. I am guessing that such a thing does exist, so before I go spelunking through the source code, perhaps someone can point me somewhere. It looks like sql_yacc.y is what is used to define the syntax that MySql uses. It has been a while sense I took a compiler class, so forgive my ignorance. is sql_yacc.y used to generate the parser which is then compiled, or is it used at runtime? The goal is to be able to hook into the MySql code without having to copy it. That way as MySql evolves, so will my tool. http://www.personnelware.com/carl/resume.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
?config/installation problems
installed mysql on gentoo linux. [EMAIL PROTECTED] etc # mysqladmin version mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' [EMAIL PROTECTED] etc # mysqladmin -p version Enter password: mysqladmin Ver 8.40 Distrib 4.0.14, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license [EMAIL PROTECTED] etc # mysqladmin variables mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' [EMAIL PROTECTED] etc # mysqladmin -p variables Enter password: +-+ --- + | Variable_name | Value Am I going to have problems further down the line if I dont sort this? Sort what?| not clear in this when or if I should edit my.cnf /mysqlaccess.cnf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help on my query statement
I have a table called TRANSACTION, it has some fields : REGDATE - date of the transaction WHAT - a string (NOT UNIQUE) CUSTOMER - customer code I want to query all WHAT that the date of the transaction was 15 days before. So, my sql query is : SELECT WHAT FROM TRANSACTION WHERE REGDATE Now() -INTERVAL 15 DAY But, because of WHAT is not unique, then I could receive some duplicates (customer can send some WHAT in the same REGDATE). How can I query only the unique WHAT ? I need to list all WHAT (uniquely) that the date transaction was 15 days before. Please help, Thanks __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Got error 12 from table handler
Hi, i got this message after querying the 3255 records table, and the query is like this: SELECT a.book_no, a.isbn_a, a.author_a, a.title_a, a.title_b, a.title_c, a.edition, a.place_publication_a, a.place_publication_b, a.place_publication_c, a.physical_desc_b, a.physical_desc_c, a.actal_price, a.currency_code, a.book_name, a.author_name, a.publ_name, a.subject_code, b.subject_desc FROM book_data a, subject b where a.subject_code=b.subject_code ORDER BY b.subject_desc, a.entry_date DESC it works fine locally on my win98 (PWS server) MySQL 4.0.15 but remotly on my FREEBSD server gives this error: query error: Got error 12 from table handler Any Suggesstions? George Atef _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index - Soundex Question
Hi Using mysql 3.28 I have a table with numerous columns and with a index on the surname column In order to give user a choice of searching on an exact name or using soundex I did the following: Inserted a new field in the table, SURNAME_SDX char(5) Populated it from the surname column UPDATE global SET SURNAME_SDX = soundex(SURNAME); And then indexed SURNAME_SDX CREATE INDEX soundex_index ON global (SURNAME_SDX); And all works just fine. Now this may be a stupid question (but I will ask anyway) When I have been adding more data to the table I'm assuming the index created on the surname column will be updated But I have been dropping the SURNAME_SDX field and then recreating it, populating it and indexing it. Am I doing this correctly or is there a quicker way. Thanks in advance. Regards John Berman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about MySQL and Oracle.
Hello! I have one question. MySQL is better than Oracle or not? Thank you. -- ___ OperaMail free e-mail - http://www.operamail.com OperaMail Premium - 28MB, POP3, more! US$29.99/year Powered by Outblaze -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance while creating indexes
OK, I'll qualify the statement. Software RAID-5 on my adaptec SCSI controller and external disk array logs a message aic7xxx_abort returns 0x2003 to /var/log/messages and the whole array shuts down (and anything else attached to the card, regardless of bus) for minutes at a time before restarting without any intervention. Google searches suggest that this configuration worked reliably before kernel 2.4.4 and may be related to lost interrups when sharing an IRQ. (My controller is sharing and IRQ with the ethernet card. Buggy ethernet driver disabling interrupts could cause this behavior.) Both 2.4.20 and 2.6.0-test9 failed with the same errors. Some people had success using noapic. Not me. So, I have given up on my software raid configuration for now because it is unstable. I'll get back to it when database issues have been solved. Is that better? On Fri, Nov 07, 2003 at 05:03:43PM -0600, William Baker wrote: Sorry for the slow reply. I was battling SCSI controller bugs as well as database issues. I have given up on the software raid for now because it is unstable. Really? I've run Linux software RAID quite happily on several systems (both RAID-5 and RAID-1) for years. Back to the subject at hand: performance. You are right, the load is meaningless outside the context of a specific machine...and often even inside the context of a specific machine. top showed that the system was disk (iowait) bound. Changing the innodb_log_buffer_size (64MB) and innodb_log_file_size (32MB) was the trick to increasing performance significantly. I was able to cut index build time in half. Ah, good. There is still way too much disk activity during the index build. Since the whole file fits in cache at several levels, it makes no sense that the CPU still reflects an average of 20% in iowait. At least user space is now around 70% cpu usage, which is up from under 50%. Other than digging into souce code and using strace, I'm clueless as to how to improve this situation. I think it's still way too high. I've tried variations of all the system variables that appear to be relevant. Did we already talk about the log flush method you're using with InnoDB? I don't recall... Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Get multiple categories in result set
Hello, I've got a table with articles and a table with categories. Using a 'temporary' table it's easy to assign multiple categories to an article. The insert is no problem, because I can perform two queries, but for the output I get one row for every every category. But i like to get one row with every category name in a field, separated by a special character or something similar. Has anyone an idea to do that without a extra query for each article? Regards, Andreas Ahlenstorf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance while creating indexes
Did we already talk about the log flush method you're using with InnoDB? I don't recall... Log flush method? As described by Mysql documentation: insert If you can afford the loss of some latest committed transactions, you can set the `my.cnf' parameter |innodb_flush_log_at_trx_commit| to 0. |InnoDB| tries to flush the log once per second anyway, though the flush is not guaranteed. /insert Well, we didn't discuss it, but I did originally post my `my.cnf` file which had this parameter set: innodb_flush_log_at_trx_commit=0 I haven't experimented with the parameter innodb_flush_method=O_DSYNC. At least not yet. bbaker -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index - Soundex Question
* John Berman Using mysql 3.28 There is no such version... you probably have version 3.23.something...? I have a table with numerous columns and with a index on the surname column In order to give user a choice of searching on an exact name or using soundex I did the following: Inserted a new field in the table, SURNAME_SDX char(5) Populated it from the surname column UPDATE global SET SURNAME_SDX = soundex(SURNAME); And then indexed SURNAME_SDX CREATE INDEX soundex_index ON global (SURNAME_SDX); And all works just fine. Now this may be a stupid question (but I will ask anyway) When I have been adding more data to the table I'm assuming the index created on the surname column will be updated That is correct, the index is updated automatically. But I have been dropping the SURNAME_SDX field and then recreating it, populating it and indexing it. There is no need to re-create the index. As long as you insert soundex(SURNAME) in the SURNAME_SDX column when you add records, and this column has an index, the index will be updated, just like the index on SURNAME. Am I doing this correctly or is there a quicker way. There is nothing wrong in the way you have been doing it, I suppose it is working just fine? But it is not necessary. Not re-creating the index should save you some time. :) -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Aliases
I am trying to do a select statement where I am using one of the aliases in the where clause. I keep getting an error that the field does not exist. example: Select invno, invdate, invamt, left(invdate,2) as month from salesfile where month = '01' MySql keeps telling me that month is not a field. Is there a way to do this? Thanks David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Aliases
* David Katz I am trying to do a select statement where I am using one of the aliases in the where clause. I keep getting an error that the field does not exist. From the manual: Note that standard SQL doesn't allow you to refer to an alias in a WHERE clause. This is because when the WHERE code is executed the column value may not yet be determined. URL: http://www.mysql.com/doc/en/Problems_with_alias.html example: Select invno, invdate, invamt, left(invdate,2) as month from salesfile where month = '01' MySql keeps telling me that month is not a field. Is there a way to do this? Yes, simply repeat the expression: Select invno, invdate, invamt, left(invdate,2) as month from salesfile where left(invdate,2) = '01' -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Get multiple categories in result set
* Andreas Ahlenstorf I've got a table with articles and a table with categories. Using a 'temporary' table it's easy to assign multiple categories to an article. The insert is no problem, because I can perform two queries, but for the output I get one row for every every category. But i like to get one row with every category name in a field, separated by a special character or something similar. Has anyone an idea to do that without a extra query for each article? From version 4.1 you can use the GROUP_CONCAT() function, I think it does exactly what you are asking: URL: http://www.mysql.com/doc/en/GROUP-BY-Functions.html#IDX1434 For versions 3.x and 4.0, I think the easiest way is to use some programming language, but it may be possible to do it using mysql only, in some cases. Is there a limited number of categories per article? Say, max five? Then you could do five LEFT JOINS and use CONCAT() to glue it all togheter into one column in the result. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Nuke_
This may be another silly question Looking at our dbase I noticed that a number of tables have been added The all start nuke_ I have no idea who added them, I can only assume my ISP added them, do they have a purpose, can I remove them etc Regards John Berman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nuke_
these are tables for either postnuke or phpnuke. Both are very nice content management systems: http://www.postnuke.com/ http://www.phpnuke.org/ This may be another silly question Looking at our dbase I noticed that a number of tables have been added The all start nuke_ I have no idea who added them, I can only assume my ISP added them, do they have a purpose, can I remove them etc Regards John Berman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug
From: your mail address To: [EMAIL PROTECTED] Subject: [50 character or so descriptive subject here (for reference)] Description: precise description of the problem (multiple lines) How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Synopsis: synopsis of the problem (one line) Submitter-Id: submitter ID Originator: your name Organization: your organization MySQL support: [none | licence | email support | extended email support ] Severity: [non-critical | serious | critical ] Priority: [low | medium | high ] Category: [mysqld, mysql client, C, PHP, PERL, ...] Class: [ sw-bug | doc-bug | change-request | support ] Release:mysql-3.23.38 Exectutable: [mysqld, mysqld-shareware, mysqld-nt or mysqld-opt] Environment: machine description System:Win95, Win98, NT or Win200 Compiler: VC++ 6.0 Architecture: i -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rekall and associated software
Hi It gives me great pleasure to inform you that Rekall, the cross-platform alternative to MS Access, is now totally GPL. So please visit http:// www.totalrekall.co.uk get youself a copy. In order for you to be able to download you must register with the Total Rekall Portal. The reason we have have made this a requirement is because we would like to be able to notify everybody when a new version becomes available. We are presently working on V2.2.0 and I am sure you would not like to miss out. You should also be aware that in order for for us to continue the future development of Rekall we will have to charge for technical support, customisation, training and consulting, therefore now that we have gone wholly GPL, you will have to take out a technical support subscription. I will post information relating to support levels and pricing once Mike and I have worked out the detail. In the meantime may I suggest that you sign up to our mailing list. Instructions can be found at the bottom of the home page -- Regards John http://www.totalrekall.co.uk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help on my query statement
* Admin-Stress I have a table called TRANSACTION, it has some fields : REGDATE - date of the transaction WHAT - a string (NOT UNIQUE) CUSTOMER - customer code I want to query all WHAT that the date of the transaction was 15 days before. So, my sql query is : SELECT WHAT FROM TRANSACTION WHERE REGDATE Now() -INTERVAL 15 DAY But, because of WHAT is not unique, then I could receive some duplicates (customer can send some WHAT in the same REGDATE). How can I query only the unique WHAT ? I need to list all WHAT (uniquely) that the date transaction was 15 days before. Looks like you need the DISTINCT keyword: URL: http://www.mysql.com/doc/en/Selecting_columns.html#IDX391 SELECT DISTINCT WHAT FROM TRANSACTION WHERE REGDATE Now() -INTERVAL 15 DAY -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data sincronization
* Gaston Escobar I need to sincronice two mysql databases that are in different places. It is imposible to centralice everything in one database. Then I would need to sincronice the changes made in both of them one time per day. Is there any way to do this? * Roger Baklund This very much depends on your application. Normally some changes needs to be done in the table definitions. Right, ids and foreign keys are an issue here. One way to get around this, is to include a new column in the table, called 'server' in this example. [...] I thought about this but I couldn't figure out how to manage foreign keys in an elegant way. AFAIK it's much more handy to have one single column primary key. A different approach could be to make sure server A only use id codes 10, and server B only use id codes 10 or similar. This would require you to make a guess on how many rows will be inserted on each server, and could introduce a problem in the future, when this limit is reached. Right, too. So, with an nonpermanent connection the design of such an application sucks. On the other hand one could use intervals for key assignment. Every time a server runs out of keys it throws a warning at the admin and rejects further inserts until it gets a new key interval. It's a way to go at least with a low number of servers. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help constructing query ...
* John Kelly
I have a table of full URLs and IPs and am using the following
query to return
distinct web requests by domain. Using SUBSTRING_INDEX it only returns the
domain part of the URL:
SELECT SUBSTRING_INDEX(url, '/', 3) as topsites, count(distinct
ip) as count
from tablename WHERE SUBSTRING_INDEX(url, '/', 3) LIKE
'%mydomain%' group by
topsites order by count
Example output:
topsitescount
http://www.mydomain.com5
http://mydomain.com 3
My question is how do I modify the query to get it to merge
requests for the
same domain by ignoring the www. so that the above would return:
http://mydomain.com 8
I think it has something to do with adding
REPLACE('url', 'www.', '')
but I can't figure out where to put it to make it work.
Try either
SUBSTRING_INDEX(REPLACE(url, 'www.', ''), '/', 3)
or
REPLACE(SUBSTRING_INDEX(url, '/', 3),'www.', '')
You don't need it in the WHERE clause, only in the field list and GROUP BY:
SELECT REPLACE(SUBSTRING_INDEX(url, '/', 3),'www.', '') as topsites,
count(distinct ip) as count from tablename
WHERE SUBSTRING_INDEX(url, '/', 3) LIKE '%mydomain%'
group by topsites order by count
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rekall and associated software go GPL
Hi It gives me great pleasure to inform you that Rekall, the cross-platform alternative to MS Access, is now totally GPL. So please visit http://www.totalrekall.co.uk and download download a copy for yourself. In order for you to be able to download you must register with the Total Rekall Portal. The reason we have have made this a requirement is because we would like to be able to notify everybody when a new version becomes available. We are presently working on V2.2.0 and I am sure you would not like to miss out. You should also be aware that in order for for us to continue the future development of Rekall we will have to charge for technical support, customisation, training and consulting, therefore, now that we have gone wholly GPL, you will have to take out a technical support subscription. I will post information relating to support levels and pricing once we worked out the detail. In the meantime may I suggest that you sign up to our mailing list. Instructions can be found at the bottom of the home page Regards John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
max_user_connections problem after upgrading
Hello all, We recently upgraded to 4.0, it went very well and the performance gains have been great. But now the server has started to act strangely. Every few days, the server starts to refuse connections, saying that there is already more than max_user_connections, but there is really only one or two active connections and our max_user_connections is 300. I have to take down and restart the server to solve the problem, and it keeps happening over and over again every few days... Am I the only one having this problem? Any suggestions? Regards, -- Henrik Skotth, Hogwarts.nu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data sincronization
* Andreas [...] On the other hand one could use intervals for key assignment. Every time a server runs out of keys it throws a warning at the admin and rejects further inserts until it gets a new key interval. It's a way to go at least with a low number of servers. Maybe even give a warning some time _before_ you run out of keys...? ;) You should never run out of keys. Every time you sync, you also check if there are many keys left to use... if you sync once a day, as soon as you have less than 10 times the expected daily usage of keys left to use, you request a new set of keys. You still would have to handle duplicates. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strategies for optimizing a read-only table
Hi Jeremy, Sorry, it seems like I'm saying this a lot lately. Is it not true that if the whole table will fit in [free] RAM, that the OS will cache the file data and there is no need for a RAM disk. I don't really see how performance would be any different than using a RAM disk. Either way, you will still have the overhead of the filesystem calls, even if data isn't actually read from disk, unlike with a HEAP table. Jonathan, I think a packed table would actually make things slower once the data is cached (overhead of unpacking the data). It's just faster when the data has to be *physically* read from disk, since the data is smaller. If disk space isn't an issue and the table doesn't have any TEXT/BLOB columns, make the rows fixed length if they aren't (change VARCHAR CHAR). This will give a small performance improvement. Also, if you don't have any TEXT/BLOB columns, you could load the data into a HEAP table, which *may* make reads *slightly* faster -- depending on your queries. Remember, with HEAP tables, indexes can't be used in all cases that MyISAM can. You can only search on indexes with =, =, IS NULL, and IN (); no range searches with , , BETWEEN, etc.; and you can only use the full index length, no prefixes. However, none of this is true in 4.1+, since you can have BTREE indexes with HEAP tables, not just HASH. :-) But you know what should actually be the best thing for your read-only table? MySQL 4's query cache! :-) Have you thought about this? Or do your queries differ too much that the cache can't be used? Hope this helps. Matt - Original Message - From: Jeremy Zawodny Sent: Saturday, November 08, 2003 11:48 PM Subject: Re: Strategies for optimizing a read-only table On Tue, Nov 04, 2003 at 08:45:08PM -0500, Jonathan Terhorst wrote: I could have sworn I posted this once before, but apparently it got lost somewhere. Apologies if you're seeing this twice: I'm wondering what I can do with MySQL to optimize reads (SELECTs) on a read-only table where data will never be INSERTed or UPDATEd. Okay, that's not entirely correct--the database will be rebuilt every night but it's small (~20,000 rows) and all the writing will take place at once, when the DB is offline to users. In contrast we anticipate read activity on the DB to be high, making it worth putting some thought into this. So far my only thoughts have been a) myisampack and b) to index every single column that our application searches on, since the calculations needed to build said indices can be performed once and forgotten. (Disk space isn't really an issue but myisampack is said to speed up individual row retrieval.) Any other ideas? I've searched for a way to manually mark MySQL tables read-only, but to no avail. Thanks, Will the whole table fit comfortable in RAM? If so, you could store it in a ram disk to prevent disk I/O from ever getting in the way. Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dupe killing (was: Data sincronization)
* Roger Maybe even give a warning some time _before_ you run out of keys...? ;) You should never run out of keys. Every time you sync, you also check if there are many keys left to use... if you sync once a day, as soon as you have less than 10 times the expected daily usage of keys left to use, you request a new set of keys. You are right. I caught that by using intervals of 10.000.000 We work still in the 5 digits area and only some history tables have 5 digit IDs at all. So there is plenty of IDs left. You still would have to handle duplicates. Thats an interesiting issue. We organise trade fairs and therefore I have to integrate lists of potential customers. Those are companies that attended other fairs and might be interested in ours, too. Or they have advertized somewhere and would fit into an event. The problem is that those entries might already exist but not exactly in the same spelling or adress. e.g. Smith Hats, Baker Street 1, London Hats Smith, Baker Street 1, London Smith Hats, Baker Str. 11, London/Soho John Smith Hats and Shoes, Baker Str. 10, London/Soho J. Smith Son, Baker 10, London It depends of the source where the adress comes from or if the company moved and we have allready the old address in the DB. Maybe we have the right address but in the source-list is an older obsolete address. Probaply the company is known to be closed but via the new list it gets reentered as active contact. And I see all kinds of misspellings, too. Is there a way to automize the dupe check ? I fear the day when I manually have to merge our second remote database into the main db. There we are talking of dupe killing in a pool of 3000 adresses that goes into another one with 7000. And I know there are a lot of dupes. :( ... Andreas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Got error 12 from table handler
On Sun, Nov 09, 2003 at 12:13:34PM +, george atef wrote: Hi, i got this message after querying the 3255 records table, and the query is like this: SELECT a.book_no, a.isbn_a, a.author_a, a.title_a, a.title_b, a.title_c, a.edition, a.place_publication_a, a.place_publication_b, a.place_publication_c, a.physical_desc_b, a.physical_desc_c, a.actal_price, a.currency_code, a.book_name, a.author_name, a.publ_name, a.subject_code, b.subject_desc FROM book_data a, subject b where a.subject_code=b.subject_code ORDER BY b.subject_desc, a.entry_date DESC it works fine locally on my win98 (PWS server) MySQL 4.0.15 but remotly on my FREEBSD server gives this error: query error: Got error 12 from table handler Any Suggesstions? [EMAIL PROTECTED]:~$ perror 12 Error code 12: Cannot allocate memory You have a memory shortage, it seems. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 57 days, processed 2,151,315,841 queries (435/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Complex query woes
I am hoping someone here might have an idea how to set up a complex query I have been trying to figure out. Basically I have a table that contains 3 types of records. Property managers, buildings and tenants. They are related upon insert by an ID and a PID (parent id). For example : ID PID CategoryName - 1 PM ABC Management 2 1 BldgGlen Heights 3 2 tenant Joe's salon Hopefully this shows how they are related. My goal is to ultimately on a Lasso (like php) page to render them like this : ABC Management Glen Heights Joe's salon Some other building Some other tenant I am able to handle the formatting fine the issue is how to get the data returned like this. I tried the following : select mgr.company, building.bldgname, tenant.company from customers as mgr,customers as building, customers as tenant where building.pid = mgr.id and tenant.pid = building.id order by mgr.company,building.bldgname,tenant.company; But it only returns 173 rows are there are 279. As you will see in http://phattwelve.hldns.com:90/workorder/findaccount2.lasso there are some property managers with buildings and no tenants, also property manager with no buildings. These get omitted by the above sql. Currently I am doing this with nested statements via lasso but is getting ridiculously slow on the live system as they add more and more clients. Is there any way around this with a single query instead of multiple queries in MySQL? Thanks Steffan --- T E L 6 0 2 . 5 7 9 . 4 2 3 0 | F A X 6 0 2 . 9 7 1 . 1 6 9 4 Steffan A. Cline [EMAIL PROTECTED] Phoenix, Az http://www.ExecuChoice.net USA AIM : SteffanC ICQ : 57234309 The Executive's Choice in Lasso driven Internet Applications Lasso Partner Alliance Member --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complex query woes
have you try left join? select mgr.company, building.bldgname, tenant.id from customers mgr left join customers building on building.pid=mgr.id left join costumers tenant on tenant.pid=building.id group by mgr.id, building.id, tenant.id order by mgr.company, building.bldgname, tenant.company hopefully it work :) -leo- - Original Message - From: Steffan A. Cline To: [EMAIL PROTECTED] Sent: Monday, November 10, 2003 12:56 PM Subject: Complex query woes Basically I have a table that contains 3 types of records. Property managers, buildings and tenants. They are related upon insert by an ID and a PID (parent id). For example : ID PID CategoryName - 1 PM ABC Management 2 1 BldgGlen Heights 3 2 tenant Joe's salon Hopefully this shows how they are related. My goal is to ultimately on a Lasso (like php) page to render them like this : ABC Management Glen Heights Joe's salon Some other building Some other tenant I am able to handle the formatting fine the issue is how to get the data returned like this. I tried the following : select mgr.company, building.bldgname, tenant.company from customers as mgr,customers as building, customers as tenant where building.pid = mgr.id and tenant.pid = building.id order by mgr.company,building.bldgname,tenant.company; But it only returns 173 rows are there are 279. As you will see in http://phattwelve.hldns.com:90/workorder/findaccount2.lasso there are some property managers with buildings and no tenants, also property manager with no buildings. These get omitted by the above sql. Currently I am doing this with nested statements via lasso but is getting ridiculously slow on the live system as they add more and more clients.
Want to monitor the replication of slave
Hello, mysql, I want to monitor the replication of slave. chk.sql content: show slave status $mysql -uroot -p chk.sql result But nothing happend. I want to know,how can I monitor the slave mysql. Any idea appreciated. Best regards. MaFai [EMAIL PROTECTED] 2003-11-10 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
