Ygthkb

Does diversity provide anything that meritocracy does not?

What are some ways of extending a description of a scenery?

What to do with threats of blacklisting?

How long has this character been impersonating a Starfleet Officer?

Why is 'diphthong' not pronounced otherwise?

Modern Algebraic Geometry and Analytic Number Theory

Equivalent of "illegal" for violating civil law

Case protection with emphasis in biblatex

How to fly a direct entry holding pattern when approaching from an awkward angle?

What is the draw frequency for 3 consecutive games (same players; amateur level)?

How to change a .eps figure to standalone class?

How do dictionaries source attestation?

How can I give a Ranger advantage on a check due to Favored Enemy without spoiling the story for the player?

Coombinatorics- The number of ways of choosing with parameters

Taking an academic pseudonym?

Is there a non trivial covering of the Klein bottle by the Klein bottle

Context html export bibliography

What is a good way to explain how a character can produce flames from their body?

Coworker asking me to not bring cakes due to self control issue. What should I do?

Is there a way to pause a running process on Linux systems and resume later?

Is there any way to make an Apex method parameter lazy?

How to extract specific values/fields from the text file?

Reading Mishnayos without understanding

Crack the bank account's password!

MySQL Image Queries

MySQL optimization - year column grouping - using temporary table, filesortis this dead simple query too slow?MySQL update one table from another joined many-to-manySubquery optimizationMYSQL Select, where rows existsSlow CREATE TEMPORARY TABLE from SELECT in MySQLMySQL COUNT(*) performanceWhy does my query hang when I put one more id in where statement?Pagination: Slow ORDER BY with large LIMIT rangeAdding a join to a table in a query makes it super slow

0

I have table employees in MySql database with Id, Name, Gender, HireDate, Photo. Photo field is blob.

Now when I run this query:

SELECT id FROM employees WHERE Photo IS NULL ;

it takes forever to return query result. I have 300,000 records and there are around 100,000 records without photo and also I tried putting limit doesn't help. Is there anyway I can optimize this query to return the result quicker?

mysql performance

share|improve this question

edited Feb 2 '15 at 21:33

frlan

375325

asked Feb 2 '15 at 16:43

AsheshAshesh

1

bumped to the homepage by Community♦ 4 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  will you please add the following info, server specification, MySQL version,my.cnf file, and if the server is shared or dedicated
  
  – Ahmad Abuhasna
  Feb 2 '15 at 17:28
  

  
  
  
  

add a comment | 

0

I have table employees in MySql database with Id, Name, Gender, HireDate, Photo. Photo field is blob.

Now when I run this query:

SELECT id FROM employees WHERE Photo IS NULL ;

it takes forever to return query result. I have 300,000 records and there are around 100,000 records without photo and also I tried putting limit doesn't help. Is there anyway I can optimize this query to return the result quicker?

mysql performance

share|improve this question

edited Feb 2 '15 at 21:33

frlan

375325

asked Feb 2 '15 at 16:43

AsheshAshesh

1

bumped to the homepage by Community♦ 4 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  will you please add the following info, server specification, MySQL version,my.cnf file, and if the server is shared or dedicated
  
  – Ahmad Abuhasna
  Feb 2 '15 at 17:28
  

  
  
  
  

add a comment | 

I have table employees in MySql database with Id, Name, Gender, HireDate, Photo. Photo field is blob.

Now when I run this query:

SELECT id FROM employees WHERE Photo IS NULL ;

it takes forever to return query result. I have 300,000 records and there are around 100,000 records without photo and also I tried putting limit doesn't help. Is there anyway I can optimize this query to return the result quicker?

mysql performance

share|improve this question

edited Feb 2 '15 at 21:33

frlan

375325

asked Feb 2 '15 at 16:43

AsheshAshesh

1

SELECT id FROM employees WHERE Photo IS NULL ;

share|improve this question

edited Feb 2 '15 at 21:33

frlan

375325

asked Feb 2 '15 at 16:43

AsheshAshesh

1

share|improve this question

edited Feb 2 '15 at 21:33

frlan

375325

asked Feb 2 '15 at 16:43

AsheshAshesh

1

edited Feb 2 '15 at 21:33

frlan

375325

edited Feb 2 '15 at 21:33

frlan

375325

asked Feb 2 '15 at 16:43

AsheshAshesh

1

asked Feb 2 '15 at 16:43

AsheshAshesh

1

2 Answers
2

active

oldest

votes

0

You could split the table into two, moving the Photo column into the new one. The new table would need only 2 columns (id, photo) and 200K rows.

CREATE TABLE employees_with_photo

( employee_id INT NOT NULL,        -- or whatever the type of employees.id

  photo BLOB,

  CONSTRAINT employees_with_photos_pk

    PRIMARY KEY (employee_id),

  CONSTRAINT employees__employees_with_photo_fk

    FOREIGN KEY (employee_id)

    REFERENCES employees (id)

) ;

Populate it (this will take some time so it can be done in batches, during a weekend or a downtime, etc. It's going to be done once anyway):

INSERT INTO employees_with_photo

  (employee_id, photo)

SELECT 

  id, photo

FROM

  employees

WHERE

  photo IS NOT NULL ;

The original query would then become like below (and would use indexes on the column_to_use_for_order and the primary key (id) columns):

SELECT e.id 

FROM employees AS e

WHERE NOT EXISTS

      ( SELECT 1

        FROM employees_with_photo AS p

        WHERE p.employee_id = e.id 

      ) 

ORDER BY e.column_to_use_for_order

LIMIT @number_of_rows_wanted ;

You would also have to ensure that any insert (also update, delete) operations on the original table, now takes both tables into consideration.

Then adjust select queries that need the photo column to use the new table and not the first one. No query should use the old employees.photo column.

Then you can drop the photo column from the employees table and you are done.

Side note: Why did you name the column id? It's more useful to name it employee_id or EmployeeID.

share|improve this answer

edited Feb 2 '15 at 18:29

answered Feb 2 '15 at 17:35

ypercubeᵀᴹypercubeᵀᴹ

76.9k11134214

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you for your suggestion but I cannot change the table. All i can change is this statement "SELECT id FROM employees WHERE Photo IS NULL ;"
  
  – Ashesh
  Feb 3 '15 at 14:06
  
  
  

  
  
  
  

add a comment | 

0

It cannot be sped up. Look at how much disk space the table uses (several GB, I expect). The query has to read the entire table. That's a lot of I/O.

Now, let's go back to the purpose of the query. What is the purpose? What would you do with 100K ids? If you need to do something about each one, then it is easy to devise a way to "find the next id with a missing photo after the one I just worked on". This would be better than doing a LIMIT from the start.

WHERE photo IS NULL AND id > ... ORDER BY id LIMIT 1

It would still take a long time to get through the entire table, but you would have done all the processing in the meantime. And, depending on the distribution of the missing photos, sometimes the above code would return very quickly, sometimes very slowly.

share|improve this answer

answered Feb 10 '15 at 6:22

Rick JamesRick James

42.9k22259

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f90824%2fmysql-image-queries%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

You could split the table into two, moving the Photo column into the new one. The new table would need only 2 columns (id, photo) and 200K rows.

CREATE TABLE employees_with_photo

( employee_id INT NOT NULL,        -- or whatever the type of employees.id

  photo BLOB,

  CONSTRAINT employees_with_photos_pk

    PRIMARY KEY (employee_id),

  CONSTRAINT employees__employees_with_photo_fk

    FOREIGN KEY (employee_id)

    REFERENCES employees (id)

) ;

Populate it (this will take some time so it can be done in batches, during a weekend or a downtime, etc. It's going to be done once anyway):

INSERT INTO employees_with_photo

  (employee_id, photo)

SELECT 

  id, photo

FROM

  employees

WHERE

  photo IS NOT NULL ;

The original query would then become like below (and would use indexes on the column_to_use_for_order and the primary key (id) columns):

SELECT e.id 

FROM employees AS e

WHERE NOT EXISTS

      ( SELECT 1

        FROM employees_with_photo AS p

        WHERE p.employee_id = e.id 

      ) 

ORDER BY e.column_to_use_for_order

LIMIT @number_of_rows_wanted ;

You would also have to ensure that any insert (also update, delete) operations on the original table, now takes both tables into consideration.

Then adjust select queries that need the photo column to use the new table and not the first one. No query should use the old employees.photo column.

Then you can drop the photo column from the employees table and you are done.

Side note: Why did you name the column id? It's more useful to name it employee_id or EmployeeID.

share|improve this answer

edited Feb 2 '15 at 18:29

answered Feb 2 '15 at 17:35

ypercubeᵀᴹypercubeᵀᴹ

76.9k11134214

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you for your suggestion but I cannot change the table. All i can change is this statement "SELECT id FROM employees WHERE Photo IS NULL ;"
  
  – Ashesh
  Feb 3 '15 at 14:06
  
  
  

  
  
  
  

add a comment | 

0

You could split the table into two, moving the Photo column into the new one. The new table would need only 2 columns (id, photo) and 200K rows.

CREATE TABLE employees_with_photo

( employee_id INT NOT NULL,        -- or whatever the type of employees.id

  photo BLOB,

  CONSTRAINT employees_with_photos_pk

    PRIMARY KEY (employee_id),

  CONSTRAINT employees__employees_with_photo_fk

    FOREIGN KEY (employee_id)

    REFERENCES employees (id)

) ;

Populate it (this will take some time so it can be done in batches, during a weekend or a downtime, etc. It's going to be done once anyway):

INSERT INTO employees_with_photo

  (employee_id, photo)

SELECT 

  id, photo

FROM

  employees

WHERE

  photo IS NOT NULL ;

The original query would then become like below (and would use indexes on the column_to_use_for_order and the primary key (id) columns):

SELECT e.id 

FROM employees AS e

WHERE NOT EXISTS

      ( SELECT 1

        FROM employees_with_photo AS p

        WHERE p.employee_id = e.id 

      ) 

ORDER BY e.column_to_use_for_order

LIMIT @number_of_rows_wanted ;

You would also have to ensure that any insert (also update, delete) operations on the original table, now takes both tables into consideration.

Then adjust select queries that need the photo column to use the new table and not the first one. No query should use the old employees.photo column.

Then you can drop the photo column from the employees table and you are done.

Side note: Why did you name the column id? It's more useful to name it employee_id or EmployeeID.

share|improve this answer

edited Feb 2 '15 at 18:29

answered Feb 2 '15 at 17:35

ypercubeᵀᴹypercubeᵀᴹ

76.9k11134214

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you for your suggestion but I cannot change the table. All i can change is this statement "SELECT id FROM employees WHERE Photo IS NULL ;"
  
  – Ashesh
  Feb 3 '15 at 14:06
  
  
  

  
  
  
  

add a comment | 

You could split the table into two, moving the Photo column into the new one. The new table would need only 2 columns (id, photo) and 200K rows.

CREATE TABLE employees_with_photo

( employee_id INT NOT NULL,        -- or whatever the type of employees.id

  photo BLOB,

  CONSTRAINT employees_with_photos_pk

    PRIMARY KEY (employee_id),

  CONSTRAINT employees__employees_with_photo_fk

    FOREIGN KEY (employee_id)

    REFERENCES employees (id)

) ;

Populate it (this will take some time so it can be done in batches, during a weekend or a downtime, etc. It's going to be done once anyway):

INSERT INTO employees_with_photo

  (employee_id, photo)

SELECT 

  id, photo

FROM

  employees

WHERE

  photo IS NOT NULL ;

The original query would then become like below (and would use indexes on the column_to_use_for_order and the primary key (id) columns):

SELECT e.id 

FROM employees AS e

WHERE NOT EXISTS

      ( SELECT 1

        FROM employees_with_photo AS p

        WHERE p.employee_id = e.id 

      ) 

ORDER BY e.column_to_use_for_order

LIMIT @number_of_rows_wanted ;

You would also have to ensure that any insert (also update, delete) operations on the original table, now takes both tables into consideration.

Then adjust select queries that need the photo column to use the new table and not the first one. No query should use the old employees.photo column.

Then you can drop the photo column from the employees table and you are done.

Side note: Why did you name the column id? It's more useful to name it employee_id or EmployeeID.

share|improve this answer

edited Feb 2 '15 at 18:29

answered Feb 2 '15 at 17:35

ypercubeᵀᴹypercubeᵀᴹ

76.9k11134214

CREATE TABLE employees_with_photo

( employee_id INT NOT NULL,        -- or whatever the type of employees.id

  photo BLOB,

  CONSTRAINT employees_with_photos_pk

    PRIMARY KEY (employee_id),

  CONSTRAINT employees__employees_with_photo_fk

    FOREIGN KEY (employee_id)

    REFERENCES employees (id)

) ;

INSERT INTO employees_with_photo

  (employee_id, photo)

SELECT 

  id, photo

FROM

  employees

WHERE

  photo IS NOT NULL ;

SELECT e.id 

FROM employees AS e

WHERE NOT EXISTS

      ( SELECT 1

        FROM employees_with_photo AS p

        WHERE p.employee_id = e.id 

      ) 

ORDER BY e.column_to_use_for_order

LIMIT @number_of_rows_wanted ;

share|improve this answer

edited Feb 2 '15 at 18:29

answered Feb 2 '15 at 17:35

ypercubeᵀᴹypercubeᵀᴹ

76.9k11134214

answered Feb 2 '15 at 17:35

ypercubeᵀᴹypercubeᵀᴹ

76.9k11134214

answered Feb 2 '15 at 17:35

ypercubeᵀᴹypercubeᵀᴹ

76.9k11134214

0

It cannot be sped up. Look at how much disk space the table uses (several GB, I expect). The query has to read the entire table. That's a lot of I/O.

Now, let's go back to the purpose of the query. What is the purpose? What would you do with 100K ids? If you need to do something about each one, then it is easy to devise a way to "find the next id with a missing photo after the one I just worked on". This would be better than doing a LIMIT from the start.

WHERE photo IS NULL AND id > ... ORDER BY id LIMIT 1

It would still take a long time to get through the entire table, but you would have done all the processing in the meantime. And, depending on the distribution of the missing photos, sometimes the above code would return very quickly, sometimes very slowly.

share|improve this answer

answered Feb 10 '15 at 6:22

Rick JamesRick James

42.9k22259

add a comment | 

0

It cannot be sped up. Look at how much disk space the table uses (several GB, I expect). The query has to read the entire table. That's a lot of I/O.

Now, let's go back to the purpose of the query. What is the purpose? What would you do with 100K ids? If you need to do something about each one, then it is easy to devise a way to "find the next id with a missing photo after the one I just worked on". This would be better than doing a LIMIT from the start.

WHERE photo IS NULL AND id > ... ORDER BY id LIMIT 1

It would still take a long time to get through the entire table, but you would have done all the processing in the meantime. And, depending on the distribution of the missing photos, sometimes the above code would return very quickly, sometimes very slowly.

share|improve this answer

answered Feb 10 '15 at 6:22

Rick JamesRick James

42.9k22259

add a comment | 

It cannot be sped up. Look at how much disk space the table uses (several GB, I expect). The query has to read the entire table. That's a lot of I/O.

Now, let's go back to the purpose of the query. What is the purpose? What would you do with 100K ids? If you need to do something about each one, then it is easy to devise a way to "find the next id with a missing photo after the one I just worked on". This would be better than doing a LIMIT from the start.

WHERE photo IS NULL AND id > ... ORDER BY id LIMIT 1

It would still take a long time to get through the entire table, but you would have done all the processing in the meantime. And, depending on the distribution of the missing photos, sometimes the above code would return very quickly, sometimes very slowly.

share|improve this answer

answered Feb 10 '15 at 6:22

Rick JamesRick James

42.9k22259

WHERE photo IS NULL AND id > ... ORDER BY id LIMIT 1

share|improve this answer

answered Feb 10 '15 at 6:22

Rick JamesRick James

42.9k22259

answered Feb 10 '15 at 6:22

Rick JamesRick James

42.9k22259

answered Feb 10 '15 at 6:22

Rick JamesRick James

42.9k22259