Re: more queries vs a bigger one
Hi,
I have finally modified that long query and splitted into smaller ones.
Now the main query is:
select sql_calc_found_rows
a.pre_title, a.title, a.post_title, substring(a.body, 1, 250) as preview,
a.hash, a.date, a.time, length(a.body) as size, a.id_categories,
n.name as newspaper, sc.category
from articles a, newspapers n, sections_categories sc
where a.id_newspapers=n.id
and a.id_categories=sc.id
and a.active_view=1
and a.id_categories=20
limit 0,30;
For each of those 30 records which are returned, I make other 3 queries:
select count(*) from articles_comments where hash_articles='[an MD5 hash
with 16 characters]';
select count(*) from articles_count where hash_articles='[an MD5 hash with
16 characters]';
select name, email, query from articles_authors where hash_articles='[an MD5
hash with 16 characters]';
Well, now instead of making a big query, MySQL will make 91 queries.
I have tested the program, but it doesn't work faster at all.
Do you have any idea what could be wrong?
There are almost 100.000 records in the database, and this query should
return 10121 records.
Here is the data structure. Please tell me if you think there is something
bad in it.
Thank you.
Teddy
DROP TABLE IF EXISTS `articles`;
CREATE TABLE `articles` (
`id_newspapers` smallint(3) unsigned NOT NULL default '0',
`id_sections` smallint(3) unsigned NOT NULL default '0',
`id` int(6) unsigned NOT NULL auto_increment,
`hash` char(16) NOT NULL default '',
`url` varchar(255) NOT NULL default '',
`full_url` varchar(255) NOT NULL default '',
`pre_title` varchar(255) not null default '',
`title` varchar(255) NOT NULL default '',
`post_title` varchar(255) not null default '',
`body` text NOT NULL,
`body_hash` char(32) NOT NULL default '',
`article_ident` varchar(255) not null default '',
`date` date NOT NULL default '-00-00',
`time` time NOT NULL default '00:00:00',
`id_categories` tinyint(3) unsigned not null default '0',
`active_view` tinyint(1) unsigned not null default '1',
PRIMARY KEY (`id_newspapers`,`id_sections`,`id`),
UNIQUE KEY `id_newspapers` (`id_newspapers`,`body_hash`),
UNIQUE KEY `hash` (`hash`),
KEY `date` (`date`),
KEY `id_categories` (`id_categories`),
FULLTEXT KEY `full` (`pre_title`,`title`,`post_title`,`body`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `articles_authors`;
CREATE TABLE `articles_authors` (
`hash_articles` char(16) NOT NULL default '',
`name` varchar(30) NOT NULL default '',
`email` varchar(255) not null default '',
`query` varchar(255) not null default '',
UNIQUE KEY `hash` (`hash_articles`,`name`),
KEY `hash_articles` (`hash_articles`),
KEY `name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `articles_comments`;
CREATE TABLE `articles_comments` (
`hash` char(16) NOT NULL default '',
`hash_articles` char(16) NOT NULL default '',
`hash_users` char(16) NOT NULL default '',
`body` text NOT NULL,
`hash_original` char(16) not null default '',
`comment_type` enum('public','private') NOT NULL default 'public',
`date` date NOT NULL default '-00-00',
`time` time NOT NULL default '00:00:00',
`remote_address` varchar(255) not null default '',
PRIMARY KEY (`hash`),
KEY `hash_articles` (`hash_articles`),
KEY `hash_users` (`hash_users`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `articles_count`;
CREATE TABLE `articles_count` (
`id` int(10) unsigned NOT NULL auto_increment,
`hash_articles` char(16) NOT NULL default '',
`hash_users` char(16) not null default '',
`date` date NOT NULL default '-00-00',
`time` time NOT NULL default '00:00:00',
`remote_address` varchar(255) NOT NULL default '',
`user_agent` varchar(255) not null default '',
PRIMARY KEY (`id`),
UNIQUE KEY `hash` (`hash_articles`,`date`,`remote_address`),
KEY `hash_articles` (`hash_articles`),
KEY `hash_users` (`hash_users`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS `newspapers`;
CREATE TABLE `newspapers` (
`id` smallint(3) unsigned NOT NULL auto_increment,
`label` varchar(20) NOT NULL default '',
`name` varchar(255) NOT NULL default '',
`script` varchar(20) NOT NULL default '',
`first_page` varchar(255) NOT NULL default '',
`base_url` varchar(255) not null default '',
`email` varchar(255) not null default '',
`importance` tinyint(3) unsigned not null default '0',
`category` tinyint(1) unsigned not null default '3',
`frequency` enum('week','month') NOT NULL default 'week',
`sect_skip` smallint(5) unsigned default NULL,
`sect_end` smallint(5) unsigned default NULL,
`art_skip` smallint(5) unsigned default NULL,
`art_end` smallint(5) unsigned default NULL,
`preview_size` smallint(3) unsigned not null default '250',
`view_size` smallint(5) unsigned default NULL,
`active_download` enum('yes','no') not null default 'yes',
`active_view` enum('yes','no') not null default 'yes',
PRIMARY KEY (`id`),
UNIQUE KEY
UNION in JDBC - WAS Re: use of indexes
Hello again Following on from this index question, the UNION worked. From a normal mysql client, it was returning my results sub-second. I am actually executing this over JDBC, using mysql-connector j. WHen I put the SQL into my Java program - it takes a minute or so. I am logging the SQL and if I copy and paste it into my mysql client, it is fast. I can execute the query first in mysql and then in the JDBC client and I get the same so it is not caching. I've done a bit of searching but found nothing - any ideas ? Chris On 7/22/05, Chris Faulkner [EMAIL PROTECTED] wrote: That was exactly the problem. Thanks. MySQL can't use two indexes on the same table at the same time. Thanks for the other suggestions but I'll use this workaround. Chris On 7/22/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I believe the conflict here is the OR. Try this... select * from table where field1 = 'VALUE1' and field2 like 'VALUE2%' union select * from table where field3 = 'VALUE1' and field2 like 'VALUE2%' Currently, MySql can't utilize two indexes on the same table at the same time but it is on their list of to-do`s, this will be a cool feature. The UNION will allow you to use both composite indexes at the same time because it is two queries. Ed -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, July 22, 2005 6:04 AM To: Chris Faulkner Cc: mysql@lists.mysql.com Subject: Re: use of indexes The system cannot used the index on field2 because it is the second half of the index in both cases, and it can only use indexes in order. It cannot use the separate indexes on field 1 and field 2 because the are ORred together. If you rephrase your query SELECT * from table WHERE field2 LIKE 'VALUE2% AND ((field1 = 'VALUE1') OR (field3 = 'VALUE3')) ; it becomes obvious that an index on field2 will be used, followed by searches of the results field1 and field3 . As a matter of interest, what numbers of hits do you expect on each of the three terms separately? If the field2 hit is is pretty selective, it does not really matter what the others do. Alec Chris Faulkner [EMAIL PROTECTED] 22/07/2005 12:46 Please respond to Chris Faulkner [EMAIL PROTECTED] To mysql@lists.mysql.com cc Subject Re: use of indexes Hi field2 is indexed. I have 2 indexes. One is on field1 and field2, the second indexes field3 and field2. You mean a separate index which only indexes field2 ? Ithought that the type of query I am doing is a good reason for doing composite indexes. Chris On 7/22/05, Eugene Kosov [EMAIL PROTECTED] wrote: Chris Faulkner wrote: HI I have a query like this select * from table where ( ( field1 = 'VALUE1' and field2 like 'VALUE2%' ) OR ( field3 = 'VALUE1' and field2 like 'VALUE2%' ) ) I have created two composite indexes - one on field1 + field2 and one on field3 + field2. Explain on the SQL indicates that the indexes are possibly used. The query takes an age to run and looking at my log indicates a full table scan. I have also tried indexing just field1 and field3 separately but this doesn't help. I have run an analyze. Chris Mysql use an index only if indexed field(s) present(s) in both OR arguments.. Sorry, but i can't find it in docs right now, so i can't give you any helpful link. I think index on field2 may help you here.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
variable type
Hi, is it possible that int variable type negative number? If not, what type of variable if I need a negative number - Start your day with Yahoo! - make it your home page
What does this error mean?
Hi, I have tried the following query and it works fine. It takes 11 seconds and this is a little too much, but this is another issue. The problem is that if I delete the following condition from it: a.id_categories=31 The query gives the following error: ERROR 1032 (HY000): Can't find record in '' What can I do to make it work? Thank you. select straight_join sql_calc_found_rows a.hash, a.pre_title, a.title, a.post_title, substring(a.body, 1, 250) as preview, a.hash, a.date, a.time, length(a.body) as size, a.id_categories, n.name as newspaper, sc.category, count(act.id) as visitors, count(aco.hash) as comments from articles a inner join newspapers n on(a.id_newspapers=n.id) inner join sections s on(a.id_sections=s.id and n.id=s.id_newspapers) inner join sections_categories sc on(a.id_categories=sc.id) left join articles_count act on(a.hash=act.hash_articles) left join articles_comments aco on(a.hash=aco.hash_articles) where a.id_categories=31 and a.date between '2005-01-01' and '2005-12-31' and a.active_view=1 group by a.hash order by visitors limit 0,30; Teddy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: variable type
Eko Budiharto wrote: Hi, is it possible that int variable type negative number? If not, what type of variable if I need a negative number Yes. INTs support the range -2147483648 to 2147483647. MySQL's column types are documented in the manual http://dev.mysql.com/doc/mysql/en/column-types.html, with specific pages on the numeric types http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html and http://dev.mysql.com/doc/mysql/en/numeric-types.html. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Questions
Dan Tappin wrote (quotes from several posts, my thoughts interspersed): I am running into repeatable table corruption with MySQL 4.x on Mac OS X 10.x. Many people, myself included, are running mysql 4.x on OS X without reporting this sort of problem, so the trick is to find out what your installations have that the rest of us don't have. All my systems have different OS's, versions of MySQL and PHP. They all have UPS's. The old 10.2 system (soon to be retired) has only a single drive. The new 10.4 system has mirrored drives on a RAID set- up. The 10.3 install (recently wiped) had the same set-up. I think I can rule out a hardware issue and a OS / MySQL issue. It even show up on tables I never interact with directly i.e. my Moveable Type db for my blogs. I create an entry and the next day it's dropped out of the database. The part that keeps scaring me is that I have seen this on 10.2, 10.3 and 10.4 all with separate hardware. My recent build has 2 drive on a mirror. I find it hard to believe that I have been that unlucky to have drive failures / failed filesystems on ALL these machines. Repeated table corruption often points to an underlying hardware or OS issue, as others have pointed out, but as you have the same behavior on a variety of platforms, I think you are right that this is unlikely in your case. The question is, what do your systems have in common? In particular, what do they have in common that working systems don't have? I previously had a MySQL install on 10.2 Client under 3.x and never had an issue or any major problems at all. I upgraded to MySQL 4.x and have subsequently installed MySQL 4.x (from the supplied pkg's) on my 10.2 client, a 10.3 Server and 10.4 Server (Tiger) install. Well Apple does not really have a front end for MySQL at all. I've gone away from Apple's updates and install my own version from the source packages provided by MySQL. You've said you install using supplied pkg's, but you've also said you install from the source. Let's be precise: Are you installing MySQL's pre-compiled binary distribution for OS X (the installer package which comes as a .dmg), or are you compiling from source? I expect you are using the installer package, but if you are building from source, you should post your configure options. Both methods work, but if you are compiling it yourself, it could be something different in your configuration which is causing your problem. The typical application here was standalone servers running MySQL, Apache and PHP 4/5 running a hand full of small websites. Things run along fine until with out warning my PHP / MySQL queries fail returning no data when there should be. To be clear, your queries succeed, in that they do not return errors from mysql, but they do not return expected data, correct? Do the same queries produce the same result from the mysql client? What makes you think that is table corruption, rather than missing data? Corrupt tables usually produce error messages to that effect. (See the manual for a list http://dev.mysql.com/doc/mysql/en/repair.html. Also see http://dev.mysql.com/doc/mysql/en/corrupted-myisam-tables.html.) As a temporary fix I created a cron job to run: /usr/local/mysql/bin/myisamchk -ov on all my tables every 4 hours. You are shutting down mysqld every 4 hours? Or are you running myisamchk while the server is running? This fixes the data issues but the problem is I am losing data: - recovering (with keycache) MyISAM-table '/var/mysql/data/xxx.MYI' Data records: 17 Data records: 14 Now this was last night at midnight. I recall manually adding the 3 records that evening and the previous cron job had only 14 records. It seems that the fix is flushing my new data down the toilet. Here is the full myisamchk output on the subject table (after adding my data back in): snip Poof!... gone again. So, myisamchk is dropping *recently added* data (rows inserted since the previous invocation of myisamchk)? I then re-inserted my data again, ran a 'REPAIR TABLE...' directly from the client and the data seems to stick. If I understand correctly, REPAIR TABLE finds no problems? I jump through these hoops each time and the problem seems to go away and then out of the blue this comes back. It's driving my crazy. Any ideas at all out there? Flush tables? A different repair system? The recommended method is to run CHECK TABLE in the client, then REPAIR TABLE if necessary http://dev.mysql.com/doc/mysql/en/repair.html, If you are using MySQL 3.23.16 and above, you can (and should) use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables. That same page also states, If you are going to repair a table from the command line [using myisamchk], you must first stop the mysqld server. Note that when you do mysqladmin shutdown on a remote server, the mysqld server is still alive for a while after
Alternatives to performing join on normalized joins?
I have a large number of job titles (40K). Each job title has multiple keywords making a one-to-many parent-child relationship. If I join job title with company name, address, company url, company city, job name, job location, job url (etc...) I have a mighty wide result set that will be repeated for each keyword. What I have done in the past (in a different, much smaller, application) is perform a join of everything except the keyword and store everything in a hashmap. Then I iterate thru each wide row in the hashmap and perform a separate SELECT statement foreach row in this hashmap to fetch the multiple keywords. Whew! That would be a lot of RAM (and paging) for this application. Are there any other more efficient approaches? Thanks, Siegfried -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
COLLATE: Do I need a new version?
Hi, COLLATE does not work in the way it is described on this page. I have mysql version: Ver 12.22 Distrib 4.0.21, for suse-linux (x86_64) Is it possible that it is a version problem? Thanx Wolle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Questions
On Jul 24, 2005, at 9:13 AM, Michael Stassen wrote: Dan Tappin wrote (quotes from several posts, my thoughts interspersed): I am running into repeatable table corruption with MySQL 4.x on Mac OS X 10.x. Many people, myself included, are running mysql 4.x on OS X without reporting this sort of problem, so the trick is to find out what your installations have that the rest of us don't have. This is also part of the frustration. If Macintouch or the OS X server admin list was full of MySQL issues I would actually feel better. Repeated table corruption often points to an underlying hardware or OS issue, as others have pointed out, but as you have the same behavior on a variety of platforms, I think you are right that this is unlikely in your case. The question is, what do your systems have in common? In particular, what do they have in common that working systems don't have? That's the big frustration. I'm also thinking now that I am the common thread i.e operator error. You've said you install using supplied pkg's, but you've also said you install from the source. Let's be precise: Are you installing MySQL's pre-compiled binary distribution for OS X (the installer package which comes as a .dmg), or are you compiling from source? I expect you are using the installer package, but if you are building from source, you should post your configure options. Both methods work, but if you are compiling it yourself, it could be something different in your configuration which is causing your problem. Sorry to be clear I'm using the installer package - 4.1.13 as of today on both suspect systems. To be clear, your queries succeed, in that they do not return errors from mysql, but they do not return expected data, correct? Do the same queries produce the same result from the mysql client? What makes you think that is table corruption, rather than missing data? Corrupt tables usually produce error messages to that effect. (See the manual for a list http://dev.mysql.com/doc/mysql/ en/repair.html. Also see http://dev.mysql.com/doc/mysql/en/ corrupted-myisam-tables.html.) As a temporary fix I created a cron job to run: /usr/local/mysql/bin/myisamchk -ov on all my tables every 4 hours. You are shutting down mysqld every 4 hours? Or are you running myisamchk while the server is running? While running. This fixes the data issues but the problem is I am losing data: - recovering (with keycache) MyISAM-table '/var/mysql/data/ xxx.MYI' Data records: 17 Data records: 14 Now this was last night at midnight. I recall manually adding the 3 records that evening and the previous cron job had only 14 records. It seems that the fix is flushing my new data down the toilet. Here is the full myisamchk output on the subject table (after adding my data back in): snip Poof!... gone again. So, myisamchk is dropping *recently added* data (rows inserted since the previous invocation of myisamchk)? Yes... even when I wasn't running a regular myisamchk. For example: I have the movable type blog installed on my 10.4 Server system. I create a new blog entry or a new category. Later that hour, day or week subsequent queries will fail and I'll get the ever wonderfull 'got error 127 from storage engine' error. Manual SELECT * FROM mt_category etc will trigger this. A myisamchk will only seem to work once I run a REPAIR TABLE mt_category etc. The recommended method is to run CHECK TABLE in the client, then REPAIR TABLE if necessary http://dev.mysql.com/doc/mysql/en/ repair.html, If you are using MySQL 3.23.16 and above, you can (and should) use the CHECK TABLE and REPAIR TABLE statements to check and repair MyISAM tables. That same page also states, If you are going to repair a table from the command line [using myisamchk], you must first stop the mysqld server. Note that when you do mysqladmin shutdown on a remote server, the mysqld server is still alive for a while after mysqladmin returns, until all queries are stopped and all keys have been flushed to disk. Another manual page http://dev.mysql.com/doc/mysql/en/crash- recovery.html says, If you run mysqld with --skip-external- locking..., the default on OS X, ...you can't reliably use myisamchk to check a table when mysqld is using the same table. It goes on to say, If you use myisamchk to repair or optimize tables, you must always ensure that the mysqld server is not using the table (this also applies if you are using --skip-external- locking). If you don't take down mysqld, you should at least do a mysqladmin flush-tables before you run myisamchk. Your tables may become corrupted if the server and myisamchk access the tables simultaneously. I am suspicious that you are creating your own problem. You report that mysqld is not crashing, nor is it complaining of crashed/ corrupted tables. Your only symptom is
Re: COLLATE: Do I need a new version?
At 18:20 +0200 7/24/05, Christian Wollmann wrote: Hi, COLLATE does not work in the way it is described on this page. I have mysql version: Ver 12.22 Distrib 4.0.21, for suse-linux (x86_64) Is it possible that it is a version problem? Yes, you need 4.1. Please see the intro to the character set chapter in the manual: http://dev.mysql.com/doc/mysql/en/charset.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
