Main Stories
Slash Boxes

Slash Open Source Project

Slashcode Log In

Log In

[ Create a new account ]

Article Poll

Poll I found this article to be
Very Helpful
Helpful
Not Helpful
Not Very Helpful
[ Results | Polls ]
Comments:0 | Votes:0

SQL Speed Issues

posted by Krow on 01:16 PM February 5th, 2001   Printer-friendly   Email story
Anonymous Coward writes "Why is it that slashdot can do a sql query like: "select * from db where message LIKE "%keywords%" in a few milliseconds under that enormous load when on my 550mhz machine with 128 megs of ram under no load takes 30 seconds to search 100k records?"
Ram would be my guess.
This discussion has been archived. No new comments can be posted.
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login
Loading... please wait.
  • by tf23 (175) <tf23NO@SPAMlottadot.com> on Tuesday February 06 2001, @07:40AM (#20) Homepage Journal
    Ok, goto The Slashcode CVS Repository [slashcode.com] page.
    Then create a term/ssh/telnet into your box. Paste the following line into the term window: cvs -d:pserver:anonymous@cvs.slashcode.com:/cvsroot/sl ashcode login hit enter Paste the following line into the term window: cvs -d:pserver:anonymous@cvs.slashcode.com:/cvsroot/sl ashcode co -r bender slash sit back and watch as it downloads everything you need. re-do the same process to update it once a week or whenever.
    --
    lottadot [lottadot.com]
  • okay, maybe I'm dumb, but I can't figure out how to "get the latest commits" as pudge suggests. CVS and I don't get along. Do I just need to get a new copy of install-slashsite or do I need the whole release? Either way, how the hell do I get ahold of whatever it is I need? ;)
  • What does top show? Do you have anything in slash that's become a runaway? That's where I'd start, 128M o' ram should be fine for tables that small. As a side question for you Perl types, what the hell can i do about this:

    htdocs/article.pl:12:undef error - Can't call method "length" without a package or object reference at /usr/local/lib/perl5/site_perl/5.005/Template/Stas h.pm line 455.

    article.pl line 12 is slashDisplay( 'display' , {etc.... this is the error from clicking on "Read More..."
    I've g
  • You're talking about bender there. I found (er, got hit by) that bug a few weeks ago. It is (and has been as of late last week) fixed in the CVS tree.

    Sourceforge Bug # 129902 [sourceforge.net]

    --
    lottadot [lottadot.com]
  • You are unequivecably the man. Not the man in that "governmental-oppresive" sort of way, but in that "james-dean-i'm-the-shit" sort of way. Thanks.
  • Although john is close..thats not exactly what the problem is. When the first charter of a search query is a wild card it can not uses indexes. Thus the database just becomes a flat file. There are a few ways to fix this. If you have a lot of RAM you can just do "alter table table_name type=heap" to put in ram. Now this is 99.9% of the time a *VERY BAD IDEA* because heap tables are 100% in mem and if mysql crashes you loose are your data. (not really an option IMHO). But the 2nd is to create a keywords database. One that has a keywords that point to a page in the mysql news table. I have had to do this already on a system I work on because the size of our database is just huge (30 million rows). But with indexs our 650 mhz machine is able to cut through it within a tenth of a second.
    Good luck
    -Gavin
  • The reason why that query is slow is because of the usage of LIKE. Whenever you use LIKE, you cause the database to table scan. The peformance curve of the LIKE is exponential. Therefore, the problem will not appear on small data sets. If you must use LIKE, put an index on the column being searched. The best approach is to remodel the tables in order to remove the need for a LIKE. If the column is searched on often, remodeling the tables is usually the suggested solution. I would be happy to lend a h
  • Wipe all the files you've downloaded.

    So, if you are keeping slash in /home/myname/slash and you are installing slash to /usr/local/slash (the default) then you would:

    su to root
    /usr/local/apache/bin/apachectl stop
    /etc/rc.d/init.d/slashd stop
    rm -fR /home/myname/slash
    rm -fR /usr/local/slash
    rm -f /etc/rc.d/init.d/slashd
    cd /home/myname
    mkdir slash
    cd slash
    {do cvs, so it grabs the files}
    make && make test && make install
    /usr/local/slash/bin/install-slashsite {command options}
    /etc/rc.d
    --
    lottadot [lottadot.com]
  • I forgot to mention - always download from CVS being logged into your system as the same user. I wasn't paying attention once, and did it w/ a user who didn't have write perms to all the files.

    After cvs flew through the update, I just went along and did the install et all. The problem was that it never overwrote any of the files. So, while I thought I was running for the latest CVS commits, I actually was not. And it was damned frustrating until I realized what I'd done.

    --
    lottadot [lottadot.com]
  • Okay, so I did all that and it didn't fix the problem, so I re-ran install-slashsite, then shook in horror and rage after reloading the page and seeing everything had been rewritten to defaults. After stopping cursing, (much to the amusement of my co-workers), I just blew everything away, reinstalled the whole kit and kaboodle (dropped the table, readded it, reinstalled bender from scratch, and then, before running install-slashsite, ran the cvs update, then after that completed, ran install-slash) so a co