mysql performance

[Anon]

Hi,

I have three table joined to retrieve data using mysql. Tested Code like

SELECT Test.name1, Exper.exper1, Try.try1, Try.try2 FROM Test LEFTt JOIN Exper ON Test.name2 = Exper.exper2.ChipID LEFT JOIN Try ON Test.name3 = Try.try3 WHERE Test.test1="HGUA11" AND Exper.exper1>"1000";

Speed is very slow, I didnt wait for result, then aborted it.

In realistic condition, I may select much more columns than aboved shew.

Have any suggestions? where is my problems?

[[deleted]]

Did you read the MySQL manual about how queries are optimized?
You should put the most limiting clause first. That means, putting the 'Exper.exper1>"1000"' directly after the WHERE, because that one cuts out 1000 records, then put the 'Test.test1="HGUA11"'.

Next you should make sure you have proper indexes on the tables, so the JOIN can use indexes properly.

Does your database server have enough capacity to deal with your queries?
If you have 128MB memory on a shared database server, and you want to do a JOIN across a few million records.... ouch...
Also, remember that once a query is started, it will run untill it is completed or the databaseserver is given a firm kick in the butt.
Simply pressing CTRL-C or 'STOP' in the browser doesn't stop the query.
In other words, once you started a big query and pressed CTRL-C, the next query can be even slower because the server is still busy, even though it appears that the previous query was stopped. That can be tricky sometimes.

[Anon]

Vincent gave great advice. The other thing is do you need to do a LEFT JOIN. That's substantially slower than a INNER JOIN.

John

[Anon]

Hi,

Thanks for all your helps. My mysql (1.3.19) seems to not allow to do inner join. Initially, I used inner join, but I couldn't retrieve data from database. Then I tried to use left join, then works.

THere are some reasons?