0

I have two tables:

module_339 (id,name,description,etc)
module_339_schedule(id,itemid,datestart,dateend,timestart,timeend,days,recurrent)

module_339_schedule.itemid points to module_339

fist table holds conferences

second one keeps the schedules of the conferences

module_339 has 3 items

module_339_schedule has 4000+ items - almost evenly divided between the 3 conferences

I have a stored function - "getNextDate_module_339" - which will compute the "next date" for a specified conference, in order to be able to display it, and also sort by it - if the user wants to. This stored procedure will just take all the schedule entries of the specified conference and loop through them, comparing dates and times. So it will do one simple read from module_339_schedule, then loop through the items and compare dates and times.

The problem: this query is very slow:

SELECT 
distinct(module_339.id)
,min( getNextDate_module_339(module_339.id,1,false)) AS ND 
FROM 
module_339 
LEFT JOIN module_339_schedule on module_339.id=module_339_schedule.itemid /* standard schedule adding */  
WHERE 1=1 AND module_339.is_system_preview<=0 
group by 
module_339.id  
order by 
module_339.id asc

If I remove either the function call OR the LEFT JOIN, it is fast again. What am I doing wrong here? Seems to be some kind of "collision" between the function call and the left join.

Catalin
  • 773
  • 1
  • 7
  • 18

2 Answers2

1

I think the group by part can be removed from this query, thus enabling you to remove the min function as well. Also, there is not much point of WHERE 1=1 AND..., so I've changed that as well. Try this:

SELECT DISTINCT module_339.id
               ,getNextDate_module_339(module_339.id,1,false) AS ND 
FROM module_339 
LEFT JOIN module_339_schedule ON module_339.id=module_339_schedule.itemid /* standard schedule adding */  
WHERE module_339.is_system_preview<=0 
ORDER BY module_339.id

Note that this might not have a lot of impact on performance.
I think that the worst part performance-wise is probably the getNextDate_module_339 function.
If you can find a way to get it's functionallity without using a function as a sub query, your sql statement will probably run alot faster then now, with or without the left join.
If you need help doing this, please edit your question to include the function and hopefully I (or someone else) might be able to help you with that.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • This will speed things up, the only problem is that this query is a simplified version of the actual query - that illustrates the bug - and is automatically generated so I will have to check if it doesn't impact anything else in the process. Nevertheless, your answer fixes the punctual question which I've asked. So I will mark your answer as solution. Thx! – Catalin May 01 '15 at 07:01
  • Forgot to tell: the stored function is quite fast by itself, it is in this combination that everything gets painfully slow. – Catalin May 01 '15 at 07:09
0

From the MySQL reference manual:

The best way to improve the performance of SELECT operations is to create indexes on one or more of the columns that are tested in the query. The index entries act like pointers to the table rows, allowing the query to quickly determine which rows match a condition in the WHERE clause, and retrieve the other column values for those rows. All MySQL data types can be indexed.

Although it can be tempting to create an indexes for every possible column used in a query, unnecessary indexes waste space and waste time for MySQL to determine which indexes to use. Indexes also add to the cost of inserts, updates, and deletes because each index must be updated. You must find the right balance to achieve fast queries using the optimal set of indexes.

As a first step I suggest checking that the joined columns are both indexed. Since primary keys are always indexed by default, we can assume that module_339 is already indexed on the id column, so first verify that module_339_schedule is indexed on the itemid column. You can check the indexes on that table in MySQL using:

SHOW INDEX FROM module_339_schedule;

If the table does not have an index on that column, you can add one using:

CREATE INDEX itemid_index ON module_339_schedule (itemid);

That should speed up the join component of the query.

Since your query also references module_339.is_system_preview you might also consider adding an index to that column using:

CREATE INDEX is_system_preview_index ON module_339 (is_system_preview);

You might also be able to optimize the stored procedure, but you haven't included it in your question.

Community
  • 1
  • 1
jaredboone
  • 71
  • 4
  • the indexes are correctly created; the stored function is quite fast when calling it by itself – Catalin May 01 '15 at 06:46
  • You might also check to see that the character sets on your database match the character sets on your tables. See http://stackoverflow.com/a/16949603/4853273. – jaredboone May 01 '15 at 09:03