Announcement

Collapse
No announcement yet.

SQL SELECT

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL SELECT

    Hi,

    I have a grid with results from a data base containing more than 2,000,000 data sets.

    It looks like this: http://desenaviegas.dnsalias.com/DB/...E_EN/index.php

    The problem is, when loading all the data sets, it takes too much time, and so I would like to limit the results.
    I found a PHP expression that fits my needs:

    $sql = "SELECT * FROM myTABLE LIMIT 100 WHERE de LIKE '%".$_POST["search"]."%'";

    Now I have some problems when trying to directly implement the SQL query:

    1 - I am not sure, if ["search"] corresponds to the "quick search" field
    2 - I would like to short the results loading time and LIMIT the results to 100
    3 - And, of course, why load all the data sets, if I just want to present the results of the TEXT SEARCH QUERY?

    After clicking the SQL tab on the left side and inserting the adapted SQL query

    SELECT
    de,
    en,
    categ,
    clients
    FROM myTable LIMIT 100
    WHERE
    de
    LIKE
    '%".$_POST["search"]."%'

    I get an error message from ScriptCase when using this SQL code.
    Could you please help me on this?


    Thanks in advance!

    Alexandre
    Last edited by Alexandre; 08-25-2016, 07:45 AM.

  • #2
    first, May be the table do not have indexes or the indexes are not correctly specified, take a look of that. The grid application handles the pagination, you should indicate partial pagination and how many results.

    Comment


    • #3
      The table has no index. And as you can see in the above link, I already have done that: pagination and 10 data sets per page. It doesn't work. It will also take a lots of time to paginate and it will still load ALL the 2,000,000 data sets included in the data base. The ideal is to have an initial ZERO DATA SET LOAD when starting the GRID and to get ONLY the term(s) that were specified in the QUICK SEARCH field (left side above)...
      Last edited by Alexandre; 08-25-2016, 03:19 PM.

      Comment


      • #4
        Originally posted by Alexandre View Post
        The table has no index. And as you can see in the above link, I already have done that: pagination and 10 data sets per page. It doesn't work. It will also take a lots of time to paginate and it will still load ALL the 2,000,000 data sets included in the data base. The ideal is to have an initial ZERO DATA SET LOAD when starting the GRID and to get ONLY the term(s) that were specified in the QUICK SEARCH field (left side above)...

        Alexandre a large table like that needs an Index, please check the query execution plan before in the database, then you can determine what type of index is appropiate for the table, if not, query can go worst.

        Comment


        • #5
          Now that the table has an INDEX (ID), it is working even slower than before... : /

          Comment


          • #6
            The index must be set for fields of where clause too. Not only primary key.
            Last edited by alvagar; 08-26-2016, 09:22 AM.

            Comment


            • #7
              The problem is, MySQL cannot index TEXT fields... (?)

              And varchar allows only a maximum of 255 characters?


              I think I got it:
              CREATE INDEX de_index ON de_en_2016 (de(30));
              Last edited by Alexandre; 08-27-2016, 05:54 AM.

              Comment


              • #8
                Hi, Alexandre: this is not a specific Scriptcase question, but a SQL one. No matter what database you use, the LIKE operator is always a costly one.

                In MS-SQL you have full text indexes that can help you. But I don't know enough about mySql. I know an index can help you use LIKE operator in queries of the form

                yourfield LIKE 'abc%text%'

                or

                yourfield LIKE 'abc%'

                But you are using a query of the form '%text%'. Here the index will not help you. In MS-SQL, that kind of query without a full text index means a table scan, that is, a record-per-record comparison until it finds the number of records specified. I assume in mySql theres a similar scenario: the engine will compare record-by-record until 100 valid rows were found. If there's no 100 valid records in the table, then MySql needs to read EVERY record just to return a less-than-100-rows dataset.

                Test this way: change the limit to "1". As long as there's at least one record, the query will not test the entire table.

                Let me know the results!

                Comment


                • #9
                  Thanks tfertil and also to all the others.
                  The point is: The query is not working.

                  SELECT
                  de,
                  en,
                  categ,
                  clients
                  FROM myTable LIMIT 100
                  WHERE
                  de
                  LIKE
                  '%".$_POST["search"]."%'

                  The reason why I asked here, what should I put instead of ['%".$_POST["search"]."%'], because ScriptCase doesn't accept this expression as it is.

                  ["search"] refers to the QUICK SEARCH field.

                  Comment


                  • #10
                    Well in scriptcase that type of query the '%".$_POST["search"]."%' is not allowed, instead use a global variable like [likeparam] if you are using a grid and do not want to use the grid search capabilities.

                    you must set up the variable in another application or inside the same application, in the onInit Event, then set the where using the sc_select_where macro.

                    also you can start the application in search mode.

                    PD: clause LIMIT goes after WHERE clause.

                    Comment


                    • #11
                      This would be something like

                      EVENTS => onApplicationInit =>

                      sc_select_where(add) = "WHERE de = '%".$_POST["search"]."%' LIMIT 20";

                      (http://www.scriptcase.net/blog/dynam...e-of-the-grid/)

                      But it is not working... It loads first all the 2,000,000 segments and I get the error message "Undefined index: search".


                      And no, I prefer to use my own template and not the grid search capabilities (search mode)
                      Last edited by Alexandre; 08-26-2016, 05:54 PM.

                      Comment


                      • #12
                        Note that your error is produced because you have the $_POST["search"] variable, you'r trying to get the content from that variable, but it doesn't exists,

                        try putting in your url http://localhost:8080/someApp/someApp.php?search=123a the param

                        then try to get the content using $_GET['search'] in the initial event, try to see the request in the browser.

                        Saludos.

                        Comment


                        • #13
                          It didn't work but never mind, I will wait a bit more for a third party solution.
                          Besides, my PHP and MYSQL versions are not the newest and this surely affects the performance of all this action.
                          I'm considering to get the http://www.devside.net/ 's WampDeveloper Pro 5, once it easily allows one to be always up to date and install the newest versions of MySQL, PHP and PHPmyAdmin.

                          However, having at the moment more than 10,000,000 text data sets to insert into a similar data base, I'm affraid the main issue will be the query velocity.
                          Users won't definitely wait 2 or 3 minutes (at least) for a word search result...
                          Perhaps I should use a SQLITE data base instead?

                          I also still considering to "speed up" the system using PrimoCache (https://www.romexsoftware.com/en-us/primo-cache/ ) but I am not sure if this procedure is safe.
                          Last edited by Alexandre; 08-27-2016, 10:06 AM.

                          Comment


                          • #14
                            I took a look at the link for WampDeveloper you mentioned: http://www.devside.net/

                            Unfortunately they don't seem to have a user form, so one cannot get a sense of ow responsive they are, or what their users think of their product.

                            Have you heard anything about it?

                            Comment


                            • #15
                              http://www.devside.net/support

                              Comment

                              Working...
                              X