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?
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.
SELECT * from table;~ 8 sec.
SELECT * from table WHERE column2='xyz';~ 20 sec.
SELECT * from table WHERE column2='xyz' ORDER BY column1;~ 50 sec.
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?