Member Avatar for DarkMonarch

Hey, been awhile.

I'm working on a portal for distributors, some type of JIT (just in time) implementation.

Now i've been reading alot on DB so i can implement the right database and the right data structure.
Now i see, i can do alot more then i'm use to do.

My question:

Is it better to pre-organized the data in the DB engin (i'm using MySQL) with more complex queries or just throw a simple query and manage the data in the PHP engin ?

i'm lookin for a best performance with the possibility of high traffic intranet.

i need your input on MySQL engin vs PHP engin, taking out all other variables.

thx

  • 3 Contributors
  • 3 Replies
  • 151 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by madCoder
Member Avatar for diafol

Depends on what you need to do with the data. IMO - I'm no expert, DBs are good for extracting data quickly - but if they're tasked with complicated transformations, they slow considerably. Also running queries within loops can accrue significant overhead. Without an idea of what you're trying to achieve (example code) it's difficult to say. If your SQL tables are normalised then the data should be able to be retrieved easily, using JOINS. Subquerying should be a 'last resort' if you can't use a JOIN. Using LIMIT to restrict the number of records retrieved will speed things up significantly if you have loads of them. MySQL is on its last legs - look to move to mysqli or PDO. Don't know if that helps. Somebody correct me if I'm wrong.

Member Avatar for DarkMonarch

Nah! i don't have any code yet, i'm still at the designing part.

yeah i'll be using mysqli, but i was refering to MySQL engin

Simple exemple:

a query that ORDER BY something ASC
versus
a quicksort or bubble sort

or do a complex query to pin point a specific data
versus
a dicotomic search

yes i understand some manipulations can only be done in code, but i'm refering to data manipulations that can be parse in ethier the code engin or the db engin.

So which one i pick for performance?

i know howto calculate the order of an algorythm theoretically, but i don't how to put an order on a db query. i'll do a search on that.

thx for the input

Member Avatar for madCoder

I have found that MySQL is very effective at doing ORDER BY and the like IF proper indexes are set up. Indexing makes a HUGE a difference and is often overlooked. The sql EXPLAIN command is very helpful for evaluating how efficiently a query is being carried out. I admit I don't have any specific time trials to prove this.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.