-1

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?

  • 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
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.

  • 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
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.

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.