Optimizing IN queries?
I have an app that joins results from a MySQL query with the results of a lookup against an external search engine, which returns its results in the form of primary-key id's of one of the tables in my database. I handle this by adding these results with an IN query. (My impression had been that this is faster than a long chain of OR's.) In the simplest case, if I'm _only_ searching against these results, the query will look something like this (I've removed some columns from the SELECT list for readability): SELECT me.id, me.quotation_id, quotation.qt, part.id, part.cit FROM cwGroup me JOIN quotation ON ( quotation.id = me.quotation_id ) JOIN part ON ( part.id = quotation.part_id ) WHERE ( quotation.id IN (107037, 304650, 508795, 712723, 1054653)) When I ran this on a query that generated a moderate number of results (over 1000, but not millions), it took MySQL 26 seconds to reply on my dev box. Can someone suggest what I can look at to speed this up? The section of the manual that talked about optimizing range queries spent a lot of time explaining how they work but very little on how to speed them up. The EXPLAIN didn't really help--only one column got a lot of results, and it's not clear to me why MySQL would take 26 seconds to fetch 1214 records. The EXPLAIN looks like this: --- *** 1. row *** id: 1 select_type: SIMPLE table: me type: range possible_keys: quotation_id key: quotation_id key_len: 4 ref: NULL rows: 1214 Extra: Using where *** 2. row *** id: 1 select_type: SIMPLE table: quotation type: eq_ref possible_keys: PRIMARY,part_id key: PRIMARY key_len: 4 ref: rqs_incs.me.quotation_id rows: 1 Extra: *** 3. row *** id: 1 select_type: SIMPLE table: part type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: rqs_incs.quotation.part_id rows: 1 --- Thanks very much. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Optimizing range search with two-table ORDER BY
: 54745 Extra: Using where; Using temporary; Using filesort *** 2. row *** id: 1 select_type: SIMPLE table: quotation type: eq_ref possible_keys: PRIMARY,part_id key: PRIMARY key_len: 4 ref: rqs_incs.cwGroup.quotation_id rows: 1 Extra: *** 3. row *** id: 1 select_type: SIMPLE table: part type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: rqs_incs.quotation.part_id rows: 1 Extra: Other queries, as said, are more complicated, adding additional columns in the searches or joining in other tables (sometimes with range searches here as well), but these don't seem to affect the underlying problem. Adding multiple-column indexes also doesn't affect things in any significant way. Any thoughts? I clearly need a significant speed improvement, not just a tweak like making a bigger sort_buffer_size or getting faster disks. Thanks for reading this far. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query optimization help needed
I asked for help with a version of this query a few months
ago, and subsequently made some improvements to it, and also
fooled around with other parts of my app that were in need of
tuning. I've since done some more extensive benchmarking and
realized that this query really is somewhat slow. Even though
the data set is rather small and everything is (I think)
properly indexed and the joins are sensible, I can't seem to
get rid of the using temporary and using filesort in my
EXPLAIN. I'd be grateful for any suggestions for improving
this.
Here's the query (obviously I run it with different values for
subject.name and different LIMIT values, but this is
representative):
SELECT citation.*, DATE_FORMAT(citation.last_modified, '%e %M, %Y') AS
last_modified
FROM citation, subject, citation_subject
WHERE subject.name = 'History'
AND citation_subject.subject_id = subject.id
AND citation_subject.citation_id = citation.id
AND citation.deleted = 0
ORDER BY citation.stripped_word, FIELD(citation.part_of_speech, 'NOUN',
'ADJECTIVE', 'ADVERB', 'VERB'), citation.id
LIMIT 150, 50
and EXPLAIN gives me this:
*** 1. row ***
table: subject
type: ref
possible_keys: PRIMARY,name
key: name
key_len: 50
ref: const
rows: 1
Extra: Using where; Using temporary; Using filesort
*** 2. row ***
table: citation_subject
type: ref
possible_keys: citation_id,subject_id
key: subject_id
key_len: 4
ref: subject.id
rows: 169
Extra: Using index
*** 3. row ***
table: citation
type: eq_ref
possible_keys: PRIMARY,deleted
key: PRIMARY
key_len: 4
ref: citation_subject.citation_id
rows: 1
Extra: Using where
Finally, here are the three tables involved. I've trimmed out the
irrelevant columns:
CREATE TABLE `citation` (
`id` int(11) NOT NULL auto_increment,
`word` varchar(50) NOT NULL default '',
`stripped_word` varchar(50) default NULL,
`part_of_speech` enum('NOUN','VERB','ADJECTIVE','ADVERB') NOT NULL default
'NOUN',
`last_modified` timestamp(14) NOT NULL,
`deleted` datetime default '-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `deleted` (`deleted`),
KEY `word` (`word`),
KEY `stripped_word` (`stripped_word`)
) TYPE=MyISAM
CREATE TABLE `citation_subject` (
`id` int(11) NOT NULL auto_increment,
`citation_id` int(11) NOT NULL default '0',
`subject_id` int(11) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `citation_id` (`citation_id`,`subject_id`),
KEY `subject_id` (`subject_id`,`citation_id`)
) TYPE=MyISAM
CREATE TABLE `subject` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(50) NOT NULL default '',
`deleted` datetime default NULL,
PRIMARY KEY (`id`),
KEY `name` (`name`)
) TYPE=MyISAM
Thank you for any suggestions.
Jesse Sheidlower
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing own password on 3.x
I'm asking on behalf of someone; I don't have access to the
machine in question:
How do you change your own password in MySQL 3.23.58, without
access to the mysql table?
The user in question discovered that he was only able to change
the password from the particular machine he was logged in from,
not for 'user'@'%'.
I see that the docs say that the SET PASSWORD FOR format is
possible only for clients with access to the mysql table. But
when the user logged into machine 'server1' and issued SET
PASSWORD = PASSWORD('newpassw0rd'), he set the password for
[EMAIL PROTECTED] only, not for [EMAIL PROTECTED],
[EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED], etc.
Is there any way he can do this, or does he need an admin
to issue a SET PASSWORD FOR command?
Thanks. I didn't see this raised on the mailing lists
recently, or in Paul's book, but I'd think it would be
something people would want to do fairly commonly.
Jesse Sheidlower
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help optimizing query
I have what I thought was a simple, well-indexed query, but it turns out that it's acting as a pretty big drag. The one thing that's clearly a problem (though I'm not sure of the extent of the problem), I'm not sure how to fix. There are three tables: citations, subjects, and a many-to-many table linking these. They look like this (edited to remove extraneous fields): CREATE TABLE `citation` ( `id` int(11) NOT NULL auto_increment, `word` varchar(50) NOT NULL default '', `last_modified` timestamp(14) NOT NULL, `deleted` datetime default NULL, PRIMARY KEY (`id`), KEY `deleted` (`deleted`), KEY `word` (`word`) ) CREATE TABLE `subject` ( `id` int(11) NOT NULL auto_increment, `name` varchar(50) NOT NULL default '', `last_modified` timestamp(14) NOT NULL, PRIMARY KEY (`id`), KEY `name` (`name`) ) CREATE TABLE `citation_subject` ( `id` int(11) NOT NULL auto_increment, `citation_id` int(11) NOT NULL default '0', `subject_id` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `citation_id` (`citation_id`,`subject_id`) ) A usual query is to get (some number of) the citations for a given subject, ordering by the word which is stripped of spaces and hyphens. I don't know of any other way to accomplish this ordering. The EXPLAIN looks like this: mysql EXPLAIN SELECT citation.*, REPLACE(REPLACE(citation.word,' ',''), '-','') AS stripped_word - FROM citation, subject, citation_subject - WHERE subject.name = 'History' - AND citation_subject.subject_id = subject.id - AND citation_subject.citation_id = citation.id - AND (citation.deleted IS NULL OR citation.deleted = 0) - ORDER BY stripped_word\G *** 1. row *** table: subject type: ref possible_keys: PRIMARY,name key: name key_len: 50 ref: const rows: 1 Extra: Using where; Using temporary; Using filesort *** 2. row *** table: citation_subject type: index possible_keys: citation_id key: citation_id key_len: 8 ref: NULL rows: 1247 Extra: Using where; Using index *** 3. row *** table: citation type: eq_ref possible_keys: PRIMARY,deleted key: PRIMARY key_len: 4 ref: citation_subject.citation_id rows: 1 Extra: Using where 3 rows in set (0.00 sec) The number of records involved is relatively small (a few thousands; in practice this query would also have a LIMIT statement and would be preceded by a COUNT(*)), but it's dragging down the application it's running in. (I have a considerably more complex query that involves millions of records and twice as many joins, that is faster.) I'm running this in Perl. Any suggestions? I'd like to get rid of the whole temporary and filesort things, but I'm not sure if that's what matters given that there's only 1 row being returned there. Thanks. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help optimizing query
On Tue, Nov 23, 2004 at 09:55:15PM +0100, [EMAIL PROTECTED] wrote: * Jesse Sheidlower [...] CREATE TABLE `citation_subject` ( `id` int(11) NOT NULL auto_increment, `citation_id` int(11) NOT NULL default '0', `subject_id` int(11) NOT NULL default '0', PRIMARY KEY (`id`), KEY `citation_id` (`citation_id`,`subject_id`) ) Try adding an index with subject_id as the first column. ALTER TABLE `citation_subject` ADD INDEX (`subject_id`,`citation_id`); Thanks. This did help slightly--I didn't realize that the order of this would make such a difference, if both were always being used. I'm now coming to the determination that there are other parts of the application functioning as the biggest drags. If this is so, I apologize for the wasted bandwidth; I'm still poking at this query though. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Testing for the existence of an index
On Tue, Aug 24, 2004 at 11:57:05AM +0200, Thomas Spahni wrote: Jesse, mysql SHOW INDEX FROM mytable; gives you all indexes for `mytable`; you can process the results with perl. Thanks very much. This works fine, and since I don't care about the return value--just that there is one--it becomes trivial. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Testing for the existence of an index
I have a database where, most of the time, I'm bulk-loading data into new tables from an external source, several million rows at a time. For this purpose, I create the tables without indexes, and then add all my indexes after the load is done, for efficiency purposes. I'd now like to add the possibility of adding some data to the existing database. In this case, the indexes exist, and then the new data will be indexed as it goes in (which is OK given the relatively small amount of data to be processed this way). I'd like to be able to test for the existence of an index, so that after the bulk-load, I can see if there are indexes, there won't be any, and I can create them; but after an addition to an existing database, there will be indexes, and I won't create them. How do I do this? It wasn't clear from the manual, and I'm away from my books now so can't look there for advice. I'm using Perl to process the data, if there's a Perlish way of doing things that would be easier than SQL. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Testing for the existence of an index
On Mon, Aug 23, 2004 at 03:45:28PM -0500, Victor Pendleton wrote: Why don't you disable the indexes before the load and enable the indexes after the data load? If I'm bulk-loading a fresh install of data, then I'll be using un-indexed tables and index them afterwards. Otherwise, I want to keep the indexes on the table, and index as I go along (so that when I'm done I don't have to re-index from scratch). In response to the other poster, who suggested opening the table up in some utility: it's not a problem for _me_ to determine if I'm bulk-loading a new batch, or adding to an existing database. I want my loading program to be able to determine this without any input from me. Thanks. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Benchmark. OT and beyond...
On Thu, May 27, 2004 at 02:20:46PM -0400, RV Tec wrote: Eric, I am sure all FreeBSD users are sick and tired of saying this. USE 4.10 Lots of people blindly follow version numbers but 5.x is a lot different than 4.x in ways I don't yet feel comfortable with using on production machines. Only one of those issues is with benchmark numbers, but that is certainly one of them. If you don't feel comfortable with 5_branch on production, that's fine, I respect your choice. On the other hand, we have lots of official documentation saying that LinuxThreads, threading, memory handling, disk access is improved in 5_branch (compared to 4). I'm looking for an OS that can handle my database. OpenBSD -- which, I just proved by a test machine -- performs better than FreeBSD. That's why I can't even imagine how poorly FreeBSD 4.10 (which has just been released) would handle my database. Probably much better. FreeBSD 5.x does a lot of things really well, and it can be very fast too, but there's no stable release of 5.x yet, and the 5.x code is clearly hampered speedwise by the presence of loads of debugging code. From the 5.x documentation: --- NOTE TO PEOPLE WHO THINK THAT FreeBSD 5.x IS SLOW: FreeBSD 5.x has many debugging features turned on, in both the kernel and userland. These features attempt to detect incorrect use of system primitives, and encourage loud failure through extra sanity checking and fail stop semantics. They also substantially impact system performance. If you want to do performance measurement, benchmarking, and optimization, you'll want to turn them off. This includes various WITNESS- related kernel options, INVARIANTS, malloc debugging flags in userland, and various verbose features in the kernel. Many developers choose to disable these features on build machines to maximize performance. --- If Linux is really twice as fast as FreeBSD, as people have reported recently, then go ahead and use it if that's what you want. But I hope everyone reporting this is using optimized software, and isn't complaining about a FreeBSD that's explicitly slowed down by running under gobs of debugging code. So I would imagine you'd see a huge speedbump from downgrading to 4.10. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Page Numbers
On Wed, Nov 12, 2003 at 08:10:58PM -0600, Mike Blezien wrote: Hello, we've set up many displays using the Previous and Next simple linking set up of search results. But now would like to implement the page number style, IE. Previous [1] [2] [3] Next style format and was hoping someone may have a sample/example queries to accomplish this. Usually working with 10 results per page display. Paul DuBois provides easy-to-understand code for this exact format in _MySQL and Perl for the Web,_ and his _MySQL Cookbook._ Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unusual date storage requirement
On Mon, Oct 27, 2003 at 05:32:34PM -0500, sean peters wrote: Hi all, I have run into a date storage problem that i don't like. A am storing historic house sales, and some of the old data i have received only contains the month and year, but not the date of the sale. Of course we want to store this information, but a DATE column won't quite do the job, because year, month, and day are all required. So, my homecooked solution is to use a DATE column, and another column as a flag to denote whether the day-of-month is valid. Then i'll need to properly craft my searches to understand this. Why not just set the day value to '00' if you don't have a value, and then check that in your client code? That way, no extra columns. I.e. if you don't have a day value, then your DATE will be $sale-date-year . - . $sale-date-month . -00, or whatever. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why change in CONCAT_WS
On Sun, Oct 26, 2003 at 10:05:07AM -0500, Will French wrote: Are there others like me who would like to preserve the empty string skipping version of CONCAT_WS. Perhaps the new version of the function could be added under a new name like CONCAT_WS_NOSKIP or perhaps the option to skip blanks could be provided in an argument. Just curious what people think. I agree absolutely. I was thrilled to discover CONCAT_WS and now use it all the time in ways very similar to how you do--joining lists of things that may or may not have values. It would be very irritating to have to rewrite this functionality within MySQL, and tedious to have to do it in the relevant programming language instead. Jesse Sheidlower [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re-establishing nuked log file
I recently restarted my MySQL server (4.0.10 in this case) with the general query log enabled, to help out with some debugging and optimization issues. After looking at a batch of these, I then deleted the log file directly, with rm foo.log, assuming that it would be re-generated as soon as the next query came in. It was not. Is there any way to get logging restarted without stopping and restarting the server itself, which is live and which I'd prefer not to interrupt? Thanks. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re-establishing nuked log file
On Tue, Sep 02, 2003 at 07:59:07AM -0700, Bruce Ferrell wrote: flush logs from the mysql command line works And so it does. Thank you. I misunderstood what the Manual said about this command, though I should have tried it first anyway. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Explanation of multiple-column indexes
On Tue, Aug 26, 2003 at 09:26:55PM -0500, Matt W wrote: Hi Jesse, - Original Message - From: Jesse Sheidlower What I'm trying to understand is how you would set up these indexes when you'd always be doing joins with another table. Suppose you have The Canonical CD Database, and you have a table songs with fields song_id, album_id, song_title, and song_length. Suppose you're often doing searches of song_title or (for some reason) song_length, and that any time you'd do such a search, you'd _always_ be joining it to the album table. It would seem that you'd want at least two multiple-indexes in the song table, one of them including song_title and album_id, the other including song_length and album_id. Is this correct? Do you need song_id (which would be a primary key on that table) in there too? What order should the indexes be in? You wouldn't necessarily want indexes on (song_title, album_id) -- in that order -- and/or (song_length, album_id). This reason for this is because if any other columns from the song table are involved in the query (in the select list or in the WHERE), MySQL will have to hit the data file for those columns anyway, and album_id as the second column in the index won't be used -- just the first column -- song_title or song_length -- if you're searching on them. However, if only the 2 columns in the index are involved in the query (searching on title or length and join with album_id), then having album_id in the index would be benficial because no seek to the data file is needed. This can be verified by seeing if EXPLAIN says Using index for the song table. [...] If every search for song_title or song_length must be joined against the album table, it's not clear which should be the first named column in this index. The experiments I've done so far have been inconclusive, and I don't think I'm understanding the process in the first place. The indexes would be: (song_title, album_id) (song_length, album_id) If the order was reversed (e.g. album_id was first in the index), the index couldn't be used for searching. And like I said above, if other columns besides the 2 in the index are involved in the query, album_id isn't used anyway. In that case, just index title and length separately for searching: (song_title) (song_length) Of course, if you included ALL columns that will be used in queries in each index (with title or length as the first column in each), then it wouldn't have to go to the data file and EXPLAIN would say Using index. But this doesn't usually give much speed improvement and is a waste of space. Just letting you know. :-) Well, if speed rather than space is my main concern, _and_ I can't predict what the searches will be--i.e. it's quite possible that some searches will be only song_title, some will be only song_length, and some will be both, and some will involve other combinations of fields not shown in this made-up example--then what? Do I have several multiple-column indexes, each with (song_title, album_id) or whatever for each field, along with single-column indexes for everything (even though every query would be joined on album_id)? Or do I need to have three- or more-column indexes for all the potential groups that might be searched? (song_title, song_length, album_id), (song_title, song_something_else, album_id), etc.? Thanks very much. Best, Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Explanation of multiple-column indexes
After some discussion in a separate thread, I've been trying to get a better understanding of the workings of multiple-column indexes, and think I'm still missing the point. I understand indexing (last_name, first_name) in that order if you'd always be searching last names and only be searching first names in combination. What I'm trying to understand is how you would set up these indexes when you'd always be doing joins with another table. Suppose you have The Canonical CD Database, and you have a table songs with fields song_id, album_id, song_title, and song_length. Suppose you're often doing searches of song_title or (for some reason) song_length, and that any time you'd do such a search, you'd _always_ be joining it to the album table. It would seem that you'd want at least two multiple-indexes in the song table, one of them including song_title and album_id, the other including song_length and album_id. Is this correct? Do you need song_id (which would be a primary key on that table) in there too? What order should the indexes be in? If every search for song_title or song_length must be joined against the album table, it's not clear which should be the first named column in this index. The experiments I've done so far have been inconclusive, and I don't think I'm understanding the process in the first place. Thanks very much. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow results with simple, well-indexed query
On Fri, Aug 22, 2003 at 10:23:37AM -0400, Keith C. Ivey wrote: On 21 Aug 2003 at 21:38, Jesse Sheidlower wrote: Huh, I was told the exact opposite, that if most of the entries are smaller than the maximum length of the field, you should use an index about the size you expect most entries to be. Why would you ever use a shorter index than the full column length if it led to such performance degradation? It depends on the query (as well as your data). In your case, the only column you're selecting is cw, so if all of cw is in the index MySQL can use the index alone and never has to look at the data file. That speeds things up quite a bit. If you were selecting multiple columns the difference might not be so great. Another point is that you're sorting by cw, and a prefix-based index won't allow you to sort completely. If your queries were mainly selecting by cw rather than sorting by it, a prefix-based index should be fine. Hmm. This is a single case; as mentioned in other queries one might be selecting by other values, and I'd usually be selecting multiple column. The sort, however, is always done either on cg.cw, or on cit.d (which I may not have shown), a date field. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow results with simple, well-indexed query
On Fri, Aug 22, 2003 at 07:33:56AM -0700, Steven Roussey wrote: Here's the CREATEs, somewhat edited to remove parts not relevant to this discussion, to save space: I never actually looked at your JOIN statement more than a quick glimpse, but I will (though not just right now). Before I do, can you try this (I still don't have data or I'd play with it myself: mysql EXPLAIN SELECT cg.cw FROM cg USE INDEX(q_id_2),q,cit,sref - WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id - AND cg.cw LIKE 't%' - AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) - ORDER BY cg.cw - LIMIT 1000,10; The actual SELECT wasn't really any faster; the first run was 4.05 sec (OK, a lot faster than the 1 m 15 sec it had been taking) but the second run was 3.66 sec, compared to the 3.5 sec or so it had been taking. Here's the EXPLAIN: mysql EXPLAIN SELECT cg.cw FROM cg USE INDEX(q_id_2),q,cit,sref - WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id - AND cg.cw LIKE 't%' - AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) - ORDER BY cg.cw - LIMIT 1000,10\G *** 1. row *** table: sref type: range possible_keys: PRIMARY,cd key: cd key_len: 4 ref: NULL rows: 3102 Extra: Using where; Using temporary; Using filesort *** 2. row *** table: cit type: ref possible_keys: PRIMARY,sref_id key: sref_id key_len: 4 ref: sref.id rows: 3 Extra: ** 3. row *** table: q type: ref possible_keys: PRIMARY,cit_id key: cit_id key_len: 4 ref: cit.id rows: 31 Extra: *** 4. row *** table: cg type: ref possible_keys: q_id_2 key: q_id_2 key_len: 4 ref: q.id rows: 1 Extra: Using where; Using index 4 rows in set (0.00 sec) Best, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow results with simple, well-indexed query
On Fri, Aug 22, 2003 at 09:03:55AM -0700, Steven Roussey wrote: All the indexes were single indexes, partly because I haven't yet made the effort to understand composite index. I guess it's time ;-). Oh. There are better places to start than this list. ;) The manual can be a great starting place, and several people on this list have written books about MySQL which are great for getting started. You can look at Amazon.com, etc. No, I have them all, I just...I guess haven't gotten around to them yet. I mistakenly figured that individually indexing everything in sight would do the trick. mysql EXPLAIN SELECT cg.cw FROM cg,q,cit,sref - WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id - AND cg.cw LIKE 't%' - AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) - ORDER BY cg.cw - LIMIT 1000,10; You do a range on the fist and last table in the chain. :( It is best to normalize your table structure. I should have noticed that upfront. The 'simple, well-indexed query' through me off. Normalizing is a great thing to learn, and probably the first thing to understand after how to do a SELECT and composite indexes. I wonder if anyone else is still reading :-/. While I don't claim to be an expert by any means, and while it may not look like it, this database _is_ highly normalized, or at least I think it is; I put a lot of effort into making it so. Let me just explain what it all does. The database holds lexical data for use in linguistic analysis. For the tables we're looking at now, it works like this: srefcontains the bibliographic details for a single physical book or other text, along with some metadata about when the text was keyed, who read it, etc. cit contains the bibliographic details for a quotation taken from a text; there might be only one cit per sref if these details don't change (e.g. a novel), or there might be many cits per sref if they do (e.g. a collection of essays, each one written by a different author at a different date). q contains a single quotation. cg contains information about specific words. I believe this is normalized because each element appears only once and is linked to others as necessary, so that, for example, a single quotation reading This is a foobarred, conformiferous structiform is stored once in the database, and foobarred, conformiferous, and structiform are each stored independently, joined to the q table on the relevant id fields. So if you had the word structiform and wanted to see the quotation, you could retrive it by the q_id that's stored in cg. It's the same for the other elements; there's only one sref section per text, so if you're at any other point and want to get the date the text was read, you have to join your way up to sref to retrieve sref.cd. Though I haven't shown them yet, there are also some other tables, holding authors at the sref level, subjects at the sref level, and authors at the cit level; for any of these, there can be any number of elements (i.e. no subjects, one subject, ten subjects etc.), so I normalized them by putting them in to separate tables linked to sref.id or cit.id etc. as appropriate. So for the queries we've been discussing, I'm looking for all the words beginning with t that have been keyed in the last six months; the reason I'm getting from the first and last table in the chain should now be obvious. In reality, I wouldn't just be retrieving the word (the cg.cw), I'd be retrieving both the quote and bibliographic info in cit, and the (unshown) author related to cit. Other queries can involve any combination of these factors--searching based on the fulltext content of a q, on an author, on a work title (perhaps not shown), on subjects, and so on and so on. I have created (individual) indexes on all the id fields, and on any value used in a search. In almost, if not every case, a search will involve a particular value used for the search as well as the id fields of relevant tables; what that means for multiple queries, I'm now not sure. I'll stop there and try to answer the suggestions in your other message. Thanks again for all the time you've been spending. Best, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow results with simple, well-indexed query
On Fri, Aug 22, 2003 at 12:42:27PM -0700, Steven Roussey wrote: But since this data is read only, why not reformulate the data for the queries you are going to make? This is the opposite of normalizing, and will require more disk space, and is not flexible, but it will be fast. Of course, it depends on what you are doing and how many types of queries you have. This 'normalize by queries' or what I refer to as 'selective denormalization' likely won't appear in any books. Hmm, I guess I expected it to be faster normalized in any case, and that I shouldn't waste all that effort I put in to normalizing it properly. I thought I was learning something :-|. I'll see if I can experiment and get a sense of the difference it will make. PS: Get a big fast SCSI RAID 10 array of 15K drives (stripe the mirrors). Actually, since this data is readonly and copies are stored elsewhere, you could get by with RAID 1. I'm guessing you are being held back by the disk, or your memory buffers. In fact, it's already running on a RAID 1 array of 15K SCSI drives. I wanted to set it up to work well with this data set. If you want fun, I could show you the numbers I get running the queries on my development laptop. Oh, and just for fun: # order the files ALTER TABLE cg ORDER BY dir1; [etc.] I had to substitute the values of dir1 in these queries, but then: # Get a new result for direction 1 SELECT STRAIGHT_JOIN cg.cw FROM cg USE INDEX(dir1), q USE INDEX(dir1), cit USE INDEX(dir1), sref USE INDEX(dir1) WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id AND cg.cw LIKE 't%' AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) ORDER BY cg.cw LIMIT 1000,10; This took 8.18 sec first, 2.29 sec immediately thereafter. # order the files ALTER TABLE cg ORDER BY dir2; [etc.] # Get a new result for direction 2 SELECT STRAIGHT_JOIN cg.cw FROM sref USE INDEX(dir2), cit USE INDEX(dir2), q USE INDEX(dir2), cg USE INDEX(dir2) WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id AND cg.cw LIKE 't%' AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) ORDER BY cg.cw LIMIT 1000,10; This took 3.05 sec first, and 2.96 sec immediately thereafter. Best, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow results with simple, well-indexed query
I'm struggling with speed issues on some queries that I would have expected to be relatively fast. Perhaps even more frustratingly, when I've tried to break these down into their components, they still execute very slowly. I've looked over all the relevant suggestions for optimization and so forth, and there's nothing I can tell that I'm missing. An example of a query is to get all the words (the cg.cw field) in a particular alphabetical range that have been added in some timespan (the sref.cd field). The cg table has about 3M rows, and the sref table about 70,000; the intervening tables are all indexed on the relevant id fields: - mysql SELECT cg.cw FROM cg,q,cit,sref - WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id - AND cg.cw BETWEEN 't' AND 'tzzz' - AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) - ORDER BY cg.cw - LIMIT 1000,10; +---+ | cw| +---+ | teeny-pop | | teeter| | teetery | | teeth-grating | | Teflon| | teflon| | teflon| | teflon| | teflubenzuron | | Tejano| +---+ 10 rows in set (7.30 sec) - That's just too slow; yet an EXPLAIN doesn't make things easy for me to see what's wrong: - mysql EXPLAIN SELECT cg.cw FROM cg,q,cit,sref - WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id - AND cg.cw BETWEEN 't' AND 'tzzz' - AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) - ORDER BY cg.cw - LIMIT 1000,10\G *** 1. row *** table: cg type: range possible_keys: q_id,cw key: cw key_len: 26 ref: NULL rows: 170982 Extra: Using where; Using filesort *** 2. row *** table: q type: eq_ref possible_keys: PRIMARY,cit_id key: PRIMARY key_len: 4 ref: cg.q_id rows: 1 Extra: *** 3. row *** table: cit type: eq_ref possible_keys: PRIMARY,sref_id key: PRIMARY key_len: 4 ref: q.cit_id rows: 1 Extra: *** 4. row *** table: sref type: eq_ref possible_keys: PRIMARY,cd key: PRIMARY key_len: 4 ref: cit.sref_id rows: 1 Extra: Using where 4 rows in set (0.00 sec) - Executing just the search on the word table, with no joins to the table with the dates, is still slow: - mysql SELECT cw - FROM cg - WHERE cw BETWEEN 's' AND 'szzz' - ORDER BY cw - LIMIT 3000,5; +-+ | cw | +-+ | sacrifice hit | | sacrifice play | | sacrifice the earth | | sacrifice throw | | sacrifice to| +-+ 5 rows in set (5.80 sec) - and has a similar EXPLAIN: - mysql EXPLAIN SELECT cw FROM cg WHERE cw BETWEEN 's' AND 'szzz' ORDER BY cw LIMIT 3000,5\G *** 1. row *** table: cg type: range possible_keys: cw key: cw key_len: 26 ref: NULL rows: 318244 Extra: Using where; Using filesort 1 row in set (0.00 sec) - Of course cw is indexed. Is there anything I can to do improve queries of this nature? There are more complicated queries from this database, but the big slowdown always seems to be when one of the possibilities (e.g. all words in 'S') is large; the other limitations don't improve things. Thanks. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow results with simple, well-indexed query
On Thu, Aug 21, 2003 at 11:34:00AM -0400, Jesse Sheidlower wrote: I'm struggling with speed issues on some queries that I would have expected to be relatively fast. Perhaps even more frustratingly, when I've tried to break these down into their components, they still execute very slowly. I've looked over all the relevant suggestions for optimization and so forth, and there's nothing I can tell that I'm missing. An example of a query is to get all the words (the cg.cw field) in a particular alphabetical range that have been added in some timespan (the sref.cd field). The cg table has about 3M rows, and the sref table about 70,000; the intervening tables are all indexed on the relevant id fields: Sorry, I should have added that these queries are being run on MySQL 4.0.10 on a lightly loaded PIII 1.4Ghz with 1GB RAM and fast SCSI drives. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow results with simple, well-indexed query
On Thu, Aug 21, 2003 at 05:59:54PM +0200, Mechain Marc wrote: What is the value of sort_buffer_size, may be you could increase the value for having faster ORDER BY (all in memory intead of using temporary file on disk). I had previously tried that--I sometimes have big GROUP BY queries as well--so the sort_buffer_size is now 8M; though I don't usually have many simultaneous users, I'm still nervous about making it much larger than that. Best, Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow results with simple, well-indexed query
On Thu, Aug 21, 2003 at 06:01:31PM +0200, Cybot wrote: Jesse Sheidlower wrote: I'm struggling with speed issues on some queries that I would have expected to be relatively fast. Perhaps even more frustratingly, when I've tried to break these down into their components, they still execute very slowly. I've looked over all the relevant suggestions for optimization and so forth, and there's nothing I can tell that I'm missing. An example of a query is to get all the words (the cg.cw field) in a particular alphabetical range that have been added in some timespan (the sref.cd field). The cg table has about 3M rows, and the sref table about 70,000; the intervening tables are all indexed on the relevant id fields: - mysql SELECT cg.cw FROM cg,q,cit,sref - WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id - AND cg.cw BETWEEN 't' AND 'tzzz' - AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) move your DATE before cw AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND cg.cw BETWEEN 't' AND 'tzzz' cause sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) should be faster than cg.cw BETWEEN 't' AND 'tzzz' and so the total rows are already limited when cg.cw BETWEEN 't' AND 'tzzz' will be executed I assume that the optimizer would take care of this, but in any case I gave it a try and it made no difference. also you can try an index with a length of 2 or 3 over cg.cw, this will result in smaller index and possible speed up things I also tried this (the current index is 25 characters on a 100-character field), and if anything it made things slower. Anyone have any other ideas or analysis? Thanks very much. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow results with simple, well-indexed query
On Thu, Aug 21, 2003 at 03:36:54PM -0700, Steven Roussey wrote: Executing just the search on the word table, with no joins to the table with the dates, is still slow: Then it is not worth while to focus on anything else until you fix that. Are the contents of this field always in lower case? Is so, then change the column to a binary type. The explain says: rows: 318244 Extra: Using where; Using filesort That means that is sorting all 318,244 (est) records first, then going down to the 3000th and giving you five records. Just a guess. See if that helps then we can move on to the join. No, the contents can be of mixed case. Where does that leave things? In a working environment I'd never be querying on this table alone, it would always be joined in to other tables that would limit things in some way, but these don't seem to be affecting things. The suggestions other people have made to try to get it to do the smaller queries first don't seem to be having much effect, unfortunately. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow results with simple, well-indexed query
On Thu, Aug 21, 2003 at 04:58:47PM -0700, Steven Roussey wrote: No, the contents can be of mixed case. Where does that leave things? **Index the length of the entire column.** It then should not need to have to do the filesort. Actually the binary option would not have really helped. The explain should say 'Using Index'. Get back to me on this and tell me the results. Huh, I was told the exact opposite, that if most of the entries are smaller than the maximum length of the field, you should use an index about the size you expect most entries to be. Why would you ever use a shorter index than the full column length if it led to such performance degradation? In any case, I reindexed cg.cw to the length of the entire column, and the result is... GOD! OK, sorry, I wasn't quite expecting this: mysql SELECT cw FROM cg WHERE cw BETWEEN 's' AND 'szzz' ORDER BY cw LIMIT 3000,5; +-+ | cw | +-+ | sacrifice hit | | sacrifice play | | sacrifice the earth | | sacrifice throw | | sacrifice to| +-+ 5 rows in set (0.02 sec) Wow! But what's the explanation for this huge improvement? Again, I was always told the opposite, and the Manual itself says: If it's very likely that a column has a unique prefix on the first number of characters, it's better to only index this prefix. MySQL supports an index on a part of a character column. Shorter indexes are faster not only because they take less disk space but also because they will give you more hits in the index cache and thus fewer disk seeks. (At sec. 5.4.2.) In a working environment I'd never be querying on this table alone, it would always be joined in to other tables that would limit things in some way, but these don't seem to be affecting things. The suggestions other people have made to try to get it to do the smaller queries first don't seem to be having much effect, unfortunately. Optimize the join once you know how to optimize its parts. One thing at a time. Hmm. When I returned to the multiple-table query that started this thread, but with the full-column index, it took a staggering 1m 15s; rerunning it speeded it up to 3.51 sec (the original was 7.30 sec), but still nothing like the improvement that the single table change made just above. The explain looks like this: mysql EXPLAIN SELECT cg.cw FROM cg,q,cit,sref - WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id - AND cg.cw BETWEEN 't' AND 'tzzz' - AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) - ORDER BY cg.cw - LIMIT 1000,10; +---++-+-+-+-++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++-+-+-+-++-+ | cg| range | q_id,cw | cw | 101 | NULL| 190550 | Using where | | q | eq_ref | PRIMARY,cit_id | PRIMARY | 4 | cg.q_id | 1 | | | cit | eq_ref | PRIMARY,sref_id | PRIMARY | 4 | q.cit_id| 1 | | | sref | eq_ref | PRIMARY,cd | PRIMARY | 4 | cit.sref_id | 1 | Using where | +---++-+-+-+-++-+ 4 rows in set (0.00 sec) Where do I go from here? And thanks for all the thought people have been putting into this. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow results with simple, well-indexed query
On Thu, Aug 21, 2003 at 06:58:29PM -0700, Steven Roussey wrote:
Jesse Sheidlower wrote:
Hmm. When I returned to the multiple-table query that started
this thread,
And it was slow. Yeah, one thing at a time. It makes it easier for
people reading this list now or in the future (if it comes up in a
search result) if we go over things one item at a time.
Since I never saw the whole table definitions (the indexes in
particular), I'll have to try and guess through it. So try this:
All the indexes were single indexes, partly because I haven't
yet made the effort to understand composite index. I guess it's
time ;-).
Here's the CREATEs, somewhat edited to remove parts not relevant
to this discussion, to save space:
CREATE TABLE `cg` (
`q_id` int(10) unsigned NOT NULL default '0',
`cw` varchar(100) default NULL,
`exp` text,
KEY `q_id` (`q_id`),
KEY `cw` (`cw`),
KEY `q_id_2` (`q_id`,`cw`),
FULLTEXT KEY `exp` (`exp`)
) TYPE=MyISAM
CREATE TABLE `q` (
`id` int(10) unsigned NOT NULL default '0',
`cit_id` int(10) unsigned NOT NULL default '0',
`q_tag` enum('q','qau','qca','qna','qsa') default NULL,
`qt` text,
PRIMARY KEY (`id`),
KEY `cit_id` (`cit_id`),
FULLTEXT KEY `qt` (`qt`)
) TYPE=MyISAM
CREATE TABLE `cit` (
`id` int(10) unsigned NOT NULL default '0',
`sref_id` int(10) unsigned NOT NULL default '0',
`w` varchar(200) default NULL,
PRIMARY KEY (`id`),
KEY `sref_id` (`sref_id`),
FULLTEXT KEY `w` (`w`),
) TYPE=MyISAM
CREATE TABLE `sref` (
`id` int(10) unsigned NOT NULL default '0',
`rdr` varchar(30) default NULL,
`kbd` varchar(20) default NULL,
`cd` date default NULL,
PRIMARY KEY (`id`),
KEY `rdr` (`rdr`),
KEY `kbd` (`kbd`),
KEY `cd` (`cd`)
) TYPE=MyISAM
ALTER TABLE cg add index(q_id,cw);
I did this, as is reflected in the CREATE above.
Tell me how that works and send the EXPLAIN.
Unfortunately, it made no difference--the first execution was
about 1 m 15 sec, and one immediately thereafter was about 3.5 sec,
as before. The EXPLAIN shows:
mysql EXPLAIN SELECT cg.cw FROM cg,q,cit,sref
- WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id
- AND cg.cw LIKE 't%'
- AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
- ORDER BY cg.cw
- LIMIT 1000,10;
+---++-+-+-+-++-+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+---++-+-+-+-++-+
| cg| range | q_id,cw,q_id_2 | cw | 101 | NULL| 190550 | Using
where |
| q | eq_ref | PRIMARY,cit_id | PRIMARY | 4 | cg.q_id | 1 |
|
| cit | eq_ref | PRIMARY,sref_id | PRIMARY | 4 | q.cit_id| 1 |
|
| sref | eq_ref | PRIMARY,cd | PRIMARY | 4 | cit.sref_id | 1 | Using
where |
+---++-+-+-+-++-+
The point here is that now you are doing a join and you are using both
columns to qualify the resultset. So we should use a composite index
rather than have individual ones (of which MySQL will choose only one).
What does this mean for regular searching? In most cases, there will be
some criteria entered that need to be searched on, and the id fields
will also be needed for the joins. For example, in the database, one
might want to search based on cg.exp (fulltext), sref.rdr, sref.cd
(the date field), sref.kbd, cit.w, and various other ones I've edited
out of this display to save space, and often a combination of several
of these at once. How should I set up indexes for the potential
searches that might be executed?
(I should mention that this is a read-only database; it's built from
a parsed SGML file and is never added to directly, if that's an
issue.)
Also, you can change line
AND cg.cw BETWEEN 't' AND 'tzzz'
To
AND cg.cw like 't%'
For better readability (how many zzz's are enough, eh?). Personal
preference.
No, I agree, and it was originally LIKE 't%' and is still like
that in the actual code being generated by the query form. I changed
it to the BETWEEN because in some playing around it seemed to be
faster this way, and in fact I was worried about having to
generate the
BETWEEN \'$val\' AND \' . $val . \'
thing in my program. If this was a glitch of my badly-indexed
original, I'm glad to fix it.
Thanks again for taking the time to look this over.
Best,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Terrifyingly different results on different 4.0.X versions
Last night I was looking over one of my applications on my development box, and realized that the results I was getting were rather different from what I had expected. It was still working fine on my working server. I copied over the current version of the tables to the dev box, and made sure that the scripts were identical, which they were, but I was still getting different results. Then I started to play around with the SQL, and discovered that even with identical data and identical queries, I was getting different results. I'm at a loss to explain this and would greatly appreciate any help figuring out what's going on. My working server is running MySQL 4.0.10, and the development server is running 4.0.13, both on FreeBSD 4.8-STABLE. I have some configuration differences but nothing that I think could affect this. I have a database of wine, and a relevant part is that there is a table wine holding info about what was purchased, and a table deaccession_event holding info about wine that I have sold or drunk, with a field deac_quantity having the number of wines drunk/sold on any particular occasion. I execute the following query: SELECT wine.id, wine.orig_qty_purch - SUM(deaccession_event.deac_quantity) AS number_left FROM wine LEFT JOIN deaccession_event ON wine.id = deaccession_event.wine_id GROUP BY wine.id ORDER BY wine.id; on my working server (4.0.10-gamma) I get: +-+-+ | id | number_left | +-+-+ | 1 | 0 | | 2 | 1 | | 3 | 1 | | 4 | 1 | [...] | 103 | 2 | | 104 | 1 | | 105 | 4 | | 106 | 4 | | 107 | 0 | | 108 | 4 | | 109 | 0 | | 110 | 0 | | 111 | 0 | | 112 | 0 | | 113 | 0 | +-+-+ and on my development server (4.0.13) I get: +-+-+ | id | number_left | +-+-+ | 1 | 0 | | 2 |NULL | | 3 |NULL | | 4 |NULL | [...] | 103 | 2 | | 104 | 1 | | 105 |NULL | | 106 |NULL | | 107 | 0 | | 108 | 4 | | 109 | 0 | | 110 | 0 | | 111 | 0 | | 112 | 0 | | 113 | 0 | +-+-+ I'm sure that these are working on identical tables and that the queries are identical. This is just a sample, and more complex queries end up with more thoroughly erroneous results. Any explanation for these results? Thanks in advance. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/Perl code reuse advice
I apologize in advance for what I realize is a rather broad question, and one that is mostly Perl and not MySQL. I've been writing Perl scripts for using MySQL databases on the Web. Paul DuBois' _MySQL and Perl for the Web_ has been enormously helpful to me, and most of my programs are based on things from this book. I'm at the point though where I have a number of applications that share large blocks of similar code, and I could use some advice on how to modularize this so I can avoid the huge hassles every time I make a change and then have to tweak a dozen similar applications. In some cases I have extremely similar structures--three separate libraries that have slightly different fields or field-names--and in other cases the designs are rather different. I've done what I can to write functional interfaces for very straightforward cases, so that the connection is handled by a function that is passed configuration info from a file accessed by Config::General, for example. I assume that I should start writing some OO modules, but I'm a little concerned since I've used, but never written, much OO code. I've looked at some of the CPAN modules, but nothing seems to be what I need; they're generally too fancy or designed to work with some more elaborate system. Some examples of the things that are giving me headaches: I'd like to be able to search across the three libraries in some easy manner; currently I've ended up just effectively rewriting the search routine for a fourth time, UNIONing the results. I have a pager routine in every program, that's identical but for a list of search parameters that can be passed for each page. It's annoying enough that I have to remember to change this list every time I change the form; having to change it for every program is that much worse. (I plan eventually to store this info in a server-side session.) The different libraries all have single-table structures, and I've unified some of the logic by putting the field names, desired form fields, etc. in a separate library that I can access from different scripts. But while this is convenient, it's not going to be possible to work when I add additional tables, which I'll need to do soon. I have extremely similar routines everywhere to generate the forms, and to parse and verify them, and to generate SQL queries from the results. Perhaps this can't be helped, as the forms have to be different, but it's getting clunky. The display routines, to generate HTML tables, are a total mess; if I want to change the order that fields display, or add fields (esp. dynamically), it's very difficult to do so. Also annoying is that each application tends to have several parts (an add, search, edit, delete, display_full, etc.), and there's a lot of shared code among the various routines. I'd like to get better interactivity among each branch of the overall application, without having to resort to the kludgy things I'm doing now. Thanks for any suggestions, or questions. I'd be happy to clarify anything or post code if required. Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow FULLTEXT searches
On Thu, Feb 27, 2003 at 06:05:02PM +0100, Thomas Spahni wrote:
Jesse
But then something else must be terribly wrong. As long as you are pulling
ten thousands of hits from the server, it may be slow. But when you reduce
the number of results with (let's say) 'LIMIT 100' I expect typical serch
times of 0.02 sec. That's what I see on a comparable machine holding 200
MB of text plus index.
Can you check for the response time on a not so common single word?
Sorry for the delay in following up.
Even when I'm searching for relatively uncommon single words, it's
still often slower than I'd like, but certainly under a second in
most cases.
Still, the problem is that I really do often need to search
for very common words, and these are extremely slow. People
will need to do phrase searches on this material that includes
shorter words or stopwords, and while I'm willing to shorten
the ft_min_word_length and remove the stopword list and take
the storage hit, it's still not OK that a query like SELECT
qt FROM q WHERE MATCH(qt) AGAINST ('in the cut' IN BOOLEAN
MODE) will take 2m20s to return 37 rows, or 'his computer'
IN BOOLEAN MODE will take 17s to return 117 rows, and that's
without sorting, or joining in five other tables some with
their own restrictions, etc.
Is there any way to improve the speed of these searches, given
that fulltext phrase searching is one of the main things I had
been hoping to use this database for? In many cases, other
restrictions from joined tables will even further reduce the
number of possible matches, but perhaps this doesn't matter if
the fulltext search is done independently of these; I don't know
how the optimizer handles this.
Thanks.
Jesse Sheidlower
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Slow COUNT queries
I have a Web application that allows users to search for text in a potentially complicated form, and then return results. Leaving aside the issue of the speed of FULLTEXT searching, which I'm discussing in a separate thread, I'm having a problem with an initial COUNT query. When the user enters their search, I first do a COUNT so I can get the total result size and set up the pager for the usual next/previous skipping through the results. When the result set is large, this initial COUNT can be extremely slow; this, for example, is from a question about words in the letter M within the last six months: mysql SELECT(cg.cw) AS cwcount FROM cg,q,cit,sref - WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id - AND cg.cw LIKE 'm%' AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH); +-+ | cwcount | +-+ |4666 | +-+ 1 row in set (1 min 11.26 sec) Everything is indexed here; the EXPLAIN shows: *** 1. row *** table: cg type: range possible_keys: q_id,cw key: cw key_len: 101 ref: NULL rows: 147780 Extra: Using where *** 2. row *** table: q type: eq_ref possible_keys: PRIMARY,cit_id key: PRIMARY key_len: 4 ref: cg.q_id rows: 1 Extra: *** 3. row *** table: cit type: eq_ref possible_keys: PRIMARY,sref_id key: PRIMARY key_len: 4 ref: q.cit_id rows: 1 Extra: *** 4. row *** table: sref type: eq_ref possible_keys: PRIMARY,cd key: PRIMARY key_len: 4 ref: cit.sref_id rows: 1 Extra: Using where 4 rows in set (0.00 sec) This is on a 1.4GHz PIII server running FreeBSD 4.7 with 1G of RAM. Needless to say, over a minute for a single user's query is unacceptably slow; generally after the COUNT, when I'm issuing LIMITed SELECT queries, things get much better. Is there any way to improve on this? Thanks. Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Slow FULLTEXT searches
I'm having a problem with FULLTEXT searches going much more slowly
than I expect, and need. It seems that this is perfectly straightforward
so I can't see why it's taking so long; other people on this list have
been reporting almost instantaneous results from FULLTEXT searches.
I'm using MySQL 4.0.10 on FreeBSD 4.7, on a 1.4 GHz PIII with 1G RAM.
It's a lightly loaded server most of the time.
The table in question is:
mysql show create table q\G
*** 1. row ***
Table: q
Create Table: CREATE TABLE `q` (
`id` int(10) unsigned NOT NULL default '0',
`cit_id` int(10) unsigned NOT NULL default '0',
`qt` text,
`note` text,
PRIMARY KEY (`id`),
KEY `cit_id` (`cit_id`),
FULLTEXT KEY `qt` (`qt`)
) TYPE=MyISAM
1 row in set (0.00 sec)
There are about 2.3M rows in this table, and it takes up about 400M.
I did shorten the ft_min_word_length to 2, since I need to search on
short words.
Here's a sample:
mysql SELECT COUNT(*) FROM q WHERE MATCH(qt) AGAINST ('computer');
+--+
| COUNT(*) |
+--+
|11892 |
+--+
1 row in set (16.43 sec)
Boolean searches are also slow:
mysql SELECT COUNT(*) FROM q WHERE MATCH(qt)
- AGAINST ('+free love -hippies' IN BOOLEAN MODE);
+--+
| COUNT(*) |
+--+
| 44 |
+--+
1 row in set (1.71 sec)
I don't get anything useful from EXPLAINs for searches like these:
mysql EXPLAIN SELECT COUNT(*) FROM q WHERE MATCH(qt)
- AGAINST ('+free love -hippies' IN BOOLEAN MODE)\G
*** 1. row ***
table: q
type: fulltext
possible_keys: qt
key: qt
key_len: 0
ref:
rows: 1
Extra: Using where
1 row in set (0.00 sec)
While a 1.7-second search may not be the end of the world, a 16-second
search is getting closer to it, and this is just the simplest case. In
practice, this would be an element of a larger search that's joining in
a number of other tables, and with a number of concurrent users. Is there
anything I can do to speed things up, or any explanation of why this is
so slow?
Thanks very much.
Jesse Sheidlower
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Slow FULLTEXT searches
On Thu, Feb 27, 2003 at 04:45:37PM +0100, Thomas Spahni wrote:
Jesse,
this is slow because you are SEARCH'ing for 'COUNT(*)' on a word occurring
many many times.
SELECT * FROM q WHERE MATCH(qt) AGAINST ('computer') LIMIT 100;
should be fast. Make sure to use a key_buffer_size as big as you can
afford, possibly keeping the whole index in memory.
This may be the answer for why it's _that_ slow for that one
query, but in general I'm afraid that's not it. I executed
your above query on my development server (to ensure the cache
was cleared), which is a somewhat slower machine, and it took
2.61 seconds--better than 16, but still problematic.
And when I changed this:
mysql SELECT COUNT(*) FROM q WHERE MATCH(qt)
- AGAINST ('+free love -hippies' IN BOOLEAN MODE);
+--+
| COUNT(*) |
+--+
| 44 |
+--+
1 row in set (1.71 sec)
to this:
mysql SELECT * FROM q WHERE MATCH(qt)
- AGAINST ('+free love -hippies' IN BOOLEAN MODE);
, it took 4.76 seconds--again, on a slower server, but this is
returning only 44 results.
It's certainly possible, and perhaps likely, that users will
need to do fulltext searches on extremely common words--more
common than computer in the above example--though limited by
requirements in other tables not shown here, and it would be
rather problematic if these searches are going to take over a
second each.
Jesse Sheidlower
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select based on related date
I'm having trouble with a query that I thought would be pretty straightforward. To simplify, I have a database of books that has, say, two tables: CREATE TABLE book ( id INT, date_purch DATE ) CREATE TABLE subj ( book_id INT, subj TEXT ) Each book can have any number of subjects, and each book has at least one subject. I'd like to get all subjects that are in the subject table that are _not_ represented in a particular date range. For example, if I have bought books with subjects 'Computing', 'Cooking', 'Baseball', and 'Fiction', but in the last six months I have only bought Fiction and Baseball, I'd like a query that will give me Computing and Cooking. I played around with a few LEFT JOINS but I still seem to be missing something. Thanks. Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem setting variable
I recently upgraded to 4.0.10, primarily in order to be able to change my minimum word length on the fly. But I can't seem to set the variable! It says it's there when I show it, but not when I try to change it: --- monopoly~ $ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 to server version: 4.0.10-gamma Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql SHOW VARIABLES LIKE 'version'; +---+--+ | Variable_name | Value| +---+--+ | version | 4.0.10-gamma | +---+--+ 1 row in set (0.00 sec) mysql SHOW VARIABLES LIKE 'ft_min%'; +-+---+ | Variable_name | Value | +-+---+ | ft_min_word_len | 4 | +-+---+ 1 row in set (0.00 sec) mysql SET ft_min_word_len=2; ERROR 1193: Unknown system variable 'ft_min_word_len' mysql --- Any idea what could be causing this? I did a Google search and someone reported the same problem on a different mailing list, but with no answer. Thanks. Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help optimizing queries
I have a database that I've worked hard to set up, and while many of my queries are working well, I have a few that are proving to be extremely slow, despite my best efforts to properly index them. I'd be grateful for any advice on how to speed these up, or an explanation of how there's no way to make it any better. Basically this is a database of quotations, with tables cg having 2.8M rows, q having 2.2M, cit 76,000, and sref 23,000. The larger tables are aspects of the quotations, the smaller ones contain bibliographic info. Most queries are getting quotations depending on some bibliographic factors. All the relevant fields for joins are indexed. The server is a 1.4Ghz PIII with 1G RAM and 15,000 RPM SCSI drives, and I'm running MySQL 4.0.9 on FreeBSD 4.7. The server is very lightly loaded. Two queries that are giving me trouble are this, which gives a count of words added in a particular timespan (sref.cd is a date field, indexed; cg.cw is an indexed VARCHAR): SELECT count(cg.cw) AS count FROM cg,q,cit,sref WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id AND cg.cw LIKE 'm%' AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH); This can take anywhere from 10-30 seconds to execute, depending on the letter, and longer with a longer date range. The EXPLAIN looks like this: mysql EXPLAIN SELECT count(cg.cw) AS count FROM cg,q,cit,sref - WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id - AND cg.cw LIKE 'm%' - AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH)\G *** 1. row *** table: cg type: range possible_keys: q_id,cw key: cw key_len: 101 ref: NULL rows: 153385 Extra: Using where *** 2. row *** table: q type: eq_ref possible_keys: PRIMARY,cit_id key: PRIMARY key_len: 4 ref: cg.q_id rows: 1 Extra: *** 3. row *** table: cit type: eq_ref possible_keys: PRIMARY,sref_id key: PRIMARY key_len: 4 ref: q.cit_id rows: 1 Extra: *** 4. row *** table: sref type: eq_ref possible_keys: PRIMARY,cd key: PRIMARY key_len: 4 ref: cit.sref_id rows: 1 Extra: Using where 4 rows in set (0.02 sec) It is somewhat faster if I ask for the cg.cw itself, instead of the count, but it's still in the many-seconds range, which is too slow. An even worse query is this one, where I'm looking for the most common words added in a particular timespan: SELECT cg.cw, count(*) AS count FROM cg,cit,sref,q WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) GROUP BY cw ORDER BY count DESC LIMIT 20; This just took 5m 34s to execute, which is totally unbearable. I understand that if it's trying to sort millions of rows it could be difficult, but is there any way to speed this up? Here's the EXPLAIN: mysql EXPLAIN SELECT cg.cw, count(*) AS count FROM cg,cit,sref,q - WHERE cg.q_id = q.id AND q.cit_id = cit.id AND cit.sref_id = sref.id - AND sref.cd DATE_SUB(CURDATE(), INTERVAL 6 MONTH) - GROUP BY cw - ORDER BY count DESC - LIMIT 20\G *** 1. row *** table: cg type: index possible_keys: q_id key: cw key_len: 101 ref: NULL rows: 2839036 Extra: Using temporary; Using filesort *** 2. row *** table: q type: eq_ref possible_keys: PRIMARY,cit_id key: PRIMARY key_len: 4 ref: cg.q_id rows: 1 Extra: *** 3. row *** table: cit type: eq_ref possible_keys: PRIMARY,sref_id key: PRIMARY key_len: 4 ref: q.cit_id rows: 1 Extra: *** 4. row *** table: sref type: eq_ref possible_keys: PRIMARY,cd key: PRIMARY key_len: 4 ref: cit.sref_id rows: 1 Extra: Using where 4 rows in set (0.00 sec) Thanks for any ideas. I'll probably have further problems when I start trying to use FULLTEXT searches, but these are the difficulties that sprung up right away. Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Configure prob with FreeBSD/Linuxthreads
I've been trying to install MySQL 4.0.9 on FreeBSD 4.7, and have been getting stuck in the configure phase. I'd be grateful for any suggestions. I'm running FreeBSD 4.7 on a single-processor 1.4GHz PIII, using gcc 2.95.4, and trying to compile with Linuxthreads. I've been using the instructions Jeremy Zawodny posted in his blog at http://jeremy.zawodny.com/blog/archives/000458.html . I modified Jeremy's command to eliminate some of the obvious things I didn't need, and started with: CFLAGS='-O -pipe -march=pentiumpro -D__USE_UNIX98\ -D_REENTRANT -D_THREAD_SAFE -DHAVE_BROKEN_REALPATH \ -I/usr/local/include/pthread/linuxthreads' CXX=cc\ CC=cc CXXFLAGS='-O -pipe -march=pentiumpro \ -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE \ -DHAVE_BROKEN_REALPATH -I/usr/local/include/pthread/linuxthreads \ -felide-constructors -fno-rtti -fno-exceptions' ./configure \ --with-mit-threads=no \ --enable-assembler \ '--with-named-thread-libs=-DHAVE_GLIBC2_STYLE_GETHOSTBYNAME_R\ -D_THREAD_SAFE -DHAVE_BROKEN_REALPATH\ -I/usr/local/include/pthread/linuxthreads -L/usr/local/lib \ -llthread -llgcc_r' --enable-thread-safe-client \ --with-libwrap --with-raid This dies relatively early with: checking for C compiler default output... configure: error: C compiler cannot create executables I've tried re-configuring with every possible variant, and it seems that the problem happens when CFLAGS has both the linuxthreads flag and the D_THREAD_SAFE flag. If the latter is omitted, configuration will proceed past this point. Since I don't know much about the whole compilation process, I'd be grateful for any suggestions as to what to try to get this to work properly. Also, once past this, if I omit all the flags on ./configure, it finishes configuration properly; if I include them, it will die later on with: checking size of char... configure: error: cannot compute sizeof (char), 77 I haven't experimented to see which configure flag might be causing this, but if anyone has any ideas, I'd be grateful. Thanks. Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Configure prob with FreeBSD/Linuxthreads
On Tue, Jan 28, 2003 at 01:17:30PM -0800, Jeremy Zawodny wrote: On Tue, Jan 28, 2003 at 12:21:37PM -0500, Jesse Sheidlower wrote: I've been trying to install MySQL 4.0.9 on FreeBSD 4.7, and have been getting stuck in the configure phase. I'd be grateful for any suggestions. I'm running FreeBSD 4.7 on a single-processor 1.4GHz PIII, using gcc 2.95.4, and trying to compile with Linuxthreads. I've been using the instructions Jeremy Zawodny posted in his blog at http://jeremy.zawodny.com/blog/archives/000458.html . I modified Jeremy's command to eliminate some of the obvious things I didn't need, and started with: [snip] This dies relatively early with: checking for C compiler default output... configure: error: C compiler cannot create executables Out of curiosity, which version of gcc are you using? As I posted a few lines up, it's gcc 2.95.4 ;-) After I sent the original message, I tried to play around with the configure variables, and discovered that it only worked by eliminating the entire '--with-named-thread-libs=-DHAVE_GLIBC2_STYLE_GETHOSTBYNAME_R\ -D_THREAD_SAFE -DHAVE_BROKEN_REALPATH\ -L/usr/local/lib \ -llthread -llgcc_r' group; I tried removing each one individually and it failed each time with the checking size of char... configure: error: cannot compute sizeof (char), 77 error. I've since given up, installed with the exact configure line shown in the MySQL docs, FreeBSD section, and it worked perfectly, so I'm worrying about moving my grant tables from 3.23.49 and so forth, instead of getting Linuxthreads to workBut I'd be happy to try to get this fixed, especially if it will help others. Best, Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Restricting access to results based on field values
I'm developing an application and trying to figure out a good way to restrict a user's access to data based on the content of particular fields. I'm sure there must be a standard way of doing this, but I don't know what it is. An example might be, in the canonical CD database, having a user who can only see results where cd.genre = 'Folk', or where song.length 10. My first thought was to have a table 'restrictions' with the userID and some element of a WHERE clause, e.g. cd.genre = 'Folk' hard-coded in it, and then when anyone issues a query, I grab everything matching their userID from the restrictions table, join it together, and stick it on to the end of the existing WHERE clause. But I'm worried that this will be vastly problematic if the structure of the tables change, or if I issue a query that turns out not to involve one of the tables (e.g. even if I usually expect queries to involve all the tables, I'll be stuck if I'm only querying the titles from the cd table and try to stick a 'song.length 10' to the WHERE clause when I'm not querying from the song table). Is there some standard way of doing this? It doesn't necessarily have to be neat, in that I'm the only one in charge of the database and I'm willing to do something in a manner that's somewhat of a pain to work with. But I also don't want to do something that will be impossible to maintain or convert to a better way. I'm using Perl, if that matters. Thanks for any ideas, sql query. Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Matching umlauted a,o,u
I've recently started to use accented characters in MySQL-- nothing extremely fancy, just the usual things in the ISO-8859-1 character set, mainly just the vowels with acute, grave, circumflex, and umlauts. Originally, everything was working fine; things got entered correctly, and I could search for them and find them regardless of the presence of an accent. Doing SELECTs confirmed this; SELECT [e-acute] = e; would return 1, and so forth. However, I soon learned that a-umlaut, o-umlaut, and u-umlaut do not in fact match a, o, and u respectively. This makes it very difficult to find things that might have these characters. I can't find anything too relevant in the manual--there's section 4.6.1.1 on the German character set that says that the accents are removed from everything execept upper- and lower-case umlauted a, o, and u. However, I didn't start my mysqld with --default-character-set=latin1_de, so I don't think it's relevant to me. Can anyone explain this to me, and more to the point, tell me what I need to do to get the umlauted a, o, and u to match the plain variety? Thanks. Jesse Sheidlower SQL, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problems installing on Solaris/Intel
On Wed, Oct 16, 2002 at 10:15:43AM -0400, Ben Goodwin wrote: I've compiled and installed this on my Solaris8/Intel box a few times without a hitch.. I don't recall seeing what version of Solaris you're running.. ? I'm running Solaris 8 with gcc 2.95.2. I also compiled with just ./configure - I didn't bother with the other options.. although that might be asking for trouble under certain circumstances... I don't have the source in front of me to check but I seem to recall being able to compile specifically withOUT curses support? Is your ncurses library up to date? Changing which curses libs to use won't affect this issue - the issue is a header/include problem, not a library problem. If that doesn't help, let me know and I'll try to suggest other things as well as check out my installation I've finally managed to get past this by updating ncurses to 5.2 and setting the -I flag to the CFLAGS and CXXFLAGS to point to the location of the new ncurses. Now I've gotten past this but hit another error that is even more mystifying: --- creating thread_test Making all in strings gcc -c -o strings-x86.o strings-x86.s Assembler: strings-x86.s, line 1 : Illegal mnemonic strings-x86.s, line 1 : Syntax error strings-x86.s, line 1 : Illegal mnemonic strings-x86.s, line 1 : Illegal mnemonic [...] strings-x86.s, line 3 : Illegal mnemonic Too many errors - Goodbye *** Error code 1 make: Fatal error: Command failed for target `strings-x86.o' Current working directory /usr/local/src/mysql-3.23.52/strings *** Error code 1 make: Fatal error: Command failed for target `all-recursive' Current working directory /usr/local/src/mysql-3.23.52 *** Error code 1 make: Fatal error: Command failed for target `all-recursive-am' --- I've looked for this error in various places and been unable to find it. Any ideas this time? And thanks again! Jesse Sheidlower sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problems installing on Solaris/Intel
I've been trying for a week to get MySQL installed on a Solaris system running on Intel, not Sparc, and am rather at the end of my line. I know almost nothing about the process of compiling things, but various people have given me advice and I still can't get it to work. All I want is a standard installation; I'd use a binary if there were one available. I'm trying with 3.23.52, though I've tried 4.0.4 with the same general results. When I try to compile MySQL, I get errors that look more or less like this (after several minutes of OK churning): --- Making all in client gcc -DUNDEF_THREADS_HACK -I./../include -I../include -I./.. -I..-I..-O3 -DDBUG_OFF -O3 -felide-constructors -fno-exceptions -fno-rtti -fno-implicit-templates -fno-exceptions -fno-rtti -DHAVE_CURSES_H -I/usr/local/src/mysql-3.23.52/include -DHAVE_RWLOCK_T -c mysql.cc In file included from mysql.cc:48: /opt/sfw/include/curses.h:96: declaration does not declare anything *** Error code 1 make: Fatal error: Command failed for target `mysql.o' Current working directory /usr/local/src/mysql-3.23.52/client *** Error code 1 make: Fatal error: Command failed for target `all-recursive' Current working directory /usr/local/src/mysql-3.23.52 *** Error code 1 make: Fatal error: Command failed for target `all-recursive-am' --- It always dies here, with some sort of curses-related problem. I run ./configure with these parameters, generally cribbed from the Manual: --- bash-2.03# CFLAGS=-O3 CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions \ -fno-rtti ./configure --prefix=/usr/local/src/mysql --enable-assembler \ --with-mysqld-ldflags=-all-static --- I have also tried specifying the --with-named-curses-libs flag, assigning it to many of the curses libraries on this system, including /opt/sfw/lib/libncurses.(a|so), /usr/lib/libcurses.(a|so), and others, all with the same results. I'm not, to be honest, even clear on what any of these are; as said, I don't know any C and I don't know what any of these compiler messages mean, what the libraries are, etc. I'd be grateful for any help anyone can give me. I've discussed this with people who are knowledgable about MySQL and compiling in general, with no luck, and I've checked the archives and can't find anything related. Thanks. Jesse Sheidlower SQL, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problems installing on Solaris/Intel
I did check out the link below, in which someone else has an error identical to mine, and Sinisa Milivojevic replied basically saying that the answer is described in detail in the Manual. Well, I can't find it in the Manual. There are things somewhat related in the various Solaris sections, and I've tried them, such as adding -DHAVE_CURSES_H to the CFLAGS and CXXFLAGS, adding /opt/sfw/lib to the LD_LIBRARY_PATH variable, and adding the flags suggested at the Solaris X86 section. None of these work; it always breaks in the same way at the same place. I appreciate that it's probably my ignorance that's preventing me from figuring this out, if it is indeed described so obviously in the Manual, but I've discussed it with someone who's very knowledgable about MySQL and he's stumped too. So I would be very grateful if anyone could explain to me what it is I'm missing. I seem not to have mentioned in my original post that I'm running gcc 2.95.2 and SunOS 5.8 on x86. Thank you. Jesse Sheidlower On Tue, Oct 15, 2002 at 01:38:38PM -0400, walt wrote: Jesse Sheidlower wrote: I've been trying for a week to get MySQL installed on a Solaris system running on Intel, not Sparc, and am rather at the end of my line. I know almost nothing about the process of compiling things, but various people have given me advice and I still can't get it to work. All I want is a standard installation; I'd use a binary if there were one available. I'm trying with 3.23.52, though I've tried 4.0.4 with the same general results. When I try to compile MySQL, I get errors that look more or less like this (after several minutes of OK churning): --- Making all in client gcc -DUNDEF_THREADS_HACK -I./../include -I../include -I./.. -I..-I..-O3 -DDBUG_OFF -O3 -felide-constructors -fno-exceptions -fno-rtti -fno-implicit-templates -fno-exceptions -fno-rtti -DHAVE_CURSES_H -I/usr/local/src/mysql-3.23.52/include -DHAVE_RWLOCK_T -c mysql.cc In file included from mysql.cc:48: /opt/sfw/include/curses.h:96: declaration does not declare anything *** Error code 1 make: Fatal error: Command failed for target `mysql.o' Current working directory /usr/local/src/mysql-3.23.52/client *** Error code 1 make: Fatal error: Command failed for target `all-recursive' Current working directory /usr/local/src/mysql-3.23.52 *** Error code 1 make: Fatal error: Command failed for target `all-recursive-am' --- It always dies here, with some sort of curses-related problem. I run ./configure with these parameters, generally cribbed from the Manual: --- bash-2.03# CFLAGS=-O3 CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions \ -fno-rtti ./configure --prefix=/usr/local/src/mysql --enable-assembler \ --with-mysqld-ldflags=-all-static --- I have also tried specifying the --with-named-curses-libs flag, assigning it to many of the curses libraries on this system, including /opt/sfw/lib/libncurses.(a|so), /usr/lib/libcurses.(a|so), and others, all with the same results. I'm not, to be honest, even clear on what any of these are; as said, I don't know any C and I don't know what any of these compiler messages mean, what the libraries are, etc. I'd be grateful for any help anyone can give me. I've discussed this with people who are knowledgable about MySQL and compiling in general, with no luck, and I've checked the archives and can't find anything related. Thanks. Jesse Sheidlower SQL, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Jesse, Check out http://www.faqchest.com/prgm/mysql-l/mysql-00/mysql-0012/mysql00122707_12134.html and search for curse Found it using google walt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problems installing on Solaris/Intel
On Tue, Oct 15, 2002 at 05:25:22PM -0400, Alan W. Rateliff, II wrote: First, install gcc 3.2. Then, check out this link (thanks to John Warburton): http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:mss:116929:200208:ngkbacmgkmgkdbbf gjdo The line numbers are different as of the latest MySQL version, but modifying the configure script as shown (just search for some keyword using your favorite text editor) works like a charm, promise. Though I did not install gcc 3.2, I did try everything in the message above, and the result was exactly the same. I would love to convince the powers that be that we should upgrade this box to Linux, if not FreeBSD, but unfortunately I don't think it'll be possible. I do agree with the sentiment expressed in the message above that the Manual should not say that it's simple to install on Solaris. Any other suggestions still welcome, thanks to everyone for their time. Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Limiting size of individual databases?
I'm sure this is a common requirement, but the only thing I could find in the docs or in a book or two was an unanswered version of the same question at http://www.mysql.com/doc/en/CREATE_DATABASE.html . I'm setting up MySQL on a small ISP and would like to be able to restrict the size of individual databases to something like 25M. What's the best way of getting this done? The server is running Solaris, if it's necessary to do it through the OS insteady of through MySQL. Thanks. Jesse Sheidlower [EMAIL PROTECTED] sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: multi-table select (not a join)
On Mon, Sep 23, 2002 at 11:20:49AM -0500, Paul DuBois wrote: I had a question about the use of the UNION command in this context. The original poster asked about getting the name of the _table_ as well as some other data, which would seem to be relatively necessary for doing many types of things with the results of the query. For example, if you issue a query that gives you the union of seven different tables, and then you want to do another query based on these results, you'll need to know which of the seven tables a particular result came from. The docs on UNION don't seem to address this, none of the responses mentioned it, and I can't seem to find any discussion of how to retrieve the table name in a SELECT query (I acknowledge that most of the time you wouldn't need it, but in a UNION you might). How do you get the table name returned as part of the query results? Or am I misunderstanding how one would work with the results? Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Server advice
I know that the what kind of server should I buy? question comes up regularly here, but the various discussions in the archives don't seem to address my issue. My current server is an old PII desktop with 64M memory and a 6GB hard drive, running FreeBSD. Thanks to the glories of FreeBSD and the speed of MySQL, and my relatively limited needs, this has been perfectly adequate up to now--I've never had any speed-related issues, the thing never crashes (I had a MySQL process running for about 320 days before someone accidentally unplugged the computer). On my busiest days I don't get more than a few hundred queries, and my current tables are all pretty simple. I'm about to start work on a considerably more complex project, and I'll need to get a new server for it. I have about 600M of XML that I want to convert to MySQL, and I will have to do the same thing on a roughly weekly basis--as the underlying data gets revised elsewhere I'll need to re-import the whole batch. When it's in the database, I'll then want to serve it on an intranet, do various statistical analyses, etc. The final format will involve at least six and possibly more tables, the largest being about 2 million rows; it will be heavily indexed. However, while I'll need the final queries to execute with reasonable speed, I still don't expect a particularly large amount of traffic. I want to stick with MyISAM tables, so I can use fulltext indexes (and heavy concurrent access won't be a major problem, so InnoDB shouldn't be necessary); I'm using 3.23.39 now and would probably upgrade to 4.0.X to take advantage of some of the newer features. I want to stick with FreeBSD. I'd be grateful for any advice on what my server needs might be, even if that advice is the familiar bigger, faster, stronger. My main concern is that doing my weekly importation of the XML shouldn't take the entire week. Also, I haven't figured out exactly how I'm going to manage the conversion (e.g., through an object-relational model, or more directly); this project is bigger than anything I've worked on before and I'm trying to approach it with caution. Thanks. Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: newbie query question
On Thu, Sep 12, 2002 at 11:46:40AM -0700, Kip Krueger wrote: I need to query a mysql db in the following fashion ... find me all records whose column 'n' have the letters 'XY' as the first two characters. where column 'n' is just a short string to clarify ... if column 'n' has the value YR12345 don't get that record. if column 'n' has the value XY5 get me that record. if column 'n' has the value XY33456 get me that record. so I am here ... select * from thetable where BLAHBLAH what is BLAHBLAH? n like 'XY%'; Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump and mysqlimport
On Wed, Aug 21, 2002 at 11:37:57AM -0700, abw wrote: Hello, Having a problem here. I'm trying to get a Mysql 3.23 database from a Red Hat 7 machine over to a Mysql 3.23 on a Solaris Sparcstation. I was able to dump the database using the mysqldump command. The database has 3 tables, and I used the following syntax: [...] I copied the files to the sparc server and did a mysqlimport. I tried: mysqlimport -u username -p database table_name I am told that it cannot find the ISM (I think that was the name) file. Well, nowhere for the mysqldump that I could find did it mention anything about this. Clearly I am doing something wrong. I searched for hours yesterday and searched google for different was to use these commands, but couldn't find anything other than the documentation and the syntax used above. Any suggestions? Am I using mysqldump and mysqlimport correctly? mysqlimport is not the opposite of mysqldump; mysqlimport is used to bulk-read the contents of textfiles into a MySQL database. If you have a mysqldump-generated file of SQL statements, you read it back into a database with the mysql command, e.g. mysql -u username -p database backup-file.sql Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Load Data Infile
On Tue, Aug 20, 2002 at 04:40:25PM -0400, Serge Paquin wrote: This is the only option? That's not a very automated process and I must import this data once a day. I also will not be able to get them to reformate their data since I'm only one of many many people downloading everyday. Is their no way for load data to do this automaticaly? Paul DuBois has some useful scripts to convert dates in the Early release software section of the page for his forthcoming _MySQL Cookbook_ at http://www.kitebird.com/mysql-cookbook/ I think you'll find it pretty useful for this. Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Stupid backup/restore question
I have a working server and a development server. From time to time I'd like to refresh the content of my development server with what's on my working server. So I take one of my regular backups, that I get by doing mysqldump database dbbackupJuly1-02. Then I gzip this, ftp it over to my development server, gunzip it, and try mysql database dbbackupJuly1-02, and immediately get an ERROR 1050 at line 11: Table 'firsttable' already exists message. What should I be doing instead? The docs don't seem to specify this, and there doesn't seem to be an ignore or replace option for the mysql command. Do I actually have to drop all the tables on my development box before loading in from the backup? Jesse Sheidlower [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Stupid backup/restore question
On Mon, Jul 01, 2002 at 01:23:38PM -0400, Keith C. Ivey wrote: On 1 Jul 2002, at 12:30, Jesse Sheidlower wrote: What should I be doing instead? The docs don't seem to specify this, and there doesn't seem to be an ignore or replace option for the mysql command. Do I actually have to drop all the tables on my development box before loading in from the backup? Have you seen the --add-drop-table option (see http://www.mysql.com/doc/m/y/mysqldump.html), or is that what you mean by your reference to dropping the tables? Thanks to all who responded with this general suggestion. I had looked at the --opt option, but ignored it as speed wasn't a real issue for this. And it didn't occur to me that this would be something to specify in the backup, rather than the restore. Without dropping the tables, how would you get rid of records that have been deleted? Well, I said in the subject line it was a stupid question! (Still writing for Copy Editor?) Yup. Jesse MySQL Query Sheidlower [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie GROUP-type question
I'm relatively new to the construction of complex queries, and I'm trying to figure one out that I'm sure is common; I wasn't able to find anything especially helpful in the docs. Suppose I have the canonical database of CDs, with one table, which includes fields for title, cost, and date_purchased. I'd like to get a result set that shows the number of CDs purchased, and their sum cost, for each month. Thus, if I bought some CDs that cost exactly $15.00 each month for the last few months, I'll get something like 2001-12 2 30.00 2002-01 3 45.00 2002-02 2 30.00 2002-03 1 15.00 (The exact format isn't too important, I can figure that out once I get the basic query down.) What's the best way to construct this SQL query? Thanks. Jesse Sheidlower - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Special characters over Web
There have been a lot of questions here about how to store, sort, etc. various special characters and alphabets in MySQL internally. Maybe this isn't really a MySQL question, but I'd like to know how to deal with such characters over a Web interface. I have a database of books that's accessed entirely over the Web via Perl interfaces. I have assorted special characters, typically accented vowels or things of that straightforward nature; sometimes there are en-dashes or em-dashes. What's the best way to work with these? I need to accept input from people running a potentially wide variety of systems, store the data in some way, and display them back on the Web. I'd think this would be a common requirement, but what I've been able to find in the manual discusses the internal storage only. Thanks. Jesse Sheidlower [EMAIL PROTECTED] sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
