13 Replies - 2855 Views - Last Post: 03 May 2010 - 10:12 AM Rate Topic: -----

#1 hadi_php  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 10
  • View blog
  • Posts: 382
  • Joined: 23-August 08

use xml as database instead of mysql

Posted 29 April 2010 - 09:56 AM

Well, i got stuck with mysql. For my site load balance i need to use file system database to reduce pressure of mysql. So i need to make some portion into xml. But i got stuck again. I just need to know where to start for use a xml file as my db. Thanks in advance. :winkiss:
Is This A Good Question/Topic? 0
  • +

Replies To: use xml as database instead of mysql

#2 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1002
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: use xml as database instead of mysql

Posted 29 April 2010 - 10:39 AM

Look into the SimpleXML classes for the XML side.

But to be honest, I don't think it will reduce much load.. You'll just be offloading calls to PHP rather than MySQL, and since it's on the filesystem you'll put more load on that.

I'd suggest optimising the queries you are running on the database over changing the method.

This post has been edited by RudiVisser: 29 April 2010 - 03:22 PM

Was This Post Helpful? 0
  • +
  • -

#3 hadi_php  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 10
  • View blog
  • Posts: 382
  • Joined: 23-August 08

Re: use xml as database instead of mysql

Posted 01 May 2010 - 03:27 AM

yes, there are several quires like mysql join query with 3 or more table table. do you let me know that how to optimize them ... instead of join query...
Was This Post Helpful? 0
  • +
  • -

#4 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1002
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: use xml as database instead of mysql

Posted 01 May 2010 - 03:49 AM

Well if you post your queries I'm sure we can take a look at them!
Was This Post Helpful? 0
  • +
  • -

#5 hadi_php  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 10
  • View blog
  • Posts: 382
  • Joined: 23-August 08

Re: use xml as database instead of mysql

Posted 01 May 2010 - 04:02 AM

like

select a.brand_name,a.des,a.price,b.name,b.pub,c.c_name,c.result from one a, two b, three c where b.id=a.id=c.parent_id AND b.id=1 AND state=1 ORDER by b.ordering DESC LIMIT $st,$per_page 


i have this type of query twice time and also other single query...
Was This Post Helpful? 0
  • +
  • -

#6 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1002
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: use xml as database instead of mysql

Posted 01 May 2010 - 04:16 AM

You should try doing proper joins.. Such as:
SELECT
	a.brand_name,
	a.des,
	a.price,
	b.name,
	b.pub,
	c.c_name,
	c.result
FROM three c
WHERE b.id = 1
	AND state = 1
LEFT JOIN one a ON a.id = c.parent_id
LEFT JOIN two b ON b.id = c.parent_id
ORDER BY b.ordering DESC
LIMIT $st, $per_page


If that doesn't work try INNER JOIN, I suck with joins ;)
Was This Post Helpful? 0
  • +
  • -

#7 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: use xml as database instead of mysql

Posted 01 May 2010 - 06:45 AM

View PostRudiVisser, on 29 April 2010 - 11:39 AM, said:

But to be honest, I don't think it will reduce much load.. You'll just be offloading calls to PHP rather than MySQL, and since it's on the filesystem you'll put more load on that.

I second that. If you're having trouble scaling MySQL, switching to XML files is just trading one problem for another, probably much worse problem. Databases are designed to scale - XML files are not. Your time would be better spent on some basic database optimization.

Start by isolating the really slow queries - MySQL's slow query logging is very handy for this. Then, use the EXPLAIN statement to look at the execution plans for those queries. Make sure that they're actually using the correct indexes and check that they're not generating temporary tables on disk. After that, you should look into caching the results of your more frequent queries using something like Memcache or APC. The MySQL query cache might also be helpful, depending on your scenario. Effective caching can make the difference between a site that's running smoothly and one that's about to fall over.

View PostRudiVisser, on 01 May 2010 - 05:16 AM, said:

If that doesn't work try INNER JOIN, I suck with joins ;)

Since you mentioned it.... ;) The JOINs need to be part of the FROM clause. Putting them in the WHERE clause like you have is syntactically incorrect. Also, the comma-join in the original query is semantically equivalent to an inner join, not a left join. It should be more like this:
SELECT
	a.brand_name,
	a.des,
	a.price,
	b.name,
	b.pub,
	c.c_name,
	c.result
FROM three c
JOIN one a ON a.id = c.parent_id
JOIN two b ON b.id = c.parent_id
WHERE b.id = 1
	AND state = 1
ORDER BY b.ordering DESC
LIMIT $st, $per_page


However, you are right about using an explicit JOIN in the FROM clause rather than the comma-join. MySQL's query optimizer isn't very smart. I've seen a couple of cases where it doesn't correctly optimize a comma-join as a join, but rather builds the entire Cartesian product of the join tables and then filters on that. Not that that's necessarily happening here, but it's at least worth checking the query plan.
Was This Post Helpful? 2
  • +
  • -

#8 hadi_php  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 10
  • View blog
  • Posts: 382
  • Joined: 23-August 08

Re: use xml as database instead of mysql

Posted 02 May 2010 - 02:13 AM

yah, left join is much quicker and good than normal join....

So you are telling that do not go with xml. Continue with mysql with optimized query and use cache system?
Was This Post Helpful? 0
  • +
  • -

#9 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1002
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: use xml as database instead of mysql

Posted 02 May 2010 - 02:42 AM

@AdaHacker - Thanks for the explanation.. Multi-Table queries were always my weak point ;)

@hadi_php - Yes, don't go with XML at all. To be honest you probably don't even need a query cache system, but it will always help. In addition to optimising the queries you should also try to reduce the amount of queries you're running in the app.. That will speed it up too!

You could even store results of all queries (that you need data from multiple times) in a class/array and you'll be able to access them all the time.

Back in the day I used my own query function that would store queries and their results, then when I tried to run them again it would just return the results as it was a duplicate query.. If your web-app is pretty big, you could just use something like that :)
Was This Post Helpful? 0
  • +
  • -

#10 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: use xml as database instead of mysql

Posted 02 May 2010 - 06:59 AM

View PostRudiVisser, on 02 May 2010 - 03:42 AM, said:

You could even store results of all queries (that you need data from multiple times) in a class/array and you'll be able to access them all the time.

Honestly, I wouldn't even bother with something like that. If the problem is actually the load on the servers, and not just painfully bad queries, then an actual caching system is going to get you way more mileage for the same amount of effort. And if you expect traffic to grow, you will need it sooner or later anyway, so why not start now?

Just stop and think about it. Storing query results in arrays, etc. is nice, but how much does it reduce the database load? By a few queries per request? That's great if you're running the same query several times, but those arrays don't persist across requests - you still need to run them all at least once per page load.

Compare that to, say, APC, which will persist across requests. The nice thing about APC is that it's easy to install, requires no additional services or servers, and is ridiculously easy to implement in your code. You would write something like this:
# Retreive the query result from cache.
$result = apc_fetch('someKey');
# If the result is not in cache, run your query and store it.
if ($result === false) {
   # Run query here and fetch all your rows into a variable called $result
   # Store the data in cache with an expiration time of 5 minutes.
   apc_store('someKey', $result, 300)
}


Since APC stores data across requests, instead of running your query once per page load, you're running it once every five minutes. For any significant amount of traffic, that's a huge savings. Naturally, you will want to vary the cache TTL depending on your needs, but with enough traffic, even caching for a minute or two can save you a significant amount of DB load. And for some queries, you might get away with caching the results for a long time if the data doesn't get updated often.
Was This Post Helpful? 0
  • +
  • -

#11 Fratyr  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 139
  • Joined: 10-April 08

Re: use xml as database instead of mysql

Posted 02 May 2010 - 01:04 PM

Try redis database. key=value one.
Was This Post Helpful? 0
  • +
  • -

#12 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1002
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: use xml as database instead of mysql

Posted 02 May 2010 - 01:12 PM

View PostAdaHacker, on 02 May 2010 - 12:59 PM, said:

Honestly, I wouldn't even bother with something like that. If the problem is actually the load on the servers, and not just painfully bad queries, then an actual caching system is going to get you way more mileage for the same amount of effort. And if you expect traffic to grow, you will need it sooner or later anyway, so why not start now?

Of course - Different circumstances require different things. My issue was never with server load, more like just reducing the sheer query count. I wrote a tonne of SERIOUSLY BAD code back in the day, needed to optimise it. This is where the query "caching" system came in, it was running the same query in several points as it required the same data in a lot of places and wasn't reusing the results.. This was more of a preliminary fix ;)
Was This Post Helpful? 0
  • +
  • -

#13 hadi_php  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 10
  • View blog
  • Posts: 382
  • Joined: 23-August 08

Re: use xml as database instead of mysql

Posted 03 May 2010 - 12:08 AM

have you got any tutorial about APC , memcache...? so that i can choose which one easier for me :helpsmilie:
Was This Post Helpful? 0
  • +
  • -

#14 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: use xml as database instead of mysql

Posted 03 May 2010 - 10:12 AM

View Posthadi_php, on 03 May 2010 - 01:08 AM, said:

have you got any tutorial about APC , memcache...? so that i can choose which one easier for me :helpsmilie:

No, I don't. The APIs are really simple, though. You can probably get a good idea just from the documentation. Each one only has about 5 functions that are directly relevant to caching data.

However, I can tell you right now that APC is definitely much easier in terms of setup. Memcache is a distributed caching system, so it requires a separate service and can pool across multiple servers. This can improve cache efficiency for larger server pools (you only have to cache data once across the server farm, rather than once per server), but it also introduces network latency. APC, on the other hand, is all on the local machine and doesn't require anything special in terms of configuration. You pretty much just install the extension, restart the web server, and you're good to go. (Of course, you may want to tweak the settings a but, but it's not required.) If the extension is packaged by your OS distributor, you could probably get it up and running on a test system within 5 minutes and start experimenting.

If you're not sure what caching system is most appropriate, you could write yourself a custom caching class to wrap the calls. That way, you could switch between APC/Memcache/whatever on the back end without having to change your application code.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1