Jump to content


Photo

how to optimize queries in general?


  • Please log in to reply
5 replies to this topic

#1 Alien

Alien
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 31 October 2003 - 06:33 AM

I\'m curious how people optimize their select statements if they are complex. MySQL 4.0 doesn\'t support nested queries(4.1 supports, but it is an alpha version), so many complex queries have to be broken down with some iterations in php scripts.

At the beginning I wrote a nested query and thought that it worked, after I ran it...I remembered that it was not a PostgreSQL DB....so I broke down the query into 3 queries with 2 loops.

However, the tables are too big(one has 200,000 rows, another has 20,000 rows, the last one has 6,000 rows), so the process takes about 2 hrs to complete on a LAN.

I just want to know, in general, how can I optimize select statements?

#2 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 31 October 2003 - 07:29 AM

Have a look at this

http://mysql.progen....QL_indexes.html

http://www.databasej...10897_1382791_4

Hope that helps!
Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#3 Alien

Alien
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 31 October 2003 - 08:18 AM

Have a look at this

http://mysql.progen....QL_indexes.html

http://www.databasej...10897_1382791_4

Hope that helps!


yea...indexing will help...but for each insert/delete, I have to pay for it since the index tree changes everytime I insert/delete. I am not sure whether it is worth...I will check with my database schema. Thanks.

#4 Kriek

Kriek
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFlorida

Posted 31 October 2003 - 12:37 PM

See -> ADOdb -> Making MySQL Efficient Tutorial
Jon Kriek · PHP Freaks
Sometimes the need to mess with their heads ...
... outweighs the millstone of humiliation.


#5 Alien

Alien
  • Members
  • Pip
  • Newbie
  • 9 posts

Posted 03 November 2003 - 07:43 AM

See -> ADOdb -> Making MySQL Efficient Tutorial


briefly scheme through it...
looks like a cool lib...will try out soon
thanks =)

#6 Kriek

Kriek
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFlorida

Posted 04 November 2003 - 02:33 AM

Alien, no problem, and glad I could be of assistance to you.
Jon Kriek · PHP Freaks
Sometimes the need to mess with their heads ...
... outweighs the millstone of humiliation.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users