SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Which is faster for a search - a table or a view?

    Hi,

    I have a VIEW in mysql that is created from 4 different unrelated tables with a UNION

    there is over 25000 records in the view. It was created solely for the purpose of doing a search. There are 4 columns and 2 are the main ones for the searching.

    It is rather slow, so I was thinking of converting the view to a table as the view will never change - it is in an old database that serves the searches of archived records only.

    Will converting the VIEW to a TABLE speed up the search?

    If so, how do I do the conversion?

    Thank you for your time.

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,095
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    You only when you try

    No, in all seriousness it should be faster because then the DBMS is able to index properly (you do have indexes, right?)

    Off the top of my head INSERT INTO newTable SELECT * FROM myView should do the trick of creating a table from the view
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Off the top of my head INSERT INTO newTable SELECT * FROM myView should do the trick of creating a table from the view
    Your head is better than any page I found when trying to test this - they all were giving the same syntax and it was wrong so I could not even try!

    However, because you gave me this I can now try as you suggested and report back that searching a large table is faster than searching a VIEW

    Thank you soo much for your help - it is appreciated

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,095
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    You're welcome How about those indexes?
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Member
    Join Date
    Apr 2012
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have 4 cols in the table and 2 have numbers (ID's) so I put the indexes on them - the other 2 cols are text and long text - can an index go on those also? I think that index is just for numbers but correct me if I am wrong

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,095
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    You can index text and long text in FULLTEXT indexes if you like, but you should only index them if you also search them. If you don't, then don't bother
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •