1

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?

  • What is the size of your indexes (show table status yourtable;) 8G is not much RAM at all for these types of operations - so without proper tuning this query will spill to disk and be IO bound. – cerd Aug 26 '15 at 19:13
  • My index_length is 1.95 GB. – thorstenthaubenwald Aug 27 '15 at 9:09
1

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
    
  • Also could consider partitioning the table based on a key. – cerd Aug 26 '15 at 19:13
  • @Jehad Keriaki Thank you for your comments. I removed the index but my queries still wont finish. Using MD5 hashes sounds like a good idea though, I will do this in the future. I also did the COUNT: It returns 8000. BTW the COUNT query took 25 sec for first time executed and 15 sec afterwards, which is also more than I was expecting. – thorstenthaubenwald Aug 30 '15 at 12:21
  • After dropping that index, create a new one on column2. There are few other tweaks to do. 1: If you don't have 255 long string to save, make the column(s) length smaller. 2: In stead of an index on column2, add it on (column2, column3) so this query runs faster: ...WHERE column2='xyz' ORDER BY column3. – Jehad Keriaki Aug 31 '15 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.