Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bobyliu
Starting Member
14 Posts |
 Posted - 2005-02-14 : 13:54:03
|
| I was doing some inner joins between some tables with MySQL. At first it was taking really long, so I had to try other ways to join the tables.so instead of usingselect c1,c2,c3 from table1 as t1inner join table2 as t2 on t2.id=t1.id... etcwhere t1.id=12345I was doing select c2, c2,c3from table1 as t1, table2 as t2, table3 as t3where t1.id=12345and t2.id=12345and t3.id=12345After doing this, the performance was boosted greatly. It was taking 0.3 second and after the change of query, it was only taking 0.07 second. I am pretty puzzled on why the second works much better. Is anybody familiar with this? |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-02-14 : 15:36:14
|
| This is more of a SQL Server site rather than a MySQL site but this answer might apply to both implementations...If you ran these two queries back to back the second one, regardless of order, should execute faster since the data has already been read in from disk and now resides in cache.But it also might be that the MySql optimizer is simply more tuned to constants in the where clause.HTH=================================================================Egotism is the anesthetic that dulls the pain of stupidity. -Frank William Leahy, football coach (1908-1973) |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-02-14 : 15:38:26
|
| With SQL Server, you want to use the ANSI joins (INNER JOIN etc) because they will soon eliminate the other type of joins. As far as MySQL, I'm not sure why one performs better than the other unless there's some caching going on like SQL server does.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
|
|
|
|
|
ways to join tables