-1

Hello everybody I have this kind of problem. I have downloaded and activated Mysql on my laptop, which is a normal notebook, and then I use a GUI and I am trying to learn how to use Mysql. I actually am at the very beginning so now I am importing tables - in csv format - and trying to run on them the same queries I used to run in ms access. The point is that Mysql on the one side seems to keep any kind of table dimension, but on the other is esxtremely slow. In fact so slow I am sure I must be doing something wrong. I have two tables, each of them of 1400000 rows and 70 columns: if I run a basic query on each of them (for instance select all rows where field A1 is '23"), then no problem. But if I run a query with a (inner?) join between two tables then it is a disaster. I begun processing such a query 6 hours ago and it's still going.....the same query, in ms access, took 15 seconds. I'm sure I must be missing something. The OS is ubuntu 12.10, the GUI is Navicat 11. Thank you

user1951561
  • 119
  • 1
  • 1
  • 9
  • Can you post a sample of your data and the query you were using. It sounds like something might be wrong with your query. You can also use EXPLAIN before your query to see where it might be going wrong. – 79IT May 25 '13 at 21:17
  • prefix the query with Explain as in Explain Select * From Table, it will tell you how mysql is processing the query. Add the output to your question. You have added the primary keys and indexes in mysql? – Tony Hopkinson May 25 '13 at 21:19
  • This that follows is the query, the GUI tells me that the query is processing. My table is not indexed and i think has no primary key (no field selected as primary key) thank you; the fields are varchar except two that are date; here follows the query: [SQL]SELECT `CCA 1`.`RAPPORTI TIPO INFO 1`.A03, `CCA 1`.`RAPPORTI TIPO INFO 2`.A03 FROM `CCA 1`.`RAPPORTI TIPO INFO 1` INNER JOIN `CCA 1`.`RAPPORTI TIPO INFO 2` ON `CCA 1`.`RAPPORTI TIPO INFO 1`.A03 = `CCA 1`.`RAPPORTI TIPO INFO 2`.A03 – user1951561 May 25 '13 at 21:28
  • i've this kind of experience, its caused of installing 32 bit mysql version on 64 bit machine, its extremely slow. after changing to 64 bit version of mysql its faster. don't know if this work for you though – dandice May 25 '13 at 22:05
  • well fear not about mysql. if you have adequate memory, perhaps a ssd drive someday, configured properly, and index that are not too much overkill but assist the common access paths, then u will be impressed. i have a few clients with over 500M rows. of course we fine turn the sql but that will follow for you – Drew May 25 '13 at 22:56

1 Answers1

1

MySQL basically only supports two joins algorithms -- index seeks and nested loop. The nested loop basically means that MySQL reads one row from the first table, then all the rows from the second table. Then another row from the first table, and all the rows from the second table. And so on. (Actually, the algorithm does blocked reads so it is not quite that bad, but you get the idea.)

Nested loop is a pretty poor choice when you have large tables. Other databases support things like hash join algorithms and merge-join algorithms, but these aren't an option.

I am guessing that your two tables are too large to fit into memory. This is causing a worst-case performance, because the SQL engine essentially has to read all the records from disk for each row (or batch of rows) from the first table.

You can fix this by building indexes on the columns. This should greatly improve performance.

You can also investigate the memory parameters. The documentation is here. Be sure that you are allocating enough memory to sql engine.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786