Post reply

Efficiently Searching Database

  • June 6, 2012 at 1:08 am

    #262454

    This is my first time working with SQL and I'm trying to write a program that searches a SQL database many many times very quickly. The database I have made will not change and is 7 columns wide and 2.5 million rows deep (though I want to make one that is 200 million rows). Column 1 is a unique 10 digit number and 2-7 are 2 digit numbers. I believe I have column 1 indexed as PRIMARY, id, and id_2. I need to be able to search my current database a couple hundred million times in about 1 second, is this possible with SQL?

    I am using Java to write my program and I'm using PreparedStatement with query = "SELECT rank0, rank1, rank2, rank3, rank4, rank5 FROM ranks WHERE deckForm = ?". It currently takes about 90 seconds on my machine using xampp/phpMyAdmin/i5/6gbram to run one hundred million selects.

    Also, it took me about 17 hours to create the database using a program I wrote. Should there be a way to make it more efficient? It would take a very long time to create the 200 million row table. Is importing from CSV faster? I tried that with a small sample of my data and it said it timed out after 5 minutes.

    Thanks

  • June 6, 2012 at 3:06 am

    #1497480

    Thanks a lot for your reply.

    Here is what phpMyAdmin shows for Indexes:

    PRIMARYBTREEYesNodeckForm2602455A

    idBTREEYesNodeckForm2602455A

    id_2BTREENoNodeckForm2602455A

    deckFormBTREENoNodeckForm2602455A

    The Create Procedure sounds like it will help a lot. Every time I try it though I get error '#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@deckForm AS SET NOCOUNT ON SELECT rank0, rank1, rank2, rank3, rank4, rank5 FRO' at line 1'. I've tried every syntax change I can find online but nothing seems to work. I changed dbo to the correct db and I also tried going into the db and then SQL to enter it there.

    I will look into those other options for importing data, thanks.

  • June 6, 2012 at 3:27 am

    #1497491

    I've tried removing that and doing everything possible to it but I get the same error message every time

  • June 6, 2012 at 3:34 am

    #1497495

    This seemed to work

    delimiter //

    CREATE PROCEDURE pokerranks2.GetRanks(

    IN deckForm INT)

    BEGIN

    SELECT rank0, rank1, rank2, rank3, rank4, rank5 FROM ranks WHERE deckForm = deckForm;

    END;

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply