Optimizing SQL queries - any tip to share?
Hello again, dear LET community,
I'm currently writing a low-end-backend (:>) for my private homepage, as that seemed easier than getting a Drupal configured for my needs for now. Also, it will be resource-efficient, as it will be tailored exactly for the site.
Anyway, I also want to share some unnecessary stories with the whole world, so I'm implementing a blog. Now I have been thinking on how to optimize the SQL querys for that, because as soon as the blog posts begin to pile up, there might be some delay.
I want the posts to be paginated in groups of 5, and because of that I thought I use the AUTO_INCREMENT and UNIQUE ID's and keep track of the total count in a separate table. When I then want the latest 5, I get the total value from the table and query the DB to get me the Posts with ID, ID-1, ID-2 and so on (almost the same with pagination, just ID-5-1 etc.). But, and that's the catch, it might happen that I have to delete a blog post. And in that case, a special ID will miss and break the system.
So, what should I do? Should I use a script to shift all ID's above the ID one down (seems like terrible hacking to me) or query every time for every blog post (seems like a waste of resources)?
Thanks in advance ^-^
select * from table order by id limit 5;
assign last/max id to link/button/whatever
select * from table where id > last_id order by id limit 5;
using seperate table is pointless.
you can add additional conditions to where clausule
That's a really bad way of doing it. How about selecting the nth row?
Aww, neat, I wouldn't have thought of something like that.
I guess my SQL is a bit rusty... ^^"
This thread is pretty much done then, I guess.
Using InnoDB as your default engine does almost wonders. Using MariaDB as your database server also helps.
actually, avoid using SELECT * FROM since it will be slower, first there will be a query to retrieve how many columns there are and their names.
Only SELECT the columns you actually need.
Also, depending on the size of your tables, sometimes smaller queries are overall quicker then one single large query.
some more pointers:
InnoDB helps with concurrency and locking for updates, not for read speed.
I looked at MariaDB vs. MySQL stats once but I didn't see anything compelling. That was a while back, though so maybe it's changed. If anything, though, I expect MySQL to be a lot faster because there are a lot of smart engineers working on it full-time, as opposed to "we're cheesed off about licenses!" people who are probably less organized.
The MySQL/MariaDB thing is mainly because Oracle is steadily working on neutering MySQL. Remember that MySQL was originally created by the "we're cheesed off about licenses" people that you disdain, and the original creators were very uncomfortable with the idea of Oracle running MySQL. I'm going to try about Maria on my next project, so we'll see how it goes.
@raindog308 Actually I absolutely agree, but MariaDB comes as a routine recomendation these days for small VPS servers because it has a slight edge on some features and performance (notice that I said "slight").
I recommended InnoDB because it is less of a hassle to configure, provides slightly better performance than MyISAM and most importantly: it's safer - so data loss is less likely to happen because of server crash, outage, etc.
Bottom line: MariaDB is "trending" right now, so someone will always recommend it.
So you recommend MariaDB for the slight performance edge for LEBs, and then run InnoDB, which is nigh-universally disabled on LEBs. :P
@Soylent MyISAM is a horrible outdated engine that works with low amounts of RAM but the performance is absolutely horrific, not to mention that if you deploy Drupal for instance with MyISAM you will wish at some point that either you hadn't discovered Drupal at all or that you where never born. Just talking from personal experience.
Also I am curios what will happen with MariaDB if (and this is highly unlikely) Oracle will decide not to release public MySQL updates anymore, since MariaDB is basically based on MySQL from tip to tail.
This is completely untrue.
I attended nearly all the MySQL sessions at last year's OpenWorld (Oracle's conference). Neutering MySQL is not on Oracle's agenda - indeed, they outlined a lot of plans to continue to grow the product.
Looks to me like they've been adding features:
There really is very overlap between the MySQL market and the Oracle DB market, so Oracle has very little motivation to eliminate MySQL or neuter it.
My point about MariaDB is that they have no vision other than "we have a different license". Great, but unless FOSS purity is really important to you, there is little compelling reason to use MariaDB.
The developers will keep going with it, just like they are now. It's a GPLv2 fork of MySQL, so it's not like it suddenly disappears when Oracle inevitably does something stupid with MySQL.
InnoDB has benefits, but they come at a huge expense in terms of resources. All I was saying is that it's funny to recommend Maria for some very slight edge in performance, but then recommend using a DB engine that needs a whole heap of RAM. For many applications, MyISAM is Good Enough™.
@raindog308 IMHO the money is in providing support for MySQL and services to the enterprise market. At the end of the day if you look closely the developers behind MariaDB are going after the same thing. If you're a large Fortune 500 company you will do business with Oracle rather than with the "people that are developing database software in a shed behind the warehouse" if you catch my drift.