ordering by count
Hi, i have a system that has media files ==> users can be fans of media ( so media becomes their favorite ). the table itself: CREATE TABLE media_fans( id int(11) unsigned not null auto_increment, user_id int(11) unsigned not null, media_id int(11) unsigned not null, primary key(id), foreign key(user_id) references users(id) on delete cascade, foreign key(media_id) references media(id) on delete cascade )Engine=InnoDB; let's say we have 1M entries in this table... How to get the Top Favorite media very quickly ? ( let's say You Tube has the sorting possibility and they have a huge amount of users and files and fans of files ) I can see 2 solutions: 1) using this sort of query: SELECT COUNT(id) as 'count' FROM media_fans ORDER BY 'count' DESC LIMIT 100 so if we have 1M rows then the 'EXPLAIN' says that we need to go through all 1M rows and type is 'INDEX' 2) using another table to store the total amount of fans for each media this solution requires another table and some triggers, so when the fan is added to media_fans some trigger works and increments a value for some media ( and then we just select the top favorite media without counting the number of fans every time ) So what i'm interested in: how do huge portals implement that ? do they recount each time ( isn't it slow ? ) or they use precalculated values ?
Use Samba Share For Data Directory
We have 5.0.27 installed on a CentOS machine that doesn't have a ton of disk space. Is it possible to point the data directory to lie on a samba connected share? The samba share does not support Unix file permissions so it is not possible to set mysql as the owner of the files. Is this possible at all? Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb, why not?
At 03:54 PM 1/25/2007, you wrote: > Another thing to consider is: heh, silly mail client :). Another thing to consider is this: http://dev.mysql.com/doc/falcon/en/index.html Though it's "Not recommended for production use", I've heard people still use it in production environments. -- Chris, Falcon doesn't currently support RI. And like Innodb, it requires its own table space so it too may get fragmented. http://dev.mysql.com/doc/falcon/en/se-falcon-createdb.html and will likely require packing (sweeping?) from time to time. It would be nice to see some benchmarks compared to InnoDb and MyISAM. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb, why not?
Hi Olaf, > I know the innodb vs myisam issue comes up quite frequently. I went through > old threads and could not find an answer to my questions. > > Generally, is there any reason/scenario not to use innodb? > > >From a feature perspective, I do not need full text indices, foreign keys > are usefull but not necessary (if I write the applications accordingly), If you think this statement makes sense, then by all means, don't use InnoDB, cause you probably don't need transactions either (if all your statements fully execute) or primary key constraints (cause you know there's only 1 row with a given value) or you don't mind another instance reading rows from your tables that don't have child rows yet. Or whatever... > transactions are also usefull but not entirely necessary. > Basically I have no excluding reasons for the one or the other. > I know this is a very general question but it seems not to make any sense > not to use innodb having such exotic features like foreign keys and > transactions. > > Maybe some of you had this dilemma in the past and can offer some insight. Read a book on database systems. I can highly recommend: http://www.amazon.com/dp/0321197844?tag=databasede095-20&camp=14573&creative=327641&linkCode=as1&creativeASIN=0321197844&adid=0P8MH25M8C0734M9XVNA&; and this website: http://www.dbdebunk.com/index.html Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb, why not?
> Another thing to consider is: heh, silly mail client :). Another thing to consider is this: http://dev.mysql.com/doc/falcon/en/index.html Though it's "Not recommended for production use", I've heard people still use it in production environments. -- Chris White PHP Programmer Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb, why not?
On Friday 26 January 2007 06:17, Olaf Stein wrote: > From a feature perspective, I do not need full text indices, This is about the only reason I've seen MyISAM promoted as table engine of choice. > I know this is a very general question but it seems not to make any sense > not to use innodb having such exotic features like foreign keys and > transactions. Foreign keys, imho, are a really good thing for a database. While they may not seem terribly important at first, consider your statement of "(if I write the applications accordingly)". I note the plural here and point out that foreign keys would centralize your data referencing, so you could write your application in 10 different languages and the data relationships would still say the same. Given the somewhat heterogeneous web development environment people see today (I've seen PHP and Java for example), it makes it a good idea as any to use the table. Another thing to consider is: -- Chris White PHP Programmer Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb, why not?
Size is an issue with InnoDB and deleting records does not reduce the size of the file. In my experience, the performance drop off is considerable once the table reaches a certain size. And it's not a slight drop off over time. If your table is going to get very large, I would reccommend using MyISAM. You have many more options for managing a large table. For instance, you can split the data up into table, say one per month. You can then create merge tables to access those tables. This gives you the ability to create datasets of varying sizes without the need to change any code. Modifying a merge table is quick and easy, so you can create a sliding 3, 6 and 12 month tables. The underlying table stay the same. If you expect many inserts while long searches are going on, MyISAM will be a problem. The searches will block the inserts, forcing them to queue up. Depending on how busy the box is, this can become a problem. InnoDB won't have this issue, but it is slower. I've actually done hybrid setups where insert tables are InnoDB and "search" tables are MyISAM. Periodically (i.e. daily) the InnoDB data is imported into the MyISAM tables, then dropped and recreated. Your code needs to know that it needs to do a UNION between the MyISAM and InnoDB table to get the most up to date information. While more complicated, it has actually worked fairly well. At least up to about 450 million rows so far. - Original Message - From: "Olaf Stein" <[EMAIL PROTECTED]> To: "MySql" Sent: Thursday, January 25, 2007 4:17 PM Subject: Innodb, why not? Hi All I know the innodb vs myisam issue comes up quite frequently. I went through old threads and could not find an answer to my questions. Generally, is there any reason/scenario not to use innodb? From a feature perspective, I do not need full text indices, foreign keys are usefull but not necessary (if I write the applications accordingly), transactions are also usefull but not entirely necessary. Basically I have no excluding reasons for the one or the other. From a speed perspective, I do not have a lot of simultaneous connections but a lot of data. Some tables have several hundred million records (growing). I read somewhere that innodb loses performance once the size of the tables exceed the amount of RAM. Is that true and if yes, how bad is that loss? I know this is a very general question but it seems not to make any sense not to use innodb having such exotic features like foreign keys and transactions. Maybe some of you had this dilemma in the past and can offer some insight. Thanks in advance Olaf -- 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]
Re: tool to parse general log (3.23.55)
On 1/25/07, Sid Lane <[EMAIL PROTECTED]> wrote: all, I have been tasked with upgrading a critical 3.23.55 database to 5.0 (.27-ish). short version is it's never been upgraded because authors have moved on and nobody's sure of everything that uses it. I enabled the general log a few days ago and have a good body of data with which to go code hunting but (being a DBA) would like to load this into a couple of tables for easier analysis. has anyone already invented this wheel or should I post my solution if I end up doing it myself? This project may be of some help: http://sourceforge.net/projects/myprofi If not, at least it would give you a starting point. There are many tools to parse the slow query log, if the above isn't helpful, maybe you can modify one of them. http://www.mysqlperformanceblog.com/2006/09/06/slow-query-log-analyzes-tools/ http://www.willamowius.de/mysql-tools.html -- -jp They say that lightning never strikes the same place twice. Niether does Chuck Norris. He doesn't have to.
Innodb, why not?
Hi All I know the innodb vs myisam issue comes up quite frequently. I went through old threads and could not find an answer to my questions. Generally, is there any reason/scenario not to use innodb? >From a feature perspective, I do not need full text indices, foreign keys are usefull but not necessary (if I write the applications accordingly), transactions are also usefull but not entirely necessary. Basically I have no excluding reasons for the one or the other. >From a speed perspective, I do not have a lot of simultaneous connections but a lot of data. Some tables have several hundred million records (growing). I read somewhere that innodb loses performance once the size of the tables exceed the amount of RAM. Is that true and if yes, how bad is that loss? I know this is a very general question but it seems not to make any sense not to use innodb having such exotic features like foreign keys and transactions. Maybe some of you had this dilemma in the past and can offer some insight. Thanks in advance Olaf -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
tool to parse general log (3.23.55)
all, I have been tasked with upgrading a critical 3.23.55 database to 5.0(.27-ish). short version is it's never been upgraded because authors have moved on and nobody's sure of everything that uses it. I enabled the general log a few days ago and have a good body of data with which to go code hunting but (being a DBA) would like to load this into a couple of tables for easier analysis. has anyone already invented this wheel or should I post my solution if I end up doing it myself?
Re: Easiest Way To Replicate DB
I am doing tests so I want to easy take my DB and make a full copy of it into a test db everytime I want to test something against the non-produciton version of DB. What is the easiest way to do this. So I have a DB called "backlog" and I want to copy it's structure and data into "backlog_test" with ease :-). Any sexy suggetions? :-) This is what I do... mysqldump -v --lock-tables=false backlog | mysql backlog_test Not practical if your dataset is huge, but if it's not that big, works great. -philip -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Easiest Way To Replicate DB
I am doing tests so I want to easy take my DB and make a full copy of it into a test db everytime I want to test something against the non-produciton version of DB. What is the easiest way to do this. So I have a DB called "backlog" and I want to copy it's structure and data into "backlog_test" with ease :-). Any sexy suggetions? :-) Your Friend, John -- John Kopanas [EMAIL PROTECTED] http://www.kopanas.com http://www.cusec.net http://www.soen.info -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: speeding up a join COUNT
Thanks! OK, here's where my understanding of MySQL and how indices work get fuzzy. In my scenario what would the difference between (I tested with different indices these and included the query times with the EXPLAIN outputs): (1) creating separate indices on entity_id and also on user_type - took 106 seconds (almost twice as long as before for some reason without these indices) EXPLAIN output: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE users ALL user_idx,entity_idx NULL NULL NULL 750106 Using where; Using temporary; Using filesort 1 SIMPLE geo_entitieseq_ref PRIMARY PRIMARY 4 users.entity_id 1 vs (2) creating a multicolumn index with entity_id and user_type - (as Filip has suggested) - took only 0.4 seconds, wow what a difference !!! EXPLAIN output: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE geo_entities ALL PRIMARY NULL NULL NULL 238 Using temporary; Using filesort 1 SIMPLE users ref geo_idx geo_idx 7 geo_entities.id,const 4202 Using where; Using index vs (3) with all three indices in place - took 86 seconds. EXPLAIN output: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE geo_entities ALL PRIMARY NULL NULL NULL 238 Using temporary; Using filesort 1 SIMPLE users ref user_type_idx,entity_idx,geo_idx geo_idx 7 geo_entities.id,const 4202 Using where; Using index -James On Jan 25, 2007, at 8:35 AM, Filip Krejc(í <[EMAIL PROTECTED]> Krejc> wrote: Hi, try CREATE INDEX geo_idx ON users(entity_id, user_type); Filip Alex Arul napsal(a): and also an index on users.entity_id (will help the join) should solve your problem. Thanks Alex On 1/24/07, Brent Baisley <[EMAIL PROTECTED]> wrote: You should create indexes on the fields you search on most. In this case, you are searching on the user_type field, so create an index on that field. Otherwise you need to scan the entire table to find out which users are of the type you are searching for. - Original Message - From: "James Tu" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Tuesday, January 23, 2007 12:04 PM Subject: speeding up a join COUNT > I'm performance testing my 'users' table. It currently has roughly 1M user records. The 'geo_entities' table has ~ 250 records. > > Here's my query. > > SELECT users.entity_id, geo_entities.entity_name, geo_entities.short_code, COUNT ( users.entity_id) > FROM users, geo_entities > WHERE users.user_type = 'user' > AND users.entity_id = geo_entities.id > GROUP BY entity_id > LIMIT 0 , 30 > > It took 51 seconds to execute. > > Both tables only have an index on their unique record id. > Is there a way to speed up this up? > > -James > > > -- > 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] -- Filip Krejci <[EMAIL PROTECTED]> Why use Windows, since there is a door?
Re: speeding up a join COUNT
Hi, try CREATE INDEX geo_idx ON users(entity_id, user_type); Filip Alex Arul napsal(a): and also an index on users.entity_id (will help the join) should solve your problem. Thanks Alex On 1/24/07, Brent Baisley <[EMAIL PROTECTED]> wrote: You should create indexes on the fields you search on most. In this case, you are searching on the user_type field, so create an index on that field. Otherwise you need to scan the entire table to find out which users are of the type you are searching for. - Original Message - From: "James Tu" <[EMAIL PROTECTED]> To: "MySQL List" Sent: Tuesday, January 23, 2007 12:04 PM Subject: speeding up a join COUNT > I'm performance testing my 'users' table. It currently has roughly 1M user records. The 'geo_entities' table has ~ 250 records. > > Here's my query. > > SELECT users.entity_id, geo_entities.entity_name, geo_entities.short_code, COUNT( users.entity_id) > FROM users, geo_entities > WHERE users.user_type = 'user' > AND users.entity_id = geo_entities.id > GROUP BY entity_id > LIMIT 0 , 30 > > It took 51 seconds to execute. > > Both tables only have an index on their unique record id. > Is there a way to speed up this up? > > -James > > > -- > 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] -- Filip Krejci <[EMAIL PROTECTED]> Why use Windows, since there is a door? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Two Databases
> The databases are on the same server, however the login details for each > database are different. Hmm, I guess that the currently connected user needs to have access to both databases, how else would it get the data? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Two Databases
On Thursday 25 January 2007 11:08, Neil Tompkins wrote: > The databases are on the same server, however the login details for each > database are different. > A query executes with the credentials of the authentication used to set up the connection. If you want to query two tables simultaneously across two databases, the user used to run the query will need SELECT access to both database.tables. -- Scanned by iCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Two Databases
The databases are on the same server, however the login details for each database are different. From: "Martijn Tonies" <[EMAIL PROTECTED]> To: Subject: Re: Query Two Databases Date: Thu, 25 Jan 2007 11:56:37 +0100 > Not sure if this is possible or not. But I've two identical tables in two > different databases. Is it possible to retrieve data from the different > tables in one query ? Yes, by using this notation: select * from mydatabase.mytable Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Find Love This New Year With match.com! http://msnuk.match.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Two Databases
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Neil Tompkins schrieb: > Not sure if this is possible or not. But I've two identical tables in > two different databases. Is it possible to retrieve data from the > different tables in one query ? (SELECT * FROM db1.table) UNION (SELECT * FROM db2.table) If db2 on a other mysql server, it is possible to use the federated storage engine of MySQL. - -- Nils Jünemann Database and System Administration studiVZ | Saarbrueckerstr. 38 | 10405 Berlin | phone +49-(0)30-405042715 www.studiVZ.net | fax +49-(0)30-28093887 | cell +49-(0)175-9331740 www.estudiLN.es | www.studentIX.pl | www.studiQG.fr | www.studiLN.it -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFuI4cYT5ehyp2X5ERAh/AAKCEDPXhlegrCu08jG0K3Eh/ZxIfgwCeIbZz hJRxd5b07AxIlFP8/RBKQx0= =G/4h -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Two Databases
> Not sure if this is possible or not. But I've two identical tables in two > different databases. Is it possible to retrieve data from the different > tables in one query ? Yes, by using this notation: select * from mydatabase.mytable Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Two Databases
Not sure if this is possible or not. But I've two identical tables in two different databases. Is it possible to retrieve data from the different tables in one query ? Thanks Neil _ MSN Hotmail is evolving check out the new Windows Live Mail http://ideas.live.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: low-priority-updates and innodb tables
hi, AFAIK, if we start mysqld with --low-priority-updates, it sets table updation a lower priority than the SELECT statements, irrespective of storage engines. hence it will affect the priority of the update operation. Ref: http://mysql.justdn.org/doc/refman/5.1/en/table-locking.html - Original Message - From: "Vitaliy Okulov" <[EMAIL PROTECTED]> To: Sent: Monday, January 22, 2007 7:27 PM Subject: low-priority-updates and innodb tables > Здравствуйте, mysql. > > Hi all. > I want to ask about low-priority-updates and innodb tables. Does > low-priority-updates=1 affect on priority of select or update query on > innodb type tables? > > -- > С уважением, > Vitaliy mailto:[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > >
