That is exactly what I was worried about. He could try restructuring the data, increasing the timeouts to compensate for the long run time, or find better hardware. Any other ideas?
From: w...@pythian.com [mailto:w...@pythian.com] On Behalf Of Singer X.J. Wang Sent: Thursday, February 03, 2011 7:20 PM To: Yannis Haralambous Cc: David Brian Chait; mysql@lists.mysql.com; y...@mpi-inf.mpg.de Subject: Re: How do increase memory allocated to MySQL? You're query is doing a full table scan. 2011/2/3 Yannis Haralambous <yannis.haralamb...@telecom-bretagne.eu<mailto:yannis.haralamb...@telecom-bretagne.eu>> just a single table with an ID field (primary index) and three data fields (indexed): id bigint(20) NO PRI NULL auto_increment yagoid varchar(32) NO UNI NULL yagoleft varchar(32) NO MUL NULL yakoright varchar(255) NO NULL what am I doing wrong? the query was just SELECT * FROM wasfoundin WHERE yakoright LIKE '%geography%' Le 4 févr. 2011 à 11:14, David Brian Chait a écrit : > Yannis, > How is the data structured? Can you give us an example of the queries > that you are trying to run? Do you have indexes in place? A very inefficient > query, or poorly structured database can lead to this type of timeout issue > on the type of low end hardware that you are using. > > Thanks, > David > > -----Original Message----- > From: Yannis Haralambous > [mailto:yannis.haralamb...@telecom-bretagne.eu<mailto:yannis.haralamb...@telecom-bretagne.eu>] > Sent: Thursday, February 03, 2011 5:43 PM > To: mysql@lists.mysql.com<mailto:mysql@lists.mysql.com> > Cc: y...@mpi-inf.mpg.de<mailto:y...@mpi-inf.mpg.de> > Subject: How do increase memory allocated to MySQL? > > Hi everybody, > > I have loaded a very big amount of data in my MySQL database (coming from the > YAGO project): > > -rw-rw---- 1 yannis admin 65 3 fév 16:07 db.opt > -rw-rw---- 1 yannis admin 6392030392 3 fév 21:35 wasfoundin.MYD > -rw-rw---- 1 yannis admin 11085793280 4 fév 04:54 wasfoundin.MYI > -rw-rw---- 1 yannis admin 8668 3 fév 16:09 wasfoundin.frm > > as you can see the MYI file is 11Gb and the MYD file 6.4Gb. > > Whenever I try to send a query through phpmyadmin, it crashes and I get > returned to the initial page of phpmyadmin. > When I send an SQL query directly, it works, but it takes many minutes (a > simple SELECT will take about 10 minutes). > > I'm using the latest version of MAMP under MacOS X (on a MacBook Pro 2.6 GHz > Intel Core 2 with 4Gb of RAM). > > What can I do to make phpmyadmin work? Is it a chance to gain more speed when > interacting through regular SQL queries, or is it hopeless? > Where can I find more information about using MySQL with such big tables? > > Thanks in advance > > -- > ------------------------------------------------------- > Yannis Haralambous > Directeur d'études > ADRESSE TEMPORAIRE : > University of Aizu > Aizu-Wakamatsu, Fukushima-ken 965-8580, Japon ADRESSE PERMANENTE : > Institut Télécom, Télécom Bretagne > Département Informatique > UMR CNRS 3192 Lab-STICC > Technopôle Brest Iroise > CS 83818, 29238 Brest Cedex 3, France > Tel: +33 2 29 00 14 27 > Fax: +33 2 29 00 12 82 > Email: > yannis.haralamb...@telecom-bretagne.eu<mailto:yannis.haralamb...@telecom-bretagne.eu> > Internet: http://omega.enstb.org/yannis > ICBM address: 48°21'31.57"N 4°34'16.76"W > Twitter: y_haralambous > ------------------------------------------------------- > ...pour distinguer l'extérieur d'un aquarium, mieux vaut n'être pas poisson > > ...the ball I threw while playing in the park has not yet reached the ground > > Es gab eine Zeit, wo ich nur ungern über Schubert sprechen, nur Nächtens den > Bäumen und Sternen von ihm vorerzählen mögen. > -- ------------------------------------------------------- Yannis Haralambous Directeur d'études ADRESSE TEMPORAIRE : University of Aizu Aizu-Wakamatsu, Fukushima-ken 965-8580, Japon ADRESSE PERMANENTE : Institut Télécom, Télécom Bretagne Département Informatique UMR CNRS 3192 Lab-STICC Technopôle Brest Iroise CS 83818, 29238 Brest Cedex 3, France Tel: +33 2 29 00 14 27 Fax: +33 2 29 00 12 82 Email: yannis.haralamb...@telecom-bretagne.eu<mailto:yannis.haralamb...@telecom-bretagne.eu> Internet: http://omega.enstb.org/yannis ICBM address: 48°21'31.57"N 4°34'16.76"W Twitter: y_haralambous ------------------------------------------------------- ...pour distinguer l'extérieur d'un aquarium, mieux vaut n'être pas poisson ...the ball I threw while playing in the park has not yet reached the ground Es gab eine Zeit, wo ich nur ungern über Schubert sprechen, nur Nächtens den Bäumen und Sternen von ihm vorerzählen mögen. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=w...@singerwang.com -- The best compliment you could give Pythian for our service is a referral.
