RE: Severe performace problem linking tables with mysql
Have you tried optimizing your query? It is more likely that the problem is a poorly optimized query and/or poorly tuned server than it is mySQL. Joins will be slow if you do not take the time to figure out the best way to do what you are trying to accomplish. Forcing the table order can really speed up joins as can making sure the tables are properly indexed and that the indices are being used by the queries. You also might want to think about tuning your server variables to achieve optimum performance. The easiest place to start is running an explain on your query to see what you can change to make it quicker. -Original Message- From: Joseph Dietz [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 10:40 AM To: [EMAIL PROTECTED] Subject: Severe performace problem linking tables with mysql PLATFORM: 3.23.52-max-nt with Windows 2000 professional (default table type) I have discovered a performace issue when joining several tables together. The performance is extremely poor when performing select queries using the WHERE clause and joining the tables with the pk_media_id = fk_media_id etc... I guess this is what people might think about when considering using mysql. With SQL 2000 Windows Server, and ACCESS, there was no loss in performace. 4 of my tables are cross reference tables as such: pk=primary key, fk= foreign key MediasMediaAuthorsAuthors pk_media_id fk_media_id, fk_author_id pk_author_id (Many authors for each media) - 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: Severe performace problem linking tables with mysql
Joseph Dietz wrote: MediasMediaAuthorsAuthors pk_media_id fk_media_id, fk_author_id pk_author_id In your table definition, is there an index on each of your keys shown above? What kind of performance 'degredation'? How many values are you checking? What WHERE or ORDER BY clauses are you using (try with none to test)? If possibly, pass along the original query that doesn't work as quickly as expected. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - 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: Severe performace problem linking tables with mysql
Hi. On Wed 2002-12-18 at 18:40:04 +, [EMAIL PROTECTED] wrote: PLATFORM: 3.23.52-max-nt with Windows 2000 professional (default table type) I have discovered a performace issue when joining several tables together. The performance is extremely poor when performing select queries using the WHERE clause and joining the tables with the pk_media_id = fk_media_id etc... I guess this is what people might think about when considering using mysql. With SQL 2000 Windows Server, and ACCESS, there was no loss in performace. 4 of my tables are cross reference tables as such: Access? You aren't serious, are you? If you really experience Microsoft Access being faster than MySQL, you are doing something *seriously* wrong. Probably keys missing or not used for whatever reason. Please post the result of EXPLAIN for the query in question and the result of SHOW INDEX. Also SHOW CREATE TABLE, if posting that information is OK with you. And whatever information you consider to be of interest. Regards, Benjamin. -- [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: Severe performace problem linking tables with mysql
+-++ +-+-+-+--+-- ---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-++ +-+-+-+--+-- ---+ | Macromolecules | const | PRIMARY,pk_macromolecule_id,i_macromolecule_id | PRIMARY | 8 | const |1 | Using temporary | | Authors | ALL| PRIMARY,pk_author_id,i_author_id | NULL|NULL | NULL|2 | | | Tissues | ALL| PRIMARY,pk_tissue_id,i_tissue_id | NULL|NULL | NULL|2 | | | MediaCells | index | fk_media_id,i_mediacell_id | fk_media_id | 16 | NULL|2 | Using index | | Medias | eq_ref | PRIMARY,pk_media_id,i_media_id | PRIMARY | 8 | MediaCells.fk_media_id |1 | | | MediaTissues| eq_ref | fk_media_id,i_mediatissue_id | fk_media_id | 16 | Medias.pk_media_id,Tissues.pk_tissue_id |1 | Using index | | MediaMacromolecules | eq_ref | fk_media_id,i_mediamacromolecule_id | fk_media_id | 16 | Medias.pk_media_id,const|1 | Using index | | MediaAuthors| eq_ref | fk_media_id,i_mediaauthor_id | fk_media_id | 12 | Medias.pk_media_id,Authors.pk_author_id |1 | Using index | | Cells | eq_ref | PRIMARY,pk_cell_id,i_cell_id | PRIMARY | 8 | MediaCells.fk_cell_id |1 | | | MediaOrganelles | index | fk_media_id,i_mediaorganelle_id | fk_media_id | 16 | NULL|2 | where used; Using index | | Organelles | eq_ref | PRIMARY,pk_organelle_id,i_organelle_id | PRIMARY | 8 | MediaOrganelles.fk_organelle_id |1 | | | Organisms | eq_ref | PRIMARY,pk_organism_id,i_organism_id | PRIMARY | 4 | Medias.fk_organism_id |1 | | | Techniques | eq_ref | PRIMARY,pk_technique_id,i_technique_id | PRIMARY | 4 | Medias.fk_technique_id |1 | Using index; Distinct | | Admin | eq_ref | PRIMARY,pk_admin_id,i_admin_id | PRIMARY | 4 | Medias.fk_admin_id |1 | Using index; Distinct | +-++ +-+-+-+--+-- ---+ 14 rows in set (22.61 sec) From: Benjamin Pflugmann [EMAIL PROTECTED] To: Joseph Dietz [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: Severe performace problem linking tables with mysql Date: Wed, 18 Dec 2002 22:22:00 +0100 Hi. On Wed 2002-12-18 at 18:40:04 +, [EMAIL PROTECTED] wrote: PLATFORM: 3.23.52-max-nt with Windows 2000 professional (default table type) I have discovered a performace issue when joining several tables together. The performance is extremely poor when performing select queries using the WHERE clause and joining the tables with the pk_media_id = fk_media_id etc... I guess this is what people might think about when considering using mysql. With SQL 2000 Windows Server, and ACCESS, there was no loss in performace. 4 of my tables are cross reference tables as such: Access? You aren't serious, are you? If you really experience Microsoft Access being faster than MySQL, you are doing something *seriously* wrong. Probably keys missing or not used for whatever reason. Please post the result of EXPLAIN for the query in question and the result of SHOW INDEX. Also SHOW CREATE TABLE, if posting that information is OK with you. And whatever information you consider to be of interest. Regards, Benjamin. -- [EMAIL PROTECTED] _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 - 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: Severe performace problem linking tables with mysql
Joseph, I've noticed this as well. MySQL seems to do most types of queries extremely well, but CERTAIN joins are very slow. I ended up having to denormalize my data structures somewhat in order to maintain good performance with MySQL. What I don't know, since I do not have access to a Linux box, is whether the problem is OS related. In any case, I found that I couldn't create data structures in the same way as on MSSQL. HTH, Dan Cumpian -Original Message- From: Joseph Dietz [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 1:40 PM To: [EMAIL PROTECTED] Subject: Severe performace problem linking tables with mysql PLATFORM: 3.23.52-max-nt with Windows 2000 professional (default table type) I have discovered a performace issue when joining several tables together. The performance is extremely poor when performing select queries using the WHERE clause and joining the tables with the pk_media_id = fk_media_id etc... I guess this is what people might think about when considering using mysql. With SQL 2000 Windows Server, and ACCESS, there was no loss in performace. 4 of my tables are cross reference tables as such: pk=primary key, fk= foreign key MediasMediaAuthorsAuthors pk_media_id fk_media_id, fk_author_id pk_author_id (Many authors for each media) _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus - 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
