MySQL queries taking very long/not finishing

MySQLmysql-5.6

I just started working with databases and have a problem with queries taking very long.

My table has the following form

CREATE TABLE table (column1 INT(10) AUTO_INCREMENT PRIMARY KEY, 
column2 VARCHAR(255) NOT NULL, 
column3 VARCHAR(255) NOT NULL, 
column4 VARCHAR(255) NOT NULL, 
column5 VARCHAR(255) NOT NULL, 
column6 VARCHAR(255) NOT NULL);

ALTER TABLE table ADD UNIQUE entry (column2, column3, column4, column5); 
ALTER TABLE table CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

I have inserted about 10 million rows.

Simple queries including a cartesian product like

SELECT a.column1, b.column1
FROM table AS a, table AS b
WHERE a.column2 = ''
AND NOT a.column3 = b.column3

take about five minutes before the results start showing.
A more complex one which includes ordering by multiple columns runs about half an hour before it eventually causes my system to freeze. There are no other concurrent database operations.

I'm running MySQL 5.6.25 on Ubuntu Vivid and didn't change any settings. My machine is a 6x3GHz Phenom II with 8 GB RAM.

Is there a problem with my table/query? Are there any default setting I should change?

edit

The total size of my data is 1.25 GB.

I tested a few more queries.
Before, I set innodb_buffer_pool_size=4G and removed the UNIQUE CONSTRAINT.

  1. SELECT * from table; ~ 8 sec.
  2. SELECT * from table WHERE column2='xyz'; ~ 20 sec.
  3. SELECT * from table WHERE column2='xyz' ORDER BY column1; ~ 50 sec.
  4. SELECT * from table WHERE column2='xyz' ORDER BY column1, column3; > 3 min.

I noticed that a temporary file is created, which is larger than 10 GB for query 4.
When doing queries with cartesian products, my system freezes after a few minutes and this file is never created (as my disk space remains constant).

This is my first real-life database project and I don't know what performance I can expect. Do I simply need different hardware for this amount of data?

Best Answer

Few points here:

  1. "Cartesian product" is not simple :)
  2. In your specific query, and based on the index you have, the time will highly depend on the distribution of the data. Try this query: SELECT COUNT(*) FROM table WHERE column2=''; I guess the result is not a small number.
  3. UNIQUE entry (column2, column3, column4, column5); is not healthy. The index is very big. It affects both, reads and writes. 'Reads' because it has to be loaded into the memory when it is used, and 'Writes' because it has to be checked (and maybe updated) whenever a DML operation is executed. If this uniqueness is a must, I suggest adding an MD5 hash for all the fields, and have that field uniquely indexed.

  4. To get smaller result, avoiding repetition, you may add WHERE... AND a.column1<b.column1. This will avoid you having:

    1, 4
4, 1
Related Question