mySQL takes forever!

[Anon]

Could anyone tell me why this query takes so long?

mysql> select school.school_id as id, school.school_name as name from school left join member_school on school.school_id=member_school.school_id where member_school.member_id=477;

RESULTS:
+------+------------------------------+
| id | name |
+------+------------------------------+
| 5048 | Auburn Bay City Western HS |
| 5562 | Auburn Bay City Western JRHS |
+------+------------------------------+
2 rows in set (<B>9 min 2.28 sec</B>)

the member_school table has:
2481 rows in set (0.88 sec)

the school table has:
1970 rows in set (1.05 sec)

I mean, 9 minutes! you gotta be kidding! what's wrong with my SQL?

[[deleted]]

Check your server, 1.5 secons is quite unacceptable for a count on 1970 rows.

Is your mysql server very busy?
Are there people inserting data into these tables?

[Anon]

Vincent,

thanks for your your quick reply. I was hoping you would answer. As far as I know, no one else is using the server at the same time and no one else is inserting data at the time. It is just a dev server (not a local machine). There is only like three people ever using it in the first place, and I doubt we're all using it simultaneously.

It is MySQL version 3.23.36.

here is a link to the phpinfo page if that answers any other questions for you:

http://dev.msboa.org/phpinfo.php3

any other ideas?

[Anon]

BTW - the 1.5 seconds was a "select * from table" kind of query, not a count...

[toma42]

do an explain on the sql to find out how the query is running through the optimizer.

Do you have an index on school_id in each table?

[Anon]

As I discovered from some comments from Vincent a few days back... I don't know much about indexing, so I doubt it. As for the "explain" bit - can you "explain" further? I don't know how to do that. :-)

thanks

[toma42]

http://www.mysql.com/doc/E/X/EXPLAIN.html

explain tells you information about how the database will process your query.

[Anon]

OK - I'm learning: here is the explain for the select I used in the first post:

+---------------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------------+------+---------------+------+---------+------+------+------------+
| school | ALL | NULL | NULL | NULL | NULL | 1970 | |
| member_school | ALL | NULL | NULL | NULL | NULL | 2481 | where used |
+---------------+------+---------------+------+---------+------+------+------------+

I'm guessing I need to set an index (or two). Advice? Is this just something I do once (at the command line) or do before each query? (sorry I sound like such a newbie, I'm not really this stupid usually) :-)

[toma42]

create index idx_member_school_id on member_school (school_id);

create index idx_school_id on school (school_id);

These are best guesses...you mileage may vary. There are some good tutorials on this site about database basics including indexes.

[Anon]

OK - now I have indices on several columns and it is faster, but I wouldn't characterize it as FAST. I was hoping someone would have further suggestions.

mysql> show index from member_school;
+---------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+---------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+---------+
| member_school | 0 | PRIMARY | 1 | member_school_id | A | 2481 | NULL | NULL | |
| member_school | 1 | idx_member_school_id | 1 | school_id | A | 2481 | NULL | NULL | |
| member_school | 1 | idx_member_id | 1 | member_id | A | 2481 | NULL | NULL | |
+---------------+------------+----------------------+--------------+------------------+-----------+-------------+----------+--------+---------+

AND...
mysql> show index from school;
+--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+---------+
| school | 0 | PRIMARY | 1 | school_id | A | 1970 | NULL | NULL | |
| school | 1 | idx_school_id | 1 | school_id | A | 1970 | NULL | NULL | |
+--------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+---------+

NETS THE FOLLOWING:
mysql> select school.school_id as id, school.school_name as name from school left join member_school on school.school_id=member_school.school_id where member_school.member_id=477;
+------+------------------------------+
| id | name |
+------+------------------------------+
| 5048 | Auburn Bay City Western HS |
| 5562 | Auburn Bay City Western JRHS |
+------+------------------------------+
2 rows in set (3.91 sec)

obviously faster than 9+ minutes, but still makes the web page load slowly. Any further suggestions?

thanks! - craig

[tasistro]

INSTEAD OF

select school.school_id as id, school.school_name as name
from school left join member_school on school.school_id=member_school.school_id
where member_school.member_id=477;

TRY

SELECT school.school_id as id, school.school_name as name FROM
member_school, school
WHERE
member_school.member_id=477
AND
member_school.school_id=school.school_id

I think in the first sql (yours) it is building the relationships first between school and member_school, a very lengthly process indeed.
Then it filters out which row has field memebr_shool.member_id as 477. I think the alternative (mine) should speed
things up since it lists the member_school table first (with which we will filter id at 477) then the shool table. It also
filters the wanted row and then joins with : member_school.school_id=school.school_id

BTW the following fields should be keys in their tables (which I understand from your previous post has already been done)
member_school.member_id
member_school.school_id
school.school_id

Let me know if it helps

Saludos
Gerardo

[[deleted]]

On a similar note, I found this snippet in the MySQL manual

http://www.mysql.com/doc/L/E/LEFT_JOIN_optimisation.html

"This gave me trouble for a while so I thought I'd pass it on. If you want a left join with a condition for the right table (the row that may or may not be there) you have to put the condition in the join condition, not the where condition.

Here's an example:

SELECT
*
FROM
t1 LEFT JOIN t2 on (t1.id_t1 = t2.id_t1 AND t2.id_t2 = 345)
WHERE
t1.id_level=17 ;"

[Anon]

interesting notes from both Gerardo and Vincent. I got the following results:

mysql> SELECT school.school_id as id, school.school_name as name FROM
-> member_school, school
-> WHERE
-> member_school.member_id=477
-> AND
-> member_school.school_id=school.school_id;
+------+------------------------------+
| id | name |
+------+------------------------------+
| 5562 | Auburn Bay City Western JRHS |
| 5048 | Auburn Bay City Western HS |
+------+------------------------------+
2 rows in set (0.14 sec)

mysql> select school.school_id as id, school.school_name as name from
-> member_school left join school on (member_school.school_id=school.school_id) where
-> member_school.member_id=477;
+------+------------------------------+
| id | name |
+------+------------------------------+
| 5562 | Auburn Bay City Western JRHS |
| 5048 | Auburn Bay City Western HS |
+------+------------------------------+
2 rows in set (0.03 sec)

as a comparison, the (indexed) original query was:

mysql> select school.school_id as id, school.school_name as name from school left join member_school on school.school_id=member_school.school_id where member_school.member_id=477;
+------+------------------------------+
| id | name |
+------+------------------------------+
| 5048 | Auburn Bay City Western HS |
| 5562 | Auburn Bay City Western JRHS |
+------+------------------------------+
2 rows in set (4.00 sec)

nice optimization guys. Now I have to figure out what you did! :-)

thanks for all the help and suggestions. Dramatic improvements!
-craig

[tasistro]

Here is what is happening:

FROM THE MYSQL MANUAL

MySQL resolves all joins using a single-sweep multi-join method. This means that MySQL reads a row from the first table, then finds a matching row in the second table, then in the third table and so on. When all tables are processed, it outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.

Craig, your SQL checks first the school table then the members table. This is a more lengthly process since probably not all schools are members and the limiting factor is on the members shool (ID=477) not on school tables. It should check members first then join. I would ask you two things:

Preceed the SQL with the EXPLAIN keyword and send us the results.

Run my sql with the where's in the followin order
WHERE
member_school.school_id=school.school_id;
AND
member_school.member_id=477

thats inverted from the previous one which follows:

mysql> SELECT school.school_id as id, school.school_name as name FROM
-> member_school, school
-> WHERE
-> member_school.member_id=477
-> AND
-> member_school.school_id=school.school_id;

I'm going to check into vincent's sql it is a very impresive improvement.

Saludos
Gerardo